Início / dba / Perguntas / 27328
Accepted
alfish
Asked: 2012-10-22 07:03:45 +0800 CST

Quão grande deve ser o mysql innodb_buffer_pool_size?

  • 772

Eu tenho um banco de dados ocupado com apenas tabelas InnoDB que tem cerca de 5 GB de tamanho. O banco de dados é executado em um servidor Debian usando discos SSD e eu configurei conexões máximas = 800 que às vezes saturam e trituram o servidor para parar. A consulta média por segundo é de cerca de 2,5 K. Então eu preciso otimizar o uso de memória para abrir espaço para o máximo de conexões possíveis.

Eu vi sugestões de que innodb_buffer_pool_size deve ser de até %80 da memória total. Por outro lado, recebo este aviso do script tuning-primer:

Max Memory Ever Allocated : 91.97 G
Configured Max Per-thread Buffers : 72.02 G
Configured Max Global Buffers : 19.86 G
Configured Max Memory Limit : 91.88 G
Physical Memory : 94.58 G

Aqui estão minhas variáveis ​​innodb atuais:

| innodb_adaptive_flushing                          | ON                                                                                                                     |
| innodb_adaptive_hash_index                        | ON                                                                                                                     |
| innodb_additional_mem_pool_size                   | 20971520                                                                                                               |
| innodb_autoextend_increment                       | 8                                                                                                                      |
| innodb_autoinc_lock_mode                          | 1                                                                                                                      |
| innodb_buffer_pool_instances                      | 1                                                                                                                      |
| innodb_buffer_pool_size                           | 20971520000                                                                                                            |
| innodb_change_buffering                           | all                                                                                                                    |
| innodb_checksums                                  | ON                                                                                                                     |
| innodb_commit_concurrency                         | 0                                                                                                                      |
| innodb_concurrency_tickets                        | 500                                                                                                                    |
| innodb_data_file_path                             | ibdata1:10M:autoextend                                                                                                 |
| innodb_data_home_dir                              |                                                                                                                        |
| innodb_doublewrite                                | ON                                                                                                                     |
| innodb_fast_shutdown                              | 1                                                                                                                      |
| innodb_file_format                                | Antelope                                                                                                               |
| innodb_file_format_check                          | ON                                                                                                                     |
| innodb_file_format_max                            | Antelope                                                                                                               |
| innodb_file_per_table                             | ON                                                                                                                     |
| innodb_flush_log_at_trx_commit                    | 2                                                                                                                      |
| innodb_flush_method                               | O_DIRECT                                                                                                               |
| innodb_force_load_corrupted                       | OFF                                                                                                                    |
| innodb_force_recovery                             | 0                                                                                                                      |
| innodb_io_capacity                                | 200                                                                                                                    |
| innodb_large_prefix                               | OFF                                                                                                                    |
| innodb_lock_wait_timeout                          | 50                                                                                                                     |
| innodb_locks_unsafe_for_binlog                    | OFF                                                                                                                    |
| innodb_log_buffer_size                            | 4194304                                                                                                                |
| innodb_log_file_size                              | 524288000                                                                                                              |
| innodb_log_files_in_group                         | 2                                                                                                                      |
| innodb_log_group_home_dir                         | ./                                                                                                                     |
| innodb_max_dirty_pages_pct                        | 75                                                                                                                     |
| innodb_max_purge_lag                              | 0                                                                                                                      |
| innodb_mirrored_log_groups                        | 1                                                                                                                      |
| innodb_old_blocks_pct                             | 37                                                                                                                     |
| innodb_old_blocks_time                            | 0                                                                                                                      |
| innodb_open_files                                 | 300                                                                                                                    |
| innodb_purge_batch_size                           | 20                                                                                                                     |
| innodb_purge_threads                              | 0                                                                                                                      |
| innodb_random_read_ahead                          | OFF                                                                                                                    |
| innodb_read_ahead_threshold                       | 56                                                                                                                     |
| innodb_read_io_threads                            | 4                                                                                                                      |
| innodb_replication_delay                          | 0                                                                                                                      |
| innodb_rollback_on_timeout                        | OFF                                                                                                                    |
| innodb_rollback_segments                          | 128                                                                                                                    |
| innodb_spin_wait_delay                            | 6                                                                                                                      |
| innodb_stats_method                               | nulls_equal                                                                                                            |
| innodb_stats_on_metadata                          | ON                                                                                                                     |
| innodb_stats_sample_pages                         | 8                                                                                                                      |
| innodb_strict_mode                                | OFF                                                                                                                    |
| innodb_support_xa                                 | ON                                                                                                                     |
| innodb_sync_spin_loops                            | 30                                                                                                                     |
| innodb_table_locks                                | ON                                                                                                                     |
| innodb_thread_concurrency                         | 4                                                                                                                      |
| innodb_thread_sleep_delay                         | 10000                                                                                                                  |
| innodb_use_native_aio                             | ON                                                                                                                     |
| innodb_use_sys_malloc                             | ON                                                                                                                     |
| innodb_version                                    | 1.1.8                                                                                                                  |
| innodb_write_io_threads                           | 4                                                                                                                      |

