Поиск узких мест в производительности MySQL. Ботанический определитель

Август 5, 2022

Главная
Информатика
Поиск узких мест в производительности MySQL. Ботанический определитель

Содержание

2. Поиск узких мест Есть проблемы с производительностью? Если нет, создайте! например, ускоренный повтор лога апача $
3. Структура определителя C конфигурация BEGIN Q запросы END
4. BEGIN END C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 Q1 Q2
5. С1 - начало $ top Кто съедает процессор? httpd/php/… => С2 mysqld => Q1 треды ядра
6. С2 - апач ест ресурсы Заменить апач на lighttpd/nginx + fastcgi или Поменять настройки тредов апача
7. С3 – используется ли своп? Используется ли своп? да, память занята httpd/php/… => C2 да, память
8. С4 – нагрузка диска $ iostat -x 1 процент использования большой по некоторым дисками или неравномерен
9. С5 – проверка железа проверьте диски (smartctl; скорость прямой записи, чтения, свободное место на дисках; статус
10. С6
11. С6 – настройка query cache mysql> SHOW VARIABLES LIKE ‘query_cache%’; Если есть сложные запросы (например, JOIN
12. С7 – настройка размера query cache Дождаться разогрева кэша mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’; Если
13. С8 – тип хранилища mysql> SELECT ENGINE,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA; Какой тип основных таблиц?
14. MyISAM key buffer MyISAM кэширует только индексы (вкл. FULLTEXT).
15. С9 – MyISAM key buffer MyISAM кэширует только индексы (вкл. FULLTEXT). mysql> SHOW GLOBAL STATUS LIKE
16. Буфер InnoDB InnoDB кэширует индексы и данные, размер буфера - параметр innodb_buffer_pool_size
17. С10 – InnoDB buffer pool Дождаться разогрева кэша mysql> SHOW GLOBAL STATUS LIKE ‘innodb_buf%’; Innodb_buffer_pool_pages_free увеличить
18. С11 – InnoDB options Если innodb_file_per_table=OFF включить innodb_file_per_table ALTER TABLE `tbl` ENGINE=InnoDB; для таблиц InnoDB innodb_flush_log_at_trx_commit:
19. С12 – файлы и треды mysql> SHOW GLOBAL STATUS LIKE ‘open_%’; Если opened_tables >> open_tables, увеличить
20. Q1 – поиск худшего запроса При пиковой нагрузке выполнить несколько раз подряд: mysql> SHOW FULL PROCESSLIST;
21. Q2 – поиск худшего запроса Включить журнал медленных запросов (log_slow_queries; long_query_time=1;) Накопить статистику, включающую пиковое время.
22. Q3 – запрос II рода SELECT блокирует другие SELECT только если между ними в очереди UPDATE
23. Q4 – оптимизация запроса Запрос содержит ORDER BY + LIMIT => Q9 Запрос содержит подзапросы: независимые
24. Q5 – простой запрос В запросе нет WHERE это ошибка => исправьте запрос => Q1 это
25. Q6 – создание индекса Составной индекс используется только последовательно без пропусков ALTER TABLE `tbl` ADD KEY(A,B,C)
26. Q7 – индекс не используется Удалите избыточные индексы. Например из KEY(A,B), KEY(A), KEY(B) можно оставить KEY(A,B)
27. Q8 – составной запрос Выполните EXPLAIN ЗАПРОС Не все JOIN выполняются по индексу => создайте необходимые
28. Q9 – ORDER BY+LIMIT (1/4) Самый медленный класс запросов в рунете. Пример из практики: SELECT *
29. Q9 – ORDER BY+LIMIT (2/4) EXPLAIN SELECT … id: 1 select_type: SIMPLE table: wp_posts type: ref
30. Q9 – ORDER BY+LIMIT (3/4) Как выполняется запрос? Используется индекс post_status=‘publish’ Перебор почти всей таблицы для
31. Q9 – ORDER BY+LIMIT (4/4) Решение: разбить запрос Сортировать только значения id Наложить ограничение LIMIT Получить
32. Q10 – независимые подзапросы Обычно в контексте WHERE или FROM. Пример: SELECT name FROM clients WHERE
33. Q11 – зависимые подзапросы Обычно в контексте SELECT или WHERE. Пример: SELECT clients.id, (SELECT count(*) FROM
34. Q12 - выборка большого объема Используйте NOT NULL – сокращает объем данных и размер индексов. Используйте
35. Q13 – изменение логики Обдумайте запрос и перепишите его полностью. Может потребоваться: изменение логики приложения изменение
36. Q14 – задайте вопрос на SQLinfo.ru Задайте вопрос на форуме http://sqlinfo.ru/forum/ дождитесь ответа => Q1
38. Скачать презентацию

Слайд 2

Поиск узких мест
Есть проблемы с производительностью?
Если нет, создайте!
например, ускоренный повтор лога

апача
$ cat access.log | awk ‘{print “GET
http://localhost” $7 “;sleep 0.1”}’ | bash
Теперь можно классифицировать

Слайд 3

Структура определителя
C
конфигурация
BEGIN
Q
запросы
END

Слайд 4

BEGIN
END
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9
Q11
Q10
Q12
Q13
Q14

Слайд 5

С1 - начало
$ top
Кто съедает процессор?
httpd/php/… => С2
mysqld => Q1
треды ядра

=> C5
загрузка менее 20% => С3

Слайд 6

С2 - апач ест ресурсы
Заменить апач на lighttpd/nginx + fastcgi
или
Поменять настройки

тредов апача
или
Избавиться от лишних циклов в скриптах
или
Настроить persistent соединения
=> C1

Слайд 7

С3 – используется ли своп?
Используется ли своп?
да, память занята httpd/php/… =>

C2
да, память занята mysqld => C6
нет, часть памяти свободна (free, buffers, cached) => С4

Слайд 8

С4 – нагрузка диска
$ iostat -x 1
процент использования большой по

некоторым дисками или неравномерен для дисков одного soft RAID => C5
процент использования небольшой по всем дискам => C6

Слайд 9

С5 – проверка железа
проверьте диски (smartctl; скорость прямой записи, чтения, свободное

место на дисках; статус RAID)
проверить /var/log/messages на предмет ошибок диска, памяти (см. также mcelog) и другого железа
Неисправное железо найдено и заменено => END;
Неисправность не обнаружена => С6

Слайд 10

С6

Слайд 11

С6 – настройка query cache
mysql> SHOW VARIABLES LIKE ‘query_cache%’;
Если есть сложные

запросы (например, JOIN или WHERE не по индексу) или не более двух процессорных ядер:
включить query_cache => C7
Если запросы неповторяющиеся или все быстрые при многоядерном процессоре:
подумать про HandlerSocket; выключить query_cache => С8
см. доклад Константина Осипова на Highload++ 2008
http://www.highload.ru/papers2008/7650.html

Слайд 12

С7 – настройка размера query cache
Дождаться разогрева кэша
mysql> SHOW GLOBAL

STATUS LIKE ‘Qcache%’;
Если Qcache_free_memory << query_cache_size => увеличить размер кэша, повторить C7
Если Qcache_free_memory > 0.5 query_cache_size => уменьшить размер кэша
=>С8
http://webew.ru/articles/1041.webew

Слайд 13

С8 – тип хранилища
mysql> SELECT ENGINE,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA;
Какой

тип основных таблиц?
MyISAM => C9
InnoDB => C10
Часть MyISAM, часть Innodb => C9,C10

Слайд 14

MyISAM key buffer
MyISAM кэширует только индексы (вкл. FULLTEXT).

Слайд 15

С9 – MyISAM key buffer
MyISAM кэширует только индексы (вкл. FULLTEXT).
mysql> SHOW

GLOBAL STATUS LIKE ‘key_%’;
Key_blocks_unused << key_blocks_used => увеличить key_buffer_size, повторить C9
Key_blocks_unused > key_blocks_used => уменьшить key_buffer_size
=> C12

Слайд 16

Буфер InnoDB
InnoDB кэширует индексы и данные, размер буфера - параметр innodb_buffer_pool_size

Слайд 17

С10 – InnoDB buffer pool
Дождаться разогрева кэша
mysql> SHOW GLOBAL STATUS

LIKE ‘innodb_buf%’;
Innodb_buffer_pool_pages_free << Innodb_buffer_pool_pages_total
увеличить innodb_buffer_pool_size; повторить C10 (вплоть до трети от общей памяти)
=> C11

Слайд 18

С11 – InnoDB options
Если innodb_file_per_table=OFF
включить innodb_file_per_table
ALTER TABLE `tbl` ENGINE=InnoDB; для таблиц

InnoDB
innodb_flush_log_at_trx_commit:
0 запись и flush лога раз в секунду
1 (default) – flush лога после каждой транзакции
2 запись после каждой транзакции, flush - раз в секунду
установите значение 0 или 2, если не страшно потерять секунду данных
=> C12

Слайд 19

С12 – файлы и треды
mysql> SHOW GLOBAL STATUS LIKE ‘open_%’;
Если opened_tables

>> open_tables, увеличить table_cache
Если таблиц очень много, проверить ограничение ОС (в Linux: fs.file-max через sysctl)
mysql> SHOW GLOBAL STATUS LIKE ‘threads_%’;
Если threads_created >> threads_connected, увеличить thread_cache_size
=> Q1

Слайд 20

Q1 – поиск худшего запроса
При пиковой нагрузке выполнить несколько раз подряд:
mysql>

SHOW FULL PROCESSLIST;
Найден запрос I рода: часто появляется в процессах, остальные запросы при этом выполняются быстро => Q4
Найден запрос II рода: во время его выполнения другие запросы ожидают в состоянии Locked => Q3
Не найдено медленных запросов => Q2

Слайд 21

Q2 – поиск худшего запроса
Включить журнал медленных запросов (log_slow_queries; long_query_time=1;)
Накопить статистику,

включающую пиковое время.
Сгенерировать дайджест: mk-query-digest из Percona Maatkit
Проверить скорость исполнения 10 самых медленных SELECT-запросов (с опцией SQL_NO_CACHE).
Все они выполняются действительно медленно => это запросы первого рода => Q4
Некоторые из них на самом деле быстрые и выполнялись медленно из-за блокировки => среди тех, которые выполняются медленно есть запросы второго рода => Q3
Не найдено медленных запросов => END;

Слайд 22

Q3 – запрос II рода
SELECT блокирует другие SELECT только если между

ними в очереди UPDATE
Если есть запросы типа апдейта статистики
UPDATE `articles` SET viewcount=viewcount++;
перенести их в отдельную таблицу. Они не медленные, но допускают блокировку => Q1
Если таблицы типа MyISAM и не используется полнотекстовый индекс, рассмотрите возможность перехода на InnoDB => C9
default: оптимизировать так же, как запросы I рода => Q4

Слайд 23

Q4 – оптимизация запроса
Запрос содержит ORDER BY + LIMIT => Q9
Запрос

содержит подзапросы:
независимые => Q10
зависимые => Q11
default:
содержит JOIN => Q8
не содержит JOIN => Q5

Слайд 24

Q5 – простой запрос
В запросе нет WHERE
это ошибка => исправьте

запрос => Q1
это не ошибка => Q12
Выполните EXPLAIN ЗАПРОС
Нет подходящего индекса => Q6
Индекс есть, но не используется => Q7
Индекс есть и используется => Q13

Слайд 25

Q6 – создание индекса
Составной индекс используется только последовательно без пропусков
ALTER TABLE

`tbl` ADD KEY(A,B,C)
позволяет:
WHERE A=10 AND B>10;
WHERE A=10 AND B=7 AND C=12;
WHERE A=10 AND B=7 ORDER BY C;
не позволяет
WHERE B=10;
WHERE A=10 ORDER BY C;
WHERE A=10 AND B>10 AND C>10;
Операция сравнения последняя при использовании индекса
=> Q1

Слайд 26

Q7 – индекс не используется
Удалите избыточные индексы. Например из KEY(A,B), KEY(A),

KEY(B) можно оставить KEY(A,B) и KEY(B). Лучше не иметь избыточности, чем использовать USE INDEX.
Таблица слишком маленькая – оптимизатор предпочитает полный скан таблицы (наполнить данными или использовать FORCE INDEX)
Бывает, что индексы отключили и забыли включить. Тест:
SELECT TABLE_SCHEMA,TABLE_NAME, GROUP_CONCAT(comment) FROM INFORMATION_SCHEMA.STATISTICS WHERE comment LIKE ‘%disabled%’ GROUP BY 1,2;
Включить: ALTER TABLE `mytable` ENABLE KEYS;
Проблема решилась => Q1, не решилась => Q14

Слайд 27

Q8 – составной запрос
Выполните EXPLAIN ЗАПРОС
Не все JOIN выполняются по индексу

=> создайте необходимые индексы (подключение каждой таблицы рассматривать как простой запрос), Q6
Много записей на выходе и так и нужно => Q12
Порядок JOIN неоптимальный => используйте SELECT STRAIGHT_JOIN => Q8
Все объединения используют индексы => Q13

Слайд 28

Q9 – ORDER BY+LIMIT (1/4)
Самый медленный класс запросов в рунете. Пример

из практики:
SELECT * FROM wp_posts
LEFT JOIN wp_post2cat
ON (wp_posts.ID = wp_post2cat.post_id)
LEFT JOIN wp_categories
ON (wp_post2cat.category_id = wp_categories.cat_ID)
WHERE (category_id = '1')
AND post_date_gmt <= '2008-09-12 00:15:59'
AND (post_status = 'publish')
AND post_status != 'attachment' GROUP BY wp_posts.ID
ORDER BY post_date DESC LIMIT 3, 3;

Слайд 29

Q9 – ORDER BY+LIMIT (2/4)
EXPLAIN SELECT …
id: 1
select_type: SIMPLE

table: wp_posts
type: ref
possible_keys: PRIMARY,post_status,post_date_gmt
key: post_status
key_len: 1
ref: const
rows: 3450
Extra: Using where; Using temporary; Using filesort
.......................

Слайд 30

Q9 – ORDER BY+LIMIT (3/4)
Как выполняется запрос?
Используется индекс post_status=‘publish’
Перебор почти всей

таблицы для проверки остальных условий
Временная таблица (в памяти, если меньше tmp_table_size), содержащая все поля таблиц, участвующих в JOIN
Cортировка всей временной таблицы (filesort, без использования индексов)
LIMIT 3,3 – оставляем записи с 4 по 6

Слайд 31

Q9 – ORDER BY+LIMIT (4/4)
Решение: разбить запрос
Сортировать только значения id
Наложить ограничение

LIMIT
Получить значения остальных полей вторым запросом
Схематическое решение:
SELECT * FROM large_table WHERE id IN
(SELECT id FROM large_table WHERE условие
AND условие AND условие ORDER BY порядок LIMIT M,N) ORDER BY порядок;
☹ IN + LIMIT будет только в MySQL 6.0
На практике два последовательных запроса, => Q1

Слайд 32

Q10 – независимые подзапросы
Обычно в контексте WHERE или FROM. Пример:
SELECT name

FROM clients WHERE age < (SELECT c1.age FROM clients c1 WHERE c1.name=‘Paul’);
Оптимизатор MySQL может ошибочно принять независимый подзапрос за зависимый и выполнять для каждой строки. В этом случае нужно разбить запрос на два. => Q1

Слайд 33

Q11 – зависимые подзапросы
Обычно в контексте SELECT или WHERE. Пример:
SELECT clients.id,

(SELECT count(*) FROM contracts WHERE contracts.client=clients.id) FROM clients;
Оптимизатор MySQL имеет больше возможностей оптимизации JOIN, чем подзапросов. Если возможно, преобразуйте в JOIN. Получилось ускорить => Q1, не получилось => Q13.

Слайд 34

Q12 - выборка большого объема
Используйте NOT NULL – сокращает объем данных

и размер индексов.
Используйте оптимальные типы: TINYINT, SMALLINT, FLOAT
Используйте однобайтовую кодировку для хранения и при подключении.
Вынесите в другую таблицу данные, которые не участвуют в выборке.
Избавьтесь от лишних индексов.
Создайте MEMORY-таблицу с данными для выборки.
Получилось ускорить => Q1, не получилось => Q13.

Слайд 35

Q13 – изменение логики
Обдумайте запрос и перепишите его полностью. Может потребоваться:
изменение

логики приложения
изменение структуры таблиц (денормализация)
создание временных таблиц, MEMORY-таблиц, и др.
явная сортировка таблицы MyISAM: ALTER TABLE `tbl` ORDER BY id;
использование пользовательских переменных. Простейший пример:
SET @i=NULL; SELECT id-@i AS gap, @i:=id FROM `table` ORDER BY id;
Получилось ускорить => Q1, не получилось => Q14.

Слайд 36

Поиск узких мест в производительности MySQL. Ботанический определитель

Содержание

Поиск узких местЕсть проблемы с производительностью?Если нет, создайте!например, ускоренный повтор лога

Структура определителяCконфигурацияBEGINQзапросыEND

BEGINENDC1C2C3C4C5C6C7C8C9C10C11C12Q1Q2Q3Q4Q5Q6Q7Q8Q9Q11Q10Q12Q13Q14

С1 - начало$ topКто съедает процессор?httpd/php/… => С2mysqld => Q1треды ядра

С2 - апач ест ресурсыЗаменить апач на lighttpd/nginx + fastcgi илиПоменять настройки

С3 – используется ли своп?Используется ли своп?да, память занята httpd/php/… =>

С4 – нагрузка диска $ iostat -x 1процент использования большой по

С5 – проверка железапроверьте диски (smartctl; скорость прямой записи, чтения, свободное

С6

С6 – настройка query cachemysql> SHOW VARIABLES LIKE ‘query_cache%’;Если есть сложные

С7 – настройка размера query cacheДождаться разогрева кэша mysql> SHOW GLOBAL

С8 – тип хранилищаmysql> SELECT ENGINE,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA;Какой

MyISAM key bufferMyISAM кэширует только индексы (вкл. FULLTEXT).

С9 – MyISAM key bufferMyISAM кэширует только индексы (вкл. FULLTEXT).mysql> SHOW

Буфер InnoDBInnoDB кэширует индексы и данные, размер буфера - параметр innodb_buffer_pool_size

С10 – InnoDB buffer poolДождаться разогрева кэша mysql> SHOW GLOBAL STATUS

С11 – InnoDB optionsЕсли innodb_file_per_table=OFFвключить innodb_file_per_tableALTER TABLE `tbl` ENGINE=InnoDB; для таблиц

С12 – файлы и тредыmysql> SHOW GLOBAL STATUS LIKE ‘open_%’;Если opened_tables

Q1 – поиск худшего запросаПри пиковой нагрузке выполнить несколько раз подряд:mysql>

Q2 – поиск худшего запросаВключить журнал медленных запросов (log_slow_queries; long_query_time=1;)Накопить статистику,

Q3 – запрос II родаSELECT блокирует другие SELECT только если между

Q4 – оптимизация запросаЗапрос содержит ORDER BY + LIMIT => Q9Запрос

Q5 – простой запросВ запросе нет WHERE это ошибка => исправьте

Q6 – создание индексаСоставной индекс используется только последовательно без пропусковALTER TABLE

Q7 – индекс не используетсяУдалите избыточные индексы. Например из KEY(A,B), KEY(A),

Q8 – составной запросВыполните EXPLAIN ЗАПРОСНе все JOIN выполняются по индексу

Q9 – ORDER BY+LIMIT (1/4)Самый медленный класс запросов в рунете. Пример

Q9 – ORDER BY+LIMIT (2/4)EXPLAIN SELECT … id: 1 select_type: SIMPLE

Q9 – ORDER BY+LIMIT (3/4)Как выполняется запрос?Используется индекс post_status=‘publish’Перебор почти всей

Q9 – ORDER BY+LIMIT (4/4)Решение: разбить запросСортировать только значения idНаложить ограничение

Q10 – независимые подзапросыОбычно в контексте WHERE или FROM. Пример:SELECT name

Q11 – зависимые подзапросыОбычно в контексте SELECT или WHERE. Пример:SELECT clients.id,

Q12 - выборка большого объемаИспользуйте NOT NULL – сокращает объем данных

Q13 – изменение логикиОбдумайте запрос и перепишите его полностью. Может потребоваться:изменение

Q14 – задайте вопрос на SQLinfo.ruЗадайте вопрос на форуме http://sqlinfo.ru/forum/дождитесь ответа=>

Похожие презентации