MySQL se torna muito lento até reiniciar

Question

Eu tenho um servidor MySQL rodando no CentOS.

Recentemente, estou tendo um problema que acontece a cada dois dias. O servidor está rodando rápido e OK, mas de repente está ficando muito lento, até que eu reinicie o MySQL, então ele retorna para um estado regular.

Isso aconteceu comigo algumas vezes, então desta vez tirei duas capturas de tela antes de executar service mysqld restart .

Antes de reiniciar:

Apósoreinício:

AmaioriadasminhastabelassãoInnoDB,algumassãoMyISAM.(4tabelasMyISAM,38tabelasInnoDB)

my.cnf:

[mysqld]bulk_insert_buffer_size=8Mconcurrent_insert=2connect_timeout=30default-storage-engine=MyISAMinnodb_buffer_pool_size=1300Minnodb_file_per_table=1interactive_timeout=1000join_buffer_size=128Mkey_buffer_size=1200Mlocal-infile=0slow_query_log=1long_query_time=0.5#skip-grant-tablesmax_allowed_packet=900Mmax_connections=40000max_heap_table_size=256Mmax_user_connections=10000max_write_lock_count=8myisam_max_sort_file_size=256Mmyisam_sort_buffer_size=64Mopen_files_limit=10192query_alloc_block_size=65536query_cache_limit=256Mquery_cache_size=384Mquery_cache_type=1query_prealloc_size=262144range_alloc_block_size=4096read_buffer_size=4Mread_rnd_buffer_size=16Msort_buffer_size=4Mtable_cache=8048table_open_cache=8000thread_cache_size=50tmp_table_size=256Mtransaction_alloc_block_size=4096transaction_prealloc_size=4096#innodb_force_recovery=5wait_timeout=1000max_connect_errors=5000open-files=50000[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

MOSTREESTADOGLOBALCOMO"% connect% ';

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 859148 |
| Max_used_connections     | 103    |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+

MOSTRAR VARIÁVEIS GLOBAIS COMO 'thread _%';

+---------------------------+---------------------------+
| Variable_name             | Value                     |
+---------------------------+---------------------------+
| thread_cache_size         | 50                        |
| thread_concurrency        | 10                        |
| thread_handling           | one-thread-per-connection |
| thread_pool_idle_timeout  | 60                        |
| thread_pool_max_threads   | 500                       |
| thread_pool_oversubscribe | 3                         |
| thread_pool_size          | 8                         |
| thread_pool_stall_limit   | 500                       |
| thread_stack              | 294912                    |
+---------------------------+---------------------------+

MOSTRE ESTADO GLOBAL COMO "threads _% ';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 49    |
| Threads_connected | 1     |
| Threads_created   | 372   |
| Threads_running   | 1     |
+-------------------+-------+

MOSTRAR ESTADO GLOBAL COMO "chave _% ';

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 1003901 |
| Key_blocks_used        | 3365    |
| Key_blocks_warm        | 0       |
| Key_read_requests      | 99176   |
| Key_reads              | 3052    |
| Key_write_requests     | 29353   |
| Key_writes             | 29347   |
+------------------------+---------+

MOSTRE ESTADO GLOBAL COMO "Q%";

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 961       |
| Qcache_free_memory      | 400828904 |
| Qcache_hits             | 1634009   |
| Qcache_inserts          | 1201887   |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 59970     |
| Qcache_queries_in_cache | 1467      |
| Qcache_total_blocks     | 3926      |
| Queries                 | 5316596   |
| Questions               | 5187929   |
+-------------------------+-----------+

MOSTRAR VARIÁVEIS GLOBAIS COMO "_size";

Empty set

    

Answer 1

Isso parece ser mais um problema de carga do cliente, em vez de um problema no servidor com vazamento de memória. Os tópicos do daemon estão mastigando aproximadamente um ou dois núcleos. Com o que eles estão ocupados? O que o SHOW FULL PROCESSLIST diz?

A reinicialização fez muito mais do que redefinir o estado do daemon. Ele eliminou 587 processos que presumivelmente tinham conexões ativas da porta 3306 (ou AF_UNIX) para o servidor. O que eles estavam fazendo? Você está feliz com o que eles estavam fazendo? Eles registraram erros fatais no momento do reinício que o deixam infeliz? Eles devem estar concluindo alguma tarefa e depois desconectando e saindo?

A reinicialização é uma solução rápida, mas parece que você quer entender como a carga do cliente cresce cada vez mais ao longo de 48 horas antes da reinicialização.

    

Answer 2

Para muito alívio imediato, depois de rever o seu manual de referência, considere

set global read_rnd_buffer_size=256K;  # from 16M per connection

Isso pode ser feito dinamicamente.
O login após isso ser feito não exigirá 16 milhões por login. Por que ler 16M (mesmo que seja da RAM) quando 256K vai ficar bem? Depois de postar outros itens solicitados, terei sugestões adicionais.

----- 2017 11 04 ------------- As sugestões a seguir precisam de sua pesquisa antes de implementar APENAS um item por dia. Alguns podem ser aplicados dinamicamente. Valores sugeridos de cfg / ini seguem para a seção [mysqld] e podem ser modificados, adicionados ou removidos.

max_connections=200 #from 40000 to support your 103 max_used_connections
max_user_connections=200 #from 10000 to be matched with max_connections
key_buffer_size  REMOVE for default of 64M.  less than 1% of 1200MB used

thread_cache_size=100 #from 50  to support your 103 max_used_connections - cap at 100 per V8
thread_concurrency=33 #from 10  for about 30% active
max_connect_errors=10 #from 5000, to better control hacker passwd guessing
innodb_print_all_deadlocks=1 # from OFF, if you ever have one, you need this data in error log
#### these are PER CONNECTION values driving your RAM footprint up the wall
#read_buffer_size  or REMOVE for default of 128K vs 4M RAM
#read_rnd_buffer_size or REMOVE for default of 256K vs 16M RAM
#join_buffer_size or REMOVE for default of 128K vs 128MB RAM 

O uso do MySQLCalculator.com ajudará você a ver quanta RAM você pode exigir se 40000 conexões simultâneas forem bem-sucedidas (o que provavelmente não acontecerá) - appx 6 Terabytes de RAM seriam necessários.

Para uma análise adicional dos resultados que refletem as mudanças implementadas, APÓS 7 dias de tempo de atividade, poste resultados de texto completos de

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;

e repost completa my.cnf.