Проблема Percona 5.6 InnoDB с неправильным использованием индексов

Я только что установил Percona 5.6 на свой новый сервер CentOS 6.4. Это быстрая машина с 32 ядрами xenon, 72 ГБ оперативной памяти, 8x SAS RAID 10. Все идет нормально

Мой старый сервер немного менее мощный, и на нем все еще работал MySQL 5.1. Так что это был настоящий апгрейд. Но у меня есть некоторые проблемы с InnoDB, кажется, он неправильно использует индексы в некоторых таблицах. Где на моей старой машине те же запросы выполнялись нормально.

Оба сервера имеют одну и ту же базу данных. Я сделал mysqldump на старой машине и импортировал его на новый сервер Percona 5.6. Индексы остались прежними. Оба сервера используют одни и те же настройки конфигурации my.cnf.

Элементы таблицы имеют индексы: item_id, item_format, item_private и содержат около 40 миллионов строк. Таблица форматов имеет индекс: format_id и содержит около 250 строк.

SELECT 
i.item_name, i.item_key, i.item_date, f.format_long
FROM
items i, formats f
WHERE 
i.item_format = f.format_id
AND
i.item_private = 0 
ORDER BY 
i.item_id DESC LIMIT 8

На моем старом сервере этот запрос занимает около 0.0003 seconds. На новом сервере он занимает 100 seconds.

Запрос с EXPLAIN на СТАРОМ сервере.

+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table |  type  | possible_keys |   key   | key_len |         ref          | rows |    Extra    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | i     | index  | item_format   | PRIMARY |       4 | NULL                 |    8 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | dbname.i.item_format |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

Запрос с EXPLAIN на НОВОМ [проблемном] сервере.

+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys |     key     | key_len |        ref         | rows |              Extra              |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | f     | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  219 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | item_format   | item_format | 4       | dbname.f.format_id | 3026 | Using where                     |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+

Вы можете видеть, что он использует временную и файловую сортировку. Видимо в этом причина медлительности.

Любая идея, как я могу решить эту проблему?


mysql innodb percona
person Mr.Boon    schedule 13.11.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Это звучит так: Ошибка № 70617. Постоянная статистика по умолчанию может привести к неожиданно длительному времени запроса.

Как бы то ни было, это не ошибка Percona, она также присутствует в версии сообщества MySQL 5.6.

Есть три возможных обходных пути:

  1. Используйте STRAIGHT_JOIN, чтобы дать оптимизатору подсказку не переупорядочивать ссылки на таблицы.

    SELECT STRAIGHT_JOIN
  i.item_name, i.item_key, i.item_date, f.format_long
FROM items i
INNER JOIN formats f
  ON i.item_format = f.format_id
WHERE i.item_private = 0 
ORDER BY i.item_id DESC LIMIT 8

    Я переписал ваш JOIN, чтобы использовать синтаксис SQL-92, который я рекомендую.

  2. Отключите новую постоянную статистику InnoDB, возвращаясь к поведению до версии 5.6.

    В вашем файле my.cnf:

    innodb_stats_persistent=0

  3. Обновите статистику оптимизатора InnoDB вручную после внесения значительных изменений в данные (например, после загрузки mysqldump):

    ANALYZE TABLE items;
ANALYZE TABLE formats;

PS: я работаю в Percona, и эту ошибку обнаружил мой коллега Justin Swanhart.

person Bill Karwin    schedule 15.11.2013