MySQL / Введение в PERFORMANCE_SCHEMA

16 августа 2011 - Иван Синёв

Много камней было брошено в адрес MySQL, ввиду отсутствия возможности трассировки сессий и снятия stats pack отчетов, показывающих какие именно события нагружают базу данных. Начиная с версии 5.5 MySQL наконец-то озадачился необходимостью решения данной проблемы и выставил прототип, который в будущем, возможно, приведет к созданию аналогичных инструментов в MySQL. Сегодняшний мой рассказ будет о таком мощном (к сожалению пока только для разработчиков MySQL) инструменте как PERFORMANCE_SCHEMA. Итак выставляем performance_schema=ON в конфигурационном файле my.cnf, и приступаем к изучению её ограниченных, но уже крайне интересных возможностей.

Для начала немного теории

Во избежании наступления на грабли, которые ожидают вас на пути работы с данной схемой, сделаю небольшой экскурс в теорию. Если вы уже знаете как устроена данная схема, но пока не понимаете как это можно применить, можете перейти сразу к практической части.
Во-первых. PERFORMANCE_SCHEMA является статической. Что это значит для нас? и почему так произшо? MySQL понемногу учится на своих ошибках и после работ на INFORMATION_SCHEMA, и в частности над представлением INFORMATION_SCHEMA.PROCESSLIST, они изменили схему работы с системными представлениями. Использование комманды SHOW PROCESSLIST (и ей подобных) может вызвать зависание сервера #56299, #61186. Причина этого недоразумения кроется в баге #42930, в котором четко написано, что запуск этой команды вызывает установку блокировки LOCK_thread_count. Это приводит к тому, что MySQL не может: подключить новую сессию, отключить отработавшую, создать новый бинарный лог и т.д. так как блокировка данного мьютекса, является критической для всего движка. Это было сделано ввиду возможности динамического выделения памяти потоками. Для того чтобы в один прекрасный момент при запросе параметров сессий представление не обратилось к освобожденному участку памяти, получив ошибку и завалив весь сервер. В исходном коде PERFORMANCE_SCHEMA не используются команды по динамическому выделению памяти типа malloc. Выделение памяти производится всего один раз, во время старта сервера. По этому все параметры данной схемы невозможно изменить в runtime. С одной стороны это дает возможность работы данной схемы без блокировок, с другой некоторые проблемы с её конфигурированием, часть из которых обходится инструментами включенными в сам PERFORMANCE_SCHEMA ENGINE. После запуска вы не сможете полностью отключить данный движок, без перезагрузки сервера.
Во-вторых. Так как эта функциональность производит мониторинг всей БД, то естественно она потребляет некоторые ресурсы. Объем потребляемых ресурсов зависит от конфигурации схемы, которую можно посмотреть выполнив запрос.
show variables like 'performance%';
+---------------------------------------------------+---------+
| Variable_name | Value |
+---------------------------------------------------+---------+
| performance_schema | ON |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_instances | 1000 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_thread_classes | 50 |
+---------------------------------------------------+---------+

Первые параметры — instances являются самыми важными, именно достаточное их количество позволит полностью мониторить всё что происходит с БД. Однако тут главное не перестараться, ибо пара mutex+rwlock создается на каждый 16-ти килобайтный блок в buffer_pool. То есть если на БД выделен достаточно большой объем оперативной памяти, то вы выжрете дофига ресурсов, а если вы зададите эти параметры слишком маленькими, то вы не сможете отслеживать все системные процессы. Как понять что вы выставили их корректно скажу чуть ниже.
По параметрам handle думаю все понятно из названия. Это максимальное количество открытых таблиц и файлов, которые вы сможете отмониторить.
Величина параметров size зависит от нагрузки на вашу БД. Её вам придется подбирать самостоятельно. К примеру если вы, запуская запрос не успеваете увидеть его статистику в events_waits_history_long, то вам стоит задуматься над увеличением этого параметра.
Параметры classes по идее не сильно важны, так как они показывают максимальное количество типов отслеживаемых объектов. Тут надо описаться на разработчиков плагинов. Если они сочли нужным, включить какие-то мьютексы в мониторинг, они обычно указывают их количество. К примеру для движка InnoBD таких классов всего 35.
Важно заметить, что не надо жадничать при выставлении этих параметров, так как если вы выставите их впритык, то при поиске пустых элементов массива PERFORMANCE_SCHEMA будет жутко тормозить. Что по идее логично, ибо найти пустой элемент в массиве который на 90% пуст проще чем найти его в массиве который на 90% полон.
Итак после включения PERFORMANCE_SCHEMA она начинает жрать ресурсы … иногда очень сильно. К примеру вот результат с одной из промышленных БД.
show engine performance_schema status;
+--------------------+--------------------------------------------------------+-----------+
| Type | Name | Status |
+--------------------+--------------------------------------------------------+-----------+
...
| performance_schema | events_waits_history.memory | 1200000 |
...
| performance_schema | events_waits_history_long.memory | 1200000 |
...
| performance_schema | mutex_instances.memory | 136000000 |
...
| performance_schema | rwlock_instances.memory | 200000000 |
...
| performance_schema | file_instances.memory | 6240000 |
...
| performance_schema | events_waits_summary_by_thread_by_event_name.memory | 17280000 |
...
| performance_schema | (pfs_table_share).memory | 24400000 |
...
| performance_schema | performance_schema.memory | 394468704 |
+--------------------+--------------------------------------------------------+-----------+

