Оптимизация связки Nginx, Apache, PHP, MySql

Неожиданно поступила задача разобраться почему определенный сайт не работает столь быстро сколь хочется. В основе его CakePHP, в связке с Apache и MySQL. В статье описание процесса поиска узких мест и приведение в порядок на столько, на сколько это возможно.
Название сайта светить не буду — думаю, программисты сами узнают. Скажу лишь, что это приложение для социальной сети нагрузкой 70-150 тысяч посетителей в обычное время. Все усложняется тем, что периодически производится рекламная рассылка, которая привлекает около 200-300 тысяч посетителей за пару часов.
Итак, под катом описание всей борьбы на протяжении 4 дней.

Данная статья нацелена на 2 группы людей. Первая — это конечно же мы, админы, которым приходится разгребать подобное безобразие. Иногда вытаскивая практически за уши умирающий сервер. Вторая часть аудитории, которые я надеюсь тоже обязательно прочтут весь материал — это программисты. Друзья, забегая вперед, скажу: не было бы такого безобразия, если бы вы правильно проектировали свои проекты, многих казусов можно было бы избежать. Особенно зная на какую аудиторию вы пишите свой проект.

В моем распоряжении оказался сервер EX10, находящийся на площадке hetzner. Для тех кто не в курсе — это 64 Гб ОЗУ и 6-ядерный процессор. Назовем это ядром системы. Есть еще 2 сервера, один со статикой, другой с бекэнд базой. Небольшое приложение, InnoDB база данных на 500Мб.

С постоянной нагрузкой, как позже выяснилось, 70-100 онлайн сессий, ситуация следующая: загрузка CPU по 100% на каждое ядро. В топе, понятное дело, MySQL и Apache дерутся за ресурсы системы.

Nginx

Первой попыткой было снизить нагрузку сервера за счет кеширования выдачи Apache с целью снять с него выдачу статики.

Установил в очень простой конфигурации: он должен был все все файлы по маске пробовать забирать из определенной папки, если в ней файла нет — забирать его с проксируемого сервера, складывать в эту папку и выдавать клиенту.

Конфиг приведен не полностью, а только нужными для данного примера блоками.

К сожалению, не очень это привело к каким-то результатам.
Плюсов было всего 3:

• Apache перестал выдавать картинки, то есть стал чуть менее загружен
• Apache перестал напрямую общаться с внешним миром, следовательно у него можно было выключить keep-alive и уменьшить количество детей.
• Нашлось первое узкое место — абсолютно всеми запросами на сайт управляет PHP скрипт на который .htaccess переадресует любой запрос. Включая, вы не поверите, всю статику, и даже css.

MySQL

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

Так как имеем на руках InnoDB — я взял для исходной позиции конфигурационный файл из стандартной поставки my-innodb-heavy-4G.cnf

Ниже опишу параметры конфигурации, на которые следует обращать внимание на высоконагруженных проектах.

back_log = 5000
max_connections = 1600
Первый параметр отвечает за количество соединений, которые могут находится в очереди до того момента, как сервер перестанет отвечать на новые запросы. Второй — сколько подключений может быть принято сервером.
У меня эти значения достаточно большие, так как конкурентных сессий в среднем выходит до 1300. Ставить больше чем нужно — не стоит, так как каждое соединение может потребовать некоторого количества ОЗУ. Об этом позже.

max_connect_errors = 50
Тут просто — количество ошибок, которые может сделать клиент до того как получит дисконнект. Пришлось увеличить, в виду того что проект в стадии разработки и шансов некорректных запросов много.

table_cache = 2048
Открытие таблицы требует некоторых ресурсов, следовательно этот параметр отвечает за количество открытых таблиц ожидающих следующего соединения некоторое время после выполнения последнего.
Узнать надо ли его менять можно по переменной
SHOW GLOBAL STATUS LIKE 'Opened_tables';
Она не должна быть как можно меньше.
Тут хорошо написано: //www.mysql.ru/docs/man/Table_cache.html

max_allowed_packet = 16M
Максимальный размер пакета. Если не пользуемся большими BLOB, изменять не имеет смысла.

binlog_cache_size = 1M
Размер кеша бинарного лога, для транзакции. В официальной документации рекомендуют увеличивать если у нас большие транзакции.
dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_binlog_cache_size

