Тормозит БД mysql

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

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

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

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

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

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

log-queries-not-using-indexes

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

/etc/init.d/mysql restart

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

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

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

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

Debian/Ubuntun

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

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

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

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

# 1.8s user time, 20ms system time, 12.62M rss, 17.92M vszn# Current date: Wed May 10 10:42:31 2017n# Hostname: itfb.com.uan# Files: full.logn# Overall: 1.26k total, 28 unique, 0.00 QPS, 0.02x concurrency ___________n# Time range: 2017-05-03 06:49:01 to 2017-05-10 09:51:34n# Attribute          total     min     max     avg     95%  stddev  mediann# ============     ======= ======= ======= ======= ======= ======= =======n# Exec time         15340s     10s     33s     12s     19s      3s     10sn# Lock time          802ms    63us    50ms   636us     1ms     3ms   144usn# Rows sent         29.12M       0  68.27k  23.66k  65.68k  28.76k  621.67n# Rows examine       4.50G     119  66.73M   3.66M   5.18M   9.61M 399.43kn# Rows affecte       6.14k       0   6.14k    4.99       0  173.42       0n# Bytes sent       101.27G      59 239.36M  82.30M 232.77M 102.27M 328.61kn# Query size       722.02k     127   1.79k  586.78  874.75  272.45  463.90nn# Profilen# Rank Query ID           Response time   Calls R/Call  Apdx V/M   Itemn# ==== ================== =============== ===== ======= ==== ===== =======n#    1 0xE49363E6D455B3DF 4958.5936 32.3%   479 10.3520 0.00  0.01 SELECT news news_sitesn#    2 0xED0C3A92B8344DD5 3356.2854 21.9%   245 13.6991 0.00  1.24 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_unitedn#    3 0xB06C6CFEF2342CAF 3082.7878 20.1%   235 13.1182 0.00  0.79 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_unitedn#    4 0xE2550306AC65F9C2 1114.3149  7.3%    89 12.5204 0.00  0.32 SELECT comment users_auth afisha_eventsn#    5 0x202C6D66467DAA6B  965.6083  6.3%    78 12.3796 0.00  0.45 SELECT catalog_productsn#    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_firmn#    7 0x6DFF2AFCF4BCD61D  225.3614  1.5%    19 11.8611 0.00  0.15 SELECT job_vacancy job_vacancy_cat users_auth catalog_firmn#    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_firmn#    9 0xF20A42C624DBA35D  153.9288  1.0%    11 13.9935 0.00  0.15 SELECT catalog_products catalog_firm subdomain catalog_packets catalog_unitedn#   10 0x14F90BDF58FEC726  125.4272  0.8%     7 17.9182 0.00  0.22 SELECT news news_sites news_catn# MISC 0xMISC              649.6202  4.2%    53 12.2570   NS   0.0 <18 ITEMS>nn# Query 1: 0.00 QPS, 0.01x concurrency, ID 0xE49363E6D455B3DF at byte 874633n# Scores: Apdex = 0.00 [1.0], V/M = 0.01n# Query_time sparkline: |       ^|n# Time range: 2017-05-03 07:03:26 to 2017-05-10 09:06:22n# Attribute    pct   total     min     max     avg     95%  stddev  mediann# ============ === ======= ======= ======= ======= ======= ======= =======n# Count         38     479n# Exec time     32   4959s     10s     15s     10s     10s   369ms     10sn# Lock time     35   287ms    66us    50ms   599us   316us     4ms   113usn# Rows sent     98  28.58M  52.75k  68.27k  61.10k  65.68k   5.67k  59.57kn# Rows examine   1  59.55M 110.23k 141.15k 127.31k 136.54k  11.78k 123.85kn# Rows affecte   0       0       0       0       0       0       0       0n# Bytes sent    99 101.15G 194.81M 239.36M 216.23M 232.77M  17.64M 201.08Mn# Query size    20 149.22k     319     319     319     319       0     319n# String:n# Databases    projectn# Hostsn# Last errno   0n# Users        sphinxn# Query_time distributionn#   1usn#  10usn# 100usn#   1msn#  10msn# 100msn#    1sn#  10s+  ################################################################n# Tablesn#    SHOW TABLE STATUS FROM `citysite_project` LIKE 'news'\Gn#    SHOW CREATE TABLE `citysite_project`.`news`\Gn#    SHOW TABLE STATUS FROM `citysite_project` LIKE 'news_sites'\Gn#    SHOW CREATE TABLE `citysite_project`.`news_sites`\Gn# EXPLAIN /*!50100 PARTITIONS*/nSELECT 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\Gn........

В данном файле формируется информация по топ 10 запросовnnПо первой таблице видно, что 1,2,3 отчеты занимают больше всего времени в виду их длительности и количество запусков.nnДалее в файле следует информация по каждому номеру запроса из таблицы и имя БД в которой запрос был выполнен.nnДалее подключаемся к БД из первого запросаn

mysql> use project

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

EXPLAIN /*!50100 PARTITIONS*/nSELECT n.id, n.name, n.txt, n.author, n.source_name, n.fishkn.................................

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

********************** 1. row **********************nid: 1nselect_type: SIMPLEntable: citysite_projectntype: ALLnpossible_keys: NULLnkey: NULLnkey_len: NULLnref: NULLnrows: 4nExtra: n1 row in set (0.00 sec)

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

nnТ.е. для группировки используется временная таблица, которая потом еще и сортируется, причем сортировка происходит без использования каких-либо индексов.nnЕсли в запросе присутствует GROUP BY, то MySQL всегда будет сортировать результаты. Если порядок выдаваемых результатов вам не важен, то лучше избавиться от данной операции (сортировки).nnЛибо же создать же индекс по полю по которому происходит сортировка, а так же индексы по вторичным ключам во всех таблицах из запроса (вторичный ключ — это поле, которое используется в JOIN).nnСохраняя выводы explaine запросов вы получите файл, который необходимо передать программисту, что бы он смог пересмотреть планы выполнения тяжелых запросов, добавить не достающих индексов, возможно изменить порядок join, исключить не обязательные условия и сортировки.nnОптимизация производительности сайта и сервера, обращайтесь [email protected]