configuração e otimização do MySQL

1

Aqui está a informação para o meu servidor Giga Dedicated 6-Core:

  • Intel Core i7 (6 x 3,20GHz)
  • 24 GB de RAM
  • SSD de 160 GB + HD de 2000 GB Eu tenho o CentOS 5.6 (64 bits) instalado e o Plesk Panel 10
  • A versão do Apache é 2.2.19
  • a versão do MySQL é 5.5
  • PHP 5.3.6

Eu estava interessado em otimizar o servidor mysql. Eu terei dezenas de databses, copule deles tem vários gigabytes de tamanho.

Por isso, estou pedindo opinião sobre os tamanhos das variáveis my.cnf .

Aqui está meu my.cnf:

key_buffer_size = 1024M   
table_cache = 1024   
sort_buffer_size = 32M  
read_buffer_size = 32M  
read_rnd_buffer_size = 16M  
myisam_sort_buffer_size = 128M  
thread_cache_size = 16  
query_cache_size= 64M  
query_cache_limit = 1M  

thread_concurrency = 8

innodb_buffer_pool_size = 500M  
innodb_additional_mem_pool_size = 128M  

max_connections=250

E aqui está o log do mysqltuner:

MySQLTuner 1.2.0 - Major Hayden <[email protected]>  
Bug reports, feature requests, and downloads at http://mysqltuner.com/  
Run with '--help' for additional options and output filtering  

-------- General Statistics --------------------------------------------------  
[--] Skipped version check for MySQLTuner script  
[OK] Currently running supported MySQL version 5.5.14  
[OK] Operating on 64-bit architecture  

-------- Storage Engine Statistics -------------------------------------------  
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster   
[--] Data in MyISAM tables: 7G (Tables: 104)  
[--] Data in InnoDB tables: 530M (Tables: 213)  
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)  
[!!] Total fragmented tables: 213  

-------- Security Recommendations  -------------------------------------------  
[OK] All database users have passwords assigned  

-------- Performance Metrics -------------------------------------------------  
[--] Up for: 4s (9 q [2.250 qps], 6 conn, TX: 10K, RX: 695)  
[--] Reads / Writes: 100% / 0%  
[--] Total buffers: 1.7G global + 80.4M per thread (250 max threads)  
[!!] Maximum possible memory usage: 21.3G (90% of installed RAM)  
[OK] Slow queries: 0% (0/9)  
[OK] Highest usage of available connections: 0% (1/250)  
[!!] Key buffer size / total MyISAM indexes: 1.0G/7.2G  
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)  
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)  
[OK] Query cache prunes per day: 0  
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)  
[OK] Thread cache hit rate: 83% (1 created / 6 connections)  
[OK] Table cache hit rate: 78% (26 open / 33 opened)  
[OK] Open file limit used: 0% (18/2K)  
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)  
[!!] Connections aborted: 16%  
[!!] InnoDB data size / buffer pool: 530.1M/500.0M  

-------- Recommendations -----------------------------------------------------  
General recommendations:  
    Run OPTIMIZE TABLE to defragment tables for better performance  
    MySQL started within last 24 hours - recommendations may be inaccurate  
    Reduce your overall MySQL memory footprint for system stability  
    Enable the slow query log to troubleshoot bad queries  
    Your applications are not closing MySQL connections properly  
Variables to adjust:  
    key_buffer_size (> 7.2G)  
    query_cache_limit (> 1M, or use smaller result sets)  
    innodb_buffer_pool_size (>= 530M)  

Qual seria a melhor configuração para o meu servidor? Qual a sua opinião, sugestão, experiência?

Atualização:

Eu corrigi algumas configurações my.cnf. Aqui estão eles:

key_buffer_size = 1024M
table_cache = 1024
sort_buffer_size = 10M
join_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
query_cache_limit = 1M
-#Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 128M
max_connections=250

E a resposta das consultas que sugerem key_buffer_size e innodb_buffer_pool_size são:

+---------------------------------------------------+
| recommended_innodb_buffer_pool_size |
+---------------------------------------------------+
| 1129M |
+---------------------------------------------------+
and
+----------------------------------------+
| recommended_key_buffer_size |
+----------------------------------------+
| 4M |
+----------------------------------------+

O recommended_key_buffer_size é muito pequeno? O que você acha que são as configurações boas o suficiente? O que me preocupa também é max_connections = 250? Isso é suficiente?

O site que hospedo tem cerca de 18.000 visitas e 70000 pageviews em um dia.

