Я пытаюсь повысить производительность сервера, и ясно, что MySQL является основным источником проблемы. Однако устранить ее очень сложно. Я использую журнал медленных запросов для нацеливания на определенные типы запросов, но реальная проблема заключается в том, что MySQL используется процессами Java, процессами PHP и заданиями cron (которые обычно также являются процессами PHP, но выполняются через командную строку, а не через Apache. )
Обычно, когда сервер становится медленным, я запускаю некоторые команды, такие как «ps» или «top», чтобы попытаться найти виновника, но даже если я знаю, что виноват MySQL, я не знаю, какую из трех «сфер» я упомянутое может действительно вызывать замедление. Другими словами, я хотел бы как-то разбить его и увидеть, что, например, 80% требований, предъявляемых к MySQL в тот момент, связаны с запросами из PHP, в то время как только 20% исходят из Java. Поскольку у меня есть автоматические задачи и периодические события, запускаемые из обеих "сфер", трудно изолировать их последствия только методом проб и ошибок.
Все находится на одном сервере, поэтому все запросы MySQL поступают с localhost. Могу ли я также, возможно, «пометить» запросы, добавить к ним комментарии или иным образом добавить какие-то метаданные, чтобы я мог позже проанализировать эти теги на предмет относительной нагрузки?
Я сомневаюсь, что есть способ получить такую информацию, но если кто-то может помочь предоставить способ дальнейшей разбивки нагрузки MySQL таким образом, чтобы помочь идентифицировать источник этих запросов (идентификаторы процессов тоже могут работать), это очень поможет. Спасибо!
Я бы предложил использовать Прокси MySQL. С помощью MySQL Proxy вы можете перехватывать и регистрировать каждый запрос, поступающий на ваш сервер.
Более простой подход был бы запущен mysqlbinlog для анализа двоичных файлов журналов, созданных MySQL (см. man mysqlbinlog).
В любом случае, если вы еще этого не сделали, было бы неплохо запустить mysqltuner чтобы увидеть, есть ли какие-либо очевидные проблемы или узкие места (например, необычное количество запросов, выполняющих JOIN без индексов).
Одна из возможностей - убедиться, что разные процессы используют разные учетные данные для подключения к mysql. Я почти уверен, что mysql регистрирует имя пользователя, выполнившего запрос, в журнале запросов.
Вы пробовали войти в службу mysql и выпустить ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ или используя mytop чтобы увидеть, какие запросы выполняются?
SHOW FULL PROCESSLIST предоставляет список всех запросов и задач, которые MySQL выполняет в настоящее время. Его необходимо запускать от имени пользователя с привилегиями PROCESS или SUPER в базе данных. mysqladmin также имеет аргумент списка процессов.
mytop очень хорошо показывает, что сервер делает на постоянной основе. Думайте об этом как о чем-то вроде вершины для MySQL.
Оба они расскажут вам, какие команды выполняются, откуда они берутся и как долго MySQL пытается их обслуживать.
Как только вы вооружитесь этой информацией, вы можете решать, что делать. Если система привязана к записи, вам может потребоваться разделить данные. Если вы читаете в переплете, есть вещи, которые могут помочь. Правильная индексация таблиц во многих случаях может значительно повысить производительность чтения. Если у вас много одинаковых запросов, может помочь добавление кеша. Добавление ведомых устройств чтения также может улучшить производительность. Кэширование и добавление ведомых устройств увеличит производительность, но добавит дополнительную сложность вашей сети и приложению.
Да, вы можете, но для профилирования без использования клиентского профилировщика требуется, чтобы вы передавали запросы через что-то, что может их перехватить - в этом случае это будет MySql-прокси. На их веб-сайте есть инструкции о том, как настроить его для поиска и профилирования запросов, а затем вы можете запускать объяснения и другие операции против тех, которые, похоже, тратят много времени.
Другой способ, который не так сложен, но может работать, - установить очень низкое значение тайм-аута медленного запроса, а затем убедиться, что вы включили журнал медленных запросов. Таким образом вы увидите все, что занимает больше нескольких секунд. Это не поможет вам, если разработчики (или их ORM) выполнят кучу основных запросов, а затем, конечно же, суммируют данные в приложении.
ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ;
Это показывает вам процессы и время выполнения. Чтобы различать источник, вы можете использовать разные логины для каждой «области» ИЛИ добавлять область во всех запросах. например, это действительный запрос:
/ * php * / ВЫБРАТЬ * ИЗ элементов;
как есть:
/ * java * / ВЫБРАТЬ * ИЗ элементов;
и т.п.
Но вы по-прежнему не видите загрузку системы, вызванную запросами. В любом случае это может быть полезно, и вы можете взломать свои инструменты мониторинга, чтобы графически отображать данные, чтобы искать корреляции с вашей производительностью.
Скрипт mk-query-digest от Maatkit не будет классифицировать по источнику, но он даст вам много информации о том, какие запросы потребляют больше всего ресурсов.
Мы помечаем запросы комментариями, как предложено выше. Затем мы регулярно записываем вывод FULL PROCESSLIST в файлы. Когда у нас возникают проблемы с производительностью, мы импортируем эти файлы в базу данных и меняем данные, чтобы увидеть длинные запросы. По крайней мере, для нас они вызывают наибольшую нагрузку.
Я был бы осторожен с запуском MySQL Proxy в производственной системе. Я обнаружил, что он иногда запирается. Кроме того, у вас есть еще одна часть инфраструктуры, которую нужно отслеживать.
Вы изучаете использование профилирования mysql?
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
mysql>set profiling=1;
<run your queries with profiling enabled>
mysql>show profiles;
Результатом будет таблица с идентификатором запроса, продолжительностью запроса и строкой запроса. Подобно этому:
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+
Отсюда вы можете разбить этот запрос дальше с помощью
mysql>show profile for query <QueryID>;
Это даст вам разбивку того, сколько времени запрос потратил на каждом этапе выполнения. Вы также можете подробнее узнать, сколько процессорного времени тратится на запрос:
mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+------------+------------+------------+
| (initialization) | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock | 0.00000700 | 0.00000000 | 0.00000000 |
| setup | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table | 0.00195800 | 0.00000000 | 0.00100000 |
| After create | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table | 0.11289400 | 0.00199900 | 0.00000000 |
| end | 0.00004600 | 0.00000000 | 0.00000000 |
| query end | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+
Я предлагаю прочитать информационную страницу, чтобы узнать, какая именно информация вам нужна, поскольку инструмент довольно подробный, но это должно помочь вам найти узкие места в демоне mysql.
Первый шаг - не использовать во всех ваших процессах одного и того же пользователя. Особенно, если все они проходят через localhost. Даже для запросов, поступающих из одного и того же инструмента (как в php), пусть в cron используют отдельного пользователя от тех, которые работают через apache.
Как только это будет сделано, вы можете использовать mysqlbinlog для анализа медленных журналов (у него есть несколько отличных полей для упорядочивания запросов / нормализации запросов) или даже использовать что-то более продвинутое, например профилировщик, чтобы найти более медленные запросы