Итак, вы купили выделенный или виртуальный сервер (VPS). Разместили на нем один или множество своих сайтов. И потекли посетители. И кажется, все хорошо. Посещаемость растет, заработок растет. Однако что это – с увеличением посещаемости сайты начинают открываться все медленнее и медленнее. Все чаще и чаще посетители жалуются, что сайт не открывается. И нужно что-то делать.

Если данная ситуация вам знакома, тогда эта статья для вас. Из нее вы узнаете, как можно самостоятельно оптимизировать работу MySQL-сервера, повысив скорость работы своих сайтов.

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

Открываем панель управления сайтом
Ну что ж, время – деньги. А особенно когда веб-сервер работает кое-как, и ваши посетители просто в ярости. Поэтому сразу перейдем к практике.

В настройке MySQL-сервера нам поможет панель администрирования phpMyAdmin. Данная панель, наверное, является наиболее полным справочником о том, как следует оптимизировать сервер MySQL. Хотя, если эта панель не установлена на вашем хостинге, все приведенные ниже шаги можно выполнить непосредственно при помощи SQL-команд.

В качестве примера будет использован VPS сервер, арендованный на хостинге http://hostpro.ua. Итак, зайдите в панель управления вашим сервером. Для этого введите в адресной строке браузера IP-адрес вашего VPS-сервера и порт панели управления. Например, http://194.33.180.160:2086/.

После того, как панель управления запросит пароль для логина root, и вы его успешно введете, вы окажетесь в святая-святых вашего веб-сервера. В нашем случае (см. на рисунке) используется панель управления cPanel. В разделе SQL Services данной панели управления выберите ссылку phpMyAdmin.

Панель phpMyAdmin: ищем проблемные участки
Итак, панель phpMyAdmin мы открыли (убедитесь, что вы открыли ее от имени администратора сервера, а не от имени одного из установленных на сервере сайтов, иначе вы не сможете изменять глобальные переменные). Теперь переходим в раздел Состояние, и начинаем его пристально изучать.

В данном разделе отображаются статистические сведения о работе вашего MySQL-сервера. Первым делом посмотрите на значения следующих счетчиков:
- MySQL сервер работает … – количество времени с момента последнего перезапуска MySQL-сервера. Как правило, хорошо работающий MySQL-сервер никогда не перезапускается. Поэтому, если значение данного счетчика не вызывает у вас уважения, значит на сервере действительно есть проблемы, из-за которых сервер MySQL часто приходится автоматически перезапускать;
- таблица Соединения строчка Максимально одновременных – показывает максимальное количество одновременных соединений с базой данных MySQL, которое было зарегистрировано с момента последнего перезапуска веб-сервера; значение данного счетчика должно быть далеко до максимально возможного количества одновременных соединений (далее мы научимся изменять эту настройку), ведь если максимальное количество одновременных соединений будет превышено, новые посетители не смогут зайти на сайт (для них будет отображено сообщение too many connections);
- таблица Соединения строчка Неудачных попыток – показывает процент всех соединений с базой данных, которые установить не удалось; чем меньше это значение, тем лучше;
- таблица Соединения строчка Прерваны – показывает процент всех соединений с базой данных, которые не удалось довести до конца; чем меньше это значение, тем лучше.

Итак, запишите где-нибудь текущий процент неудачных и прерванных соединений. В дальнейшем мы сравним это значение со значением после оптимизации работы MySQL-сервера. И полученная разница покажет, насколько успешно мы смогли оптимизировать наш MySQL-сервер.

Теперь переходим к значениям переменных (в разделе Состояние, ниже). Нас интересуют значения, которые выделены красным цветом. Данным цветом панель phpMyAdmin сигнализирует о проблемах в работе вашего MySQL-сервера.

Следующий шаг после того, как проблемная переменная найдена – чтение описания, которое приводится для данной переменной. Например, переменная Created_tmp_disk_tables. На нашем сервере значение этой переменной – 268 k, что очень много. Читаем описание: «Количество временных таблиц, автоматически созданных сервером на диске, во время выполнения SQL-выражений. Если значение Created_tmp_disk_tables велико, следует увеличить значение переменной tmp_table_size, чтобы временные таблицы располагались в памяти, а не на жестком диске.». Что же из этого следует?

Панель phpMyAdmin: изменяем значения глобальных переменных
Настройка сервера MySQL чаще всего заключается в изменении глобальных переменных. Делается это при помощи SQL-запросов. Однако перед тем как изменить значение переменной, следует посмотреть, чему значение этой переменной равно сейчас.

