Тормозит БД mysql

Rate this post

Что делать и как решить проблемы с производительностью базы данных MySQL. В данном случае мы не будем говорить об оптимизации сервера БД, а речь пойдет непосредственно о запросах в БД.

Итак имеет следующее, mysqltuner показывает нам, что все параметры БД настроены корректно и ресурсы используются эффективно. Но все же происходят некие зависания. Как найти проблемные запросы.

Лог медленных запросов

Для начала нам необходимо получить статистику. Для этого в БД включаем лог медленных запросов. Для этого в файл /etc/my.cnf добавим строки

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 2

1 2	log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2

log_slow_queries переменная задающая путь к файлу лога
long_query_time минимальное время выполнения запроса, который будет считаться медленным. Т.е. все запросы время выполнения которых 2 и более секунды попадут в лог

Иногда имеет смысл включить логирование запросов у которых нет индексов

log-queries-not-using-indexes

1	log-queries-not-using-indexes

После добавления значений в конфигурацию выполним перезагрузку бд

/etc/init.d/mysql restart

1	/etc/init.d/mysql restart

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

Обработка лога медленных запросов

Если у вас интенсивно выполняются запросы, и их достаточно много и медленных, то лог может вырости до больших размеров в несколько десятков гигабайт. Конечно же в таком логе разобраться практически не реально. При этом лог содержит повторяющиеся запросы. Что бы привести лог медленных запросов к читабельному виду, необходимо обработать его утилитой mk-query-digest или pt-query-digest.

Для установки Percona Toolkit можно воспользоваться командой

Centos

# wget http://percona.com/get/percona-toolkit.rpm
# yum install perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey
# rpm -ihv percona-toolkit-*.noarch.rpm

# wget http://percona.com/get/percona-toolkit.rpm

# yum install perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey

# rpm -ihv percona-toolkit-*.noarch.rpm

Debian/Ubuntu

# wget http://percona.com/get/percona-toolkit.deb
# dpkg -i percona-toolkit_*all.deb

1 2	# wget http://percona.com/get/percona-toolkit.deb # dpkg -i percona-toolkit_*all.deb

Далее запускаем обработку отчета

pt-query-digest /var/log/mysql/mysql-slow.log > mysql-slow-report.txt

1	pt-query-digest /var/log/mysql/mysql-slow.log > mysql-slow-report.txt

В результате мы получаем файл сгруппированных запросов

# 1.8s user time, 20ms system time, 12.62M rss, 17.92M vsz
# Current date: Wed May 10 10:42:31 2017
# Hostname: itfb.com.ua
# Files: full.log
# Overall: 1.26k total, 28 unique, 0.00 QPS, 0.02x concurrency ___________
# Time range: 2017-05-03 06:49:01 to 2017-05-10 09:51:34
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         15340s     10s     33s     12s     19s      3s     10s
# Lock time          802ms    63us    50ms   636us     1ms     3ms   144us
# Rows sent         29.12M       0  68.27k  23.66k  65.68k  28.76k  621.67
# Rows examine       4.50G     119  66.73M   3.66M   5.18M   9.61M 399.43k
# Rows affecte       6.14k       0   6.14k    4.99       0  173.42       0
# Bytes sent       101.27G      59 239.36M  82.30M 232.77M 102.27M 328.61k
# Query size       722.02k     127   1.79k  586.78  874.75  272.45  463.90

