Hoje inicia as atividades do Blog da PerformanceDB.
Aqui será postada dicas, informações e técnicas gerais sobre MySQL.

Fiquem de olho, comentem, compartilhem, enviem ideias, ajudem sempre a manter os assuntos do seu interesse sendo postados.

É comum ver administradores desativando o SELINUX para possibilitar alterações nas configurações do MySQL, tais como a mudança do diretório de armazenamento de dados o “datadir”, porém essa não é uma prática muito segura, em alguns casos de corporações que passam por processos de auditoria ou de ambientes que exigem altos padrões de segurança, o correto é configurar o SELINUX adequadamente, alterando a parametrização. O erro mais comum encontrado é o seguinte:

130321 11:50:51 mysqld_safe Starting mysqld daemon with databases from /datadir
...
2013-03-21 11:50:52 2119 [Warning] Can't create test file /datadir/boxy.lower-test
2013-03-21 11:50:52 2119 [Warning] Can't create test file /datadir/boxy.lower-test
2013-03-21 11:50:52 2119 [ERROR] /usr/sbin/mysqld: Can't create/write to file
'/datadir/boxy.pid' (Errcode: 13 - Permission denied)
2013-03-21 11:50:52 2119 [ERROR] Can't start server: can't …

Se o seu banco de dados passa por constantes operações de “DELETE” ou “UPDATE” ele provavelmente ficará fragmentado, fazendo com que os índices já não sejam tão eficientes como antes e os datafiles ocupem mais espaço do que o necessário em disco. Seria, algo similar a fragmentação de filesystem.

Através do catalogo do MySQL é possível identificar quanto espaço livre existe no datafile e assim identificar uma margem ou porcentagem de fragmentação por objeto. Até a versão 5.1.21 essa margem era descrita através da coluna “TABLE_COMMENT” da tabela “INFORMATION_SCHEMA.TABLES”, sendo assim bastava executar a seguinte consulta:

SELECT table_schema,
table_name,
table_comment
FROM information_schema.tables
WHERE engine LIKE 'InnoDB'
AND table_comment RLIKE 'InnoDB free: ([0-9]{6,}).*';

Da versão 5.1.28 em diante isso foi corrigido, sendo possível avaliar a …

Foi disponibilizada uma nova versão Beta do MySQL Workbench, essa versão conta principalmente com recursos desenvolvidos para o MySQL 5.7, porém ainda existem novidades para a versão 5.6.

Os recursos que mais me chamaram a atenção e me interessam a principio, são as melhorias no Explain Visual, o que auxilia bastante na exibição gráfico dos planos de execução, principalmente quando existe a necessidade do DBA explicar ao Desenvolvedor como melhorar as consultas. Além disso as novas integrações com o MySQL Fabric parecem muito promissoras.

O “Processlist” ou “Administration – Client Connections”, ficou muito melhor, agora eles utilizam o mesmo conceito já usado no SQL Server e Oracle, exibindo o nome do programa, e traz mais informações a respeito da sessão do usuário, exibindo inclusive as “THREADS” da sessão:

…

Com o constante uso do banco de dados, operações como “delete” e “update”  acabam causando a fragmentação dos objetos nos datafiles, e isso muitas vezes prejudica a performance do banco de dados e aloca espaço desnecessariamente. Com isso torna-se necessário implementar uma rotina de manutenção capaz de otimizar o ambiente, evitando o uso de espaço desnecessário e desfragmentando os índices da tabela para uma melhor performance.

Geralmente isso é feito manualmente ou através de shell scripts, mas preferi criar um “PROCEDURE” e implementar uma rotina via “SCHEDULE” próprio MySQL, segue:

#
use mysql;
#
# TABELA DE LOG PARA MONITORAR O PROCESSO
DROP TABLE IF EXISTS mysql.mantained_table_history;
CREATE TABLE mysql.mantained_table_history(
mantained_table_schema  VARCHAR(255),
mantained_table_name VARCHAR(255),
mantained_type  VARCHAR(255),
mantained_status VARCHAR(10),
mantained_date …

É possível analisar o tamanho dos objetos do banco de dados através do catalogo do Innodb, mas para que essas informações sejam precisas, as estatisticas devem estar atualizadas!

Veja como atualizar as estatisticas no post:

http://mathiasbrem.com.br/update-statistics-mysql-innodb/

http://mathiasbrem.com.br/rotina-analyze-automatizada-via-schedule-mysql/

A consulta a seguir ajuda a avaliar o tamanho dos objetos do banco de dados:

select
INNODB_SYS_TABLESTATS.NAME,
INNODB_SYS_TABLESTATS.NUM_ROWS,
concat(round(((INNODB_SYS_TABLESTATS.CLUST_INDEX_SIZE+OTHER_INDEX_SIZE) * round(INNODB_SYS_TABLESPACES.PAGE_SIZE/1024,0)) / 1024 / 1024, 2),'G') AS TOTAL_SIZE, …

Essa rotina foi desenvolvida com o intuito de atualizar as estatisticas de todos os databases da instância automaticamente, de forma dinâmica.

#
use mysql;
#
# TABELA DE LOG PARA MONITORAR O PROCESSO
DROP TABLE IF EXISTS mysql.mantained_table_history;
CREATE TABLE mysql.mantained_table_history(
TABLE_SCHEMA  VARCHAR(255),
TABLE_NAME VARCHAR(255),
MANTEINED_TYPE  VARCHAR(255),
STATUS VARCHAR(10),
DATE datetime,
STARTIME datetime,
ENDTIME datetime
)engine=MyISAM;
#
#
#ROTINA DE ANALYZE COM LOG + MONITORAMENTO
#
use mysql;
#
# PROCEDURE QUE REALIZA O ANALYZE
#
DELIMITER $$
DROP PROCEDURE IF EXISTS RotinaAnalyze $$
CREATE PROCEDURE RotinaAnalyze()
BEGIN
#
DECLARE done INT DEFAULT FALSE;
DECLARE query_analyze varchar(255);
DECLARE ANALYZE_TABLE_SCHEMA varchar(255);
DECLARE ANALYZE_TABLE_NAME varchar(255);
DECLARE date_analyze datetime;
DECLARE startime_analyze datetime;
DECLARE endtime_analyze datetime;
DECLARE analyze_tables CURSOR FOR select TABLE_SCHEMA, …

Original post - http://anothermysqldba.blogspot.com/2014/08/mysql-foreign-keys-example-error-1452.html

 Então, eu encontrei uma situação hoje lidar com a necessidade de atualizar um campo, mas o usuário não foi capaz de fazê-lo por causa das restrições de chave estrangeira relacionados. 

Este blog com ser um exemplo simples que mostra uma chave estrangeira e como atualizá-los se você tiver que fazê-lo. 

Primeiro vamos criar uma tabela simples e preenchê-lo com dados aleatórios. 

CREATE TABLE `table_w_code` ( 
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
`NameofCode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
PRIMARY KEY (`SOMECode`) 
) ENGINE=InnoDB ;  …

¿Qué pasa?

Simples! Atualização de estatistas do MySQL

Os bancos de dados utilizam estatísticas para definir seus planos de acesso aos dados e assim executar o caminho mais eficiente na busca pelos dados solicitados por determinada consulta. Para isso o banco de dados deve manter suas estatísticas sempre atualizadas. Por padrão o InnoDB já realiza essa atualização automaticamente, evitando que haja qualquer tipo de problema de performance por falta de informações de acesso.

Essa atualização forçada ou automatizada é controlada pela variável:

innodb_stats_persistent

Mais informações a respeito dessa configuração estão disponiveis em:

http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html

Ok, …

Como verificar a existência de locks no ambiente ?

SELECT
*
FROM
information_schema.processlist
WHERE
state IN ('Waiting for table flush' , 'Locked', 'Table lock')
OR state REGEXP 'Waiting for .* lock'
ORDER BY time DESC

E como identificar as consultas ou sessões que estão realizando o lock?

SELECT
TRX.TRX_MYSQL_THREAD_ID,
TRX.TRX_ISOLATION_LEVEL,
TRX.TRX_STARTED,
TRX.TRX_STATE,
PROCESSLIST.USER,
PROCESSLIST.HOST,
PROCESSLIST.DB,
PROCESSLIST.COMMAND,
PROCESSLIST.TIME,
PROCESSLIST.STATE,
LOCK_WAITS.REQUESTING_TRX_ID,
LOCK_WAITS.BLOCKING_TRX_ID,
LOCK_WAITS.BLOCKING_LOCK_ID
FROM
INFORMATION_SCHEMA.INNODB_LOCKS LOCKS,
INFORMATION_SCHEMA.INNODB_TRX TRX,
INFORMATION_SCHEMA.PROCESSLIST PROCESSLIST,
INFORMATION_SCHEMA.INNODB_LOCK_WAITS LOCK_WAITS
WHERE
LOCKS.LOCK_TRX_ID = TRX.TRX_ID
AND TRX.TRX_MYSQL_THREAD_ID = PROCESSLIST.ID;