Si të aktivizoni regjistrin e ngadaltë të pyetjeve të MySQL

Pyetjet e papritura të ngadalta janë një nga çështjet më të zakonshme të performancës së MySQL. Një pyetje që funksionon në mënyrë të pranueshme në zhvillim mund të lëkundet kur stresohet nga një ngarkesë prodhimi.

Aplikacionet e mëdha mund të ekzekutojnë qindra pyetje unike të bazës së të dhënave sa herë që goditet një pikë fundore. Kjo e bën të vështirë të veçosh pyetjet që po shkaktojnë vonesa në përgjigjen e serverit. Regjistri i ngadaltë i pyetjeve të MySQL është një opsion korrigjimi që mund t'ju ndihmojë të identifikoni deklaratat e dyshimta SQL, duke ofruar një pikënisje për hetimet tuaja.

Aktivizimi i regjistrit të ngadalshëm të pyetjeve

Regjistri është një mekanizëm i integruar për regjistrimin e pyetjeve të gjata SQL. Pyetjet që nuk përfundojnë brenda një kohe të konfiguruar do të shkruhen në regjistër. Leximi i përmbajtjes së regjistrit ju tregon SQL-në që është ekzekutuar dhe kohën e marrë.

Regjistrimi i ngadaltë i pyetjeve është çaktivizuar si parazgjedhje. Mund ta aktivizoni në serverin tuaj duke ekzekutuar komandën e mëposhtme nga një guaskë administrative MySQL:

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL slow_query_log=1;

Ndryshimi zbatohet menjëherë. Pyetjet e ngadalta tani do të regjistrohen në /var/log/mysql/mysql-slow.log. Ju mund ta rishikoni këtë skedar në mënyrë periodike për të identifikuar pyetjet me performancë të dobët.

MySQL numëron një pyetje si të ngadaltë nëse duhen më shumë se 10 sekonda për të përfunduar. Ky kufi zakonisht është shumë i relaksuar për aplikacionet në internet që përballen me përdoruesit, ku priten përgjigje pothuajse të menjëhershme. Ju mund ta ndryshoni kufirin duke vendosur variablin long_query_time:

SET GLOBAL long_query_time=1;

Vlera cakton kohëzgjatjen minimale për pyetjet e ngadalta. Është e rëndësishme të gjesh një ekuilibër që i përshtatet aplikacionit tuaj. Një prag shumë i lartë do të përjashtojë pyetjet që në fakt po ndikojnë në performancën. Në të kundërt, vlerat shumë të ulëta mund të shkaktojnë kapjen e shumë pyetjeve, duke krijuar një regjistër tepër të zhurmshëm.

Duke përdorur skedarin e konfigurimit të MySQL

Duhet të aktivizoni regjistrin e ngadaltë të pyetjeve në skedarin tuaj të konfigurimit MySQL nëse planifikoni ta përdorni atë për një kohë të gjatë. Kjo do të sigurojë që regjistrimi të rifillojë automatikisht pas rinisjes së serverit MySQL.

Vendndodhja e skedarit të konfigurimit mund të ndryshojë sipas shpërndarjes së platformës. Zakonisht është në /etc/mysql/my.cnf ose /etc/mysql/mysql.conf.d/mysqld.cnf. Shtoni linjat e mëposhtme për të përsëritur cilësimet që u aktivizuan në mënyrë dinamike më lart:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Rinisni MySQL për të aplikuar ndryshimet tuaja:

$ sudo service mysql restart

Regjistri i ngadaltë i pyetjeve tani do të jetë aktiv sa herë që fillon serveri MySQL.

Përshtatja e përmbajtjes së regjistrit

Regjistri zakonisht përfshin vetëm pyetjet SQL që përjashtojnë pragun të ngadaltë dhe janë dorëzuar nga aplikacionet e klientëve. Kjo përjashton çdo operacion të ngadalshëm administrativ që mund të ndodhë, të tilla si krijimi i indekseve dhe optimizimet e tabelave, si dhe pyetjet që kanë potencialin për të qenë të ngadaltë në të ardhmen.

Ju mund ta zgjeroni regjistrin për të përfshirë këtë informacion duke bërë ndryshimet e mëposhtme në skedarin tuaj të konfigurimit:

  • log_slow_admin_statements=1 – Përfshin deklarata administrative SQL si ALTER TABLE, CREATE INDEX, LËSHI INDEX dhe OPTIMIZE TABLE. Kjo është rrallë e dëshirueshme pasi këto operacione zakonisht ekzekutohen gjatë skripteve të mirëmbajtjes dhe migrimit. Megjithatë, ky cilësim mund të jetë i dobishëm nëse aplikacioni juaj gjithashtu i kryen këto detyra në mënyrë dinamike.
  • log_slow_replica_statements=1 – Ky cilësim mundëson regjistrimin e ngadaltë të pyetjeve për pyetjet e përsëritura në serverët e kopjeve. Kjo është çaktivizuar si parazgjedhje. Përdorni log_slow_slave_statements në vend të kësaj për versionet e MySQL 8.0.26 dhe më të vjetra.
  • log_queries_not_using_indexes=1 – Kur ky cilësim aktivizohet, pyetjet që pritet të marrin të gjitha të dhënat nga tabela ose pamja e synuar do të regjistrohen, edhe nëse janë Mos e përjashtoni pragun e ngadaltë të pyetjes. Kjo mund të ndihmojë në identifikimin kur një pyetjeje i mungon një indeks ose nuk është në gjendje ta përdorë atë. Pyetjet që kanë një indeks të disponueshëm do të regjistrohen përsëri nëse u mungojnë kufizimet që kufizojnë numrin e rreshtave të marrë.