# Profile
# Rank Query ID           Response time   Calls R/Call  Apdx V/M   Item
# ==== ================== =============== ===== ======= ==== ===== =======
#    1 0xE49363E6D455B3DF 4958.5936 32.3%   479 10.3520 0.00  0.01 SELECT news news_sites
#    2 0xED0C3A92B8344DD5 3356.2854 21.9%   245 13.6991 0.00  1.24 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united
#    3 0xB06C6CFEF2342CAF 3082.7878 20.1%   235 13.1182 0.00  0.79 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united
#    4 0xE2550306AC65F9C2 1114.3149  7.3%    89 12.5204 0.00  0.32 SELECT comment users_auth afisha_events
#    5 0x202C6D66467DAA6B  965.6083  6.3%    78 12.3796 0.00  0.45 SELECT catalog_products
#    6 0x5A104D67C9C735B4  547.2057  3.6%    35 15.6344 0.00  1.05 SELECT afisha_event_images afisha_events_cats afisha_events afisha_cats catalog_firm
#    7 0x6DFF2AFCF4BCD61D  225.3614  1.5%    19 11.8611 0.00  0.15 SELECT job_vacancy job_vacancy_cat users_auth catalog_firm
#    8 0xE740BCB10CDA14C0  160.9528  1.0%     9 17.8836 0.00  0.04 SELECT afisha_event_images afisha_events_cats afisha_events afisha_cats catalog_firm
#    9 0xF20A42C624DBA35D  153.9288  1.0%    11 13.9935 0.00  0.15 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united
#   10 0x14F90BDF58FEC726  125.4272  0.8%     7 17.9182 0.00  0.22 SELECT news news_sites news_cat
# MISC 0xMISC              649.6202  4.2%    53 12.2570   NS   0.0 <18 ITEMS>

# Query 1: 0.00 QPS, 0.01x concurrency, ID 0xE49363E6D455B3DF at byte 874633
# Scores: Apdex = 0.00 [1.0], V/M = 0.01
# Query_time sparkline: |       ^|
# Time range: 2017-05-03 07:03:26 to 2017-05-10 09:06:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         38     479
# Exec time     32   4959s     10s     15s     10s     10s   369ms     10s
# Lock time     35   287ms    66us    50ms   599us   316us     4ms   113us
# Rows sent     98  28.58M  52.75k  68.27k  61.10k  65.68k   5.67k  59.57k
# Rows examine   1  59.55M 110.23k 141.15k 127.31k 136.54k  11.78k 123.85k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent    99 101.15G 194.81M 239.36M 216.23M 232.77M  17.64M 201.08M
# Query size    20 149.22k     319     319     319     319       0     319
# String:
# Databases    project
# Hosts
# Last errno   0
# Users        sphinx
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `citysite_project` LIKE 'news'\G
#    SHOW CREATE TABLE `citysite_project`.`news`\G
#    SHOW TABLE STATUS FROM `citysite_project` LIKE 'news_sites'\G
#    SHOW CREATE TABLE `citysite_project`.`news_sites`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT n.id, n.name, n.txt, n.author, n.source_name, n.fishka,             UNIX_TIMESTAMP( n.date_show ) AS date_show             FROM news AS n             LEFT JOIN news_sites ON news_sites.news_id = n.id             WHERE date_show <= NOW()             AND n.cat_id != 8             AND news_sites.site_id = @site_id\G
........

# 1.8s user time, 20ms system time, 12.62M rss, 17.92M vsz

# Current date: Wed May 10 10:42:31 2017

# Hostname: itfb.com.ua

# Files: full.log

# Overall: 1.26k total, 28 unique, 0.00 QPS, 0.02x concurrency ___________

# Time range: 2017-05-03 06:49:01 to 2017-05-10 09:51:34

# Attribute total min max avg 95% stddev median

# ============ ======= ======= ======= ======= ======= ======= =======

# Exec time 15340s 10s 33s 12s 19s 3s 10s

# Lock time 802ms 63us 50ms 636us 1ms 3ms 144us

# Rows sent 29.12M 0 68.27k 23.66k 65.68k 28.76k 621.67

# Rows examine 4.50G 119 66.73M 3.66M 5.18M 9.61M 399.43k

# Rows affecte 6.14k 0 6.14k 4.99 0 173.42 0

# Bytes sent 101.27G 59 239.36M 82.30M 232.77M 102.27M 328.61k

# Query size 722.02k 127 1.79k 586.78 874.75 272.45 463.90

# Profile

# Rank Query ID Response time Calls R/Call Apdx V/M Item

