оптимизация

Месяц назад в посте «Оптимизация 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 секунды.

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

Да уж, 9 лет занимаюсь веб-разработками, а в оптимизации сайтов и базы данных MySQL в частности, каждый раз нахожу для себя много нового. Проблема в том, что во время разработки сложно оценить все узкие места, они возникают когда в базе появляются тысячи, десятки тысяч или даже миллионы записей в таблицах.  Тут я собрал разные полезные фишки и советы, связанные с оптимизацией MySQL открытые мной в последнее время.

PROCEDURE ANALYSE()

Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по оптимальной длине/типу полей. Например SELECT * FROM table PROCEDURE ANALYSE().

Известно, что использование типа полей занимающего большую память делает более медленной работу с таблицами. Enum быстрее чем Varchar, varchar быстрее чем text, medium int быстрее чем int и т.д. Часто можно оптимизировать типы в зависимости от данных в таблице.

Определение не эффективных индексов

Индексы в таблицах — большое благо, но не стоит забывать что кроме того, что индексы ускоряют выборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), так как при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные часто обновляются/добавляются использование индексов должно быть сбалансированным.

С помощью этого хитрого запроса можно увидеть 10 самых малоэффективных индексов во всей базе данных:

SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME LIMIT 10;

Взято отсюда: http://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтобы увидеть статистику по всем индексам.

EXPLAIN — Определение используемых индексов и строк, которые нужно обработать MySQL базе данных для выполнения запроса.

Очень просто, добавляем к нашему запросу слово EXPLAIN. Например: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

Полный отказ от ORDER BY rand();

Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не используются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тысячах записей могут начаться проблемы. Разумно переписать код, чтобы все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей очень много, имеет смысл сделать кеширование — например делать случайную сортировку раз в час.

Отключить InnoDB если он не нужен

Это движки таблиц. MyISAM — быстрее и проще, InnoDB — умеет много полезного, например транзакции, отсутствие блокировок. Если все эти фишки не нужны, то кто-то советует оставаться на MyISAM, кто-то наоборот переходить на InnoDB. Я пока ещё этот вопрос для себя не решил и использую MyISAM.

В любом случае если InnoDB не используется стоит отключить его поддержку, т.к. сервер кушает лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

Медленные запросы

Некоторые запросы выполняются очень медленно. Скорей всего о большинстве них вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а сейчас там несколько десятков тысяч и т.д. Найти такие запросы, на которые стоит обратить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

log_slow_queries = /var/log/mysql/mysql-bin.log
long_query_time = 2

2 — это количество секунд, больше которых выполняемый запрос будет считаться медленным и добавляться в лог. Если добавить ещё строчку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не использованы индексы.

Затем перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с медленными запросами за последние 7 дней (по умолчанию).

Кеширование в MySQL

Стоит конечно писать скрпиты так, чтобы запросов в базу данных было как можно меньше, но часть ваших проблем на себя может взять MySQL. Правильная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и выполняться очень быстро. У меня например 75% запросов выполняется из кеша. Конечно, этот процент зависит от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки нужны разные настройки. Чтобы узнать что менять, смотрим следующий пункт.

Автоматический тюнинг

С помощью простой утилитки mysqltuner можно обнаружить самые основные узкие места в конфигурации MySQL.

Выполняем на сервере:

wget http://mysqltuner.com/mysqltuner.pl
chmod u+x mysqltuner.pl
./mysqltuner.pl

Далее нужно ввести root логин и пароль к MySQL серверу, а скрипт проанализирует данные и выведет советы в автоматическом режиме. Я меняю параметры в конфиге, на которые нужно обратить внимание, а затем через несколько дней смотрю опять.

Также полезные советы можно посмотреть в phpMyAdmin если нажать ссылку «Текущее состояние MySQL» на главной странице.

Минимизирование изменения таблиц

Добавление или изменения данных в таблицы приводит к тому, что перестраиваются все индексы и сбрасывается кэш таблицы. Хорошо, если в таблице немного записей. У меня в нескольких проектах есть таблицы в которых несколько миллионов записей, благодаря индексам выборки из таблиц происходят быстро, но при изменении таблицы и при одновременном обращении к ней, происходит резкое замедление выполнения выборок (запросы SELECT).

Это я вот к чему, иногда для оптимизации работы с базой данных имеет смысл переделать логику работы скриптов. Например, я сейчас делаю кеширование, чтобы все обновления сохранялись в небольшой временной таблице, а добавление всех данных в большую таблицу (и её изменение) происходило только раз в сутки.

Оптимизация таблиц

Иногда следует выполнять OPTIMIZE TABLE, т.к. таблицы со временем фрагментируются из-за изменения, добавления данных и скорость доступа к ним, со стороны дисковой системы сервера уменьшается.

Полезные ссылки по Оптимизации MySQL:

20 Советов по оптимизации MySQL
Сервер на стероидах
MySQL Optimization