Fazendo um trabalho recentemente com o MySQL na versão 5.5.13, esta que ainda não conta com a variável default_tmp_storage_engine, precisei realizar alguns testes que resultou nesse artigo. Na verdade, o que quero compartilhar com você é exatamente como fazer um ajuste fino relacionado à criação de tabelas temporárias pelo MySQL. Não estou me referindo aqui àquelas tabelas que são criadas com um comando CREATE TEMPORARY TABLE, mas sim, àquelas que são criadas pelo MySQL em tempo de execução para resolver consultas UNION, GROUP BY, DISTINCT e até mesmo comandos simples como DESCRIBE (ou DESC) e SHOW COLUMNS, utilizados para exibir o modelo conceitual da tabela e exibir as colunas que fazem parte de uma determinada tabela, respectivamente.
Muitas consultas sofrem com problemas de performance por serem organizadas em tabelas internamente criadas pelo próprio MySQL na memória e até em disco, quando o MySQL criará uma tabela MyISAM para organizar, classificar e retornar, por exemplo, um número X de registros de acordo com uma cláusula LIMIT (quando você utiliza LIMIT, sua consulta acabará lendo todos os dados da tabela e retornando um número X de linhas).
Os problemas com tabelas temporárias podem ser:
- Falta de planejamento para a escrita de consultas, sendo os resultados de tais consultas organizados em uma parte da memória, o que pode gerar uma grande disputa por recursos em uma máquina;
- Se uma tabela temporária criada pelo MySQL for mais que tmp_table_size, a performance poderá ser mais penalizada ainda, já que uma tabela MyISAM (até a versões 5.5) será criada em disco para que os dados possam ser copiados para a mesma, trabalhados (classificações, ordenações e imposição de condições WHERE e LIMIT) e depois ser removido;
- Caso o diretório temporário rode sobre um sistema de arquivos como ext3/ext4, a performance poderá ainda mais degradada, pois, tais sistemas de arquivos não apresentam benefícios em relação à performance – o melhor aqui será citado à frente!
Existem dois tipos de tabelas temporárias que são criadas pelo MySQL:
- Tabelas temporárias em memória: são aquelas criadas internamente pelo MySQL, com espaço em memória controlado pelas variáveis tmp_table_size/max_heap_table_size, para resolver uma consulta que conte com as seguintes cláusulas:
- UNION, ORDER BY, GROUP BY, DISTINCT;
- Que utilize o SQL Hint SQL_SMALL_RESULT
- Tabelas temporárias em disco: são aquelas criadas em disco por exceder o espaço em memória configurado nas variáveis tmp_table_size/max_heap_table_size e/ou nas seguinte situações:
- Caso a consulta tenha UNION, ORDER BY, GROUP BY, DISTINCT e a quantidade de dados da tabela temporária em memória exceda o valor de tmp_table_size/max_heap_table_size;
- Quando as tabelas envolvida na consulta contar com colunas cujo tipo de dados seja BLOB/TEXT;
- Um simples SHOW COLUMNS ou DESCRIBE;
As variáveis de ambiente ou sistema tmp_table_size e max_heap_table_size podem ser consultadas como mostro abaixo:
mysql> SELECT VARIABLE_NAME,CONCAT((VARIABLE_VALUE/1024/1024),' MB') AS VARIABLE_NAME
-> FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-> WHERE VARIABLE_NAME IN ('tmp_table_size','max_heap_table_size')\G *************************** 1. row ***************************
VARIABLE_NAME: MAX_HEAP_TABLE_SIZE
VARIABLE_NAME: 16 MB
*************************** 2. row ***************************
VARIABLE_NAME: TMP_TABLE_SIZE
VARIABLE_NAME: 16 MB
2 rows in set (0.03 sec)
Tais tabelas são geradas e em muitas delas o administrador de sistemas ou de bancos de dados nem percebe quando tal fenômeno ocorre, pois, o próprio engine do MySQL resolve tal criação, utilização e remoção de tais tabelas. Em muitos dos testes que tenho realizado, o processo de remoção das tabelas tem se mostrado o mais caro para todo o sistema computacional, uma vez que a tabela a ser removida, conta com todos os dados que foram trabalhados e enviados para o cliente que requisitou tais informação. Uma das forma de monitoramento da criação de tabelas temporárias pelo MySQL é utilizando o PERFORMANCE_SCHEMA, que na versão 5.7 já apresenta grandes evoluções e nenhum problema com overhead (na versão 5.5 ele gera um overhead adicional e penaliza por vezes a execução de consultas vindas do ambiente de produção). Outras forma e ainda mais utilizada é a consulta direta de variáveis conhecidas como variáveis de status, estas que são contadores de ocorrências de determinados aspectos no MySQL.
Monitorando tabelas temporárias com variáveis de status
Para quem ainda não utilizou as variáveis de status do MySQL para controle e monitoramento de ações executadas pelo servidor de bancos de dados, saiba que existe uma grande oportunidade de visualizar e resolver muitos problemas de performance através das mesmas, pois, estas são agrupadas por algum aspecto relacionado ou funcionamento do servidor de bancos de dados MySQL (threads em cache/created, quantidade de consultas, objetos e dados em cache, etc). Como neste artigo estamos falando sobre tabelas temporárias, daremos foco aqui no agrupamento que mostra como as tabelas temporárias estão sendo criadas atualmente em um servidor de bancos de dados MySQL.
mysql> SELECT VARIABLE_NAME,VARIABLE_VALUE -> FROM INFORMATION_SCHEMA.GLOBAL_STATUS -> WHERE VARIABLE_NAME LIKE 'Created_%tables'\G *************************** 1. row *************************** VARIABLE_NAME: CREATED_TMP_DISK_TABLES VARIABLE_VALUE: 0 *************************** 2. row *************************** VARIABLE_NAME: CREATED_TMP_TABLES VARIABLE_VALUE: 3 2 rows in set (0.00 sec)
No SQL exibido acima, fiz a extração das variáveis com status de controle de criação dos tipos de tabelas temporárias solicitadas pelo MySQL ao resolver internamente as consultas enviadas pelos usuários. Com base nos tipos de declarações que podem gerar tabelas temporárias, como já dito, estas podem ser criadas em memória ou em disco. Existe ainda aquela tabela temporária que é criada em memória e depois precisa ser transferida para o disco, pois, a quantidade de dados que a mesma precisa armazenar para resolver uma consulta é maior que o espaço configurado na variável de ambiente tmp_table_size. Quando uma tabela temporária é criada em disco, depois de extrapolar a configuração de tmp_table_size/max_heap_table_size, esta tabela será controlada pelo engine MyISAM. Isso é ruim, pois gera uma dependência de outras variáveis e dependendo do tipo de operação que será realizada com os dados, o bloqueio por tabela (característica do engine) passa ser um fator restritivo. Além disso, caso a utilização de determinadas declarações como UNION, UNION ALL e DISTINCT não forem pensadas, você precisará pensar em uma forma de otimizar o sistema de arquivos do diretório temporário do MySQL, pois tais declarações requerem a criação de tabelas temporárias sobre o disco.
Alguns fatos aqui:
- Quando o MySQL cria uma nova tabela temporária em memória, a variável de status CREATED_TMP_TABLES é incrementada;
- Quando o MySQL precisa levar uma tabela temporária da memória para o disco ou ainda, criar uma nova tabela temporária em disco, a variável de status CREATED_TMP_DISK_TABLES é incrementada.
Tendo dito isso, fica fácil então monitorarmos o percentual de tabela temporárias que são criadas em disco – o que realmente é bem caro, em termos de recursos computacionais, para o MySQL. A performance é dramaticamente afetada se o MySQL está mal configurado neste ponto, já que a maioria ainda utiliza discos rígidos regulares e sistema de arquivo NTSF/EXT3/EXT4.
mysql> SELECT @TMP:=VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_tables'; SELECT @DISK:=VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_disk_tables'; SELECT CONCAT(ROUND((@DISK/(@DISK+@TMP))*100), '%') 'PERCENTUAL DE TABELA TMP CRIADAS EM DISK'; +----------------------+ | @TMP:=VARIABLE_VALUE | +----------------------+ | 10025 | +----------------------+ 1 row in set (0.00 sec) +-----------------------+ | @DISK:=VARIABLE_VALUE | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) +------------------------------------------+ | PERCENTUAL DE TABELA TMP CRIADAS EM DISK | +------------------------------------------+ | 0% | +------------------------------------------+ 1 row in set (0.00 sec)
Bom, o que precisamos agora é de carga no MySQL para que possamos movimentar mais estes números. A quantida que é vista em @TMP é justamente a quantidade de tabelas temporárias que o MySQL criou em memória para executar a consulta em tabelas do INFORMATION_SCHEMA. Como o @DISK, ou o CREATED_TMP_DISK_TABLES, é igual a 0, precisamos agitar, shake the MySQL!! É o que faremos com a suite de benchmark nativa do MySQL, o mysqlslap. O comando + uma consulta de peso considerável é a seguinte (não! Não rode isso em ambiente de produção!!!):
Primeiro de tudo, acesse o MySQL e crie os seguintes objetos através do mysql client ou a interface gráfica que você mais gosta:
use test
/* perceba a coluna killer do tipo blob */
create table test.t1(id int primary key, name blob, `date` timestamp default current_timestamp()) engine=innodb;
delimiter //
create procedure test.t1(num int)
begin
/* vamos inserir uma qtd num na tabela */
set @var = 0;
while @var <= num do
insert into test.t1 set id=@var, name=md5(@var), date=null;
set @var = @var +1;
end while;
end;
//
delimiter ;
/* execute a procedure para inserir um num x de linhas na tabela */
call test.t1(100000);
- Se estiver rodando MS Windows:
mysqlslap --user=root --password='' --number-of-queries=10000 --concurrency=10 --query='SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10'
- Se estiver rodando Linux (mais divertido):
nohup for wb in {1..100}; do mysqlslap --user=root --password='' --number-of-queries=10000 --concurrency=10 --query='SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10 UNION SELECT COUNT(DISTINCT ID) AS X, NAME FROM test.t1 GROUP BY 2 LIMIT 10'; done &
Vamos lá! O que tem de especial nas consultas acima? COUNT, DISTICT, UNION e GROUP BY. Killer! Interessante executar a consulta em background e correr para o diretório temporário para verificar os arquivos de tabelas temporárias criadas e sentir o drama:
[root@wbsrv101 tmp]# ls -lha total 112K -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_0.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_0.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_13.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_13.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_15.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_15.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_18.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_18.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_2.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_2.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_29.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_29.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_3.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_3.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_34.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_34.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_35.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_35.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_36.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_36.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_37.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_37.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_38.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_38.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_4.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_4.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_40.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_40.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_41.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_41.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_42.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_42.MYI -rw-rw---- 1 mysql mysql 0 Apr 16 12:00 #sql_aad_43.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_43.MYI -rw-rw---- 1 mysql mysql 520 Apr 16 12:00 #sql_aad_5.MYD -rw-rw---- 1 mysql mysql 1.0K Apr 16 12:00 #sql_aad_5.MYI drwxr-xr-x 2 mysql mysql 4.0K Apr 16 12:00 . drwxr-xr-x 7 mysql mysql 4.0K Apr 16 11:43 .. # número de arquivos que correspondem às # tabelas temporárias criadas em disco [root@wbsrv101 tmp]# ls -lha | wc -l 39
Após este movimento, podemos ver que as tabelas temporárias criadas em disco são realmente MyISAM, já que apresentam os arquivos MYI (aqruivos de índice) e MYD (arquivo de dados), são prefixadas com uma tralha ‘#’ e que os números das variáveis de status que registram a criação das tabelas temporárias agora são outros. Repetimos a mesma consulta para pegar o percentual de tabelas criadas em disco, que é o nosso problema de performance aqui:
mysql> SELECT @TMP:=VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_tables'; SELECT @DISK:=VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_disk_tables'; SELECT CONCAT(ROUND((@DISK/(@DISK+@TMP))*100), '%') 'PERCENTUAL DE TABELA TMP CRIADAS EM DISK'; +----------------------+ | @TMP:=VARIABLE_VALUE | +----------------------+ | 1054451 | +----------------------+ 1 row in set (0.00 sec) +-----------------------+ | @DISK:=VARIABLE_VALUE | +-----------------------+ | 153135 | +-----------------------+ 1 row in set (0.00 sec) +-----------------------------------------------+ | Current percentual of created tmp_disk tables | +-----------------------------------------------+ | 13% | +-----------------------------------------------+ 1 row in set (0.00 sec)
Neste teste registramos que 13% do total de tabelas temporárias criadas pelo servidor de bancos de dados MySQL, foram criadas em disco; um número não muito alto, se você considerar um servidor médio, atendendo a um sistema médio (assunto que dá um outro artigo). Uma boa solução aqui seria então adotar um dos sistemas de arquivos tmpfs ou ramfs para o diretório configurado como o tmpdir do MySQL.
- tmpfs – sistema de arquivos que manterá os arquivos em memória RAM, com espaço limitado;
- ramfs – sistema de arquivos que manterá os arquivos em memória RAM, com espaço delimitado pelo tamanho da memória disponível (pode ser perigoso no caso de bancos de dados);
Para saber o valor atual de tmpdir:
[root@wbsrv101 ~]# mysql -u root -p -e "select @@tmpdir;" Enter password: +----------+ | @@tmpdir | +----------+ | /tmp | +----------+
Agora que você já sabe o qual é o diretório temporário, basta selecionar um dos tipos de sistema de arquivos e montá-lo em /tmp. Antes de mais nada, tenha certeza de ter parado o MySQL, monte o sistema de arquivos e suba o MySQL.
[root@examsrv01 ~]# service mysql stop Stopping mysqld: [ OK ] [root@examsrv01 ~]# mount -t tmpfs -o size=1G tmpfs /tmp [root@examsrv01 ~]# service mysql start Starting mysqld: [ OK ] [root@examsrv01 ~]# df -Th /tmp Filesystem Type Size Used Avail Use% Mounted on tmpfs tmpfs 1.0G 0 1.0G 0% /tmp
No próximo artigo, vou falar de PERFORMANCE_SCHEMA e instrumentação do MySQL.
Até lá!
De 0 a 10, o quanto você recomendaria este artigo para um amigo?