Месяц назад в посте «Оптимизация MySQL» я писал в основном общие советы по оптимизации, которые для меня были полезны и которые я собрал из нескольких источников. В этот раз пишу некоторые нюансы с которыми столкнулся в процессе оптимизации своих баз данных.
В первую очередь я обратил внимание на те параметры, которые отображаются красным в PhpMyAdmin, и на которые обращает внимание tuning-primer.sh.
tuning-primer.sh — хороший скрипт, анализирует логи и параметры MySQL и даёт советы по оптимизации, мне он понравился больше чем mysqltuner.pl
Для установки выполняем:
wget http://day32.com/MySQL/tuning-primer.sh
chmod 755 tuning-primer.sh
Потом запускаем:
./tuning-primer.sh
Большое значение Created_tmp_disk_tables
В процессе работы MySQL для каждого соединения постоянно создаются временные таблицы, большое значение Created_tmp_disk_tables (у меня было 45% от общего числа создаваемых) означает, что временные таблицы создаются не в памяти, а в временных файлах на диске, что замедляет работу MySQL. За выделение памяти для временных таблиц отвечают параметры tmp_table_size и max_heap_table_size в конфиге. Я увеличивал значения этих параметров, но количество создаваемых таблиц на диске не уменьшилось.
Проблема оказалось была вот в чём: если в таблицах используются поля типов TEXT (TEXT, TINYTEXT, MEDIUMTEXT …) или BLOB, то таблица не может быть размещена в памяти, а только на диске. Эти типы полей могут содержать в каждом поле большой объём текста или данных, и в MySQL данные физически не хранятся в самой таблице, а в поле содержится ссылка на сами данные.
Раньше для текстовых данных с длиной поля больше 255 символов альтернативы особой не было, всегда приходилось использовать поле типа TEXT. Но, начиная с версии 5.0.3 тип поля VARCHAR может хранить в себе 65535 байт (до этого только 255 байт). Для всех видов SQL операций, работа с полем VARCHAR происходит быстрее, чем с полями типа *TEXT, но самое главное, что временные таблицы с полями VARCHAR хранятся в памяти.
После того, как я сконвертировал все поля типов TEXT в VARCHAR, значение created_time_disc_tables уменьшилось, и теперь таблицы создаваемые на диске составляют 8% от общего количества, т.е. улучшение в 5 с половиной раз.
Правда, тут есть небольшое ограничение. Одна строка данных (сумма размеров всех полей) в MySQL таблице не может быть больше 65535 байт, т.е. нельзя создать в таблице два поля VARCHAR размером 65000 байт, например. Можно создать два поля размером 32767 байта, или одно поле 32767 и два по 16388.
Кэш запросов и высокое значение Qcache_lowmem_prunes
Кэш запросов может неплохо помочь в случае слабо оптимизированных скриптов, когда часто выполняются одни и те же выборки SELECT. Если запрос уже находится в кэше, то MySQL не обращается к таблицам, а берёт его прямо из кэша, что очень быстро (т.к. кэш в памяти). У меня в моих базах данных из кэша берутся 90% всех запросов.
Qcache_lowmem_prunes — показывает количество запросов которые были удалены из кэша из-за нехватки памяти. Если не хватает размера кэша, то MySQL оставляет в памяти только самые популярные запросы, а редкие из него удаляет. Чтобы уменьшить большое количество Qcache_lowmem_prunes я сначала пытался постепенно увеличивать размер кэша запросов, доведя его до 768 мегабайт. Это не помогло, Qcache_lowmem_prunes не уменьшилось, процент эффективности кеша также не вырос.
Затем я более подробно изучил, как работает кэш, и поменял тактику. Дело в том, что если таблица меняется (происходит INSERT, DELETE или UPDATE), то все запросы из кэша для этой таблицы удаляются. Если кэш большого размера, то на его обслуживание нужно тоже время (т.к. память не может быть освобождена моментально). Часто обновляемые таблицы, по сути, только замедляют работу с кэшем, т.к. запросы почти сразу же удаляются и только фрагментируют кэш.
Для всех таблиц которые часто обновляются, я прописал директиву SQL_NO_CACHE (например SELECT SQL_NO_CACHE * FROM table), принудительно ограничивающую кэширование. Также для больших таблиц, везде где возможно, я прописал названия выбираемых полей в SELECT (например SELECT SQL_NO_CACHE id,name FROM table), что уменьшило размер возвращаемых данных, и увеличило количество запросов, которые могут поместиться в кэше. Несмотря на то, что сейчас размер кеша 256 Мб, количество Qcache_lowmem_prunes резко уменьшилось.
Борьба с Table_locks_waited и перевод некоторых таблиц на InnoDB
Table_locks_waited — показывает количество событий, когда запрос на блокировку таблиц был выполнен через определенный период ожидания. MyISAM — основной движок таблиц в MySQL имеет один большой недостаток, когда происходит изменение таблицы (кроме INSERT в конец таблицы), то она блокируется на чтение, а когда происходит чтение из таблицы, то она блокируется на запись. Другими словами запросы SELECT и UPDATE, DELETE, INSERT не могут проходить одновременно, и когда они пытаются делать это одновременно, какому то запросу приходиться ожидать и возникает Table_locks_waited событие.
Для небольших таблиц блокировка занимает доли секунды и практически не заметна, но ситуация меняется, если в таблице сотни тысяч или миллионы записей. Каждое обновление занимает заметное время, и в случае большой нагрузки происходит лавинообразный рост блокировок, в логе медленных запросов появляется много безобидных SELECT’ов, выполняющихся по несколько секунд, хотя обычно они выполняются за тысячные доли секунды, а некоторые страницы сайтов начинают медленно отдаваться.
Самое обидное, что эти ситуации сложно отследить при создании сайта, я столкнулся с этим через полтора года после того как некоторые таблицы выросли до миллиона записей. Решений этой проблемы несколько.
Первый способ, кардинальный, переделать логику работы с базой данной так, чтобы она обновлялась как можно реже, либо выборки SELECT шли из одной таблицы, а обновления INSERT, UPDATE, DELETE в другую, и например, раз в сутки они синхронизировались.
Второй способ, для таблиц MyISAM есть неплохое решение (если в таблицу происходят только частые вставки INSERT) использовать оператор DELAYED (Например INSERT DELAYED INTO table VALUES (…) ). При таких вставках они не выполняются в данный момент времени, а вставляются отложено с низким приоритетом, т.е. MySQL ждёт пока выполнятся все SELECT запросы и таблица освободится для записи. Такая вставка происходит реже, MySQL собирает сразу несколько таких запросов и вставляет их все вместе, что снижает нагрузку. Большой минус — для очень загруженных таблиц, запрос вставки может вообще никогда не произойти (если таблица не освободится), также в случае падения MySQL сервера все ожидающие запросы пропадут.
По моему опыту, даже не на очень загруженных таблицах данные могут вставлены только через несколько минут (запросы которые ожидают, или долго выполняются можно видеть в реальном режиме времени с помощью утилиты mytop). Из-за своих недостатков, DELAYED подойдет только для не очень важных данных, которые сразу не нужны, и которые не так страшно потерять, например логи.
Третий способ, перевести такие таблицы на InnoDB — более продвинутый движок для таблиц, который поддерживает транзакции, но самое главное, блокировка в InnoDB работает не на уровне всей таблицы, а на уровне отдельных строк. Т.е. могут происходить одновременные запросы на запись и чтение в таблице.
Плюсы InnoDB:
- Поддержка транзакций (если не нужны, то не является плюсом)
- Меньше вероятность потерять данные при падении сервера (только если используются бинарные логи)
- Блокировки на уровне строки (а не всей таблицы, как для MyISAM)
- Выдерживает большую нагрузку при больших размерах таблиц
- Показывает большую производительность при одновременных запросах на чтение и запись
Минусы InnoDB:
- Индексы в 2-3 раза большего размера, чем для MyISAM
- Нельзя восстановить таблицу из файла (без бинарного лога), нужно регулярно делать бэкапы, чтобы не потерять данные
- Для быстрой работы InnoDB нужно существенно больше оперативной памяти (переменная innodb_buffer_pool_size)
- Точное количество элементов в таблице, в определённый момент времени можно узнать только примерно (запросы COUNT (*) для всей таблицы очень медленно выполняются)
- Время выполнения вставки/обновления больше чем для таблиц MyISAM.
InnoDB или MyISAM это тема для большого холивара, в интернете я так и не нашёл единого мнения что лучше, у кого то серьёзные аргументы за MyISAM, у кого то за InnoDB . Эти движки таблиц имееют свои различия, плюсы и минусы, и выбор больше зависит от архитектуры построенной базы данных. Для себя я вывел правило: если таблица редко обновляется, то лучше использовать MyISAM, если таблица имеет большой размер и часто обновляется, то лучше использовать InnoDB. Конечно, блокировки случаются и в InnoDB — но для моих данных это случается на несколько порядков реже, а если и случаются то время блокировки незначительное (несколько миллисекунд).
Для быстрой работы InnoDB самое главное это размер буфера innodb_buffer_pool_size, т.к. InnoDB сильно кеширует индексы и данные в памяти. Размер памяти для этой переменной зависит от размера таблиц, я у себя выделил 1 гигабайт памяти (у меня InnoDB таблицы вместе с индексами занимают 600-700 мегабайт).
Для максимально быстрой работы InnoDB устанавливаем innodb_flush_logs_at_trx_commit равным нулю. Если переменная равна нулю, то логи сохраняются не после каждой транзакции, что уменьшает надёжность, но даёт самую быструю скорость. Если innodb_flush_logs_at_trx_commit = 1, то логи сохраняются после каждой транзакции, что крайне медленно, если значение равно двум — то раз в 1-2 секунды.
После месяца исследований и тестов, удалось решить все проблемы с базой данных, всё забегало очень шустро, пропали почти все медленные запросы. Ещё раз убедился в поговорке «век живи, век учись».