# ==== ================== =============== ===== ======= ==== ===== =======

# 1 0xE49363E6D455B3DF 4958.5936 32.3% 479 10.3520 0.00 0.01 SELECT news news_sites

# 2 0xED0C3A92B8344DD5 3356.2854 21.9% 245 13.6991 0.00 1.24 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united

# 3 0xB06C6CFEF2342CAF 3082.7878 20.1% 235 13.1182 0.00 0.79 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united

# 4 0xE2550306AC65F9C2 1114.3149 7.3% 89 12.5204 0.00 0.32 SELECT comment users_auth afisha_events

# 5 0x202C6D66467DAA6B 965.6083 6.3% 78 12.3796 0.00 0.45 SELECT catalog_products

# 6 0x5A104D67C9C735B4 547.2057 3.6% 35 15.6344 0.00 1.05 SELECT afisha_event_images afisha_events_cats afisha_events afisha_cats catalog_firm

# 7 0x6DFF2AFCF4BCD61D 225.3614 1.5% 19 11.8611 0.00 0.15 SELECT job_vacancy job_vacancy_cat users_auth catalog_firm

# 8 0xE740BCB10CDA14C0 160.9528 1.0% 9 17.8836 0.00 0.04 SELECT afisha_event_images afisha_events_cats afisha_events afisha_cats catalog_firm

# 9 0xF20A42C624DBA35D 153.9288 1.0% 11 13.9935 0.00 0.15 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_united

# 10 0x14F90BDF58FEC726 125.4272 0.8% 7 17.9182 0.00 0.22 SELECT news news_sites news_cat

# MISC 0xMISC 649.6202 4.2% 53 12.2570 NS 0.0 <18 ITEMS>

# Query 1: 0.00 QPS, 0.01x concurrency, ID 0xE49363E6D455B3DF at byte 874633

# Scores: Apdex = 0.00 [1.0], V/M = 0.01

# Query_time sparkline: | ^|

# Time range: 2017-05-03 07:03:26 to 2017-05-10 09:06:22

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count 38 479

# Exec time 32 4959s 10s 15s 10s 10s 369ms 10s

# Lock time 35 287ms 66us 50ms 599us 316us 4ms 113us

# Rows sent 98 28.58M 52.75k 68.27k 61.10k 65.68k 5.67k 59.57k

# Rows examine 1 59.55M 110.23k 141.15k 127.31k 136.54k 11.78k 123.85k

# Rows affecte 0 0 0 0 0 0 0 0

# Bytes sent 99 101.15G 194.81M 239.36M 216.23M 232.77M 17.64M 201.08M

# Query size 20 149.22k 319 319 319 319 0 319

# String:

# Databases project

# Hosts

# Last errno 0

# Users sphinx

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s

# 10s+ ################################################################

# Tables

# SHOW TABLE STATUS FROM `citysite_project` LIKE 'news'\G

# SHOW CREATE TABLE `citysite_project`.`news`\G

# SHOW TABLE STATUS FROM `citysite_project` LIKE 'news_sites'\G

# SHOW CREATE TABLE `citysite_project`.`news_sites`\G

# EXPLAIN /*!50100 PARTITIONS*/

SELECT n.id, n.name, n.txt, n.author, n.source_name, n.fishka, UNIX_TIMESTAMP( n.date_show ) AS date_show FROM news AS n LEFT JOIN news_sites ON news_sites.news_id = n.id WHERE date_show <= NOW() AND n.cat_id != 8 AND news_sites.site_id = @site_id\G

........

В данном файле формируется информация по топ 10 запросов

По первой таблице видно, что 1,2,3 отчеты занимают больше всего времени в виду их длительности и количество запусков.

Далее в файле следует информация по каждому номеру запроса из таблицы и имя БД в которой запрос был выполнен.

Далее подключаемся к БД из первого запроса

mysql> use project

1	mysql> use project

И выполняем план запроса предложенный в отчете

