Início / dba / Perguntas / 53201
Accepted
user30431
Asked: 2013-11-13 10:37:43 +0800 CST

MySQL cria tabelas temporárias em disco. Como faço para parar?

  • 772

Estamos executando um site (Moodle) que os usuários atualmente acham lento. Acho que rastreei o problema do MySQL criando tabelas temporárias no disco. Eu observo a variável created_tmp_disk_tablesna administração do servidor Mysql Workbench e o número aumenta com aproximadamente 50 tabelas/s. Após um dia de uso, created_tmp_disk_tablesé >100k. Além disso, a memória não parece ser liberada. O uso continua aumentando até que o sistema se torne praticamente inutilizável e tenhamos que reiniciar o MySQL. Eu preciso reiniciá-lo quase todos os dias e ele começa usando cerca de 30-35% da memória disponível e terminando o dia com 80%.

Eu não tenho blobs no banco de dados e também não tenho controle sobre as consultas, então não posso tentar otimizá-las. Também usei o Percona Configuration Wizard para gerar um arquivo de configuração, mas o my.ini também não resolveu meu problema.

Perguntas

  1. O que devo mudar para impedir o MySQL de criar tabelas temporárias no disco? Existem configurações que eu preciso alterar? Devo jogar mais memória nele?

  2. Como posso impedir o MySQL de consumir minha memória?

Editar

Ativei slow_querieso log e descobri que a consulta SELECT GET_LOCK() foi registrada como lenta. Uma pesquisa rápida revelou que eu havia permitido conexões persistentes na configuração do PHP ( mysqli.allow_persistent = ON). Eu desliguei isso. Isso reduziu a taxa na qual o MySQL consome memória. Ele ainda está criando tabelas temporárias.

Eu também verifiquei se o key_buffer sizeé grande o suficiente. Eu olhei para a variável key_writes. Isso deve ser zero. Se não, aumente o key_buffer_size.Eu tenho zero key_readse zero key_writes, então suponho que o key_buffer_sizeé grande o suficiente.

Aumentei o tmp_table_sizee max-heap-table-sizepara 1024M pois um aumento em created_tmp_disk_tables pode indicar que as tabelas não cabem na memória. Isso não resolveu.

Ref: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

Editar 2

Se você vir muitos sort_merge_passespor segundo na saída SHOW GLOBAL STATUS, considere aumentar o sort_buffer_sizevalor. Eu tinha 2 sort_merge_passesem uma hora, então considero sort_buffer_sizeque é grande o suficiente.

Ref: Manual do MySQL emsort_buffer_size

Editar 3

Modifiquei os buffers de classificação e junção, conforme sugerido por @RolandoMySQLDBA. O resultado é exibido na tabela abaixo, mas acho que created_tmp_tables_on_diskainda é alto. Reiniciei o servidor mysql depois de alterar o valor e verifiquei created_tmp_tables_on_diskdepois de um dia (8h) e calculei a média. Alguma outra sugestão? Parece-me que há algo que não cabe dentro de algum tipo de recipiente, mas não consigo descobrir o que é.

+---------------------+-------------+-------------+--------------------+
| Tmp_table_size,     | Sort_buffer | Join_buffer | No of created      |
| max_heap_table_size |             |             | tmp_tables on disk |
+---------------------+-------------+-------------+--------------------+
| 125M                | 256K        | 256K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 512K        | 512K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 1M          | 1M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 4M          | 4M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+



Esta é a minha configuração:

+-----------------------+-----------------------+
|DATABASE SERVER        |WEB SERVER             |
+-----------------------+-----------------------+
|Windows Server 2008 R2 |Windows Server 2008 R2 |
+-----------------------+-----------------------+
|MySQL 5.1.48           |IIS 7.5                |
+-----------------------+-----------------------+
|4 Core CPU             |4 Core CPU             |
+-----------------------+-----------------------+
|4GB RAM                |8GB RAM                |
+-----------------------+-----------------------+

Informação adicional