Если у вас есть свободных 400Mb оперативки — включайте!
По мимо расхода оперативной памяти, данная схема так же понижает скорость выполнения запросов. По некоторым из оценок, со всеми подписчиками (что это расскажу ниже) деградация производительности достигает порядка 25% на чтение и 19% на запись, а просто выставление параметра performance_schema=ON без мониторинга какой либо деятельности 8% на чтение. А ввиду того, что просто так выключить вы её не сможете — будьте бдительны.
Третье, на что следует обратить внимание — это качество мониторинга. Как я уже писал выше, если у вас большая БД, то ряд параметров выставленных по умолчанию, вам может не подойти. Узнать об этом досадном недоразумении вы сможете выполнив комманду
show status like "performance%";
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Performance_schema_cond_classes_lost | 0 |
...
| Performance_schema_thread_instances_lost | 0 |
+------------------------------------------+-------+

Если какой-то из счетчиков принимает значение выше ноля, то надо увеличивать соответствующий параметр.
Четвертое. Если вы уже поняли, что именно вам следует мониторить, и какие именно параметры за это отвечают, то можно сэкономить часть ресурсов системы. Смело запускайте update этой таблицы.
select * from setup_consumers;
+----------------------------------------------+---------+
| NAME | ENABLED |
+----------------------------------------------+---------+
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | YES |
...
| file_summary_by_event_name | YES |
| file_summary_by_instance | YES |
+----------------------------------------------+---------+

Или же этой
select * from setup_instruments;
+------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock | YES | YES |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync | YES | YES |
...
| wait/io/file/innodb/innodb_temp_file | YES | YES |
+------------------------------------------------------------+---------+-------+

Пятое. Если вы поменяли параметры конфигурации, запросы или провели другие оптимизации, и хотите оценить результат — не обязательно перезапускать сервер. Можно просто странкейтить все summary таблицы в PERFORMANCE_SCHEMA, для получения свежих результатов и часок подождать, для накопления статистики и очистки всех текущих и исторических таблиц.
Последнее на что хотелось бы обратить внимание. При анализе производительности, часто возникает желание посмотреть ожидание по всем событиям не просто в каких-то абстрактных единицах, а в живых и ощутимых секундах. Это легко сделать.
select *from setup_timers;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE |
+------+------------+

С одной лишь оговоркой. Если вы работаете вы циклах процессора — то у вас максимально возможные точные значения, если же вы переключаетесь на секунды, то получите нехилую погрешность, величина которой уникальна для каждой машины и может быть выявлена из таблицы.
select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE | 2395388059 | 1 | 72 |
| NANOSECOND | 1000000000 | 1000 | 513 |
| MICROSECOND | 1000000 | 1 | 207 |
| MILLISECOND | 1038 | 1 | 225 |
| TICK | 101 | 1 | 531 |
+-------------+-----------------+------------------+----------------+

Из практики могу сказать, что такие (~500) оверхэды на конвертацию циклов в наносекунды, сожрут порядка 5% от всего времени выполнения тестов. Однако я видел машины на которых эти оверхэды составляют более 12%. Так что при интерпретации результатов в реальном времени будьте аккуратны.

И так с настройкой мы вроде разобрались. Теперь приступим к тому, где же нам можно посмотреть данные по нагрузке и главное как?
show tables;
+----------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------+
...
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
...
| file_summary_by_event_name |
| file_summary_by_instance |
...
| threads |
+----------------------------------------------+