Regjistrimi i pyetjeve që nuk përdorin indekse mund të rrisë ndjeshëm folshmërinë. Mund të ketë situata në të cilat pritet ose nevojitet një skanim i plotë i indeksit. Këto pyetje do të vazhdojnë të shfaqen në regjistër edhe pse nuk mund të zgjidhen.

Ju mund të vlerësoni pyetjet kufitare pa indekse duke vendosur ndryshoren log_throttle_queries_not_using_indexes. Kjo përcakton numrin maksimal të regjistrave që do të shkruhen në një periudhë prej 60 sekondash. Një vlerë prej 10 do të thotë që deri në 10 pyetje do të regjistrohen në minutë. Pas ngjarjes së dhjetë, nuk do të regjistrohen më pyetje derisa të hapet dritarja tjetër prej 60 sekondash.

Interpretimi i regjistrit të ngadalshëm të pyetjeve

Çdo pyetje që futet në regjistrin e ngadaltë të pyetjeve do të shfaqë një grup rreshtash që duken të ngjashëm me sa vijon:

# Time: 2022-07-12T19:00:00.000000Z
# User@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223  Lock_time: 0.000010  Rows_sent: 5143  Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

Linjat e komentuara mbi pyetjen përmbajnë kohën në të cilën është ekzekutuar, përdoruesin MySQL me të cilin klienti është lidhur dhe statistikat që ofrojnë kohëzgjatjen dhe numrin e rreshtave të dërguar. Shembullit të mësipërm iu deshën 3,5 sekonda për t'u përfunduar dhe shikoi mbi 320,000 rreshta, përpara se t'i dërgonte klientit vetëm 5,143. Ky mund të jetë një tregues se indekset që mungojnë po bëjnë që MySQL të inspektojë shumë regjistrime.

Mund të përfshini opsionalisht më shumë informacion në regjistër duke vendosur variablin e sistemit log_slow_extra=1 në skedarin tuaj të konfigurimit. Kjo do të shtojë ID-në e thread-it, numrin e bajteve të marra dhe të dërguara, dhe numrin e rreshtave të konsideruara për renditje, si dhe numrin e kërkesave specifike të deklaratave që ofrojnë dukshmëri në mënyrën se si MySQL e trajtoi pyetjen.

Skedari i regjistrit duhet të trajtohet me kujdes pasi përmbajtja e tij do të jetë e ndjeshme. Pyetjet shfaqen të plota, pa asnjë maskim të vlerave të parametrave. Kjo do të thotë që të dhënat e përdoruesit do të jenë të pranishme nëse jeni duke përdorur regjistrin e ngadaltë të pyetjeve në një server prodhimi. Qasja duhet të kufizohet tek zhvilluesit dhe administratorët e bazës së të dhënave që po akordojnë deklaratat SQL.

Regjistrimi i ngadaltë i pyetjeve dhe kopjet rezervë

Një zhgënjim i zakonshëm me regjistrin e ngadaltë të pyetjeve lind kur po përdorni gjithashtu MySQLDump për të krijuar kopje rezervë të bazës së të dhënave. Pyetjet e gjata SELECT * FROM ... do të ekzekutohen për të marrë të dhënat nga tabelat tuaja dhe për t'i futur ato në kopjen rezervë. Ata do të përfshihen në regjistrin e ngadaltë të pyetjeve si çdo deklaratë tjetër SQL. Kjo mund të ndotë regjistrin nëse jeni duke bërë kopje rezervë rregullisht.

Ju mund ta adresoni këtë duke çaktivizuar përkohësisht regjistrin e ngadaltë të pyetjeve përpara se të ekzekutoni mysqldump. Mund ta riaktivizoni regjistrin pas përfundimit të kopjimit. Rregulloni skriptin tuaj rezervë në mënyrë që të duket i ngjashëm me sa vijon:

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

Kjo do ta mbajë aktivitetin MySQLDump jashtë regjistrit të ngadaltë të pyetjeve, duke e bërë më të lehtë fokusimin në SQL që ekzekutohet nga aplikacioni juaj.

Përmbledhje

Regjistri i ngadaltë i pyetjeve të MySQL është një nga mënyrat më efektive për të identifikuar shkakun e problemeve të performancës. Filloni duke vlerësuar vonesën që po përjetoni dhe përdorni këtë vlerë si long_query_time tuaj. Zvogëloni vlerën nëse asgjë nuk shfaqet në regjistër pasi të keni riprodhuar problemin.

Regjistri i ngadaltë i pyetjeve nuk do t'ju tregojë saktësisht se si ta rregulloni ngadalësimin. Sidoqoftë, aftësia për të parë SQL-në e saktë të marrë nga serveri ju lejon të përsërisni deklarata me performancë të dobët dhe më pas të vlerësoni efektin e optimizimeve. Shtimi i një indeksi ose kufizimi që mungon mund të jetë ndryshimi midis një pyetjeje që prek mijëra rreshta dhe atij që funksionon me një grusht.