+--------------------+---------+
|PARAM               |VALUE    |
+--------------------+---------+
|Num of tables in Db |361      |
+--------------------+---------+
|Size of database    |2.5G     |
+--------------------+---------+
|Database engine     |InnoDB   |
+--------------------+---------+
|Read/write ratio    |3.5      |
|(Innodb_data_read/  |         |
|innodb_data_written)|         |
+--------------------+---------+
|Avg table size      |15k rows |
+--------------------+---------+
|Max table size      |744k rows|
+--------------------+---------+

Esta configuração foi dada a mim, então eu tenho controle limitado sobre ela. O servidor da web está usando muito pouca CPU e RAM, então excluí essa máquina como um gargalo. A maioria das configurações do MySQL se origina de uma ferramenta de geração automática de configuração.

Eu monitorei o sistema usando PerfMon durante alguns dias representativos. A partir disso, concluo que não é o sistema operacional que está trocando para o disco.

My.ini

[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/DBs/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=125
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
table-definition-cache= 1024
max-heap-table-size= 32M
thread_cache_size=38

MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=1024K
read_rnd_buffer_size=256K
sort_buffer_size=1024K
join_buffer_size=1024K


INNODB Specific options
innodb_data_home_dir="D:/DBs/"
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=8
mysql performance

3 respostas

  1. Best Answer

    Olhando para o my.ini, tenho duas sugestões

    SUGESTÃO #1

    Eu aumentaria as seguintes configurações no seumy.ini

    sort_buffer_size=4M
join_buffer_size=4M

    Isso fará com que algumas junções e ordenações permaneçam na memória. É claro que, uma vez que a JOINou an ORDER BYprecise de mais de 4M, ele será paginado para o disco como uma tabela MyISAM.

    Se você não puder fazer login como root@localhost, reinicie o mysql com

    C:\> net stop mysql
C:\> net start mysql

    Se você puder fazer login como root@localhost, não precisará reiniciar o mysql para usar essas configurações.

    Basta executar isso no cliente MySQL:

    SET @FourMegs = 1024 * 1024 * 4;
SET GLOBAL sort_buffer_size = @FourMegs;
SET GLOBAL join_buffer_size = @FourMegs;

    SUGESTÃO #2

    Como seus dados estão na unidade D:, você pode ter E/S de disco na unidade C:.

    Execute esta consulta:

    mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)

    Como executo o mysql na minha área de trabalho com padrões, minhas tabelas temporárias estão sendo gravadas no Drive C:. Se a unidade D for um disco melhor que a unidade C:, talvez você possa mapear tabelas temporárias para a unidade D:configurando tmpdir da my.iniseguinte maneira:

    tmpdir="D:/DBs/"

    Você terá que reiniciar o mysql já que tmpdir não é uma variável dinâmica.

    De uma chance !!!

    ATUALIZAÇÃO 2013-11-29 10:09 EST

    SUGESTÃO #3

    Dado o fato de que o MySQL está rodando no Windows e você não pode tocar nas consultas no pacote principal, tenho duas idéias que devem ser feitas juntas.

    IDEIA #1: Mova o banco de dados para uma máquina Linux

    Você deve ser capaz de

    • Configurar uma máquina Linux
    • Instale o MySQL na máquina Linux
    • Habilitar log binário para MySQL no Windows
    • mysqldump o banco de dados para um arquivo SQL de texto
    • Carregar arquivo SQL para MySQL rodando no Linux
    • Configurar replicação de MySQL/Windows para MySQL/Linux

    IDEIA #2: Reconfigure o Moodle para apontar para a máquina Linux

    O Moodle foi projetado para LAMP em primeiro lugar. Basta alterar os arquivos de configuração para apontar para a máquina Linux em vez de localhost.

    Aqui está um link para um documento antigo do Moodle 2.3 sobre como configurar o MySQL: http://docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database

    Tenho certeza de que os documentos mais recentes também estão disponíveis.

    Qual é o ponto de mover o banco de dados para Linux ???

    Como isso ajuda a situação da tabela temporária ???

    Eu sugeriria então configurar um disco RAM como a pasta de destino para suas tabelas temporárias

    A criação da tabela temporária ainda acontecerá, mas será gravada na RAM e não no disco. reduzindo a E/S de disco.

    ATUALIZAÇÃO 2013-11-29 11:24 EST

    SUGESTÃO #4

    Eu sugiro revisitar a SUGESTÃO #2 com um disco RAID-0 rápido (32+ GB), configurando-o como Drive T: (T para Temp). Depois de instalar esse disco, adicione isso a my.ini:

    [mysqld]