max_heap_table_size = 64M
tmp_table_size = 64M
Насколько я понимаю, учитывается меньший из них. Параметр отвечает за максимальный размер временной таблицы, умещающейся в памяти. Если таблица его достигает она кладется на диск. Следовательно необходимо стараться что бы таблиц на диске создавалось как можно меньше. Посмотреть какое отношение временных таблиц к таблицам на диске на данный момент можно запросив
show status like '%tmp%tables';
www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/

sort_buffer_size = 8M
Что бы не обмануть никого, не возьмусь переводить. Уточню лишь, что в документации советуют смотреть на этот параметр только если
show status like ‘%Sort_merge_passes%’; больше нуля
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sort_buffer_size

join_buffer_size = 2M
Насколько понимаю, максимальный размер буфера, рассчитанного на операции не использующие индекс. Пока не трогал.
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_join_buffer_size

thread_cache_size = 4096
Максимальное количество треадов, которые остаются для повторного использования после выполнения запроса. Полезно держать достаточным для того что бы MySQL как можно меньше делал новых треадов и использовал старые. Понять эффективность данного параметра можно по отношению параметров Threads_created / Connections;
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_thread_cache_size

query_cache_size = 256M
query_cache_limit = 8M
Я думаю тут лучше чем мой соратник, автор этого перевода habrahabr.ru/post/41166/ никто не скажет.
Это наверное наиболее важный параметр, так что лучше перечитать.

thread_stack = 192K
Не возьмусь описывать назначение данного параметра, обращу лишь внимание на то что он тоже влияет на количество потребляемой ОЗУ, так как тоже выделяется на каждое соединение. Следовательно опять умножайте на max connections

long_query_time = 2
log_long_format
log-queries-not-using-indexes
У сервера MySQL есть очень удобный инструмент для оценки производительности БД. Это лог файл длинных запросов. По моему опыту это чаще всего неэффективные запросы или запросы неиспользующие индекс.
Советую с этим лог файлом идти к программерам.

key_buffer_size = 1G
Параметр отвечает за кеширование индексов в памяти, для оптимизации данного значения смотрите на Key_read_requests, Key_reads. Второй параметр отвечает за количество чтений с диска а не их буфера.
mysqltips.blogspot.com/2007/03/key-buffer.html

read_buffer_size = 1M
boombick.org/blog/posts/3 — После прочтения этого текста, не рискну что либо добавлять, так как не буду уверен в своей правоте.

read_rnd_buffer_size = 24M
Параметр влияет на скорость операций сортировки. К сожалению не нашел как оценить его эффективность.
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_rnd_buffer_size
www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
Параметры влияют на сортировку, так же не рискнул их менять. Увеличил первый предполагая, что это увеличит производительность сложных запросов.

sync_binlog = 0
В нашем случае означает не синхронизировать бинарных лог на диск через системные функции. Если параметр больше нуля, то сервер будет синхронизировать данные каждые n запросов.
dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog

innodb_buffer_pool_size = 4G
Увеличение этого параметра снижает количество дисковых операций. К сожалению тоже не нашел как его лучше замерить. Поскольку база небольшая решил его сильно не увеличивать. Где-то встречал совет, в случае большой БД увеличивать этот параметр до 70% ОЗУ.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

innodb_log_buffer_size = 32M
Если верить описанию, снижает дисковые операции при тяжелых транзакциях.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

innodb_log_file_size = 1024M
Если верить документации, то увеличение лог файла уменьшает загруженность IO операций диска, но увеличивает время восстановления в случае сбоев.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size

innodb_flush_log_at_trx_commit = 0
В значении 0, сброс буферов происходит раз в минутусекунду, а не после каждого инсерта.
dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_thread_concurrency = 14
Рекомендуют ставить чуть больше чем количество ядер.

innodb_sync_spin_loops=10
Насколько я понял, влияет на количество попыток доступа к заблокированным данным. Увеличивая данное значение мы можем потерять процессорное время, а уменьшая — надежность записи в БД.
www.mysqlperformanceblog.com/2006/09/07/internals-of-innodb-mutexes/
dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_sync_spin_loops

БД и оперативная память

Есть прекрасный скрипт на perl который дает базовое понимание того что надо поменять в БД. mysqltuner.pl/mysqltuner.pl
Очень часто данный скрипт ругается на максимальное потребление памяти сервером mysql.
Если посмотреть в исходник — вот как эта программа считает использование памяти:

per_thread_buffers = read_buffer_size +read_rnd_buffer_size + sort_buffer_size + thread_stack +join_buffer_size;
total_per_thread_buffers = per_thread_buffers * max_connections;
server_buffers = key_buffer_size +max_tmp_table_size;
server_buffers +=innodb_buffer_pool_size;
server_buffers +=innodb_additional_mem_pool_size;
server_buffers +=innodb_log_buffer_size;
server_buffers +=query_cache_size;
total_possible_used_memory =server_buffers + total_per_thread_buffers;

Мне было полезно понять где я не правильно указал значения.
Кстати, сразу кидаться занижать параметры, если скрипт ругается на большое потребление ОЗУ базой, не стоит. Так как многие отмечают, что это лишь теоретический показатель и БД может никогда не попытаться забрать себе столько памяти.

Оптимизация структуры БД.

Когда мы сделали все возможные настройки и все равно не получили хорошего результата — пора обратиться к slow-log файлу.
В стандартной комплектации к mysql есть приложение mysqldumpslow.
Запустив
mysqldumpslow -s c <путь к слоу-лог файлу>отсортированный по количеству вхождений список запросов к базе данных, которые были слишком долгими или не использовали индексы. Обычно добавление правильных индексов исправляет обе проблемы.

В большинстве случаев, когда вы видите в выводе этой программы большое количество долгих запросов(переменная count), копируете кусок этого запроса и ищете в тексте лог файла пример такого запроса.
Далее заходите в клиент БД и выполняете этот запрос, добавив вначале слово explain.
Про это так же можно прочитать подробнее вот тут:
habrahabr.ru/post/31072/

Так можно увидеть использует ли запрос индекс или нет.
Если таблице не хватает индексов их можно смело добавлять, хотя перебарщивать тоже не стоит. Индексы нужны тем столбцам которые используются после where и order. Начинайте с уникальных индексов на каждый стоблец. Иначе индекс может не сработать.
Вот тут можно узнать более подробно как эти индексы работают:
dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Скажу честно, после оптимизации примерно 5 ключевых запросов сервер смог обрабатывать 500-700 подключений вместо 50, а время выдачи php-страницы сократилось до 1с вместо 8с. При максимальной нагрузке время выдачи страницы составило 5с вместо 50с. (Имеются в виду замеры производительности с помощью Apache Benchmark c примерно 1000 потоков)

Еще немного об nginx.

После оптимизации заметил, что при больших нагрузках откидывает запросы больше определенного количества уже сам nginx, а не apache. При этом память и CPU не загружены.
Стал разбираться. Увидел в логах, что сервер nginx пытается открыть файлов больше, чем ему положено.
В ОС Suse, с которым мне пришлось столкнуться, за это ограничение отвечает файл
/etc/security/limits.conf
Дописал я туда вот такие строчки:

Рестарта сервера не понадобилось.

Apache2

Сильно изменять конфигурацию я пока не стал. Единственное что сделал — так это выключил keep-alive. Что бы апач мог спокойно выдать ответ и заняться следующим запросом в тот момент, когда nginx все еще отдает клиенту страницу по медленному каналу.

eAccelerator

Не забываем о том что у данного оптимизатора тоже есть некоторое количество параметров которые можно поменять.

Вот что изменил я:
eaccelerator.shm_size = «2096»
Размер виртуальной памяти в мегабайтах который можно использовать

eaccelerator.shm_only = «1» — использовать только ОЗУ и не использовать диск, в борьбе за io на софт рейде из 2 сата дисков решил сделать так.

Вот еще что будет полезно почитать:

habrahabr.ru/post/41166/
habrahabr.ru/post/108418/
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Отдельное спасибо моему другу, который помог вычитать статью и исправить целую гору грамматических и стилистических ошибок.

Вместо послесловия

Друзья, я понимаю что ошибок и заблуждений в выводах приведенных в этой статье целая куча.
Большая просьба к гуру, своими комментариями, помочь сделать статью лучше.

UPD, Резюме по комментам

Спасибо за столь большой интерес. Не ожидал.

хорошая ссылка, рекомендую к прочтению
www.percona.com/files/presentations/percona-live/dc-2012/PLDC2012-optimizing-mysql-configuration.pdf
спасибо Albertum

По поводу систем кеширования php:
да действительно eAccelerator не единственный вариант.
Есть так же APC, и да действительно его собираются встроить в PHP.
Что лучше судить не возьмусь, так как не делал достаточного количества тестов.

Альтернативы MySQL тоже присутствуют и их много.
Ключевые конечно:
mAriadb
percona