Uma observação lateral que pode ser relevante: vejo que quando tento inserir uma postagem grande (digamos mais de 10 KB) do Drupal (que fica em um servidor da Web separado) no banco de dados, ela dura para sempre e a página não retorna corretamente.

Em relação a isso, estou querendo saber qual deve ser o meu innodb_buffer_pool_size para um desempenho ideal. Agradeço suas sugestões para definir este e outros parâmetros de forma otimizada para este cenário.

mysql innodb

4 respostas

  1. Best Answer

    Seu innodb_buffer_pool_size é enorme. Você configurou em 20971520000. São 19,5135 GB. Se você tiver apenas 5 GB de dados e índices do InnoDB, deverá ter apenas cerca de 8 GB. Mesmo isso pode ser muito alto.

    Aqui está o que você deveria fazer. Primeiro execute esta consulta

    SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

    Isso fornecerá o RIBPS, tamanho recomendado do pool de buffer do InnoDB, com base em todos os dados e índices do InnoDB, com um adicional de 60%.

    Por exemplo

    mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

mysql>

    Com essa saída, você definiria o seguinte em /etc/my.cnf

    [mysqld]
innodb_buffer_pool_size=8G

    Próximo,service mysql restart

    Após a reinicialização, execute o MySQL por uma ou duas semanas. Em seguida, execute esta consulta:

    SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

    Isso lhe dará quantos GB reais de memória estão em uso pelo InnoDB Data no InnoDB Buffer Pool neste momento.

    Eu escrevi sobre isso antes: O que definir innodb_buffer_pool e por quê ..?

    Você pode simplesmente executar essa DataGBconsulta agora, em vez de reconfigurar, reiniciar e esperar uma semana.

    Esse valor DataGBse assemelha mais ao tamanho do InnoDB Buffer Pool + (porcentagem especificada em innodb_change_buffer_max_size). Tenho certeza de que isso será muito menos do que os 20.000 milhões que você reservou agora. A economia de RAM pode ser usada para ajustar outras coisas como

    ADVERTÊNCIA Nº 1

    Isso é muito importante observar: às vezes, o InnoDB pode exigir um adicional de 10% sobre o valor do innodb_buffer_pool_size . Aqui está o que a documentação do MySQL diz sobre isso:

    Quanto maior você definir esse valor, menos E/S de disco será necessária para acessar dados em tabelas. Em um servidor de banco de dados dedicado, você pode definir isso para até 80% do tamanho da memória física da máquina. Esteja preparado para reduzir esse valor se esses outros problemas ocorrerem:

    A competição por memória física pode causar paginação no sistema operacional.

    O InnoDB reserva memória adicional para buffers e estruturas de controle, de modo que o espaço total alocado seja aproximadamente 10% maior que o tamanho especificado.

    O espaço de endereço deve ser contíguo, o que pode ser um problema em sistemas Windows com DLLs carregadas em endereços específicos.

    O tempo para inicializar o buffer pool é aproximadamente proporcional ao seu tamanho. Em grandes instalações, esse tempo de inicialização pode ser significativo. Por exemplo, em um servidor Linux x86_64 moderno, a inicialização de um pool de buffers de 10 GB leva aproximadamente 6 segundos. Consulte a Seção 8.9.1, “O pool de buffers do InnoDB” .

    ADVERTÊNCIA Nº 2

    Vejo os seguintes valores em seumy.cnf

    | innodb_io_capacity                                | 200 |
