Troubleshooting my sql_performance_addons

<Insert Picture Here>

Отладка производительности MySQL: дополнительные материалы

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

Пояснение

• Дополнительные материалы созданы с целью напомнить слушателю о работе встроенных инструментов MySQL

• За основу взята текущая версия, рекомендуемая для использования: 5.5

• Новые возможности, доступные в нестабильной версии, опущены, так как цель мастер-класса — помочь в реальной ситуации, а не в «прекрасном далёком»

• Данная шпаргалка предполагает, что слушатель знаком с представленными инструментами и лишь структурирует информацию.

• Детали и подробное описание доступны в официальном руководстве пользователя MySQL по адресу

• http://dev.mysql.com/doc/refman/5.5/en/index.html

http://dev.mysql.com/doc/refman/5.5/en/index.html

EXPLAIN

• mysql> explain extended select * from t1 join t2;

• ++++++++++++

• | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

• ++++++++++++

• | 1 | SIMPLE | t1 | index | NULL | col_int_key | 5 | NULL | 4 | 100.00 | Using index |

• | 1 | SIMPLE | t2 | index | NULL | col_int_key | 5 | NULL | 6 | 100.00 | Using index; Using join buffer (Block Nested Loop) |

• ++++++++++++

• 2 rows in set, 1 warning (0.00 sec)

•

• Note (Code 1003): /* select#1 */ select `test`.`t1`.`col_int_key` AS `col_int_key`,`test`.`t1`.`pk` AS `pk`,`test`.`t2`.`col_int_key` AS `col_int_key`,`test`.`t2`.`pk` AS `pk` from `test`.`t1` join `test`.`t2`

Во что реально был преобразован запрос

Номер SELECT-а

Тип SELECT-а

Таблица, для которой выдаётся информация в данной строке

Тип доступа к данным

Возможные ключи

Ключ, который реально был использован

Длина ключаКакие стоблцы сравнивались с индексом

Количество просмотренных строк

% строк, который будет отфильтрован

rows × filtered / 100 — количество строк, которые будут объединены с предыдущей таблицей

Дополнительная информация

Произведение строк в этом столбце: сколько строк будет просмотрено всего

Для данного примера приблизительное значение — 4*6 = 24

EXPLAIN: что обозначает поле type

Название Описание

system В таблице одна строка

const В таблице только одна строка, соответствующая выбранному условию, которая может быть выбрана по уникальному ключу: WHERE primary_key=CONST

eq_ref Только одна строка будет прочитана для каждой комбинации строк предыдущих таблиц. Применяется в JOIN-ах по уникальному NOT NULL ключу при сравнениях при помощи знака =

ref Все строки, необходимые для соединения, могут быть прочитаны при помощи неуникального ключа или первых нескольких знаков ключа при сравнениях = и <=>

fulltext Поиск по полнотекстовому ключу

ref_or_null То же, что и ref, но с дополнительным поиском строк со значением NULL:WHERE key_column=expr OR key_column IS NULL

index_merge Использована оптимизация Index Merge: поиск по нескольким ключам.

unique_subquery Подзапрос IN, выбирающий значения по первичному ключу, был заменён более эффективной функцией поиска по индексу

index_subquery Подзапрос IN, выбирающий значения по неуникальному ключу, был заменён более эффективной функцией поиска по индексу

range Будут возвращены только строки, выбранные из определённого набора значений. Поиск происходит по индексу. Применяется для операций сравнения =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()

index Будет просмотрен индекс целиком

ALL Будет просмотрена вся таблица

EXPLAIN: что обозначает поле Extra

Содержимое поля Описание

const row not found Пустая таблица

Distinct Поиск прекращается после первого значения подходящего под условие, так ищутся уникальные значения

Full scan on NULL key Для выполнения подзапроса индексне может быть использован

Impossible HAVING Условие HAVING всегда ложно

Impossible WHERE Условие WHERE всегда ложно

Impossible WHERE noticed after reading const tables Условие WHERE всегда ложно для указанных const и system условий.

No matching min/max row Не найдено строки, удовлетворяющей условию SELECT MIN()/MAX() ... WHERE

no matching row in const table Для запроса с JOIN не найдено строк, удовлетворяющих условию по уникальному ключу

No tables used Запрос не использует таблиц (запрос без FROM или с FROM DUAL)

Not exist Оптимизация LEFT JOIN: для каждой строки из левой таблицы будет просмотрена единственная строка из правой, независимо, есть ли в ней более одного совпадения

