<Insert Picture Here>

Введение в отладку производительности MySQL приложений

Света СмирноваВедущий инженер технической поддержки MySQL

Никакой рыбы!

Удочки

• Одиночный запрос• Параллельное выполнение

– Внутренние блокировки– Борьба за системные ресурсы

• Системные ресурсы• Репликация• Инструменты

– Контроль опций– INFORMATION_SCHEMA– PERFORMANCE_SCHEMA– Системные утилиты

Узкое место

Как выявить узкое место

• ...• <some code>• Измеряем Время_Начала (операции)• mysql_real_query(....)• Измеряем Время_Завершения (операции)• <some code>• ....•

• Нам нужно• (Время_Завершения - Время_Начала)

С чего начать?

Отлаженные запросы — основа высокой производительности!

Безграничные возможности: всегда тестируйте!

Настройки

• Переменные– SET [SESSION|GLOBAL] var_name=NEW_VALUE– Сессионные (SESSION)– Глобальные (GLOBAL)

• Параметры командной строки• Конфигурационные файлы

Параллельное выполнение

Итерации

Binary log, SQL thread и IO thread

Репликация и итерации

Одиночный запрос: где найти?

• При разработке приложения• PROCESSLIST

– SHOW [FULL] PROCESSLIST– INFORMATION_SCHEMA.PROCESSLIST

• Лог медленных запросов (slow query log)– По требованию– Вывод в таблицу– mysqldumpslow

• Лог вашего приложения• Жалобы пользователей

Одиночный запрос: что он делает?

• PERFORMANCE_SCHEMA.EVENTS_STAGES_%• PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_%

– mysql> SELECT STMTS.SQL_TEXT, STAGES.EVENT_NAME, STAGES.TIMER_WAIT FROM PERFORMANCE_SCHEMA.EVENTS_STAGES_HISTORY_LONG STAGES JOIN PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_HISTORY_LONG STMTS ON (STMTS.EVENT_ID=STAGES.NESTING_EVENT_ID) WHERE CURRENT_SCHEMA='EMPLOYEES';

– +­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­+

– | sql_text   | event_name                  | timer_wait    |

– +­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­+

– | select co  | stage/sql/init              |     189755000 |

– | select co  | stage/sql/Opening tables    |      40828000 |

– | select co  | stage/sql/System lock       |     185069000 |

• SHOW STATUS LIKE 'Handler_%'

Одиночный запрос: почему?

• EXPLAIN– EXPLAIN EXTENDED - всегда в 5.7+– EXPLAIN PARTITIONS - всегда в 5.7+– EXPLAIN FORMAT=JSON

• INFORMATION_SCHEMA.OPTIMIZER_TRACE• SHOW INDEX

– Как чинить• OPTIMIZE или ANALYZE TABLE• innodb_stats_persistent

Одиночный запрос: устранение проблемы

• Переписать запрос!• Если нельзя или не помогает:

– Index hints:• FORCE|USE INDEX• IGNORE INDEX• Обязательно тестируйте все обновления!

– Настройки оптимизатора:• optimizer_search_depth• optimizer_prune_level• optimizer_switch

Параллельные запросы и транзакции

• Борьба за системные ресурсы• Виды блокировок, которые пользователь может

контролировать:– Уровень таблицы– Уровень строки– Metadata– Есть ещё постраничные, но они использовались только

одним движком

• Что пользователь контролировать может только относительно:– Блокировки, несвязанные непосредственно с

обрабатываемыми данными, такие как query cache, блокировки на уровне движка и т.п.

Параллельные запросы и транзакции:как вычислить

• Универсальное средство:– PROCESSLIST,

PERFORMANCE_SCHEMA.EVENTS_STAGES_%• Locked• Waiting for *

• На уровне движка:– SHOW ENGINE INNODB STATUS– Таблицы INNODB_* в INFORMATION SCHEMA

• INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS

• Performance Schema– METADATA_LOCKS, TABLE_HANDLES– EVENTS_TRANSACTIONS

5.7

Параллельные запросы и транзакции:средства InnoDB

• Мониторы– SHOW ENGINE INNODB STATUS – Опция innodb­status­file

– innodb_status_output

– innodb_status_output_locks• Больше информации о блокировках• Всегда включайте!

• TRANSACTIONS

5.6.16

Параллельные запросы и транзакции:средства InnoDB

• Таблицы Information Schema– INNODB_TRX, INNODB_LOCK_WAITS

