Eu apenas configurei um novo servidor para um grande banco de dados MySQL de cerca de 70GB. Esse banco de dados é regularmente escrito por processos automáticos que precisam escrever o mais rápido possível em seus dados. Antes, tínhamos um servidor com SSD de 120 GB, mas mudamos para HDD porque a quantidade de dados está ficando cada vez maior.
O problema é que, quando os processos estão em execução, a CPU aumenta para mais de 150% e as operações de gravação ficam mais lentas ...
O servidor tem uma CPU de 4 núcleos - 8 threads, 32 GB de RAM e 2x2TB HDD com um controlador RAID LSI 2108 (RAID 1). O MariaDB 10.0 é o único servidor em execução nesta máquina. OS é Ubuntu 14.04 instalado recentemente. Tem um servidor escravo um pouco menos poderoso, é por isso que eu habilitei o log binário.
Eu ajustei a configuração do InnoDB assim:
query_cache_type = OFF
tmp_table_size = 1G
max_heap_table_size = 1G
transaction-isolation = READ-COMMITTED
binlog_format = row
innodb_log_file_size = 6G
innodb_buffer_pool_size = 24G
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 300
innodb_io_capacity_max = 400
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout= 240
innodb_use_fallocate = 1
innodb_random_read_ahead = 1
innodb_flush_neighbors = 0
innodb_checksum_algorithm = crc32
innodb_fast_shutdown = 0
skip-innodb_doublewrite
Enquanto os processos estão sendo executados, o log lento está cheio dessas linhas (a consulta destacada é aleatória, pode ser inserida, atualizada ou excluída em qualquer tabela):
# User@Host: user_prod[user_prod] @ xxxxx [xxx.xxx.xxx.xxx]
# Thread_id: 177018 Schema: user_prod QC_hit: No
# Query_time: 18.318539 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 1
SET timestamp=1413450644;
update 'pages_objects' set 'status_comments' = 'idle', 'updated_at' = '2014-10-16 09:10:57' where 'id' = '331667763652878';
Estou preso e não encontro ajuda no Google ... Você tem alguma idéia de onde o problema poderia vir? Obrigado :)
Edit: Uma amostra de processlist enquanto minha CPU está chegando a 250% (inferno, sim!):
+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+-------------------------------- ----------------------------------------------------------------------+----------+
| 378 | user_prod | server.ip:46542 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 2985 | user_prod | server.ip:60257 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 4001 | user_prod | server.ip:38046 | user_prod | Execute | 0 | preparing | select * from 'pages_users' where 'user_id' = '1247143319' and 'page_id' = '169449309753828' limit 1 | 0.000 |
| 6533 | user_prod | server.ip:54548 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 7582 | user_prod | server.ip:59995 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 13179 | user_prod | server.ip:33221 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 14624 | user_prod | server.ip:41004 | user_prod | Execute | 0 | Writing to net | select * from 'pages_users' where 'user_id' = '100000010909375' and 'page_id' = '476930419093906' li | 0.000 |
| 54642 | user_prod | server.ip:45540 | user_prod | Execute | 0 | update | insert into 'pages_users' ('user_id', 'page_id', 'updated_at', 'created_at') values ('1318873669', ' | 0.000 |
| 55244 | user_prod | server.ip:47407 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 55426 | user_prod | server.ip:47983 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 107408 | user_prod | server.ip:57303 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 204661 | user_prod | server.ip:45568 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 204717 | user_prod | server.ip:51573 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 204795 | user_prod | server.ip:52682 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 204844 | user_prod | server.ip:53290 | user_prod | Sleep | 0 | | NULL | 0.000 |
| 204972 | user_prod | server.ip:54717 | user_prod | Sleep | 20 | | NULL | 0.000 |
| 204999 | user_prod | server.ip:55069 | user_prod | Sleep | 13 | | NULL | 0.000 |
| 205006 | user_prod | server.ip:55159 | user_prod | Sleep | 11 | | NULL | 0.000 |
| 205020 | user_prod | server.ip:55377 | user_prod | Sleep | 7 | | NULL | 0.000 |
| 205026 | user_prod | server.ip:55443 | user_prod | Sleep | 5 | | NULL | 0.000 |
| 205028 | user_prod | server.ip:55524 | user_prod | Sleep | 3 | | NULL | 0.000 |
| 205031 | user_prod | server.ip:55569 | user_prod | Sleep | 2 | | NULL | 0.000 |
| 205032 | user_prod | server.ip:55573 | user_prod | Sleep | 2 | | NULL | 0.000 |
| 205034 | user_prod | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
24 rows in set (0.00 sec)
Eu investigaria se table_open_cache limit não é o motivo do gargalo. Talvez os threads estejam simplesmente bloqueando, causando tempo de execução longo e alta CPU.
Além disso, ativar o cache de consulta pode ajudar, como a troca de query_cache_type para ON e a configuração de query_cache_size para 1G.
Se isso ajudar, você também pode tentar jogar com a quantia, porque diferentes valores podem dar resultado diferente, até mesmo a regra "mais é melhor" às vezes não se aplica.
Tags mysql mariadb ubuntu-14.04