tmpdir="T:\"

    A reinicialização do MySQL seria necessária, usando

    net stop mysql
net start mysql

    BTW eu disse RAID-0 de propósito para que você possa obter um bom desempenho de gravação em um RAID-1, RAID-10. Um disco de tabela tmp não é algo que eu tornaria redundante.

    Sem otimizar as consultas como @RaymondNijland comenta, você não pode reduzir a contagem de criação de tabelas temporárias de forma alguma. SUGGESTION #3e SUGGESTION #4oferecer a aceleração da criação de tabelas temporárias e E/S de tabelas temporárias como a única alternativa.

    • 16

  2. Eu respondo minha própria pergunta aqui para completar

    Vou selecionar @RolandoMySQLDBA como a resposta preferida porque me deu mais dicas, embora não tenha realmente resolvido meu problema.

    Abaixo estão os resultados da minha investigação

    Conclusão

    O MySQL no Windows apenas cria muitas tabelas temporárias e o ajuste do MySQL modificando o conteúdo dos arquivos de configuração não ajudou.

    Detalhes

    A tabela detalha os parâmetros que modifiquei em my.ini respectivamente antes de executar qualquer consulta. O MySQL foi reiniciado entre cada teste.

    Usei como template o my.ini encontrado na pergunta original e então alterei o valor dos parâmetros um a um conforme a tabela abaixo.

    Eu usei o JMeter para gerar 100 solicitações da Web simultâneas (já que isso representava nosso uso) repetidas 10 dez vezes. Cada um Testconsistia, assim, em 1000 pedidos no total. Isso resultou em chamadas de banco de dados subsequentes. Isso mostrou que o MySQL criaria muitas tabelas temporárias, independentemente de quais parâmetros de configuração alteramos.

    +----+------------+-------+---------------+------------+
|Test|Parameter   |Value  |NumOfTempTables|Db Max Conn |
+----+------------+-------+---------------+------------+
| 1  |key_buffer_ | 25M   | 30682         | 29         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 2  |key_buffer_ | 55M   | 30793         | 29         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 3  |key_buffer_ | 100M  | 30666         | 28         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 4  |key_buffer_ | 125M  | 30593         | 24         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 5  |query_cache_| 100M  | 30627         | 32         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 6  |query_cache_| 250M  | 30761         | 26         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 7  |query_cache_| 500M  | 30864         | 83*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 8  |query_cache_| 1G    | 30706         | 75*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 9  |tmp_table_  | 125M  | 30724         | 31         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 10 |tmp_table_  | 250M  | 30689         | 90*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 11 |tmp_table_  | 500M  | 30792         | 28         |
|    |size        |       |               |            |  
+----+------------+-------+---------------+------------+
| 12 |Sort_buffer&| 256K  | 30754         | 28         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 13 |Sort_buffer&| 512K  | 30788         | 30         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 14 |Sort_buffer&| 1M    | 30788         | 28         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 15 |Sort_buffer&| 4M    | 30642         | 35         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 16 |innodb-     | 1G    | 30695         | 33         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |
+----+------------+-------+---------------+------------+
| 17 |innodb-     | 2G    | 30791         | 28         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            | 
+----+------------+-------+---------------+------------+
| 18 |innodb-     | 3G    | 30719         | 34         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |  
+----+------------+-------+---------------+------------+

    *Média de três corridas

    As imagens abaixo descrevem a quantidade de memória e CPU necessária ao servidor de banco de dados para as diferentes configurações. As linhas pretas indicam os valores mínimo e máximo e as barras azuis indicam os valores inicial e final. A memória máxima foi 4096Ma indicada na questão.

    Memory Usage CPU Usage

    • 13

  3. You should also check whether your In-Memory Temporary Table Size is limited by Maximum Heap Table Size variable.

    When using the MEMORY storage engine for in-memory temporary tables, MySQL automatically converts an in-memory temporary table to an on-disk table if it becomes too large.

    If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. The maximum size for in-memory temporary tables is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller.

    • 3

relate perguntas