Долгое выполнение запросов в MySQL

Question

[antoo]

Добрый день.
Неожиданно для меня, один из проектов стал очень быстро развиваться и сейчас встала проблема с базой данных.
Используется связка PHP + MySQL.

Есть таблица, в ней уже ~20 млн записей %)
Структура таблицы:

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| task_id | int(9)        | NO   | PRI | NULL    |       |
| user_id | int(9)        | NO   | PRI | NULL    |       |
| checked | enum('0','1') | NO   | MUL | 0       |       |
| taken   | enum('0','1') | NO   | MUL | 0       |       |
+---------+---------------+------+-----+---------+-------+

Запрос:

SELECT DISTINCT `task_id` FROM `tasks_pending` WHERE `checked`='0' AND `taken`='0' LIMIT 50;

Проблема в том, что время выполнения запросов очень большое, ~3 минут, что недопустимо.

Начитался про индексы, расставил все что мог:


Первый индекс (task_id) убирать нельзя, он в основе лежит.
Машина довольно мощная и думаю, что должна тянуть быстрее: Intel® Core™ i7-2600, RAM: 16 GB, вся таблица должна влезать целиком.

Машина настраивалась скриптом mysqltuner, всегда его использовал и не испытывал проблем, пока не столкнулся с High-Load'ом.

EXPLAIN говорит следующее: скриншот, потому что не влезает
Подскажите пожалуйста, что я сделал неверно и как это исправить? Спасибо.

UPD: Огромное спасибо всем за советы, каждый совет хороший, поэтому лучшего ответа выбрать не могу, всех поблагодарил за помощь в профиле.

Answer 1

[rakot]

Попробуйте составной ключ checked, taken, task_id. Именно в таком порядке, может помочь. А вообще какая таблица InnoDB?

Comments

[antoo]

Попытка поставить индекс повесила весь сервер до отключки SSH, поднялся только после принудительной перезагрузки, это нормально?

[rakot]

Нет конечно, подвешенный сервер это всегда ненормально. Тем более машинка не дохлая. Так всё же таблица InnoDB? Это важно.

[antoo]

Да, InnoDB.

[rakot]

Если других процессов требующих память на сервере нет и не пользуетесь таблицами MyISAM, то innodb_buffer_pool_size можно выставлять в 60-80% доступной памяти, в вашем случае я бы выставил 12G. Остальные настройки желательно подбирать опытным путём, innodb_buffer_pool_size это самое быстрое, что можно поменять и даст эффект.

Еще замечание по InnoDB, первичный ключ всегда должен быть автоинкрементным полем, это связанно со страничной механикой хранения данных в InnoDB, если есть желание можете почитать об этом в книге «MySQL оптимизация производительности», но эту оптимизацию можно отложить на потом. Сейчас отключаем нагрузку на сервер, меняем настройки, перезапуск и добавляем ключ.

[antoo]

Поменял настройки, но добавление индекса опять затягивается на большое время, уже минут 40. Сервер не повис на этот раз.

Answer 2

[AGvin]

Вы используете DISTINCT, что само по себе плохо.

«Если LIMIT # указывается совместно с DISTINCT, MySQL остановится, как только найдет # уникальных строк.»

Советую прочитать про то, как MySQL оптимизирует DISTINCT:

• happy.kiev.ua/mysql/manual.ru_MySQL_Optimisation.html#DISTINCT_optimisation
  
• habrahabr.ru/post/125428/
  

Comments

[AGvin]

То есть, хотя вы и указали LIMIT 50, но в реале, MySQL может обработать уйму записей, пока не насобирает те самые 50 уникальных

[antoo]

Соответствующих условию checked='0', taken='0' всего около 150 обычно, так что не страшно даже если он переберет все.

[AGvin]

Так же, было бы полезно разбить таблицу habrahabr.ru/post/66151/

Answer 3

[CKOPOBAPKuH]

task_id_2 лишний, запрос который будет его использовать точно так же сможет использовать первую половину первичного индекса. так что task_id_2 дублирующий и его нужно убрать
индексы отдельно на checked и отдельно на taken не очень полезны.
индекс по (checked, taken, task_id) который вам посоветовал первый комментатор — действительно имеет смысл попробовать.