E aqui está o que o mysqltuner diz sobre essas configurações:

MySQLTuner 1.2.0 - Major Hayden 
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.14
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 114)
[--] Data in InnoDB tables: 530M (Tables: 219)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 221

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 32m 2s (11K q [0.179 qps], 461 conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 2.2G global + 40.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 12.0G (51% of installed RAM)
[OK] Slow queries: 0% (0/11K)
[OK] Highest usage of available connections: 8% (20/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/3.5M
[OK] Key buffer hit rate: 99.9% (688K cached / 510 reads)
[OK] Query cache efficiency: 57.3% (4K cached / 7K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234 sorts)
[!!] Temporary tables created on disk: 34% (661 on disk / 1K total)
[OK] Thread cache hit rate: 95% (20 created / 461 connections)
[OK] Table cache hit rate: 98% (611 open / 622 opened)
[OK] Open file limit used: 14% (329/2K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 530.2M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)

Eu realmente aprecio sua ajuda. Eu sei que este é um monte de texto para você ler, mas eu estou apenas tentando aprender alguma coisa.

    
por Denis 10.07.2011 / 18:25

1 resposta

2

Você precisa definir o MyISAM Key Cache e o InnoDB Buffer Pool para acomodar os dados que você possui. publiquei duas consultas em o DBA StackExchange para calcular o tamanho recomendado para esses caches .

Para o MyISAM, uma vez que apenas páginas de índice são armazenadas em cache, esta consulta recomendará o tamanho de key_buffer_size com base na soma de seus arquivos .MYI e o limitará em 4G se exceder 4G:

SELECIONAR CONCAT (REDONDO (KBS / POWER (1024, SE (PowerOfTwo < 0,0, IF (PowerOfTwo > 3,0, PowerOfTwo))) + 0,4999), SUBSTR ('KMG', SE (PowerOfTwo < 0,0, IF (PowerOfTwo > 3,0, PowerOfTwo)) + 1,1)) recommended_key_buffer_size
FROM (SELECIONE MENOS (POWER (2,32), KBS1) KBS DE
(SELECT SUM (index_length) KBS1 FROM informações_schema.tables
WHERE engine = 'MyISAM' E table_schema NOT IN ('information_schema', 'mysql')) AA
) A, (SELECIONE 2 PowerOfTwo) B;

Para o InnoDB, como os dados e páginas de índice são armazenados em cache, essa consulta recomendará o tamanho de innodb_buffer_pool_size com base na soma de seus dados e páginas de índice:

SELECIONAR CONCAT (REDONDO (KBS / POWER (1024, SE (PowerOfTwo < 0,0, IF (PowerOfTwo > 3,0, PowerOfTwo))) + 0,49999), SUBSTR ('KMG', SE (PowerOfTwo < 0,0, IF (PowerOfTwo > 3,0, PowerOfTwo)) + 1,1)) recommended_innodb_buffer_pool_size
FROM (soma SELECT (comprimento_de_data + comprimento_Index) KBS FROM information_schema.tablesM
WHERE engine = 'InnoDB') A,
(SELECIONE 2 PowerOfTwo) B;

De acordo com a sua exibição de mysqltuner.pl, você tem cerca de 24 GB de RAM, 530MB id InnoDB, 7.2GB de índices MyISAM. Não importa o que as recomendações surgem, por favor, use o bom senso definindo innodb_buffer_pool_size para um número de aproximadamente 530MB, mas abaixo de 1GB. Como o MyISAM não tira proveito da memória em certas instâncias, você pode deixar o key_buffer_size como 1024M, porque mysqltuner.pl diz que 90% da RAM é usada pelas conexões de banco de dados. O sistema operacional também precisa de memória RAM.

Eu diminuiria isso:

[mysqld]
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M

Reinicie o mysql depois e execute mysqltuner.pl. Seu objetivo aqui é obter o máximo possível de uso de memória abaixo de 80%, para que o sistema operacional tenha espaço para a memória.

CAVEAT: Por favor, desculpe a coluna chamada 'PowerOfTwo'. Deve realmente ser chamado PowerOf1024. O objetivo do PowerOfTwo é exibir as respostas recomendadas em GB, MB, KB

  • (SELECT 0 PowerOfTwo) exibe o valor em Bytes
  • (SELECT 1 PowerOfTwo) exibe o valor em QuiloBytes
  • (SELECT 2 PowerOfTwo) exibe o valor em MegaBytes
  • (SELECT 3 PowerOfTwo) exibe o valor em GigaBytes
por 11.07.2011 / 17:59