Qual deve ser o tamanho do mysql innodb_buffer_pool_size?

Eu tenho uma base de dados ocupada com apenas tabelas InnoDB que é de cerca de 5GB de tamanho. O banco de dados roda em um servidor Debian usando discos SSD e I've configuram 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.5K. Então eu preciso otimizar o uso de memória para dar espaço para o máximo de conexões possíveis.

I'vi sugestões de que o tamanho do innodb_buffer_pool_size deve ser até %80 da memória total. Por outro lado, eu 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 as minhas variáveis inodb actuais:

| 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 nota lateral que pode ser relevante: Vejo que quando tento inserir um post grande (digamos mais de 10KB) do Drupal (que fica em um servidor web separado) para a base de dados, ele dura para sempre e a página não retorna corretamente.

Em relação a estes, I'estou me perguntando qual deveria ser o meu tamanho innodb_buffer_pool_size para um ótimo desempenho. Agradeço as suas sugestões para definir este e outros parâmetros de forma ideal para este cenário.

alfish
Pergunta editada :em
Administradores de bases de dados
mysql innodb
21º outubro 2012 в 3:03
27 vistas
Solução

O seu **innodb_buffer_pool_size*** é enorme. Você tem o valor de 20971520000. That's 19.5135 GB. Se você tiver apenas 5GB de dados e índices InnoDB, então você deve ter apenas cerca de 8GB. Mesmo isto pode ser muito alto.

Aqui está o que deves 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;

Isto lhe dará o RIBPS, Tamanho Recomendado do Pool InnoDB Buffer baseado em todos os dados e índices 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 esta saída, você definiria o seguinte em /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G

A seguir, service mysql restart

Após o reinício, execute o mysql durante uma semana ou duas. 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 pela InnoDB Dados no Pool de Buffers da InnoDB neste momento.

Eu já escrevi sobre isto antes : https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181

Você poderia simplesmente executar esta consulta DataGB agora mesmo ao invés de reconfigurar, reiniciar e esperar uma semana.

Este valor DataGB mais se assemelha ao tamanho do InnoDB Buffer Pool + (percentagem especificada em innodb_change_buffer_max_size). Tenho certeza de que este será muito menor do que os 20000M que você reservou neste momento. A poupança em RAM pode ser usada para afinar outras coisas como

CAVEAT #1

Isto é muito importante de notar : Por vezes, a InnoDB pode exigir um adicional de 10% sobre o valor para o innodb_buffer_pool_size. Aqui está o que a Documentação MySQL diz sobre isto:

Quanto maior for este valor, menos I/O de disco é necessário para aceder dados em tabelas. Em um servidor de banco de dados dedicado, você pode configurar isso para a 80% do tamanho da memória física da máquina. Esteja preparado para reduzir este valor se estes outros problemas ocorrerem:

a competição pela memória física pode causar paging na operação sistema.

InnoDB reserva memória adicional para amortecedores e estruturas de controle, de modo que o espaço total atribuído é aproximadamente 10% maior do que o tamanho especificado.

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

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

CAVEAT #2

Eu vejo os seguintes valores no seu my.cnf.

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

Este número impedirá a InnoDB de aceder a 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 já escrevi sobre isso antes no DBA StackExchange

Eu apenas respondi uma pergunta como esta no ServerFault 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;
Community
Resposta editada :em
Comentários (8)

Seu título pergunta sobre o tamanho do innodb_buffer_pool_size, mas eu suspeito que esse não é o verdadeiro problema. (Rolando comentou sobre o porquê de você ter definido grande o suficiente, mesmo muito grande).

I've ajustaram conexões máximas = 800 que às vezes saturam e trituram o servidor para parar.

Isso não é claro. 800 utilizadores em "Sleep" o modo tem um impacto praticamente nulo no sistema. 800 fios ativos seriam um desastre. Quantos threads são "running"?

Os fios estão a bloquear-se uns aos outros? Veja SHOW ENGINE INNODB STATUS para algumas pistas sobre bloqueios, etc.

Alguma dúvida aparece no slowlog? Vamos's otimizá-las.

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

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

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

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

Comentários (0)

Mais memória é sempre melhor, mas na minha experiência a maior parte das vezes o tamanho do buffer pool não deve caber no tamanho dos seus dados. Muitas tabelas estão inativas na maioria das vezes, como as tabelas de backup espalhadas por aí, então o tamanho do pool de buffer inodb deve se ajustar ao seu tamanho de dados.

O período de tempo que você especifica para páginas ativas influencia a performance, mas lá's um ponto ótimo, onde você ganhou't obter mais performance para um tamanho de buffer maior. Você poderia estimar/calcular/medir que, por show engine innodb status, você poderia estimar/calcular/medir que

Colin 't Hart
Resposta editada :em
Comentários (0)