Si të analizoni problemet e performancës së MySQL

Të përballesh me ngarkesën e tepërt të serverit MySQL, apo të vëresh se disa pyetje kërkojnë shumë kohë për t'u ekzekutuar? Ja se si të filloni kur hetoni problemet e performancës MySQL.

Para fillimit, duhet të jeni të vetëdijshëm se çdo server dhe grup i të dhënave vjen me sfidat e veta të performancës. Ky artikull synon të ofrojë udhëzime të përgjithshme se si të kontrolloni konfigurimin e serverit tuaj dhe pyetjet individuale për çështje të fshehura.

Trajtimi i ngarkesës së serverit

Treguesi më i dukshëm se diçka nuk është në rregull, shpesh rrjedh nga një ngadalësim i përgjithshëm. Nëse po shihni periudha të qëndrueshme të përdorimit të burimeve të larta, ndryshimi i skedarit të konfigurimit të MySQL mund të çojë në cilësime më optimale.

Një vend i mirë për të filluar është të ekzekutoni MySQLTuner. Ky skenar vlerëson automatikisht serverin tuaj MySQL kundrejt 300 treguesve të mundshëm të performancës. Do të prodhojë një listë sugjerimesh që mund t'ju ndihmojnë të përfitoni më shumë nga mjedisi juaj.

MySQLTuner shpërndahet si një skrip Perl, kështu që do t'ju duhet Perl të instaluar në sistemin tuaj. Përdorni komandat e mëposhtme për të shkarkuar dhe ekzekutuar MySQLTuner:

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl --host 127.0.0.1 --username root --pass mysql-password

Sintaksa e lidhjes është e ngjashme me klientin e linjës së komandës mysql. Ju duhet të lidheni si përdorues root në mënyrë që skripti të ketë akses të plotë në serverin tuaj.

MySQLTuner është i dobishëm sepse vlerësimi i tij bazohet në grupin e të dhënave tuaj që pret serverin tuaj. Sidoqoftë, rezultati është thjesht sugjerues - jo çdo rekomandim do të ketë një ndikim dhe disa mund të zvogëlojnë performancën.

MySQLTuner është një skrip thjesht vetëm për lexim. Nuk do të bëjë asnjë ndryshim në cilësimet e serverit tuaj. Nëse pranoni një sugjerim, duhet të përditësoni manualisht variablin e treguar në skedarin tuaj të konfigurimit MySQL. Vendndodhja e këtij skedari ndryshon sipas shpërndarjes së sistemit operativ. Vendndodhjet e zakonshme përfshijnë /etc/mysql/my.cnf dhe /etc/mysql/mysql.conf.d/mysqld.cnf.

Pas ndryshimit të një ndryshoreje, rinisni serverin MySQL:

sudo /etc/init.d/mysql restart

Tani duhet ta lini serverin të funksionojë normalisht për një kohë. Më pas mund të ekzekutoni përsëri MySQLTuner për të rivlerësuar performancën e serverit. Mund të sugjerojë një ndryshim tjetër në të njëjtën variabël. Vazhdoni të bëni ndryshime, por sigurohuni që të balanconi të gjitha cilësimet. Ju nuk do të jeni domosdoshmërisht në gjendje të vendosni çdo variabël në vlerën e tij të sugjeruar pa shfaqur sugjerime të reja. Prodhimi i MySQLTuner këshillon që serveri të funksionojë për 24 orë për të marrë të dhëna të sakta vlerësimi.

Përshtatja e madhësive të tamponit

Ndryshimi i madhësive të buffer-it dhe cache-it mund të sjellë një përmirësim të dukshëm të performancës. MySQL paracakton madhësi relativisht të vogla të tamponit, të cilat nuk do të funksionojnë mirë për ngarkesa më të mëdha pune. Vlerat shkruhen në skedarin e konfigurimit MySQL duke përdorur K, M ose G për të treguar njësinë e ruajtjes (p.sh. 512M ). do të thotë 512 megabajt).

