MySQL, который мы не знаем

О чем доклад

Зачем нужен мониторинг
Подход к решению проблем
- Поиск проблем
  - Мониторинг с Prometheus, Grafana
  - Мониторинг with Anemometer
- Определение причин низкой производительности
- Подготовка и проверка решения
- Внедрение решения и валидация результатов
Полезные советы
Ссылки (тестовый стенд, инструменты)

Зачем нужен мониторинг

Как посмотреть текущее состояние дел?
Когда серверу станет совсем плохо?
Что будет на black friday?
Можно ли не делать ничего?

Ты не можешь управлять тем,
что не можешь измерить

Подход к решению проблем

Поиск проблем
Открываем черный ящик

Основные проблемы производительности

Подход к решению проблем

Процессор
Память
Диск: место, I/O
Сеть: большое количество данных, медленные клиенты
Конфигурация

Сложность: найти причину плохой производительности

Откуда получить данные о системе?

Performance Schema
Information Schema
Slow query log
```
SHOW
```
```
EXPLAIN
```
Метрики операционной системы:
- Память
- Диск
- Сеть
...

Слишком много
"сырых" данных

Визуализация + интерфейсы

Подход к решению проблем

Основные задачи системы мониторинга

Метрики нужно собирать, хранить, отслеживать в динамике
Унифицированный формат для метрик от разных источников
Query language (PromQL)
Инструменты для визуализации
Оповещения

/ Мониторинг с Prometheus, Grafana

Подход к решению проблем

Архитектура

/ Мониторинг с Prometheus, Grafana

Prometheus server

Targets
(exporters)

Data visualization and export:

Grafana
Prometheus web UI
API

Pull metrics

PromQL

Alertmanager
Pushgateway
Service discovery

Подход к решению проблем

Targets
(exporters)

Exporters

/ Мониторинг с Prometheus, Grafana

Exporter'ы - программы (GO) для сбора метрик из источников: операционная система, база данных (MySQL, PostreSQL), ...

Работает как процесс, слушает определенный порт и по HTTP запросу "выставляет" метрики.

mysqld_exporter может находиться на одной машине (контейнере), а мониторить другую (hello RDS).

...

mysqld_exporter может собирать разное количество метрик в зависимости от версии сервера MySQL и конфигурации.

Подход к решению проблем

Типы метрик

/ Мониторинг с Prometheus, Grafana

Counter

Gauge

Histogram

Количество 404 ошибок

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

Какой процент запросов было выполнено за время < 500 ms?

TYPE
HELP ->

Подход к решению проблем

Визуализация данных

/ Мониторинг с Prometheus, Grafana

Prometheus web UI
Grafana, percona dashboards

Провести анализ показателей
Установить точку отсчета
Настроить оповещения

Достаточно данных

Посмотрим что получилось

hints
CPU
slow queries

Подход к решению проблем

Как найти проблемные запросы?

/ Мониторинг с Prometheus, Grafana

Подход к решению проблем

Slow log

/ Проблемные запросы

Файл на диске, например /var/lib/mysql/slow.log
Логирование может быть отключено
В лог попадают запросы, время выполнения которых > slow_launch_time
Читать возможно, но неудобно. Понять общую картину невозможно

# Time: 2020-05-25T20:50:53.399620Z
# User@Host: root[root] @  [192.168.240.2]  Id:  5333
# Query_time: 3.526603  Lock_time: 0.000147 Rows_sent: 20000  Rows_examined: 1987320 Thread_id: 5333 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 10469146 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 1987320 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-05-25T20:50:49.873017Z End: 2020-05-25T20:50:53.399620Z
SET timestamp=1590439849;
SELECT * FROM test_table_big WHERE number = '50' ORDER BY number LIMIT 20000;

...

Из нечитабельного текстового файла получаем конфету при помощи Anemometer !

Подход к решению проблем

Мониторинг с Anemometer и как он устроен?

/ Проблемные запросы

SELECT STATEMENT_DIGEST_TEXT('SELECT * FROM city WHERE ID = 130') AS digest;

Подготовка данных:
- pt-query-digest (консольная утилита):
  - парсит slow log файл, сохраняет запросы в таблицу в отдельной базе данных
  - составляет по запросам дайджест (группировка однотипных запросов):

SELECT * FROM `city` WHERE `ID` = ?

Отображение данных: веб-интерфейс на PHP

Получаем проблемный SQL запрос

Подход к решению проблем

EXPLAIN

/ Проблемные запросы

А если нет?
SELECT ускорится? А INSERT?
Как измерить?
Почему запрос проблемный?

Достаточно добавить индекс

Подход к решению проблем

Почему возникает проблема?

/ Проблемные запросы

Как сделать быструю базу данных?

