Сегодня разработчики продолжают разрабатывать и развертывать приложения, используя архитектуру LAMP (Linux®, Apache, MySQL и PHP/Perl). Однако администраторы серверов часто не имеют достаточного контроля над самими приложениями, поскольку их написал кто-то другой. В этой серии из трех частей обсуждается ряд проблем конфигурации сервера, которые могут повлиять на производительность приложений. Эта статья, третья и последняя часть этой серии, посвящена настройке уровня базы данных для достижения максимальной эффективности.
Что касается настройки MySQL,
существует три метода повышения скорости работы сервера MySQL. Порядок эффективности от низкого к высокому:
Замените проблемное оборудование. Настройте параметры процесса MySQL. Оптимизируйте запрос.
Замена проблемного оборудования часто является нашей первой задачей, прежде всего потому, что базы данных могут потреблять много ресурсов. Но это решение заходит далеко. Фактически, вы часто можете удвоить скорость вашего центрального процессора (ЦП) или диска и увеличить объем памяти в 4–8 раз.
Второй метод — настроить сервер MySQL (также известный как mysqld). Настройка этого процесса означает правильное распределение памяти и информирование mysqld о том, какому типу нагрузки она будет подвергаться. Ускорение работы диска не так важно, как уменьшение количества необходимых обращений к диску. Аналогично, обеспечение корректной работы процесса MySQL означает, что он тратит больше времени на обслуживание запросов, чем на фоновые задачи, такие как работа с временными дисковыми таблицами или открытие и закрытие файлов. Настройка mysqld является темой этой статьи.
Лучший подход — убедиться, что запрос оптимизирован. Это означает, что к таблице применяются соответствующие индексы, а запросы пишутся таким образом, чтобы в полной мере использовать возможности MySQL. Хотя в этой статье не рассматривается настройка запросов (тема, которая рассматривалась во многих книгах), она настраивает mysqld для составления отчетов о запросах, которые могут нуждаться в настройке.
Хотя этим задачам назначен порядок, вам все равно необходимо обратить внимание на настройки оборудования и mysqld, чтобы правильно настроить запрос. Ничего страшного, если машина работает медленно, я видел, как очень быстрые машины выходили из строя из-за большой нагрузки при выполнении хорошо продуманных запросов, потому что mysqld был занят большим количеством загруженной работы и не мог обработать запрос.
Регистрация медленных запросов.
На сервере SQL таблицы данных хранятся на диске. Индексы предоставляют серверу возможность находить определенные строки данных в таблице без необходимости поиска по всей таблице. Когда необходимо выполнить поиск по всей таблице, это называется сканированием таблицы. Вообще говоря, вам может потребоваться получить только часть данных в таблице, поэтому полное сканирование таблицы потребует большого количества операций ввода-вывода на диске и, следовательно, много времени. Эта проблема усугубляется, когда данные необходимо объединить, поскольку необходимо сравнить несколько строк данных по обе стороны соединения.
Конечно, сканирование таблицы не всегда вызывает проблемы; иногда эффективнее прочитать всю таблицу, чем выбрать подмножество данных (для принятия этих решений используется планировщик запросов в серверном процессе). Если индекс используется неэффективно или вообще не может использоваться, это будет замедлять запросы, и эта проблема будет становиться более значимой по мере увеличения нагрузки на сервер и размера таблицы. Запросы, выполнение которых занимает больше времени, чем заданный диапазон времени, называются медленными запросами.
Вы можете настроить mysqld для регистрации этих медленных запросов в журнале медленных запросов с соответствующим названием. Затем администраторы просматривают этот журнал, чтобы определить, какие части приложения требуют дальнейшего изучения. В листинге 1 показана конфигурация, которую необходимо выполнить в my.cnf, чтобы включить медленную регистрацию запросов.
Листинг 1. Включение журнала медленных запросов MySQL
[mysqld]; включить журнал медленных запросов, по умолчанию 10 секунд log-slow-queries; запросы журнала, занимающие более 5 секунд, long_query_time = 5; журнал запросов, которые не используют индексы, даже если они занимают меньше, чем long_query_time MySQL 4.1 и более поздние версии onlylog-; запросы без использования индексов
Эти три параметра используются вместе для регистрации запросов, выполнение которых занимает более 5 секунд и не использует индексы. Обратите внимание на предупреждение о том, что запросы к журналам не используют индексы: вы должны использовать MySQL 4.1 или выше. Журналы медленных запросов сохраняются в каталоге данных MySQL и называются имя_хоста-slow.log. Если вы хотите использовать другое имя или путь, вы можете использовать log-slow-queries = /new/path/to/file в my.cnf, чтобы добиться этого.
Чтение журналов медленных запросов лучше всего выполнять с помощью команды mysqldumpslow. Указав путь к файлу журнала, вы можете увидеть отсортированный список медленных запросов, а также количество раз, когда они встречаются в файле журнала. Очень полезная функция заключается в том, что mysqldumpslow удаляет все указанные пользователем данные перед сравнением результатов, поэтому разные вызовы одного и того же запроса считаются как один, что может помочь определить запрос, который требует больше всего работы;
Кэширование запросов
Многие приложения LAMP в значительной степени полагаются на базы данных, но выполняют одни и те же запросы снова и снова. Каждый раз, когда выполняется запрос, база данных должна выполнить одну и ту же работу — проанализировать запрос, определить, как его выполнить, загрузить информацию с диска и вернуть результаты клиенту. MySQL имеет функцию, называемую кэшем запросов, которая сохраняет результаты запросов (которые будут использоваться позже) в памяти. Во многих случаях это значительно улучшит производительность. Проблема, однако, в том, что кэширование запросов отключено по умолчанию.
Добавьте query_cache_size = 32M в /etc/my.conf, чтобы включить кэш запросов размером 32 МБ.
Мониторинг кэша запросов
После включения кэша запросов важно понять, эффективно ли он используется. MySQL имеет несколько переменных, на которые можно посмотреть, чтобы понять, что происходит в кеше. В листинге 2 показано состояние кэша.
Листинг 2. Отображение статистики кэша запросов
mysql> ПОКАЗАТЬ СТАТУС КАК 'qcache%';+-------------------------+------------+ |Имя_переменной |Значение |+-------------------------+------------+| Qcache_free_blocks | | Qcache_free_memory || Qcache_hits || Qcache_inserts || Qcache_lowmem_prunes || Qcache_total_blocks |+----- -------- -------- ---+----------------+8 рядов в наборе (0,00 сек)
Объяснение этих пунктов приведено в Таблице 1.
Таблица 1. Описание имени переменной кэша запросов MySQL
Qcache_free_blocks Количество соседних блоков памяти в кэше. Большое число указывает на наличие фрагментов. FLUSH QUERY CACHE дефрагментирует кэш для получения свободного блока.
Qcache_free_memory Освободить память в кеше.
Qcache_hits увеличивается каждый раз, когда запрос попадает в кеш.
Значение Qcache_inserts увеличивается каждый раз при вставке запроса. Коэффициент промахов — это количество попаданий, разделенное на количество вставок, вычитая это значение из 1, чтобы получить коэффициент попаданий; В приведенном выше примере примерно 87% запросов попадают в кеш.
Qcache_lowmem_prunes Сколько раз в кэше заканчивалась память, и его приходилось очищать, чтобы освободить место для дополнительных запросов. Это число лучше всего наблюдать в течение длительного периода времени; если число растет, это может указывать на серьезную фрагментацию или плохую память. (Вышеупомянутые free_blocks и free_memory могут сказать вам, в каком случае это происходит).
Qcache_not_cached Количество запросов, которые не подходят для кэширования, обычно потому, что они не являются операторами SELECT.
Qcache_queries_in_cache Количество запросов (и ответов), кэшированных в данный момент.
Qcache_total_blocks Количество блоков в кэше.
Часто разницу можно увидеть, отображая эти переменные с интервалом в несколько секунд, что может помочь определить, эффективно ли используется кеш. Запуск FLUSH STATUS может сбросить некоторые счетчики, что может быть очень полезно, если сервер работает какое-то время.
Очень заманчиво использовать очень большой кеш запросов и рассчитывать на кэширование всего. Поскольку mysqld должен выполнять обслуживание кеша, например, выполнять обрезку при нехватке памяти, сервер может увязнуть в попытках управлять кешем. Как правило, если FLUSH QUERY CACHE занимает много времени, значит, кэш слишком велик.
Обеспечение соблюдения ограничений
Вы можете установить ограничения в mysqld, чтобы гарантировать, что загрузка системы не приведет к истощению ресурсов. В листинге 3 показаны некоторые важные настройки, связанные с ресурсами, в my.cnf.
Листинг 3. Настройки ресурсов MySQL
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
Максимальное количество подключений регулируется в первой строке. Как и в случае с MaxClients в Apache, идея состоит в том, чтобы обеспечить выполнение только того количества подключений, которое разрешено службой. Чтобы определить максимальное количество подключений, установленных в данный момент на сервере, выполните SHOW STATUS LIKE 'max_used_connections'.
Строка 2 сообщает mysqld о прекращении любых соединений, которые простаивали более 10 секунд. В приложении LAMP время, необходимое для подключения к базе данных, обычно равно времени, которое требуется веб-серверу для обработки запроса. Иногда, если нагрузка слишком велика, соединение зависает и занимает место в таблице соединений. Если у вас несколько интерактивных пользователей или вы используете постоянные соединения с базой данных, устанавливать меньшее значение не рекомендуется!
Последняя строка — безопасный метод. Если у хоста возникают проблемы с подключением к серверу и он пытается много раз повторить попытку, прежде чем сдаться, хост будет заблокирован и его нельзя будет запустить до тех пор, пока не будет выполнена FLUSH HOSTS. По умолчанию 10 сбоев достаточно, чтобы вызвать блокировку. Изменение этого значения на 100 даст серверу достаточно времени для устранения проблемы. Если соединение не может быть установлено после 100 повторных попыток, то использование более высокого значения не поможет, и соединение может вообще не установиться.
Буферы и кэширование
MySQL поддерживает более 100 настраиваемых настроек, но, к счастью, освоение некоторых из них удовлетворит большинство потребностей. Чтобы найти правильные значения для этих настроек, вы можете просмотреть переменные состояния с помощью команды SHOW STATUS, которая может определить, работает ли mysqld так, как мы ожидаем. Память, выделенная для буферов и кэшей, не может превышать объем памяти, доступной в системе, поэтому настройка обычно требует некоторого компромисса.
Настраиваемые параметры MySQL могут применяться ко всему процессу mysqld или к отдельным клиентским сеансам.
Настройки на стороне сервера
Каждую таблицу можно представить в виде файла на диске, который необходимо сначала открыть, а затем прочитать. Чтобы ускорить процесс чтения данных из файлов, mysqld кэширует эти открытые файлы до максимального числа, указанного в table_cache в /etc/mysqld.conf. В листинге 4 показан способ отображения действий, связанных с открытием таблицы.
Листинг 4. Отображение действий, открывающих таблицы
mysql> ПОКАЗАТЬ СТАТУС LIKE 'open%tables';+---------------+-------+| Имя_переменной |+-------- -------+-------+| Открытые_таблицы || Открытые_таблицы |+---------------+------- +2 строки в наборе (0,00 сек.)
В листинге 4 показано, что на данный момент открыто 5000 таблиц и необходимо открыть 195 таблиц, поскольку в кэше нет доступных дескрипторов файлов (поскольку статистика была очищена ранее, может быть только 5000 открытых таблиц). . Если значение Opened_tables быстро увеличивается при повторном запуске команды SHOW STATUS, это указывает на недостаточную скорость попадания в кэш. Если Open_tables намного меньше параметра table_cache, значение слишком велико (но наличие места для роста никогда не является плохой вещью). Например, используйте table_cache = 5000, чтобы настроить кеш таблицы.
Подобно кэшу таблиц, существует также кэш потоков. mysqld порождает потоки по мере необходимости при получении соединений. На загруженном сервере, где соединения быстро меняются, кэширование потоков для последующего использования может ускорить первоначальное соединение.
В листинге 5 показано, как определить, достаточно ли кэшировано потоков.
Листинг 5. Отображение статистики использования потоков
mysql> ПОКАЗАТЬ СТАТУС LIKE 'threads%';+-------------------+--------+| Имя_переменной |+---- --------------+--------+| Threads_connected || Threads_created || Threads_running 3 |+---- ---------------+--------+4 ряда в наборе (0,00 сек)
Важным значением здесь является Threads_created, это значение увеличивается каждый раз, когда mysqld необходимо создать новый поток. Если это число быстро увеличивается при выполнении последовательных команд SHOW STATUS, вам следует попытаться увеличить кэш потока. Например, для достижения этой цели вы можете использовать thread_cache = 40 в my.cnf.
Ключевой буфер содержит индексный блок таблицы MyISAM. В идеале запросы на эти блоки должны поступать из памяти, а не с диска. В листинге 6 показано, как определить, сколько блоков было прочитано с диска, а сколько — из памяти.
Листинг 6. Определение эффективности ключевых слов
mysql> показать статус, например '%key_read%';+-------------------+-----------+| Имя_переменной Значение |+ ------------------+----------+| Key_read_requests || Key_reads 98247 |+------- -----------+-----------+2 ряда в наборе (0,00 сек)
Key_reads представляет количество запросов, поступающих на диск, а Key_read_requests — общее количество. Коэффициент промахов — это количество запросов на чтение, попавших на диск, деленное на общее количество запросов на чтение — в данном случае примерно 0,6 промахов в памяти на каждые 1000 запросов. Если количество обращений к диску превышает 1 на 1000 запросов, вам следует рассмотреть возможность увеличения буфера ключевых слов. Например, key_buffer = 384M установит размер буфера 384 МБ.
Временные таблицы можно использовать в более сложных запросах, где данные необходимо сохранить во временную таблицу перед дальнейшей обработкой (например, в предложении GROUP BY, в идеале временная таблица создается в памяти); Но если временная таблица становится слишком большой, ее необходимо записать на диск. В листинге 7 приведены статистические данные, связанные с созданием временных таблиц.
Листинг 7. Определение использования временных таблиц
mysql> ПОКАЗАТЬ СТАТУС КАК 'created_tmp%';+--------------------------+-------+| Имя_переменной | +-------------------------+-------+| Created_tmp_disk_tables || Created_tmp_files || +---------------------+-------+3 ряда в наборе (0,00 сек) |
Created_tmp_tables будет увеличиваться каждый раз, когда используется временная таблица; Created_tmp_disk_tables также будет увеличиваться для дисковых таблиц; Для этого соотношения не существует строгих правил, поскольку оно зависит от используемого запроса. Наблюдение за Created_tmp_disk_tables с течением времени покажет вам соотношение созданных дисковых таблиц, и вы сможете определить эффективность вашей установки. И tmp_table_size, и max_heap_table_size контролируют максимальный размер временных таблиц, поэтому убедитесь, что оба значения установлены в my.cnf.
Настройки для каждого сеанса
Следующие настройки специфичны для каждого сеанса. Будьте очень осторожны при установке этих чисел, поскольку при умножении на количество возможных соединений эти параметры представляют собой большой объем памяти! Вы можете изменить эти числа в рамках сеанса с помощью кода или изменить эти настройки в my.cnf для всех сеансов.
Когда MySQL должен выполнить сортировку, он выделяет буфер сортировки для хранения строк данных, считываемых с диска. Если данные, подлежащие сортировке, слишком велики, их необходимо сохранить во временный файл на диске и отсортировать еще раз. Если переменная состояния sort_merge_passes велика, это указывает на активность диска. В листинге 8 показаны некоторые сведения счетчика состояния, связанные с сортировкой.
Листинг 8. Отображение статистики сортировки
mysql> ПОКАЗАТЬ СТАТУС LIKE "sort%";+-------------------+---------+| Имя_переменной |+--- ----------------+---------+| Sort_merge_passes || Диапазон_сортировки || Сортировать_строки || Сортировать_скан |+-- ------------------+---------+4 ряда в наборе (0,00 сек)
Если sort_merge_passes велик, это означает, что вам нужно обратить внимание на sort_buffer_size. Например, sort_buffer_size = 4M устанавливает размер буфера сортировки равным 4 МБ.
MySQL также выделяет некоторую память для чтения таблицы. В идеале индекс предоставляет достаточно информации для чтения только тех строк, которые вам нужны, но иногда запросу (плохо спроектированному или из-за характера данных) необходимо прочитать большой объем данных из таблицы. Чтобы понять это поведение, вам необходимо знать, сколько операторов SELECT было выполнено и сколько раз нужно было прочитать следующую строку данных в таблице (а не получить доступ непосредственно через индекс). Команда для достижения этой функциональности показана в листинге 9.
Листинг 9. Определение коэффициента сканирования таблицы
mysql> ПОКАЗАТЬ СТАТУС КАК "com_select";+---------------+--------+| Имя_переменной |+--------- ------+--------+| Com_select |+----------+--------+1 строка в наборе (0,00 сек) mysql> ПОКАЗАТЬ СТАТУС КАК "handler_read_rnd_next";+-----------------------+----------- +| Имя_переменной Значение | |+-----------------------+-----------+| Handler_read_rnd_next |+- ----- ------------------+----------+1 ряд в наборе (0,00 сек)
Handler_read_rnd_next / Com_select приводит к соотношению сканирования таблицы - в данном случае 521:1. Если значение превышает 4000, следует проверить read_buffer_size, например read_buffer_size = 4M. Если это число превышает 8 миллионов, пришло время обсудить настройку этих запросов с разработчиками!
3 Основные инструменты
Хотя команда SHOW STATUS может быть очень полезна при понимании конкретной настройки, вам также потребуются некоторые инструменты для интерпретации больших объемов данных, предоставляемых mysqld. Есть три инструмента, которые я считаю важными; ссылки на них можно найти в разделе «Ресурсы».
Большинству системных администраторов знакома команда top, которая обеспечивает постоянно обновляемое представление о процессоре и памяти, используемых задачами. mytop эмулирует top; он обеспечивает просмотр всех подключенных клиентов и выполняемых ими запросов. mytop также предоставляет текущие и исторические данные об эффективности буфера ключевых слов и кэша запросов, а также статистику по выполненным запросам. Это полезный инструмент, позволяющий увидеть, что происходит в вашей системе (скажем, в течение 10 секунд), вы можете просмотреть информацию о состоянии сервера и показать любые соединения, вызывающие проблемы.
mysqlard — это демон, подключенный к серверу MySQL, отвечающий за сбор данных каждые 5 минут и сохранение их в базе данных циклического перебора в фоновом режиме. Существует веб-страница, на которой отображаются такие данные, как использование кэша таблиц, эффективность ключевых слов, подключенные клиенты и использование временных таблиц. Хотя mytop предоставляет моментальную информацию о состоянии сервера, mysqlard предоставляет долгосрочную информацию о состоянии. В качестве бонуса mysqlard использует часть собранной информации, чтобы дать несколько советов по настройке сервера.
Еще один инструмент для сбора информации SHOW STATUS — mysqlreport. Его отчетность гораздо сложнее, чем у mysqlard, поскольку необходимо анализировать каждый аспект работы сервера. Это отличный инструмент для настройки вашего сервера, поскольку он выполняет соответствующие вычисления переменных состояния, чтобы определить, какие проблемы необходимо устранить.
Заключение
В этой статье представлены некоторые базовые знания о настройке MySQL и завершается серия из трех частей, посвященная настройке компонентов LAMP. Настройка в основном предполагает понимание того, как работают компоненты, определение правильности их работы, внесение некоторых корректировок и повторную оценку. Каждый компонент — Linux, Apache, PHP или MySQL — имеет различные требования. Понимание каждого компонента в отдельности может помочь устранить узкие места, которые могут замедлить работу вашего приложения.