Recentemente migramos do MySQL 5.5 para 5.6 ( 5.6.44-86.0-log Percona Serverpara ser mais preciso) e temos um grande problema. Consultas aparentemente simples são executadas por minutos em vez de milissegundos em uma tabela com mais de 300 milhões de registros.
Esta é a tabela em questão:
CREATE TABLE `articles` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`feed_id` int(11) unsigned NOT NULL,
`date` double(16,6) NOT NULL,
`score` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `feed_id_date` (`feed_id`,`date`),
KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Sim, dateé doubleporque precisamos de precisão de microssegundos. Uma solução que ainda não exploramos é transformá-lo em bigint.
Aqui está uma consulta típica que falha:
mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826,6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
| 1 | SIMPLE | a | index | feed_id_date,date | feed_id_date | 12 | NULL | 339355570 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
1 row in set (0.00 sec)
Observe o número de linhas. É praticamente uma varredura de mesa completa. Essa consulta é executada por 3 a 5 minutos, o que é inaceitável para uma carga OLTP. Agora veja as duas consultas a seguir onde selecionamos as duas feed_idem questão uma a uma:
mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| 1 | SIMPLE | a | range | feed_id_date,date | feed_id_date | 12 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| 1 | SIMPLE | a | range | feed_id_date,date | feed_id_date | 12 | NULL | 473 | Using where; Using index |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
E, de fato, este é o número correto de linhas para cada feed_id:
mysql> SELECT COUNT(*) FROM articles a WHERE a.feed_id=6826;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM articles a WHERE a.feed_id=6827;
+----------+
| COUNT(*) |
+----------+
| 474 |
+----------+
1 row in set (0.00 sec)
Isso é muito intrigante para mim. Temos todos os tipos de combinações na INcláusula. Alguns usuários têm mais de 1.000 feed_idregistros que funcionam bem, mas em algumas situações dois registros na INcláusula são suficientes para fazer uma varredura completa da tabela.
No EXPLAINé visível que o problema só surge quando o typeis index, e não range. Em nossa instância antiga do MySQL 5.5 EXPLAIN, as mesmas consultas e o mesmo conjunto de dados sempre mostram o rangetipo e nunca tivemos esse problema.
Isso pode estar relacionado à configuração? Aqui está o meu my.cnf:
[mysqld]
skip-external-locking
skip-name-resolve
transaction-isolation = READ-COMMITTED
max_connections = 5000
max_user_connections = 4500
back_log = 2048
max_allowed_packet = 128M
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 8M
myisam_sort_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 0
key_buffer = 10M
thread_stack = 256K
thread_cache_size = 100
tmp_table_size = 256M
max_heap_table_size = 1G
query_cache_min_res_unit = 1K
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 46G
innodb_buffer_pool_instances = 32
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_file_per_table = 1
innodb_io_capacity = 50000
A VM possui 64 GB de RAM e armazenamento de 100k+ iops, mas isso certamente não está relacionado ao hardware.