В панели phpMyAdmin для этого достаточно перейти в раздел Переменные, после чего найти нужную переменную. При этом обратите внимание, что название переменной в панели phpMyAdmin пишется без знаков подчеркивания (см. рисунок). Вместо знаков подчеркивания используются пробелы.

После того, как значение переменной найдено, перейдите в раздел SQL панели phpMyAdmin, и измените значение данной переменной при помощи SQL-запроса вида SET GLOBAL переменная = значение.

Например, у нас в разделе Состояние красным цветом было выделено значение переменной Created_tmp_disk_tables. И в описании к этой переменной было указано, что нужно увеличить значение переменной tmp_table_size. Значит:
- в разделе Переменные найдите переменную tmp table size, и запомните ее значение; у нас значение данной переменной было равно 33,554,432, то есть, для временных таблиц выделялось 32 Мбайта;
- в разделе SQL вводим команду SET GLOBAL tmp_table_size = 48554432. То есть, увеличиваем буфер примерно до 48 Мбайт (размер всех буферов указывается в байтах).

Наиболее частые проблемные участки
Общий алгоритм оптимизации MySQL-сервера мы рассмотрели. Теперь давайте вкратце рассмотрим, какие переменные следует изменять при наличии тех или иных «красных значений».

Handler_read_rnd, Handler_read_rnd_next, Select_full_join. Красные значения этих переменных говорят о том, что таблицы баз данных MySQL не проиндексированы, либо что ваши сайты, при запросах к таблицам, не используют возможности индексов. Решение этой проблемы будет рассмотрено в следующих статьях.

Slow_launch_threads. Количество потоков, которые создавались очень медленно. Решение этой проблемы будет рассмотрено в следующих статьях.

Table_locks_waited. Указывает на проблемы с SQL-запросами, которые совершают ваши сайты. Чтобы решить эти проблемы, следует оптимизировать SQL-запросы.

Slow_queries. Количество запросов, которые выполнялись очень медленно. Для решения данной проблемы следует включить ведение лога медленных запросов, после чего оптимизировать все SQL-запросы, которые будут занесены в лог-файл. Эти действия в данной статье рассмотрены не будут.

Created_tmp_disk_tables. Увеличьте значение переменной tmp_table_size (размер буфера в байтах). Фактически, значение данного буфера определяет максимальный размер временной таблицы, которая будет храниться в оперативной памяти. То есть, если временная таблица имеет больший размер, чем tmp_table_size, она будет располагаться на жестком диске.

Sort_merge_passes. Увеличьте значение переменной sort_buffer_size (буфер для выполнения сортировки результатов запроса).

Opened_tables. Увеличьте значение переменной table_cache. В данном буфере хранятся дескрипторы всех открытых сервером MySQL таблиц.

Threads_created. Увеличьте значение переменной thread_cache_size.

Key_reads. Увеличьте значение переменной key_buffer_size. Данный буфер является общим для всех работающих сайтов. Рекомендуется устанавливать размер данного буфера, равный 30-35% от общего количества оперативной памяти на сервере.

Максимально одновременных. И последний совет. В разделе Переменные панели phpMyAdmin посмотрите значение переменной max connections. Если текущее количество одновременных соединений с сервером MySQL (таблица Соединения строчка Максимально одновременных) приближается к максимально возможному, следует увеличить значение этой переменной. Либо подумать над покупкой отдельного хостинга для части сайтов, работающих на данном хостинге.

Редактирование файла my.cnf
Изменение переменных при помощи SQL-запроса SET GLOBAL имеет один существенный недостаток – после перезапуска MySQL-сервера все сделанные вами изменения будут потеряны. Поэтому вместо ввода SQL-команд правильнее отредактировать содержимое файла my.cnf, который находится в каталоге /etc.

Достаточно найти в этом текстовом файле раздел [mysqld], и либо ввести в нем нужные переменные и их значения (в формате переменная = значение), либо отредактировать значения уже существующих переменных.

О редактировании данного файла читайте в будущих статьях на эту серию.

Что делать дальше
После того, как вы изменили значения нужных переменных, снова зайдите в раздел Состояние, и щелкните по ссылке Сбросить (перезапустить сервер MySQL мы не можем, ведь после перезапуска все сделанные нами изменения будут потеряны). Так вы обнулите все счетчики сервера. И нам останется лишь подождать день-два, чтобы посмотреть:
- снизился ли процент неудачных и прерванных соединений;
- исчезли ли «красные» значения переменных.

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