Место в памяти для хранения данных (таблиц, индексов)
Общий ресурс
Размер буфера ограничен
Данные хранятся страницами (default 16KB)
Если места не хватает, менее востребованные страницы вытесняются

InnoDB buffer pool

Небольшой объем буфера (128 MB) + большое количество данных +
много запросов =
плохая производительность

Как правило, диск работает медленнее оперативной памяти

Подход к решению проблем

Создаем highload на ровном месте

2 таблицы:

CREATE TABLE `test_table_*` (
`id` int NOT NULL AUTO_INCREMENT,
`number` tinyint DEFAULT NULL,
`string_short` varchar(5) DEFAULT NULL,
`string_long` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Table	Rows	Size
test_table_small	~ 100 000	~ 70 MB
test_table_big	~ 4 000 000	~ 2.4 GB

Нет индексов
(кроме PK)

/ Проблемные запросы

Можно ли с этим жить?

Buffer pool 128 MB

Нет, страдают все

Подход к решению проблем

Table	Query
test_table_small	SELECT AVG(number) FROM test_table_small WHERE number = ?
test_table_big	SELECT * FROM test_table_big WHERE number = ? ORDER BY number LIMIT 20000

/ Проблемные запросы

Добавляем индекс

Но сначала замеряем производительность

Подход к решению проблем

Benchmarks

/ Проблемные запросы

Sysbench

Консольная утилита
Используется LuaJIT
Мультипоточность из коробки
Кастомные тесты
Не только для MySQL и баз данных

Подход к решению проблем

Benchmarks, Sysbench

/ Проблемные запросы

> sysbench        [options]         [testname]         [command]

prepare
run
cleanup

Типичный тест состоит из команд:

event

call

Подход к решению проблем

--mysql-host=playground-db
--threads=1
--time=60

custom_insert_test

run

Результаты

/ Benchmarks, Sysbench

Operation	Threads	Events	Time, s
SELECT	1	10	61.6
SELECT	10	46	74.4
INSERT	1	7205	60.0
INSERT	10	59382	59.83

Operation	Threads	Events	Time, s
SELECT	1	48	61.3
SELECT	10	145	61.4
INSERT	1	12601	59.7
INSERT	10	46905	59.8

ALTER TABLE `playground-db`.`test_table_big`
ADD INDEX `idx_number` (`number` ASC) VISIBLE;

До

После

Посмотрим на buffer pool, CPU

Cтало лучше?

Подход к решению проблем

Что еще можно сделать?

/ Проблемные запросы

Увеличить buffer pool size до 4 GB

Проверяем состояние буфера после изменений
Проверяем содержимое буфера

Подход к решению проблем

Результаты

/ Проблемные запросы

Operation	Threads	Events	Time, s
SELECT	1	704	60.01
SELECT	10	1260	60.12
INSERT	1	14158	59.66
INSERT	10	56767	59.83

Operation	Threads	До	После ADD INDEX	После buffer pool size 4 GB
SELECT	1	10	48	704
SELECT	10	46	145	1260
INSERT	1	7205	12601	14158
INSERT	10	59382	46905	56767

Общая таблица по events

Подход к решению проблем

Результаты

/ Проблемные запросы

Подход к решению проблем

Стоило ли добавлять индекс?
Нужно ли было увеличивать размер buffer pool?

Выводы

/ Проблемные запросы

Нет универсальных рецептов: у вас другой профиль нагрузки и данные
Заниматься мониторингом, установить baseline
По возможности проводить тесты до и после изменений (составить точный профиль нагрузки тяжело)
Не допускать попадания плохих запросов
Дважды подумать над CREATE TABLE

Подход к решению проблем

Tips and tricks

INVISIBLE индексы (MySQL 8.0)
Проверить и выключить неиспользуемые индексы (осторожно):
SELECT * FROM sys.schema_unused_indexes;
Удалить избыточные индексы:
SELECT * from sys.schema_redundant_indexes;
Префиксные индексы
Полное сканирование по таблицам:
SELECT * from sys.schema_tables_with_full_table_scans;
Дайджест без anemometer:
SELECT * FROM performance_schema.events_statements_summary_by_digest;

Tips and tricks

Не всегда получается оптимизировать. Другие варианты

Выполнение запросов в не пиковое время
Кеширование
Выполнение на реплике
Другая база данных может подойти лучше
...

Спасибо

Ссылки

Запустить тестовый стенд из презентации можно локально:
https://github.com/helters/mysql-playground

https://prometheus.io/

https://habr.com/ru/company/southbridge/blog/455290/

https://github.com/akopytov/sysbench

https://github.com/prometheus/mysqld_exporter

https://hub.docker.com/r/boxinc/anemometer

https://github.com/grafana/grafana

https://github.com/percona/grafana-dashboard

https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html