• mysql> SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, BLOCKING_TRX_ID, BLOCKING_LOCK_ID, TRX_QUERY FROM INNODB_TRX JOIN INNODB_LOCK_WAITS on (INNODB_TRX.TRX_ID = INNODB_LOCK_WAITS.REQUESTING_TRX_ID) WHERE TRX_STATE = 'LOCK WAIT'\G

• ********************* 1. row *********************

• TRX_ID: C2F

• TRX_REQUESTED_LOCK_ID: C2F:0:1352:2

• BLOCKING_TRX_ID: C2C

• BLOCKING_LOCK_ID: C2C:0:1352:2

• TRX_QUERY: update titles set title='Senior Engineer'

• where title='Senior Engineer'

• 1 row in set (0.01 sec)

– INNODB_METRICS

Параллельные запросы и транзакции:блокировки метаданных• mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID from metadata_locks\G

• ********************** 1. row **********************

•     OBJECT_NAME: t2

•       LOCK_TYPE: SHARED_WRITE

•     LOCK_STATUS: GRANTED

• OWNER_THREAD_ID: 20

• ...

• ********************** 5. row **********************

•     OBJECT_NAME: t2

•       LOCK_TYPE: EXCLUSIVE

•     LOCK_STATUS: PENDING

• OWNER_THREAD_ID: 21

...

Параллельные запросы и транзакции:устранение проблем

• Краткосрочное– Выбрать наименее важную транзакцию и убить её

• KILL <MYSQL_THREAD_ID>

• Долгосрочное– Спланировать приложение так, чтобы конкурирующие

транзакции разнести по времени выполнения– Избегать длинных транзакций без необходимости– Если autocommit=0

• Использовать READ-ONLY транзакции• Закрывать транзакции после SELECT и ALTER

Борьба за системные ресурсы

• Память• CPU• Диск• Сеть

Память

• Диагностика– free– top– vmstat

• Проблемы– mysqld использует слишком мало памяти

• Буферы не настроены на высокую производительность• Система ограничивает ресурсы, которые доступны

mysqld– mysqld использует слишком много памяти

Диск

• Диагностика– df– iostat– ls -l /proc/{PID_OF_MYSQLD}/fd

• Починка– Параметры InnoDB

• innodb_adaptive_flushing• innodb_doublewrite• innodb_flush_log_at_trx_commit• innodb_flush_method• innodb_io_capacity• innodb_max_dirty_pages_pct

•– Binary log

• Formats– ROW– STATEMENT– MIXED

• binlog_row_image• sync_binlog

CPU

• Диагностика– top– iostat– ps

• Параллельность и количество ядер

innodb_thread_concurrency Execution time

1 7.8164

2 4.3959

4 2.5889

8 2.6708

16 3.4669

32 3.4235

Сеть

• Важно!– Надёжность– Пропускная способность– Скорость

• Диагностика– ping– --log-warnings=2– tcpdump

• Послать большой (1G и больше) файл и мониторить

Репликация

• Мастер– Медленная сеть– Потери при записи в binary log

• Disk IO• Блокировки

• Слэйв– IO thread

• То же, что и у мастера– SQL thread

• Один поток против нескольких на мастере• Взаимодействие с другой нагрузкой на mysqld слэйва

Репликация: диагностика

• SHOW SLAVE STATUS• Performance Schema Replication Tables

– replication_connection_status• mysql> select SERVICE_STATE, RECEIVED_TRANSACTION_SET, LAST_ERROR_NUMBER from replication_connection_status\G

• ******************** 1. row *********************

•            SERVICE_STATE: ON

• RECEIVED_TRANSACTION_SET:

• 9fa18fb7­d6f2­11e3­bc7e­a088b4923078:1

•        LAST_ERROR_NUMBER: 0

• 1 row in set (0.00 sec)– replication_execute_status

5.7

Ссылки

• Одиночный запрос:– http://glebshchepa.blogspot.co.uk/2012/04/optimizer-new-explain-formatjson.html– http://www.slideshare.net/myxplain/powerful-explain-in-mysql-56– http://www.slideshare.net/SvetaSmirnova/troubleshooting-my-sqlperformanceaddons– http://www.slideshare.net/SvetaSmirnova/performance-schema-mysql

• Параллельные запросы и транзакции:– http://shop.oreilly.com/product/0636920021964.do– http://planet.mysql.com/entry/?id=643406– https://blogs.oracle.com/mysqlinnodb/entry/get_started_with_innodb_metrics

• Общие– http://dev.mysql.com/doc/refman/5.7/en/index.html– https://blogs.oracle.com/mysqlinnodb/– http://marcalff.blogspot.ru/

?

СПАСИБО!

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.