Mysql лог запитів. Профілювання запитів в MySQL
Профілювання запитів MySQL - це корисний метод аналізу загальної продуктивності додатків на основі БД. При розробці середніх і великих додатків, як правило, сотні запитів розподіляються по великій базі коду, і БД обробляє безліч запитів в секунду. Без профілювання запитів стає дуже важко визначити знаходження і причини виникнення вузьких місць додатків. У цьому мануалі описані деякі корисні методи профілювання запитів за допомогою вбудованих інструментів MySQL.
Лог повільних запитів MySQL
Лог повільних запитів MySQL (або slow query log) - це лог, в який MySQL відправляє повільні і потенційно проблемні запити.
Ця функція поставляється з MySQL, але за замовчуванням відключена. MySQL визначає, які запити потрібно внести в цей лог, за допомогою спеціальних змінних, які дозволяють профілювати запит на основі вимог до продуктивності додатки. Зазвичай в цей лог вносяться запити, обробка яких займає більше часу, і запити, які неправильно індекси.
змінні профілювання
Базовими серверними змінними для настройки лога повільних запитів MySQL є:
slow_query_log глобальна
slow_query_log_file глобальна
long_query_time глобальна / сесійний
log_queries_not_using_indexes глобальна
min_examined_row_limit глобальна / сесійний
slow_query_log - логічна змінна для включення і виключення балки повільних запитів.
slow_query_log_file - абсолютний шлях до файлу логу запитів. Каталог файлу повинен належати користувачеві mysqld і мати відповідні права на читання і запис. Демон mysql, швидше за все, буде запущений як mysql, але щоб переконатися в цьому, запустіть команду в терміналі Linux:
ps -ef | grep bin / mysqld | cut -d "" -f1
Висновок покаже поточного користувача і користувача mysqld.
cd / var / log
mkdir mysql
chmod 755 mysql
chown mysql: mysql mysql
- long_query_time - час в секундах для перевірки довжини запиту. При значенні 5 в балці будуть реєструватися всі запити, обробка яких займає більше 5 секунд.
- log_queries_not_using_indexes - логічне значення, яке визначає, чи потрібно реєструвати запити, які не використовують індекси. При аналізі такі запити важливі.
- min_examined_row_limit - визначає мінімальну кількість рядків для аналізу. Зі значенням 1000 всі запити, які аналізують менше 1000 рядків, будуть проігноровані.
Змінні сервера MySQL можуть бути встановлені в файлі конфігурації MySQL або динамічно за допомогою призначеного для користувача інтерфейсу або командного рядка MySQL. Якщо змінні встановлені в файлі конфігурації, вони будуть зберігатися при перезапуску сервера, але для їх активації потрібно перезавантажити сервер. Конфігураційний файл MySQL зазвичай знаходиться в /etc/my.cnf або /etc/mysql/my.cnf. Щоб знайти конфігураційний файл, введіть (можливо, необхідно розширити пошук на інші кореневі каталоги):
find / etc -name my.cnf
find / usr -name my.cnf
Знайшовши конфігураційний файл, додавайте потрібні змінні в розділ:
….
slow-query-log \u003d 1
slow-query-log-file \u003d /var/log/mysql/localhost-slow.log
long_query_time \u003d 1
log-queries-not-using-indexes
Щоб зміни вступили в силу, потрібно перезапустити сервер. Якщо зміни необхідно активувати негайно, встановіть змінні динамічно:
mysql\u003e SET GLOBAL slow_query_log \u003d "ON";
mysql\u003e SET GLOBAL slow_query_log_file \u003d "/var/log/mysql/localhost-slow.log";
mysql\u003e SET GLOBAL log_queries_not_using_indexes \u003d "ON";
mysql\u003e SET SESSION long_query_time \u003d 1;
mysql\u003e SET SESSION min_examined_row_limit \u003d 100;
Щоб перевірити значення змінних:
mysql\u003e SHOW GLOBAL VARIABLES LIKE "slow_query_log";
mysql\u003e SHOW SESSION VARIABLES LIKE "long_query_time";
Один з недоліків динамічної зміни змінних MySQL полягає в тому, що змінні будуть втрачені при перезавантаженні сервера. Тому всі важливі змінні, які необхідно зберегти, слід додати в файл.
Генерування запиту для профілювання
Тепер ви знайомі з настройками логів повільних запитів. Спробуйте згенерувати дані запиту для профілювання.
Примітка: Наведений тут приклад був виконаний на занедбаному екземплярі MySQL без налаштованих логів повільних запитів. Ці тестові запити можна виконувати через графічний інтерфейс або командний рядок MySQL.
При моніторингу балки повільних запитів корисно відкрити два вікна терміналу: одне з'єднання для відправки операторів MySQL, а друге - для перегляду логу запитів.
Зайдіть на сервер MySQL за допомогою консолі як користувач з привілеями SUPER ADMIN. Для початку створіть тестову базу даних і таблицю, додайте в неї фіктивні дані і включіть лог повільних запитів.
Примітка: У ідеалі цей приклад краще виконувати в середовищі без будь-яких інших додатків, що використовують MySQL, щоб уникнути захаращення балки запитів.
$\u003e Mysql -u -p
mysql\u003e CREATE DATABASE profile_sampling;
mysql\u003e USE profile_sampling;
mysql\u003e CREATE TABLE users (id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (255));
mysql\u003e INSERT INTO users (name) VALUES ( "Walter"), ( "Skyler"), ( "Jesse"), ( "Hank"), ( "Walter Jr."), ( "Marie"), ( "Saul "), (" Gustavo "), (" Hector "), (" Mike ");
mysql\u003e SET GLOBAL slow_query_log \u003d 1;
mysql\u003e SET GLOBAL slow_query_log_file \u003d "/var/log/mysql/localhost-slow.log";
mysql\u003e SET GLOBAL log_queries_not_using_indexes \u003d 1;
mysql\u003e SET long_query_time \u003d 10;
mysql\u003e SET min_examined_row_limit \u003d 0;
Тепер у вас є тестова база даних і таблиця з невеликою кількістю даних. Лог повільних запитів включений. Ми навмисне встановили високий час обробки запиту і відключили перевірку кількості рядків. Щоб переглянути лог, введіть:
cd / var / log / mysql
ls -l
Поки що в папці не повинно бути логу повільних запитів, так як на даний момент запитів не було. Якщо ж такий лог вже є, це означає, що БД вже стикалася з повільними запитами з тих пір, як ви включили підтримку балки повільних запитів. Це може спотворити результати цього прикладу. Поверніться у вкладку MySQL і запустіть:
mysql\u003e USE profile_sampling;
mysql\u003e SELECT * FROM users WHERE id \u003d 1;
Виконаний запит просто отримує дані і використовує індекс першого ключа з таблиці. Цей запит був швидким і використовував індекс, тому в балці повільних запитів він не реєструється. Поверніться в каталог і переконайтеся, що лог запитів не було створено. Тепер поверніться у вікно MySQL і запустіть:
mysql\u003e
У цьому запиті не використовується індекс. Тепер в балці /var/log/mysql/localhost-slow.log повинна з'явитися приблизно такий запис:
# Time: 140322 13:54:58
use profile_sampling;
SET timestamp \u003d 1395521698;
Ще один приклад. Збільште мінімальну кількість рядків для аналізу і відправте такий запит:
mysql\u003e SET min_examined_row_limit \u003d 100;
mysql\u003e SELECT * FROM users WHERE name \u003d "Walter";
Дані не будуть додані в лог, оскільки при запиті було проаналізовано менше 100 рядків.
Примітка: Якщо дані не були додані в лог, потрібно перевірити кілька факторів. Спочатку перевірте права каталогу, в якому створюється лог. Він повинен належати користувачеві / групі mysqld і мати привілеї chmod 755. Потім слід перевірити, чи немає на сервері інших налаштувань повільних запитів, які скасовують ваші параметри. Скиньте значення за замовчуванням, щоб видалити всі змінні повільних запитів з конфігураційного файлу, і перезавантажте сервер. Також можна динамічно встановити глобальних змінних їх стандартні значення. Якщо ви вносите зміни динамічно, вийдіть із системи і увійдіть в MySQL, щоб оновити параметри.
Аналіз даних профілювання запиту
Розглянемо такі дані:
# Time: 140322 13:54:58
# [Email protected]: Root @ localhost
# Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10
use profile_sampling;
SET timestamp \u003d 1395521698;
SELECT * FROM users WHERE name \u003d "Jesse";
Ця запис відображає:
- Час виконання запиту
- Хто його відправив
- Як довго оброблявся запит
- довжину
- Скільки рядків було повернуто
- Скільки рядків було проаналізовано
Це корисно, тому що будь-який запит, який порушує зазначені в змінних вимоги до продуктивності, потрапляє в лог. Це дозволяє розробнику або адміністратору швидко відслідковувати неробочі запити. Крім того, дані профілювання запиту допоможуть вам визначити, які обставини знижують продуктивність додатків.
Використання mysqldumpslow
Профілювання можна включати в додатки на основі БД, щоб забезпечити помірний потік даних.
У міру зростання розміру балки стає важко розібрати всі дані, а проблемні запити легко в ньому загубляться. MySQL пропонує інструмент mysqldumpslow, який допомагає уникнути цієї проблеми шляхом поділу балки повільних запитів. Бінарний файл пов'язаний з MySQL (в Linux), тому можна просто запустити команду:
mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
Для налаштування виводу команда може приймати різні параметри. У наведеному вище прикладі будуть відображатися перші 5 запитів, відсортованих за середнім часом запиту. Такі рядки більш читабельні, а також згруповані за запитом.
Count: 2 Time \u003d 68.34s (136s) Lock \u003d 0.00s (0s) Rows \u003d 39892974.5 (79785949), [Email protected]
SELECT PL.pl_title, P.page_title
FROM page P
INNER JOIN pagelinks PL
ON PL.pl_namespace \u003d P.page_namespace
WHERE P.page_namespace \u003d N
…
У висновку відображені такі дані:
- Count: скільки разів запит потрапляв в лог.
- Time: середня і загальний час запиту (в дужках).
- Lock: час блокування таблиці.
- Rows: кількість повернутих рядків.
Команда виключає числові і рядкові значення, тому однакові запити з різними умовами WHERE сприймаються як однакові. Інструмент mysqldumpslow усуває необхідність постійно переглядати лог повільних запитів, замість цього дозволяючи проводити регулярні автоматичні перевірки. Параметри команди mysqldumpslow дозволяють виконувати складні вирази.
розбивка запитів
Ще один інструмент профілювання, про який слід пам'ятати, - це інструмент для складної розбивки запитів. Він дозволяє визначити проблемні запити в балці повільних запитів і запустити його в MySQL. Спочатку потрібно включити профілювання, а потім виконати запит:
mysql\u003e SET SESSION profiling \u003d 1;
mysql\u003e USE profile_sampling;
mysql\u003e SELECT * FROM users WHERE name \u003d "Jesse";
mysql\u003e SHOW PROFILES;
Після включення профілювання SHOW PROFILES покаже таблицю, яка пов'язує Query_ID з виразом SQL. Знайдіть Query_ID, відповідний запущеному запитом, і запустіть наступний запит (замініть # вашим Query_ID):
mysql\u003e SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID \u003d #;
Команда поверне таблицю:
| SEQ | STATE | DURATION |
| 1 | starting | 0.000046 |
| 2 | checking permissions | 0.000005 |
| 3 | opening tables | 0.000036 |
| … | … | … |
STATE - це крок в процесі виконання запиту, а DURATION - це час, необхідний для завершення цього кроку в секундах. Це не дуже корисний інструмент, але він може допомогти визначити, яка частина виконання запиту викликає найбільшу затримку.
Примітка: Цей інструмент не слід використовувати в середовищі виробництва.
Продуктивність балки повільних запитів
Залишилося тільки розібратися, як лог повільних запитів впливає на продуктивність. В цілому запускати лог повільних запитів в виробничому середовищі безпечно; ні CPU, ні I / O не повинні постраждати. Проте, у вас повинна бути стратегія моніторингу розміру балки, щоб лог не став надто великим для файлової системи. Крім того, при запуску балки повільних запитів в середовищі виробництва слід встановити long_query_time значення 1 або вище.
висновок
Лог повільних запитів може допомогти визначити проблемні запити і оцінити загальну продуктивність запитів. При цьому розробник може отримати докладне уявлення про те, як в додатку виконуються запити MySQL. Інструмент mysqldumpslow дозволяє управляти балками повільних запитів і легко включати їх в процес розробки. Визначивши проблемні запити, ви можете оптимізувати обробку запитів для підвищення продуктивності.
Tags:поняття
Список сервера (лог-файли, журнал сервера) - фали, що зберігаються на сервері, які містять системну інформацію сервера, а так-же протоколюється всі можливі дані про відвідувача веб-ресурсу.
Список використовуються системними адміністраторами для аналізу відвідувачів, Вивчення закономірності поведінки певних груп користувачів, а так-же отримання різної інформації про них, такий як: використовуваний браузер, IP адреса, дані про географічне положення клієнта і багато іншого. По мимо аналізу, таким чином можна дізнатися про несанкціоновані доступи до сайту, точніше дізнатися, ким саме він був проведений, і передати дані про даний випадок у відповідні органи.
Дані в лог-файлі, в чистому вигляді, будуть не зрозумілим пересічним користувачам, які побачать в усьому цьому всього-лише набір символів в незрозумілому порядку. Але для системних адміністраторів і веб-розробників, це є цілком читабельним текстом, і досить корисною інформацією.
послідовність подій
При кожному зверненні клієнта до веб-ресурсeсе, спрацьовують одразу кілька подій, про послідовність яких ми і поговоримо.
1. Здійснення запиту сторінки. При введенні адреси в браузерну рядок, або при переході по активний веб-посиланням, припустимо, зі сторінки пошукової видачі, браузер здійснює пошук і з'єднання з сервером, на якому розташована сторінки, і здійснює її запит. При цьому, він передає серверу таку інформацію:
- IP-адреса комп'ютера клієнта, який запитує сторінку (в разі використання проксі-сервера, IP адреса вашого проксі);
- адреса запитуваної користувачем інтернет-сторінки (IP-адреса);
- точний час і дата, коли було здійснено запит;
- дані про фактичне місцезнаходження клієнта (в разі якщо використовується проксі-сервер, то фактична адреса проксі);
- інформація про використаний клієнтом браузері (назва, версія і т.д.);
- дані про веб-сторінки, з якої було здійснено перехід клієнта.
2. Передача запитуваних даних. Відбувається передача запитуваних даних (інтернет-сторінка, файли, cooki, і ін.) Від сервера на комп'ютер користувача.
3. Запис в журнал сервера. Після всього, відбувається запис в журнал, в якому вказуються всі дані, які фігурували в минулих двох подіях. Це вся інформація відправлена \u200b\u200bв першому пункті, а так-же інформація про переданих даних.
Як подивитися логи сервера
Лог-файли, зберігаються в файлі access.log не залежно від того, яким типом веб-сервера ви користуєтеся (Apache, Nginx, проксі-сервером squid і т. д.) Даний файл є текстовим документом, на кожному рядку якого, записується по одному зверненню. Форматів запису в access.log досить багато, але найбільш популярним є combined, при якому, запис має такий вигляд і послідовність:
Код:% h% l% u% t \\ "% r \\"%\u003e s% b \\ "% (Referer) i \\" \\ "% (User-Agent) i \\"
де:
% h - хост / IP-адреса, з якого зроблений запит;
% t - час запиту до сервера і часовий пояс сервера;
% r - версія, вміст і тип запиту;
% s - код стану HTTP;
% b - кількість відданих сервером байт;
% (Referer) - URL-джерело запиту;
% (User-Agent) - HTTP-заголовок, з інформацією про запит (клієнтську програму, мову і т.д.);
% (Host) - ім'я Virtual Host, до якого йде звернення.
в готовому вигляді даний рядок має приблизно такий вигляд:
127.0.0.1 - - "GET /index.php HTTP / 1..0 (compatible; MSIE 7.0; Windows NT 5.1)"
На читання логів в ручну, піде досить багато часу і сил. Тому, досвідчені веб-майстри використовують спеціальне програмне забезпечення, які називають «Аналізатори лог-файлів». Вони аналізують всі дані, які досить складні до прочитання людиною, і видають структуровані дані. Це такі програми як: Analog, WebAnalizer, Webalizer, Awstats, Webtrends, і т.д. Видів спеціального програмного забезпечення досить багато, серед них є як платні програми, так і безкоштовні. Тому я впевнений, що кожен знайде собі щось до душі.
Де знайти логи сайту
Якщо у вас звичайний хостинг, то скоріше за все вам доведеться написати своєму хостера і запитати у нього логи. Так-же, досить часто, ви можете запросити їх через панель хостера. У різних хостерів - по різному. Для прикладу, що б запитати у мого хостера, досить зробити один клік на головній сторінці панелі:
Якщо у вас є доступ до системних папок сервера, то ви можете знайти логи за адресою / Etc / httpd / logs / access_log в 99 випадках з 100.
Журнал помилок error.log
Error.log - файл, в якому так-же ведуться логи. Але не відвідувачів, а виникли на сервері помилок. Як і у випадку з access.log, Кожен рядок файлу - відповідає за одну виникла помилку. Запис ведеться з урахуванням такої інформації, як: точна дата і час виникнення помилки, IP-адреса якого була видана помилка, тип помилки, а так-же причина її виникнення.
висновок
Список є досить потужним і інформативним інструментом, з якими необхідно працювати. Але в наш час, їх замінюють такі інструменти, як Яндекс.Метрика, Google Analytics і т.д., тим самим спрощуючи нам життя. Однак, якщо ви плануєте розвиватися, рости і пізнавати щось нове, безсумнівно рекомендую вам познайомитися з даною темою ближче.
Журнали подій - перший і найпростіший інструмент для визначення статусу системи і виявлення помилок. Основних логів в MySQL чотири:
- Error Log - стандартний лог помилок, які збираються під час роботи сервера (в тому числі start і stop);
- Binary Log - лог всіх команд зміни БД, потрібен для реплікації і бекапов;
- General Query Log - основний лог запитів;
- Slow Query Log - лог повільних запитів.
лог помилок
Цей журнал містить всі помилки, які сталися під час роботи сервера, включаючи критичні помилки, а також зупинки, включення сервера і попередження (warnings). З нього потрібно почати в разі збою системи. За замовчуванням всі помилки виводяться в консоль (stderr), також можна записувати помилки в syslog (за замовчуванням в Debian) або окремий лог-файл:
Log_error \u003d / var / log / mysql / mysql_error.log
# Помилки будуть писатися в mysql_error.log
Рекомендуємо тримати цей журнал включеним для швидкого визначення помилок. А для розуміння, що означає та чи інша помилка, в MySQL присутня утиліта perror:
Shell\u003e perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network
# Пояснює значення кодів помилок
Бінарний (він же двійковий) лог
У бінарний лог записуються всі команди зміни бази даних, стане в нагоді для реплікації і відновлення.
Чи включається так:
Log_bin \u003d /var/log/mysql/mysql-bin.log expire_logs_days \u003d 5 max_binlog_size \u003d 500M
# Вказує розташування, термін життя і максимальний розмір файлу
Врахуйте, що якщо ви не збираєтеся масштабувати систему і реалізовувати відмовостійкість, то бінарний лог краще не включати. Він вимогливий до ресурсів і знижує продуктивність системи.
лог запитів
У цьому журналі містяться всі отримані SQL-запити, інформація про підключення клієнтів. Може стати в нагоді для аналізу індексів і оптимізації, а також виявлення помилкових запитів:
General_log_file \u003d /var/log/mysql/mysql.log general_log \u003d 1
# Включає лог і вказує розташування файлу
Також його можна включити / відключити під час роботи сервера MySQL:
SET GLOBAL general_log \u003d "ON"; SET GLOBAL general_log \u003d "OFF";
# Для застосування не потрібно перезавантажувати сервер
Лог повільних запитів
Журнал стане в нагоді для визначення повільних, тобто неефективних запитів. Детальніше читайте в цій статті.
Перегляд логів
Для перегляду логів на Debian (Ubuntu) потрібно виконати:
# Лог помилок tail -f / var / log / syslog # Лог запитів tail -f /var/log/mysql/mysql.log # Лог повільних запитівtail -f /var/log/mysql/mysql-slow.log
# Якщо логи не вказані окремо, то знаходяться в / var / lib / mysql
ротація логів
Не забувайте стискати (архівувати, обертатися) лог-файли, щоб вони займали менше місця на сервері. Для цього використовуйте утиліту logrotate, Відредагувавши файл конфігурації /etc/logrotate.d/mysql-server:
# - I put everything in one block and added sharedscripts, so that mysql gets # flush-logs "d only once. # Else the binary logs would automatically increase by n times every day. # - The error log is obsolete, messages go to syslog now./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log (Daily rotate 7 missingok create 640 mysql adm compress sharedscripts postrotate test -x / usr / bin / mysqladmin || exit 0 # If this fails, check debian.conf! MYADMIN \u003d "/ usr / bin / mysqladmin --defaults-file \u003d / etc / mysql / debian.cnf" if [-z "` $ MYADMIN ping 2\u003e / dev / null` "]; then # Really no mysqld or rather a missing debian-sys-maint user? # If this occurs and is not an error please report a bug. #if ps cax | grep -q mysqld; then if killall -q -s0 -umysql mysqld; then exit 1 fi else $ MYADMIN flush-logs fi endscript)
# Стискає і архівує потрібні логи, очищає файли
DDL Log
MySQL також веде лог мови опису даних. У нього збираються дані операцій типу DROP_TABLE and ALTER_TABLE. Лог використовується для відновлення після збоїв, які сталися під час виконання таких операцій. DDL Log - бінарний файл, що не призначений для читання користувачем, тому не змінюйте і не видаляйте його.
Найголовніше
Завжди включайте лог помилок, використовуйте лог запитів для перевірки з'єднання додатки з базою даних, перевірки запитів і роботи. Лог повільних запитів знадобиться для оптимізації роботи MySQL.
Профілювання запитів в Mysql застосовується для оцінки продуктивності вашого застосування. При розробці середніх і великих додатків доводиться мати справу з сотнями запитів розподіленими по вашому коду, які виконуються кожну секунду. Без техніки профілювання запитів буває дуже складно знайти через що страждає швидкодію вашої програми.
Що таке лог повільних запитів в MySQL?
Лог повільних запитів в MySQL - лог, що відзначає повільні і потенційно проблемні запити. MySQL за замовчуванням підтримує такий функціонал, але він відключений. За допомогою налаштування певних змінних сервера ми можемо вказати які саме запити нас цікавлять. Найчастіше нам потрібні запити які вимагають певну кількість часу на своє виконання або запити, які некоректно обробляють індекси.
Налаштування змінних профілювання
Основні змінні для настройки лога запитів:
Slow_query_log G slow_query_log_file G long_query_time G / S log_queries_not_using_indexes G min_examined_row_limit G / S
зауваження: G - глобальні змінні, S - системні змінні
- slow_query_log - логічне значення включає лог
- slow_query_log_file - абсолютний шлях до файлу журналу. Власником каталогу повинен бути користувач mysqld, А також директорія повинна мати коректні дозволу для читання і запису. Найчастіше демон mysql працює від імені користувача mysql.
Для перевірки виконайте наступну команди:
Ps -ef | grep bin / mysqld | cut -d "" -f1
Висновок команди дасть вам ім'я поточного користувача і користувача mysqld. Приклад настройки директорії / var / log / mysql:
Cd / var / log sudo mkdir mysql sudo chmod 755 mysql sudo chown mysql: mysql mysql
- long_query_time - час в секундах для перевірки тривалості запиту. Наприклад, при значенні 5, кожен запит тривалістю більше 5 секунд буде записаний в лог.
- log_queries_not_using_indexes - логічне значення, включає збереження запитів не використовують індекси. Такі запити дуже важливі при аналізі.
- min_examined_row_limit - вказує мінімальне значення кількості рядів даних для аналізу. Значення 1000 проігнорує запити повертають менше 1000 рядів значень.
Встановити ці змінні можна в файлі конфігурації MySQL, динамічно через MySQL GUI або командний рядок MySQL. Якщо змінні вказані в файлі конфігурації, то сервер встановить їх при черговому запуску. Зазвичай цей файл розташовується за адресою / etc, / usr, /etc/my.cnf або /etc/mysql/my.cnf. Ось команди пошуку файлу конфігурації (іноді слід розширити пошук на інші кореневі каталоги):
Find / etc -name my.cnf find / usr -name my.cnf
Коли знайдете файл, додавайте потрібні змінні в розділі:
; ... slow-query-log \u003d 1 slow-query-log-file \u003d /var/log/mysql/localhost-slow.log long_query_time \u003d 1 log-queries-not-using-indexes; тут не треба значення
Зміни вступлять тільки при черговому запуску MySQL, якщо вам необхідно динамічна зміна параметрів, використовуйте інші методи установки змінних:
Mysql\u003e SET GLOBAL slow_query_log \u003d "ON"; mysql\u003e SET GLOBAL slow_query_log_file \u003d "/var/log/mysql/localhost-slow.log"; mysql\u003e SET GLOBAL log_queries_not_using_indexes \u003d "ON"; mysql\u003e SET SESSION long_query_time \u003d 1; mysql\u003e SET SESSION min_examined_row_limit \u003d 100;
Перевірити значення змінних можна наступним чином:
Mysql\u003e SHOW GLOBAL VARIABLES LIKE "slow_query_log"; mysql\u003e SHOW SESSION VARIABLES LIKE "long_query_time";
Основний недолік динамічної установки - значення будуть втрачені при запуску системи. Рекомендується вказувати важливі параметри в конфіги MySQL.
замітка: Синтаксис динамічної установки параметрів через команду SET і з використанням конфігураційного файлу трохи різниться, наприклад slow_query_log / slow-query-log. В офіційній документації СУБД ви знайдете повний опис синтаксису. Формат Option-File використовується для файлу конфігурації, System Variable Name - імена змінних при динамічної установці значень.
Генерація даних для профілювання запиту
Ми розглянули основні пункти настройки профілювання, тепер створимо цікавлять нас запити. Цей приклад використовувався на занедбаному MySQL сервері без будь-яких попередніх налаштувань журналу. Приклади запитів можна запускати як через MySQL GUI, так і командними засобами СУБД. При моніторингу балки повільних запитів, часто відкривають два вікна з з'єднанням: одне для запуску запитів, інше - для перегляду журналу.
$\u003e Mysql -u -p mysql\u003e CREATE DATABASE profile_sampling; mysql\u003e USE profile_sampling; mysql\u003e CREATE TABLE users (id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (255)); mysql\u003e INSERT INTO users (name) VALUES ( "Walter"), ( "Skyler"), ( "Jesse"), ( "Hank"), ( "Walter Jr."), ( "Marie"), ( "Saul "), (" Gustavo "), (" Hector "), (" Mike "); mysql\u003e SET GLOBAL slow_query_log \u003d 1; mysql\u003e SET GLOBAL slow_query_log_file \u003d "/var/log/mysql/localhost-slow.log"; mysql\u003e SET GLOBAL log_queries_not_using_indexes \u003d 1; mysql\u003e SET long_query_time \u003d 10; mysql\u003e SET min_examined_row_limit \u003d 0;
Тепер у нас є БД з тестовими даними. Ми запустили профілювання, але час спрацьовування і кількість рядків ми спеціально встановили малим. Щоб переглянути лог скористайтеся командою:
Cd / var / log / mysql ls -l
За ідеї, файлу лога ще не повинно існувати, так як ми не зробили запитів до БД. Якщо ж він існує, то це означає що профілювання було налаштоване раніше, а це може спотворити результати прикладу. Виконайте в консолі:
Mysql\u003e USE profile_sampling; mysql\u003e SELECT * FROM users WHERE id \u003d 1;
Наш запит використовує Primary Key індекс з таблиці. Запит відпрацював дуже швидко з використанням індексу, тому в балці він не повинен позначитися. Зверніть увагу, файл логу не повинен був створитися.
Тепер виконайте наступне:
Mysql\u003e SELECT * FROM users WHERE name \u003d "Jesse";
Тут ми не використали індексів. Тепер ми повинні побачити цей запит в балці:
Sudo cat /var/log/mysql/localhost-slow.log # Time: 140322 13:54:58 # [Email protected]: Root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp \u003d 1395521698; SELECT * FROM users WHERE name \u003d "Jesse";
Розглянемо ще один приклад. Підніміть планку кількості рядків у відповіді і виконайте наступний запит:
Mysql\u003e SET min_examined_row_limit \u003d 100; mysql\u003e SELECT * FROM users WHERE name \u003d "Walter";
Запит не відіб'ється в балці, так як ми не перевищили 100 рядків у відповіді на запит.
замітка: Якщо дані не відображаються в балці, то в першу чергу потрібно розглянути наступні фактори. Перше - права на каталог, де зберігається файл журналу. Група і користувач повинні відповідати користувачеві mysqld, права повинні бути - chmod 755. Друге - можливо профілювання було налаштоване раніше. Видаліть всі існуючі значення змінних профілізації виробів із файлу конфігурації і перезапустіть сервер або встановіть змінні динамічно. Якщо ви використовували динамічний метод, то вийдете і зайдіть знову в консоль MySQL.
Аналіз даних профілювання запитів
Розглянемо вищенаведений приклад:
# Time: 140322 13:54:58 # [Email protected]: Root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp \u003d 1395521698; SELECT * FROM users WHERE name \u003d "Jesse";
Тут ми бачимо:
- Час, коли був запущений запит
- Користувач, який виконав запит
- Час роботи запити
- тривалість блокування
- Кількість обраних рядків
- Кількість проаналізованих рядків
Ці дані дуже корисні, так як при їх допомоги ми зможемо знайти і усунути причину уповільнення системи. Так само розробник або адміністратор MySQL завжди зможе побачити проблемні запити і хочеться відзначити, що тут їх знайти набагато швидше, ніж вивчаючи програмний код програми. При тривалому профілювання можна відстежити умови роботи при низькому швидкодії.
Використання mysqldumpslow
Лог постійно записує дані, як правило, пише він набагато більше, ніж з нього читають. При великому розмірі балки, читати його стає проблематично. До складу MySQL входить інструмент mysqldumpslow, який допомагає зберегти цілісність журналу. Сама програма поєднана з MySQL (на Linux системах). Для її використання виконайте необхідну команду і передайте їй шлях до лог файлу:
Sudo mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
Існує ряд параметрів допомагають налаштувати висновок команди. У прикладі нижче ми побачимо п'ять останніх запитів відсортованих за середньою тривалістю. В результаті читати лог стає набагато зручніше. (Висновок змінений, щоб показати реальні значення в балці):
Count: 2 Time \u003d 68.34s (136s) Lock \u003d 0.00s (0s) Rows \u003d 39892974.5 (79785949), [Email protected] SELECT PL.pl_title, P.page_title FROM page P INNER JOIN pagelinks PL ON PL.pl_namespace \u003d P.page_namespace WHERE P.page_namespace \u003d N ...
Що ми бачимо:
- Count - кількість входжень запиту в лог
- Time - середнє і загальний час запиту
- Lock - час блокування таблиці
- Rows - Кількість обраних рядків
Команда виключає числові і рядкові дані запиту, тобто запити з однаковим умовою WHERE будуть вважатися однаковими. Завдяки такому інструменту не доводиться постійно переглядати лог. За рахунок великої кількості параметрів команди, можна відсортувати висновок як вам зручно. Так само існують розробки сторонніх розробників зі схожим функціоналом, наприклад pt-query-digest.
розбивка запитів
Слід приділити увагу ще одному інструменту, який дозволяє розбивати складні запити. Найчастіше доводиться брати запит з балки, а потім запускати його прямо в консолі MySQL. Спочатку треба включити профілювання, а потім виконати запит:
Mysql\u003e SET SESSION profiling \u003d 1; mysql\u003e USE profile_sampling; mysql\u003e SELECT * FROM users WHERE name \u003d "Jesse"; mysql\u003e SHOW PROFILES;
Після включення профілювання, SHOW PROFILES покаже таблицю зв'язує Query_ID і SQL вираз. Знайдіть відповідний Query_ID і виконайте наступний запит (замініть # на ваш Query_ID):
Mysql\u003e SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID \u003d #;
Приклад виведення:
SEQ STATE DURATION 1 starting 0.000046 2 checking permissions 0.000005 3 opening tables 0.000036
STATE - крок в процесі виконання запиту, DURATION - тривалість кроку в секундах. Цей інструмент не так вже й часто використовується, але іноді він може бути надзвичайно корисний у визначенні причини зниження швидкості запиту.
Детальний опис стовпців:
Детальний опис кроків:
замітка: Цей інструмент не слід використовувати в робочому режимі сервера, за винятком аналізу специфічних запитів.
Швидкодія балки повільних запитів
Останнє питання - як позначається робота профілювання на швидкодію сервера в цілому. У продуктовому режимі сервера можна цілком спокійно використовувати таке логирование, воно не повинно позначатися ні на CPU ні на I / O. Проте варто звернути увагу на розмір файлу лога, він не повинен стає непомірно великим. Так само з досвіду хотілося б відзначити, що встановлювати значення змінної long_query_time варто рівним від 1 секунди і вище.
важливо: Не варто використовувати інструмент профілювання - SET profiling \u003d 1 - для запису всіх запитів, тобто змінну general_log в продуктовому режимі і при великих навантаженнях не рекомендується використовувати.
висновок
Профілювання запитів може вам дуже допомогти у виділенні проблемного запиту і в оцінці загальної продуктивності. Також розробник може вивчити як працюють запити MySQL його додатки. Інструмент mysqldumpslow допомагає переглядати і обробляти лог запитів. Після визначення проблемних запитів залишиться налаштувати їх на максимальну продуктивність.