Answer 4

[script88]

1. Покажите конфиг MySQL,
2. Одним mysqltuner Вам не обойтись
3. Советую заменить MySQL на Perocona Server.

Comments

[antoo]

конфиг
о Perocona почитаю, спасибо

[script88]

1 innodb_flush_log_at_trx_commit советую выставить «2»
2 Временные таблицы в память загоняем tmpdir = /dev/shm
3 Включить skip_name_resolve
4 table-cache = 4096 и table_definition_cache = 4096 (если много – лучше несколько инстансов mysqld)
5 join-buffer-size = 32M (выделяется сразу на запросы без индексов)
6 query-cache-size = 128M и query-cache-limit = 128M ( У Вас стоит слишком много )
7 innodb_buffer_pool_size = по размеру таблиц
8 если buffer pool > 1Gb то innodb_buffer_pool_instances = 4
9 на быстрых дисках можно экспериментировать innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800
10 sync_binlog = 1 самый надежный
Пока что все.

[CKOPOBAPKuH]

советы по конфигурации вроде бы ок, но они для случая «большой поток быстрых запросов». в вашем текущем случае, автор, проблема не в конфигурации, а в конкретном запросе и в индексах, поэтому стоит вначале решить эту проблему и уже потом конфигурировать. но на основе статистики (которую percona server даёт в достаточном количестве), а не на основе случайного угадывания на кофейной гуще людьми из интернетов.
оптимальные настройки зависит от профиля нагрузки.

[script88]

Они не на случай большой поток быстрых запросов, а нацелены на highload проекты. И Данные взяты не с кофейной гущи как Вы написали, а на основе тех данных которые автор предоставил. А если в проекте появятся еще пару таких запросов, которые будут вызываться достаточно часто, то не удивительно будет то как mysql будет вылетать в своп. По этому Высказывание с данной точки зрения ИМХО лижбы ляпнуть.

[CKOPOBAPKuH]

следует разделять большой поток быстрых однотипных запросов и маленький поток тяжёлых сложных запросов. первое обычно называют highload, второе — high performance, hpc или big data. судя по словам «Проблема в том, что время выполнения запросов очень большое, ~3 минут», я предположил, что у автора нету потока в сотни запросов в секунду.

данных для того чтобы дать совет не с потолка здесь недостаточно. вы не знаете, какие ещё данные есть на сервере и какие ещё запросы используются. и сколько каких запросов. а от этого очень сильно зависит ответ. не говоря уже о то, что вы даже не знаете, ssd там или нет. большая ли write-нагрузка или нет. innodb_flush_log_at_trx_commit=2 не годится для абсолютно любых серверов.
и у меня достаточно много опыта работы с mysql, чтобы понимать, что ходить по QA, просить показать конфиг и после этого сразу же давать космически нелепый совет — это довольно глупое поведение. но я вас не виню, сам когда-то такой был: )

[script88]

ИМХО мне все равно какой у Вас опыт и что вы понимаете, совет я давал не Вам, а автору, который в свою очередь решит воспользоваться моим советом или нет, по поводу дисков пункт 9 я пояснил.
«не годится для абсолютно любых серверов» — пустые слова которые ни чем не подкреплены.

[CKOPOBAPKuH]

ничем не подкреплены? просто посмотрите, что значит значение 2, подумайте, возможно ли нарушение consistency, во всех ли ситуациях скорость важнее чем consistency, и если окажется что нарушение возможно и скорость не всегда важнее, то это значит, что ваш совет не универсальный.
это в целом правильный совет (и советы 5..8 в большинстве случаев тоже правильные), но они все не универсальные. вашу позицию я понял, свою вроде бы тоже высказал, так что давайте, наверное, прекращать уже этот бессмысленный спор.

Answer 5

[edogs]

Попробуйте повесить составной индекс на checked,taken или даже на task_id, checked,taken
а запрос можно попробовать поменять на запрос вида
select task_id from tasks_pending where checked=0 and taken=0 group by task_id limit 50;

Comments

[CKOPOBAPKuH]

