Como sintonizar o Servidor MySQL

Este blog é parte da série de posts

MySQL Sintonia Pergunta

Como identificar MySQL consultas lentas e preocupações relacionadas

Como sintonizar o Servidor MySQL

Para continuar agora com o próprio servidor:

Bem além de simplesmente dizer fazer o que Pedro diz , deixa exemplos revisão de como eles podem resolver por si próprios.

Para começar, você pode compará-lo my.cnf contra o Q & A versão que está disponível gratuitamente via Percona. É esta uma solução ideal? Não, mas vai permitir que você tome um novo olhar para o seu arquivo de configuração depois que você responde a todas as suas perguntas, através do seu assistente de configuração.

https://tools.percona.com/wizard

innodb_buffer_pool_size -

select @@innodb_buffer_pool_size;

Definir o innodb_buffer_pool_size é de longe um dos locais mais importantes para a MySQL InnoDB database.Some boa leitura sobre este tema está abaixo:

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Você precisa ter em mente que não existe um "one size fits all consulta" para definir o innodb_buffer_pool_size. Nós temos algumas orientações e sugestões que surgiram ao longo dos anos (ver abaixo) comuns e também temos várias teorias diferentes sobre as melhores opções.

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;


SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;

É seguro dizer que, se você tem o conjunto 8M padrão como sua variável innodb_buffer_pool_size então esta deve ser uma das primeiras definições que se ajustam.

Basicamente, permitir que isso tem toda a memória disponível, você pode dar ao luxo de dar. Isto, obviamente, leva em conta vários fatores, como o que mais precisa alocar memória no servidor? Tenha em mente que Disk I / O é importante para o desempenho e mais memória você permitir que esta variável tem o menor disco I / O deve ser exigido para acesso à tabela.

Vamos também considerar isso ....
Uma vez que a recomendação geral é para definir the innodb_log_file_size em 25% do tamanho do buffer , devemos avaliar o que temos para o innodb_log_file_size perconceitos do Barão , em seguida, olhar para ver como o relaciona com o innodb_buffer_pool_size.

Por exemplo, seguindo a lógica de Barão mensagem:

> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)

1 row in set (59.99 sec)

Log sequence number 3991367755
1 row in set (0.01 sec)

> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
| 1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
| 1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)

Agora, quando você comparar estes resultados com as "diretrizes"

SELECT CONCAT(ROUND(KBS/POWER(1024,
-> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
-> SUBSTR(' KMG',IF(PowerOf1024<0,0,
-> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
-> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
-> WHERE engine='InnoDB') A,
-> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G |
+-------------------------------------+

Você obter um resultado diferente. Enquanto a verificação do Barão é dependente do intervalo de tempo é executado dentro (que é por isso que você deve verificar isso durante o pico de tráfego) que pode lhe dar uma visão mais realista sobre o seu tráfego e uso. Gostaria de olhar para definir o innodb_buffer_pool_size de 2G não 1G neste exemplo simples.

SET @ Sequence1 = 3982683217;
SET @ Sequence2 = 3991367755;
selecionar (@ Sequence2 - @ Sequence1) / 1024/1024 como MB_per_min;
select ((@ Sequence2 - @ Sequence1) / 1024/1024) * 60 como MB_per_hour;
selecionar (((@ Sequence2 - @ Sequence1) / 1024/1024) * 60) * 4 como estimated_buffer_pool;
seleccionar ((((@ Sequence2 - @ Sequence1) / 1024/1024) * 60) * 4) / 1024 como estimated_buffer_pool_GB;

innodb_log_file_size -

select @@innodb_log_file_size;

Eu descobri que eu gosto de conceitos do Barão sobre como configurar o innodb_log_file_size. Lembre-se de executar as suas sugestões durante os horários de pico para obter verdadeiros leituras. Quanto maior o tamanho desses arquivos o melhor desempenho com grandes conjuntos de dados, mas nada vem de graça, você vai aumentar os tempos de recuperação. Tempos de recuperação aumento pode não parecer uma grande preocupação para alguns, até que é um banco de dados dependente de receita que você está esperando e assistindo processo para sempre. A recomendação geral é para ajustá-la em 25% do tamanho do buffer.

select ( @@innodb_buffer_pool_size * .25 )

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely /

innodb_log_buffer_size -

select @@innodb_log_buffer_size;

Lembro-me de uma vez eu tive esse conjunto em um servidor para innodb_log_buffer_size = 128M. Foi uma boa escolha? Não significa que eu queria memória quando eu provavelmente não precisava. Era um banco de dados pesado muito escrever, mas esta definição era muito provável alta. Concentre-se nos padrões de primeiro e depois duplicá-la (8MB - 16MB) máx.

innodb_additional_mem_pool_size -

select @@innodb_additional_mem_pool_size;

Peter aborda essa configuração no seu blog . Você pode enfrentá-lo e avaliar as opções após outras definições foram abordados na minha opinião.

innodb_flush_log_at_trx_commit -

select @@innodb_flush_log_at_trx_commit;

> Set GLOBAL innodb_flush_log_at_trx_commit = 2;
Peter aborda essa configuração no seu blog também. Esta é uma melhoria de desempenho de teste variável e valor comumente ignorado, ajustando 0-2. Tenha em mente os riscos.

thread_cache -

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
| 50 |
+---------------------+

http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 4 |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 962 |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
| 99.5842 |
+-------------------------+

Mantenha um olho sobre este status: mostrar o status como "Threads_created ';
Se ele sobe, então você tem que definir muito baixo e que precisa ser levantada.

query_cache_size -

select ((@@query_cache_size / 1024) / 1024);

Os valores permitidos são múltiplos de 1024.

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html # sysvar_query_cache_size

Preste atenção a esta definição o mais lido dependente sua aplicação se torna. Eu costumava correr com ele definido para key_buffer = 16M e eu podia e, provavelmente, deve ter dobrado isso.

key_buffer_size -

select @@key_buffer_size;

Com a mudança para MySQL InnoDB como engine de armazenamento padrão é provável que alguns pudemos ver cada vez menos tabelas criadas como MyISAM. Isso não é um fato apenas uma opinião. O key_buffer_size será muito importante para você, se você utilizar tabelas MyISAM.

Revisão de Shlomi Noach post para ajudá-lo a encontrar o que que facilmente tabelas que tipo de motor.
"Veja por tamanho da tabela (quase exatamente como apresentado na INFORMATION_SCHEMA)" - Shlomi Noach.

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+

table_cache -

show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| table_open_cache_instances | 1 |
+----------------------------+-------+

De acordo com o manual é uma boa fórmula para ajudar a determinar atable_definition_cache tamanho.

SELECT 400 + (@@table_open_cache / 2);

> SHOW status like '%Opened_tables%';

table_open_cache_instances
Um valor de 8 ou 16 é recomendado em sistemas que usam rotineiramente 16 ou mais núcleos, o padrão é 1.

Esperemos que este expõe as variáveis e configurações que precisam ser revistos para ajudá-lo a obter o melhor de seu banco de dados MySQL.