EXPLAIN /*!50100 PARTITIONS*/
SELECT n.id, n.name, n.txt, n.author, n.source_name, n.fishk
.................................

EXPLAIN /*!50100 PARTITIONS*/

SELECT n.id, n.name, n.txt, n.author, n.source_name, n.fishk

.................................

В результате мы получим план выполнения запроса.

Например

********************** 1. row **********************
id: 1
select_type: SIMPLE
table: citysite_project
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: 
1 row in set (0.00 sec)

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

id: 1

select_type: SIMPLE

table: citysite_project

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra:

1 row in set (0.00 sec)

У Вас вывод получится абсолютно другой, но будет все теже десять строк, вот что они означают:

id – номер запроса (их может быть несколько если есть подзапросы)
select_type – тип запроса SELECT.
- SIMPLE — Простой запрос SELECT без подзапросов или UNION
- PRIMARY – SELECT – самый внешний запрос в JOIN
- DEPENDENT SUBQUERY – подзапрос, который зависит от внешнего запроса
- DERIVED – SELECT является частью подзапроса внутри FROM
- SUBQUERY – первый SELECT в подзапросе
- UNCACHABLE SUBQUERY – подзапрос не кешируемый
- UNION – следующий SELECT в UNION
- DEPENDENT UNION – следующий SELECT в UNION, зависимый от внешнего запроса
- UNION RESULT – результат UNION
Table – таблица, по которой производится запрос
Type — показывает как MySQL связывает таблицы. Полезных полей в выводе,так как может показать отсутвие индексов.
Значения данного поля:
- System – таблица имеет только одну строку
- Const – таблица имеет только одну соответствующую строку, которая проиндексирована. Быстрый тип соединения.
- Fulltext – используется полнотекстовый индекс таблицы
- Eq_ref – все части индекса используются для связывания. Так же быстрый тип для соединения.
- Ref – все соответствующие строки индексного столбца считываются для каждой комбинации строк из предыдущей таблицы.
- Ref_or_null – то же самое, что и ref, но также содержит строки со значением null для столбца
- Index_merge – соединение использует список индексов для получения результирующего набора. Столбец key вывода команды EXPLAIN будет содержать список использованных индексов.
- Unique_subquery – подзапрос IN возвращает только один результат из таблицы и использует первичный ключ.
- Index_subquery – тоже, что и Unique_subquery, но возвращает более одного результата.
- Range – индекс, использованный для нахождения соответствующей строки в определенном диапазоне, обычно, когда ключевой столбец сравнивается с константой, используя операторы вроде: BETWEEN, IN, >, >=, etc.
- Index – сканируется все дерево индексов для нахождения соответствующих строк.
- All – Для нахождения соответствующих строк используются сканирование всей таблицы. Это самое худщее значение и говорит о том что у Вас нет индекса.
Possible_keys – показывает индексы, которые могут быть использованы для нахождения строк в таблице.
Key– указывает на использованный индекс.
Key_len – длина индекса, которую оптимизатор MySQL выбрал для использования.
Ref – указываются столбцы или константы, которые сравниваются с индексом, указанным в поле key.
Rows – отображает число записей, обработанных для получения выходных данных. Если число строк велико, то запрос также требует оптимизации
Extra – содержит дополнительную информацию, относящуюся к плану выполнения запроса. Например значения “Using temporary”, “Using filesort” показывают проблему в запросе.

Т.е. для группировки используется временная таблица, которая потом еще и сортируется, причем сортировка происходит без использования каких-либо индексов.

Если в запросе присутствует GROUP BY, то MySQL всегда будет сортировать результаты. Если порядок выдаваемых результатов вам не важен, то лучше избавиться от данной операции (сортировки).

Либо же создать же индекс по полю по которому происходит сортировка, а так же индексы по вторичным ключам во всех таблицах из запроса (вторичный ключ — это поле, которое используется в JOIN).

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

Оптимизация производительности сайта и сервера, обращайтесь [email protected]