Что делать и как решить проблемы с производительностью базы данных 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)
У Вас вывод получится абсолютно другой, но будет все теже десять строк, вот что они означают:[sociallocker]n
- id – номер запроса (их может быть несколько если есть подзапросы)
- select_type – тип запроса SELECT.n
- 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 связывает таблицы. Полезных полей в выводе,так как может показать отсутвие индексов.nЗначения данного поля:n
- 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” показывают проблему в запросе.
[/sociallocker]nnТ.е. для группировки используется временная таблица, которая потом еще и сортируется, причем сортировка происходит без использования каких-либо индексов.nnЕсли в запросе присутствует GROUP BY, то MySQL всегда будет сортировать результаты. Если порядок выдаваемых результатов вам не важен, то лучше избавиться от данной операции (сортировки).nnЛибо же создать же индекс по полю по которому происходит сортировка, а так же индексы по вторичным ключам во всех таблицах из запроса (вторичный ключ — это поле, которое используется в JOIN).nnСохраняя выводы explaine запросов вы получите файл, который необходимо передать программисту, что бы он смог пересмотреть планы выполнения тяжелых запросов, добавить не достающих индексов, возможно изменить порядок join, исключить не обязательные условия и сортировки.nnОптимизация производительности сайта и сервера, обращайтесь [email protected]