Range checked for each record (index map: N) MySQL не нашёл подходящего индекса, но нашёл, что индекс может быть использован после нахождения данных из предыдущих таблиц

EXPLAIN: что обозначает поле Extra

Содержимое поля Описание

Scanned N databases Оптимизация запросов к INFORMATION_SCHEMA: сколько директорий просмотрено для выполнения запроса

Select tables optimized away Запрос содержит только агрегирующие функции, которые могут быть выполнены без просмотра таблиц (по индексу)

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table

Оптимизация запросов к INFORMATION_SCHEMA

unique row not found Нет строк, удовлетворяющих условию по уникальному ключу

Using filesort MySQL нужно сделать дополнительную работу, чтобы возвратить строки в отсортированном порядке. Filesort не всегда обозначает использование файлов: сортировка может производиться полностью в памяти

Using index Результат запроса может быть возвращён только из индекса без доступа к таблице (SELECT ind_col ... WHERE ind_cond

Using index for group-by Только индекс используется для получение результата запросов GROUP BY или DISTINCT

Using join buffer Данные из соединяемых таблиц сначала считываются в JOIN BUFFER, затем происходит объединение

Using sort_union(...), Using union(...), Using intersect(...) Как индексы соединяются для оптимизации index_merge

Using temporary Для выполнения запроса создаётся временная таблица

Using where Для выборки используется условие WHERE

Переменные Handler_%Название Описание

Handler_commit Количество запросов COMMIT

Handler_delete Сколько раз строки были удалены из таблицы. Удобно для наблюдения за тем, как выполняется запрос DELETE из большой таблицы

Handler_prepare Счётчик PREPARE для двухфазных операций COMMIT

Handler_read_first Сколько раз прочитана первая строка из индекса. Может быть индикатором full index scan

Handler_read_key Количество запросов на чтение строки на основании записей в ключе. Индикатор правильного использования индексов.

Handler_read_last Сколько раз прочитана последняя строка из индекса.

Handler_read_next Количество чтений следующей по порядку строки индекса

Handler_read_prev Количество чтений предыдущей по порядку строки индекса

Handler_read_rnd Количество запросов чтения строки на основе фиксированной позиции. Индикатор запросов, требующих сортировки или же объединений, не использующих индекс

Handler_read_rnd_next Количество запросов на чтение следующей строки из таблицы. Индикатор full table scan и неправильного использования индексов

Handler_rollback Количество запросов ROLLBACK

Handler_savepoint Количество запросов SAVEPOINT

Handler_savepoint_rollback Количество запросов ROLLBACK до SAVEPOINT

Handler_update Количество запросов на обновление строк.

Handler_write Количество запросов на запись строки в таблицу.

SHOW INDEX

• mysql> show index from tind;

• ++++++++++++++

• | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

• ++++++++++++++

• | tind | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |

• | tind | 1 | f1 | 1 | f1 | A | 6 | NULL | NULL | YES | BTREE | | someone |

• | tind | 1 | f2 | 1 | f2 | NULL | 6 | NULL | NULL | YES | FULLTEXT | | |

• ++++++++++++++

• 3 rows in set (0.01 sec)

Название таблицы

Тип индекса

Пользовательский комментарий

(при создании индекса)

Как ключ упакован

Сколько значений частично проиндексировано

Дозволен ли NULL

Дополнительная информация

(например, disabled)

Количество уникальных значений

Как отсортированы значения.

А — в порядке возрастания

NULL — не отсортированы

Название колонки

Порядковый номер. Имеет значение для индекса по нескольким полям

Название индекса

Уникален ли индекс? 0 - уникален

Optimizer switches


index_merge Включена ли оптимизация поиска по нескольким ключам

index_merge_intersection Оптимизация пересечений: условие ANDПример: key_col1 < 10 AND key_col2 = 'foo'

index_merge_union Оптимизация объединений: условие ORПример: key_col1 = 'foo' OR (key_col2 = 'bar' AND key_col3 = 'baz')

index_merge_sort_union Оптимизация объединений, при которых index_merge_union применён быть не можетПример: (key_col1 > 10 OR key_col2 = 'bar') AND key_col3 = 'baz'

Таблицы INFORMATION_SCHEMA.INNODB_%


INNODB_TRX Транзакции, которые выполняются в настоящий момент

INNODB_LOCKS Блокировки, удерживаемые транзакциями в настоящий момент

INNODB_LOCK_WAITS Блокировки, которых ждут транзакции в данный момент

INNODB_CMP Информация о сжатых таблицах

INNODB_CMP_RESET Информация о сжатых таблицах. Информация сбрасывается после запроса. Использовать для отслеживания изменений.

INNODB_CMPMEM Информация о сжатых страницах

INNODB_CMPMEM_RESET Информация о сжатых страницах. Информация сбрасывается после запроса. Использовать для отслеживания изменений.

ENGINE INNODB STATUS


BACKGROUND THREAD Активность background thread

SEMAPHORES Внутренние семафоры. Например, при CHECK TABLE

TRANSACTIONS Текущие транзакции

FILE I/O Операции ввода-вывода, все внутренние нити

INSERT BUFFER AND ADAPTIVE HASH INDEX

Использование insert буфера и adaptive hash index

LOG Операции с InnoDB log файлами

BUFFER POOL AND MEMORY Использование InnoDB buffer pool и памяти

ROW OPERATIONS Строковые операции, статистика

Буфера, влияющие на производительностьНазвание Описаниеjoin_buffer_size Объединения и table scans. Выделяется для каждых двух таблиц в JOIN

net_buffer_length Буфер для каждого соединения, содержит запросы и их результаты.

query_prealloc_size Для обработки запроса. Чем длиннее запрос, тем больше должен быть этот буфер.

read_buffer_size Для каждого последовательного сканирования таблицы

read_rnd_buffer_size Буфер для сортировки результатов. Сказывается на производительности ORDER BY

sort_buffer_size Буфер, выделяемый для каждой сортировки

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

thread_cache_size Количество нитей, созданных для обработки соединений, которое может быть использовано повторно.

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

tmp_table_size Максимальный размер для внутренних служебных таблиц в памяти: по достижении этого размера конвертируются на диск

query_cache_size Размер кэша запросов. Не рекомендуется устанавливать более 100 MB

table_definition_cache Сколько определений таблиц хранить в кэше

table_open_cache Сколько дескрипторов таблиц хранить в кэше

SHOW GLOBAL STATUS: производительность

Название ОписаниеAborted_clients, Aborted_connects Отключённые клиенты и соединения

Binlog_[stmt_]cache_use, Binlog_[stmt_]cache_disk_use Активность использования Binlog cache

Bytes_received, Bytes_sent Трафик

Com_* Статистика по запросам и командам (INIT DB при соединении, ...)

Created_tmp_[disk_]tables, Created_tmp_files Статистика по внутренним временным таблицам и файлам

Handler_* См. раньше

Innodb_* Статистика InnoD: buffer pool, lor, i/o операции, другое

Key_blocks_* Статистика использования ключей

Open_*, Opened_* Открытые файлы, таблицы и т.п. Opened — с начала работы. Если Open равно размеру соответствующего буфера, а Opened велико, подумайте над увеличением буфера

Qcache_* Статистика использования Query cache

Connections, Queries, Questions Количество соединений и запросов, Queries содержит запросы из хранимых процедур, Questions - нет

Select_*, Sort_* Статистика для запросов SELECT и сортировок

Table_locks_* Табличные блокировки

Threads_* Статистика по соединениям

Таблицы в Performance SchemaНазвание Описание

cond_instances Состояния с начала запуска сервера (напр., wait/synch/cond/sql/DEBUG_SYNC::cond)

events_waits_current Какой блокировки ждёт каждая нить.

events_waits_history Последние performance_schema_events_waits_history_size ожиданий

events_waits_history_long Последние performance_schema_events_waits_history_long_size ожиданий

events_waits_summary_by_instance Агрегатор ожиданий по инстанциям (напр., wait/synch/mutex/sql/LOCK_uuid_generator)

events_waits_summary_by_thread_by_event_name Агрегатор ожиданий по нитям и названиям (напр., wait/synch/mutex/sql/PAGE::lock)

events_waits_summary_global_by_event_name Глобальный агрегатор ожиданий по названиям,без привязки к нитям.

file_instances Список однажды открытых файлов

file_summary_by_event_name Агрегатор файловых операций по событиям ( wait/io/file/sql/map)

file_summary_by_instance Агрегатор файловых операций по имени файла

mutex_instances Список мутексов. С ненулевым LOCKED_BY_THREAD_ID - текущие.

performance_timers Типы таймеров.

rwlock_instances Блокировки ввода-вывода.

setup_consumers Настройки: включён ли вывод в соответствующую таблицу.

setup_instruments Настройки: какие события отслеживать.

setup_timers Настройки: какой таймер используется.

threads Все внутренние нити сервера (не путать с соединениями).

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.

Troubleshooting my sql_performance_addons

Software

Transcript of Troubleshooting my sql_performance_addons