| innodb_read_io_threads                            | 4   |
| innodb_thread_concurrency                         | 4   |
| innodb_write_io_threads                           | 4   |

    Esses números impedirão o InnoDB de acessar vários núcleos

    Por favor, defina o seguinte:

    [mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

    Eu escrevi sobre isso antes no DBA StackExchange

    Acabei de responder a uma pergunta como esta em Server Fault usando uma fórmula mais concisa:

    SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;
    • 351

  2. Algo assim? Usando SHOW VARIABLESe SHOW GLOBAL STATUS:

    Expressão: innodb_buffer_pool_size / _ram
    Significado: % de RAM usada para InnoDB buffer_pool
    Intervalo recomendado: 60~80%

    Expressão: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
    Significado: Requisições de leitura que tiveram que atingir o disco
    Intervalo recomendado: 0-2%
    O que fazer se estiver fora do intervalo: Aumente innodb_buffer_pool_size se você tiver o suficiente BATER.

    Expressão: Innodb_pages_read / Innodb_buffer_pool_read_requests
    Significado: Solicitações de leitura que tiveram que atingir o disco
    Intervalo recomendado: 0-2%
    O que fazer se estiver fora do intervalo: Aumente innodb_buffer_pool_size se você tiver RAM suficiente.

    Expressão: Innodb_pages_written / Innodb_buffer_pool_write_requests
    Significado: Gravar solicitações que tiveram que atingir o disco
    Intervalo recomendado: 0-15%
    O que fazer se estiver fora do intervalo: Verifique innodb_buffer_pool_size

    Expressão: Innodb_buffer_pool_reads / Uptime
    Significado: Lê
    o intervalo recomendado: 0-100/s.
    O que fazer se estiver fora do intervalo: Aumente innodb_buffer_pool_size?

    Expressão: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
    Significado: InnoDB I/O
    Faixa recomendada: 0-100/s.
    O que fazer se estiver fora do intervalo: Aumente innodb_buffer_pool_size?

    Expressão: Innodb_buffer_pool_pages_flushed / Uptime
    Significado: Grava (limpa)
    Intervalo recomendado: 0-100/s.
    O que fazer se estiver fora do intervalo: Aumente innodb_buffer_pool_size?

    Expressão: Innodb_buffer_pool_wait_free / Uptime
    Significado: Contador para quando não há páginas livres no buffer_pool. Ou seja, todas as páginas estão sujas.
    Faixa recomendada: 0-1/seg.
    O que fazer se estiver fora do intervalo: Primeiro, certifique-se de que innodb_buffer_pool_size esteja definido razoavelmente; se ainda tiver problemas, diminua innodb_max_dirty_pages_pct

    • 25

  3. Seu título pergunta sobre innodb_buffer_pool_size, mas suspeito que esse não seja o problema real. (Rolando comentou por que você o definiu grande o suficiente, até grande demais.)

    Eu configurei conexões máximas = 800 que às vezes saturam e trituram o servidor para parar.

    Isso não está claro. 800 usuários no modo "Sleep" tem praticamente zero impacto no sistema. 800 threads ativos seria um desastre. Quantos threads estão "em execução"?

    Os threads estão bloqueando uns aos outros? Veja SHOW ENGINE INNODB STATUS para algumas dicas sobre impasses, etc.

    Alguma consulta está aparecendo no slowlog? Vamos otimizá-los.

    Qual versão você está usando? O XtraDB (um substituto do InnoDB) faz um trabalho melhor ao usar vários núcleos. 5.6.7 faz um trabalho ainda melhor.

    innodb_buffer_pool_instances -- mude para 8 (assumindo um buffer_pool de 20G); ele reduzirá um pouco a contenção Mutex.

    Você está vinculado à E/S ou está vinculado à CPU? As soluções são radicalmente diferentes, dependendo da sua resposta.

    SSD -- Pode ser melhor se todos os arquivos de log estivessem em unidades não SSD.

    • 7

  4. Mais memória é sempre melhor, mas na minha experiência, na maioria das vezes, o tamanho do buffer pool não deve caber no tamanho dos dados. Muitas tabelas estão inativas na maioria das vezes, como tabelas de backup espalhadas, portanto, o tamanho do buffer pool do innodb deve se ajustar ao tamanho dos dados ativos.

    O período de tempo especificado para páginas ativas influencia o desempenho, mas há um ponto ideal, onde você não obterá mais desempenho para um tamanho de buffer maior. Você poderia estimar/calcular/medir isso porshow engine innodb status

    • 6

relate perguntas