• innodb_buffer_pool_size : Si rregull i përgjithshëm, kjo zakonisht vendoset në 70-80% të kujtesës suaj të disponueshme. Ai përcakton madhësinë e grupit të përdorur për të zbutur pyetjet në tabelat InnoDB. Mundohuni ta bëni këtë të paktën aq të madhe sa madhësia totale e grupit tuaj të të dhënave, me kusht që të keni memorie të mjaftueshme në dispozicion.
• innodb_buffer_pool_instances : Një vlerë midis 1 dhe 64, që përcakton numrin e grupeve të tamponëve të InnoDB që do të funksionojnë. Çdo faqe e ruajtur në grupin e tamponit i caktohet rastësisht një prej rasteve. Një numër më i madh i rasteve mund të përmirësojë konkurencën.
• innodb_log_file_size : Madhësia maksimale e skedarëve të ribërjes së regjistrit në një grup regjistrash. Këta skedarë përdoren gjatë rikuperimit të përplasjes për të rivendosur transaksionet jo të plota. Vlerat më të mëdha përmirësojnë performancën, por shkaktojnë një rritje të kohës së rikuperimit në rast përplasjeje.
• key_buffer_size : Kjo është e ngjashme me innodb_buffer_pool_size por përdoret për tabelat MyISAM. Vini re se nëse jeni duke përdorur ekskluzivisht ose tabelat MyISAM ose InnoDB, duhet të vendosni variablin përkatës në përputhje me rrethanat dhe të ndryshoni tjetrin në një madhësi relativisht të ulët, si p.sh. 32M. Përndryshe, do të humbisni RAM-in duke siguruar një hapësirë të madhe buferi për një lloj tabele të papërdorur.
• join_buffer_size : Kjo cakton madhësinë e bufferit të përdorur për bashkime pa indekse. Rritja e madhësisë së këtij buffer do të përshpejtojë pyetjet, të cilat përdorin bashkime të paindeksuara. Vendosja e tij shumë e lartë mund të çojë në probleme me kujtesën, pasi një buffer bashkimi ndahet për çdo bashkim të plotë midis tabelave. Lidhjet komplekse midis tabelave të shumta do të kenë nevojë për bufera të shumëfishta, secili me kapacitet join_buffer_size, i cili mund të konsumojë shpejt shumë RAM. Vlera e paracaktuar është 256K.
• sort_buffer_size : Pëlqen join_buffer_size , por i zbatueshëm për operacionet radhitje duke përdorur filesor . Vlerat më të larta mund të përshpejtojnë renditjen e grupeve të mëdha të rezultateve, por rrezikojnë rritjen e përdorimit të kujtesës në një server shumë aktiv.

Gjithmonë duhet të vlerësoni çdo ndryshim kundrejt madhësisë së grupit të të dhënave tuaja dhe burimeve harduerike të serverit tuaj. Vendosja e këtyre vlerave shumë të ulëta do të ndikojë në performancën e pyetjes, ndërsa anasjelltas, vendosja e tyre shumë e lartë mund të çojë në përdorim të tepërt të memories apo edhe rraskapitje të kujtesës. MySQLTuner do të lëshojë paralajmërime nëse konfigurimi juaj rrezikon të konsumojë të gjithë memorien e disponueshme të sistemit.

Analizimi i pyetjeve të ngadalta

Aktivizimi i regjistrit të ngadaltë të pyetjeve ju jep njohuri për pyetjet me performancë të dobët. Ju mund ta bëni këtë nga një sesion i guaskës MySQL:

sudo mysql
SET GLOBAL slow_query_on = "On";
SET GLOBAL slow_query_log_file = "/slow-query.log";
SET GLOBAL long_query_time = 5;

Ky konfigurim do të regjistrojë çdo pyetje që kërkon më shumë se pesë sekonda për t'u ekzekutuar në /slow-query.log. Ekzaminoni këtë skedar në mënyrë periodike për të identifikuar pyetjet e gjata.

Pasi të keni gjetur një pyetje problematike, mund të përdorni një deklaratë EXPLAIN për të kuptuar se çfarë po shkakton ngadalësimin. Parashtesoni pyetjen me EXPLAIN dhe ekzekutoni komandën në një guaskë MySQL. Do të merrni rezultate të tabeluara që tregojnë se si MySQL planifikon të ekzekutojë pyetjen.

Dalja EXPLAIN përfshin informacion mbi indekset e disponueshme, çelësat e përdorur dhe numrin e regjistrimeve të vlerësuara. Interpretimi i të dhënave EXPLAIN është një temë më vete. Udhëzime të detajuara mbi kuptimin e secilës fushë është në dispozicion në dokumentacionin MySQL.

Mund të jetë më e lehtë të përdoret MySQL Workbench për të ekzekutuar një EXPLAIN grafikisht. Kjo mund t'ju ndihmojë të vizualizoni qasjen e motorit të bazës së të dhënave për marrjen e grupit të rezultateve. Brenda MySQL Workbench, shtypni Ctrl+T për të hapur një skedë të re pyetjesh. Shkruani pyetjen tuaj dhe shtypni Ctrl+Alt+X për ta ekzekutuar atë si EXPLAIN (Nuk keni nevojë të shtoni prefiksin EXPLAIN me dorë.). Në panelin e rezultateve, do të shihni planin vizual të ekzekutimit që thekson operacionet e përfshira.

Roli i Indekseve

Është e rëndësishme të siguroheni që grupi juaj i të dhënave të përmbajë indekset e duhura. Përdorimi i saktë i indekseve rrit ndjeshëm performancën e pyetjeve.

SELECT * FROM users WHERE Email = 'example@example.com';

ALTER TABLE my_table ADD INDEX my_index (my_field);

SELECT * FROM my_table WHERE x = 1 AND y = 2 ORDER BY z;
 
ALTER TABLE my_table ADD INDEX covering_index (x, y, z);