Связаться по:
vkarabedyants Telegram Viber
+7 (499) 350-10-69

Блог о системном администрировании серверов и сайтов

Установка, настройка программного обеспечения Linux, Windows операционных систем

Тормозит БД mysql

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

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

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

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

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

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

/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


Debian/Ubuntu

# 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.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

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

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)

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

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

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

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

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

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

1 Response

  1. Pingback : Отслеживание времени выполнения PHP скриптов » Администрирование серверов

Оставить комментарий

Лимит времени истёк. Пожалуйста, перезагрузите CAPTCHA.