threads — содержит ссылки на процессы, которые по прежнему живут в INFORMATION_SCHEMA.PROCESLIST, если процесс умер — то тред все равно будет жить, некоторое время, по этому если будете джойнится, используйте внешнее соединение.
events_waits_current (history, hisotry_long) — содержит самые последние события ожидания, размер таблиц задается параметрами. Это самая нужная таблица, для тех кто столкнулся с непонятными блокировками или же пытается понять какой участок кода тормозит, ибо она содержит ссылку на строку исходника.
events_waits_summary — содержат информацию позволяющую получить среднюю температуру по больнице. Самые интересны таблицы для администратора БД.
file_summary — статистика по вводу выводу, позволяет получить самые горячие таблицы. Тут необходимо понимать, что если вы не выставили innodb_file_per_table=1 при создании БД, то ничего путного для движка InnoDB вам тут увидеть не удастся.
Во всех таблицах эвентов, содержится так же вся информация о вводе выводе. Так как скорее всего она будет продублирована в file_summary в более понятном виде — то при запросах из них лучше игнорировать event_name like ‘wait/io/file/%’.
С теорией вроде бы закончили, перейдем к практике. Для чего же все это надо и какие полезности можно получить из этой схемы.

Практика

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

select substring_index(file_name, '/', -1) file_name, event_name, count_read, count_write from file_summary_by_instance where COUNT_READ+COUNT_WRITE > 0 order by COUNT_READ+COUNT_WRITE desc limit 30;
+-------------------------------------+--------------------------------------+------------+-------------+
| file_name | event_name | count_read | count_write |
+-------------------------------------+--------------------------------------+------------+-------------+
| proc.MYD | wait/io/file/myisam/dfile | 102716781 | 0 |
| ib_logfile1 | wait/io/file/innodb/innodb_log_file | 0 | 1008958 |
| innodb1 | wait/io/file/innodb/innodb_data_file | 43106 | 958070 |
| event.MYD | wait/io/file/myisam/dfile | 633053 | 126470 |
| #sql149f_301db7_b.frm | wait/io/file/sql/FRM | 180012 | 0 |
...
| event.MYI | wait/io/file/myisam/kfile | 0 | 126466 |
| #sql149f_337a12_3.frm | wait/io/file/sql/FRM | 71372 | 0 |
...
| job_events#P#job_events_201108.ibd | wait/io/file/innodb/innodb_data_file | 8 | 34662 |
...
| olap_transactions_hourly_amount.frm | wait/io/file/sql/FRM | 3637 | 8264 |
+-------------------------------------+--------------------------------------+------------+-------------+

На что стоит обратить внимание:
proc.MYD — справочник который содержит коды процедур, ничего не пешем, много читаем. Стоит настроить драйвер, чтобы процедуры кэшировались и включить пул соединений.
ib_logfile1 — ну тут мы безсильны, это все таки InnoDB.
innodb1 — либо мы создавали InnoDB таблицы до выставления innodb_file_per_table=1 либо ждем MySQL 5.6 для более детальных разъяснений что он туда пишет.
event.MYD, event.MYI — ну о качестве этого продукта я уже высказывался.
tmp/#sql149f_301db7_b.frm и иже с ними — ну кто-то активно использует временные таблицы — надо искать более детальную информацию в эвентах. По событиям можно заджойнится на processlist и если вам повезет вы поймаете их с поличным.
job_events#P#job_events_201108.ibd — одна из самых горячих таблиц innodb. Много пишем ничего не читаем, стоит подумать об архитектуре, все ли мы верно сделали.
olap_transactions_hourly_amount.par — ну тут вроде все честно, каждый час меняем партицию, уровень чтений примерно соответствует уровню записи.
Это просто пример анализа, у вас конечно же ряда таблиц может и не быть, но на то вы и администратор, чтобы знать что у вас делается с каждой таблицей.
Самые горячие блокировки

select event_name, source, sum(timer_wait) timer_wait from events_waits_history_long where event_name not like 'wait/io/file%' group by event_name, source order by 3 desc limit 30;
+------------------------------------------------------+--------------------+---------------+
| event_name | source | timer_wait |
+------------------------------------------------------+--------------------+---------------+
| wait/synch/cond/sql/COND_queue_state | event_queue.cc:765 | 1998358450083 |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock | mi_rnext.c:43 | 82397115 |
...
| wait/synch/mutex/sql/LOCK_plugin | sql_plugin.cc:744 | 607986 |
+------------------------------------------------------+--------------------+---------------+

(информация в этой таблице на живой БД очень быстро обновляется, по этому для вычисления именно средней температуры по больнице лучше использовать events_waits_summary_by_thread_by_event_name, там нет конкретной строки кода, однако там информация более объективна, пример приведен именно для того чтобы показать — качайте исходники)
Разрыв между первым и вторым местом пугает. Идем в исходники.
if (!thd->killed)
{
if (!abstime)
mysql_cond_wait(&COND_queue_state, &LOCK_event_queue);
else
mysql_cond_timedwait(&COND_queue_state, &LOCK_event_queue, abstime); // вот она наша строка
}