это плохой совет.
если индекс будет (task_id, checked,taken), то запрос select task_id from tasks_pending where checked=0 and taken=0 group by task_id limit 50 только проиграет от использования этого индекса, причём сам он его использовать не будет (потому что mysql сравнительно умный) и вам придётся заставлять его делать плохое через форс индекс.

[MrMig]

Поясните, пожалуйста, про плохой совет. Почему в данном случае индекс такого вида — кака?

[Melkij]

Потому что в составных индексах порядок столбцов имеет первостепенную важность. Если правда интересно — рекомендую книгу «MySQL. Оптимизация производительности», Бэрон Шварц. Много правильных вещей объясняется.

Если коротко и только на этом примере:
Чтобы было возможно сгруппировать результат по индексу, все поля, участвующие в where должны стоять левее в индексе. Сперва исполняется where, только потом — group. Если индекс не охватывает полностью where, для группировки он непригоден.

[edogs]

Тут есть нюанс. Если выбирается _только_ task_id как в задаче ТС и есть составной индекс на task_id,checked,taken, то task_id может быть взят напрямую из индексного файла после where по checked,taken, не затрагивая саму таблицу. Это поведение кажется с 5.1 появилось. Поэтому конкретно для задачи ТС (где выбирается только task_id) тройной индекс может помочь. Можем ошибаться несколько, поэтому сказали _попробовать_, но смысл в этом определённо есть.

[Melkij]

edogs, ошибаетесь. Этот приём называется «покрывающий индекс» — когда данных в индексе достаточно для ответа на запрос и данные не поднимаются вовсе.
Но поля селекта обрабатываются только после group — и если task_id поставить на первое место индекса — это индекс будет вообще неприменим для этого запроса, т.к. не отвечает необходимости левостороннего размещения полей. Чтобы узнать значения в select, сперва надо обработать where и group.

[Melkij]

> тройной индекс может помочь.
Он определённо поможет. Сейчас у ТС идёт array merge двух индексов (тоже не самая простая штука), но группировка во временной таблице, при том группировка без малого 400тыс. записей — быстрой не может быть.
А индекс по checked, taken, task_id позволит группировать этот запрос по индесу, плюс, если запрашиваются только поля индекса — index scan, не стоящий почти ничего.
Так же подойдёт индекс по taken, checked, task_id
А вот task_id на первом месте — оптимизатор сразу откинет этот индекс как неподходящий, по нему ничего отфильтровать нельзя.

Answer 6

[myadzel]

Замените ENUM на INT(1) UNSIGNED, забудьте про ENUM.

Comments

[edogs]

О_О?
Вы реально предлагаете заменить 2 поля по 1 байту ( enum ) в таблице с 20 миллионами строк на 2 поля по 4 байта ( int )? По сути удвоить объем таблицы?
Если уж так хочется использовать целые то надо не тратить место, а наоборот — экономить на размере, поскольку checked и taken имеют всего 4 комбинации значений, их можно в tinyint загнать их легко.

[myadzel]

Вы тролль? Сколько лет вы курите маны?

int(1) и tinyint(1) ведут себя одинаково

[Melkij]

А вы?
int(любое что-то) — всегда 4 байта.
tinyint(любое что-то) — всегда 1 байт.
Это самое «что-то» — лишь подсказка, в частности, терминалу о том, сколько пробелов резервировать под вывод значения.

dev.mysql.com/doc/refman/5.1/en/numeric-type-attributes.html

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

[calg0n]

Я просто оставлю это здесь: www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/
ENUM по любому будет быстрее.

[Eugene]

А если без JOIN?

[calg0n]

Проверил. ENUM все равно быстрее. Без джойна естественно разница не так ощутима:
INT: 0.00050...0.00071 sec
ENUM: 0.00032...0.00057 sec

[Eugene]

10 раз померяли и взяли среднее?

[calg0n]

Если не верите — проверьте сами. ENUM удобнее, если нужно хранить фиксированный набор данных (штаты, страны, цвета и т.п.) и если их много. А так конечно если будет что-то типа ENUM('yes','no') то разницы с тем же BOOLEAN не будет.

Answer 7

[ComodoHacker]

Материализуйте результат запроса. Заведите отдельную таблицу, при поступлении задач вставляйте, при назначении удаляйте.