Ну тут вроде ничего страшного это просто слип. Но согласитесь, копаться в исходниках MySQL это совсем не камильфо. При возникновении тупиковых ситуаций без этого никак, по этому обрисую топ самых частых проблем возникающих по мьютексам.
В последних версиях MySQL появилась продвигаемая фича innobd_adaptive_hash_index. Как вы знаете поиск в B-Tree операция не оптимальная. По этому если ваша таблица небольшая, и индекс может поместиться в оперативную память MySQL создает копию этого индекса в виде хэш мапы в данном буфере. Поиск при этом становится максимально быстрым. Однако данное решение имеет один подводный камень. У этого кэша есть всего один мьютекс синхронизации. Заход в эту критическую секцию выполняется как при чтении так и при записи для любой таблицы или же индекса, по этому если у вас есть большой объем конкурирующих транзакций и этот мьютекс становится горячим вы увидите в топе запроса.
| wait/synch/rwlock/innodb/btr_search_latch | btr0sea.c:XXX | 183897 |
Если это так — стоит задуматься об отключении этого параметра.
InnoDB использует атомарные опперации вместо потоков для имплементации мьютексов и блокировок на чтение запись. Такая реализация считается на данный момент самой оптимальной для многопроцессорных систем. Вместо блокировок, на сколько я понимаю, там инкрементально увеличиваются какие-то счетчики. Если вы заметили в топах ожидания типа wait/synch/mutex/innodb/rw_lock_mutex или wait/synch/mutex/innodb/thr_local_mutex не поленитесь зайти в исходники и проверить находится ли строчка этих ожиданий под секцией #ifdef INNODB_RW_LOCKS_USE_ATOMICS или же у вас билд собранный более ранним компилятором и вы идете в #else /* INNODB_RW_LOCKS_USE_ATOMICS */ и тем самым не используете всей прелести данной технологии.
В одном из последних плагинов InnoDB был введен специальный мьютекс wait/synch/mutex/innodb/flush_list_mutex. Как известно все грязные блоки после изменения некоторое время висят в оперативной памяти, и лишь по прошествии некоторого времени сбрасываются на диск. Ранее для синхронизации сброса использовался мьютекс буферного пула, однако это было неудачное решение и для этого процесса была создана отдельная критическая секция. Это позволяет лучше масшабироваться, и отслеживать процесс записи на диск.
Если вы видите большое количество ожиданий мьютекса wait/synch/rwlock/innodb/checkpoint_lock вам надо либо увеличить размер лог файлов либо у вас слишком низкое значение параметра innodb_max_dirty_pages_pct. Постарайтесь сделать процесс чекпойнта менее аггресивным.
Самый популярный пожалуй будет wait/synch/mutex/innodb/buf_pool_mutex. Этот мьютекс отвечает за подгрузку данных в буферный пул. Если данный мьютекс висит в топах у вас два выхода: изменение архитектуры БД таким образом чтобы в оперативную память грузилось как можно меньше данных, или же переход на XtraDB. Слышал я такую версию что этот плагин гораздо эффективнее работает с большими БД нежели InnoBD, но сам не проверял.
Есть блокировки которые просто говорят, что у вас все плохо, но помочь вам в этом никто не сможет. К примеру блокировки типа kernel_mutex очень болезненно бьют по тем у кого много транзакций, ибо при создании каждой транзакции, ей необходимо скопировать в свою область памяти весь список текущих транзакций. Если список немаленький, блокировка (жизненно важная в других процессах) будет надолго удержана.
Из своей практики могу сказать, что лучше всего описаны мьютексы для плагина innodb.
По остальным в большинстве случаев придется читать исходники.
Так же с использованием данной схемы можно отслеживать блокировки при тотальном зависании инстанса. Но это уже совсем другая история.

Заключение

Данный механизм конечно же не заменит нам столь любимых трейсов в Oracle и мощи stats pack однако уже сейчас там есть на что посмотреть. Если у вас подвисает БД и вы не знаете с чего начать — начните с PERFORMANCE_SCHEMA. Там всегда найдется куча полезной информации, тем более что уже сейчас Oracle не стоит на месте и в релизе 5.6.X нам обещали два офигительных представления, которые показывают статистику по использованию всех таблиц и индексов (я уже заценил, что там выдается и лично мне эта информация показалась крайне полезной, искать с её помощью недостающие или же наоборот неиспользуемые индексы просто замечательно). Что ж надеемся и ждем.

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

Рейтинг: 0 Голосов: 0 2244 просмотра
Комментарии (0)

Нет комментариев. Ваш будет первым!