MySQL optimalizáció egyszerűen

Számtalan esetben előfordulhat, hogy egy megöröklött rendszert kell utólag rendbe hozni vagy extrémebb esetben egy aktuális fejlesztés közepén észleljük, hogy a tervezett terhelést sehogy nem bírja el az üzemelő MySQL kiszolgáló. Ilyenkor jön elő a szokásos történet: a lehető legfájdalommentesebben próbáljuk meg optimalizálni egy kicsit az RDBMS működését.

Számos hasznos kiegészítő alkalmazás és megoldás létezik erre a célra, most azonban két személyes kedvencünket mutatnánk be, amelyekkel könnyen kaphatunk információt a MySQL aktuális állapotáról és segítségükkel a jellemző működési paramétereket könnyen tudjuk optimalizálni.

1.) MySQLTuner-Perl

(http://mysqltuner.com/)

Folyamatosan fejlesztett, a MySQL aktuális verziójával is kompatibilis eszköz, amely nemcsak a működési paramétereket és határértékeket figyeli, de kitér az indexek használatának statisztikái mellett sok hasznos tanácsra. Jól érthető kimenete segít a hiba felderítésében. Részletesen, miket is ellenőriz a script futtatása után:

• Vannak-e bármelyik adatbázisban töredezett táblák.
• Adatbázisonkét:
  • Használunk-e egynél több adatbázis motort egy adatbázison belül: Ez még nem feltétlenül okoz gondot, de az adatbázis replikálást megakadályozza. Ellenőrizzük, hogy ha már mindenképpen többféle motort is használunk egy adatbázison belül, akkor azt ne próbáljuk meg replikálni.
  • Alapértelmezett collection-ok (illesztések) száma: normál esetben ez adatbázisonként egy, de egyedi esetekben előfordulhat, hogy többet is alkalmazunk kell. Ami itt hátrányt okozhat, az az eltérő collection-al bíró táblákra épülő lekérdezések végrehajtásának sebességcsökkenése a konverziók miatt. Ha lehetséges, szüntessük meg az eltérő collection-okat. (Pl. sok esetben tapasztaljuk, hogy egy WordPress oldal alapértelmezésben utf8_general_ci kódlappal van telepítve, majd a kiegészítő pluginok egy része nem ezt használja fel, hanem latin1_swedish_ci vagy más kódlap illesztéssel hozza létre a tábláit.)
  • Továbbá részletes információval szolgál a táblák, nézetek számáról, helyhasználatukról, rekordok számáról, stb.
• Index statisztikák: Azonosítja azokat az indexeket, amelyeket felesleges használnunk: egy index akkor jó, ha az adott mező szelektivitását jelentősen csökkenti. Például egy több tízezer soros felhasználói törzs táblában felesleges a nem (férfi/nő) oszlopra indexet tennünk, mert az nem rendelkezik magas szelektivitással, de pl. a keresztnév már jobb ebből a szempontból.
• Biztonsági szempontból ellenőrzi, hogy anonim módon lehet-e csatlakozni az adatbázis kiszolgálóhoz, hogy van-e olyan felhasználó, akinek a jelszava megegyezik a felhasználói nevével, valamint van-e olyan felhasználó, akire vonatkozóan nincs host szintű megkötés a belépéshez.
• Részletes metrikát ad a kiszolgáló állapotáról, memória használatáról, csatlakozások számáról, a különböző cache értékekről (aktuális és beállított határértékek is), temporary használtságról, log-ok állapotáról, stb.
• Adatbázis motoronként külön-külön statisztikát ad az egyes beállított működési paraméterekről/határértékekről és a hozzájuk tartozó aktuális értékekről.
• Legvégül pedig egy jól használható összefoglalóban javaslatokat tesz a kiszolgáló optimalizálásához szükséges lépésekről: FONTOS, hogy ezeket kellő fenntartással kezeljük! Csak olyan beállításhoz nyúljunk, amelyről egyrészt pontosan tudjuk, hogy miért nem megfelelő, másrészt tudjuk mivel jár a módosítása.
• A kimenetét testre szabhatjuk, illetve távoli kiszolgálón is tudjuk futtatni.

Az általa végzett ellenőrzések listája folyamatosan bővül ahogy fejlesztik, az aktuális dokumentációban megtalálható mindig a részletes lista.

2.) Percona Toolkit for MySQL

(https://docs.acquia.com/articles/tools-parsing-slow-query-log)

Ha már a kiszolgálót optimalizáltuk, ideje hogy az alkalmazásaink felé forduljunk: lehet-e gyorsítani a működésükön a lassú lekérdezések felgyorsításával?

A MySQL-ben bekapcsolt slow query log (ha alapból nincs bekapcsolva a lassú lekérdezések naplózása, a MySQLTuner-Perl használata után már úgyis bekapcsolta mindenki) segítségével tudjuk azonosítani azokat a lekérdezéseket, amelyek valami miatt túlontúl lassan futnak le. A pt-digest-query használatával egy jól olvasható szöveges riportot kapunk a leglassabb lekérdezéseink végrehajtásáról.

Nem feltétlenül a leglassabb lekérdezést kell/érdemes optimalizálni legelőször, hanem azt, amelyik gyorsítása a megkapott kapott listában a legtöbb eredményt hozná: Ha egy query 8mp-ig fut, de csak óránként egyszer, annak gyorsítása kevesebb eredményt hoz, mint egy percenként lefutó 5mp-es query optimalizálása.

A riport segítségével azonosíthatjuk a hiányzó, vagy nem megfelelő indexeket, az esetlegesen töredezett táblákat, vagy a nem megfelelően használt join-okat is.

Összefoglaló

E két eszköz rövid bemutatásával csak példát szeretnénk mutatni a könnyen felhasználható igazi „svájci-bicska” jellegű optimalizációs megoldások használatára. Látható, hogy ésszel és kellő gondossággal használva őket nagymértékű teljesítmény javulásokat lehet elérni a legtöbb esetben. Ha Te is ismersz hatékony és jól hasznosítható optimalizációs eszközöket, esetleg kérdésed van, vagy netán éppen adatbázis optimalizációs problémád lenne, vedd fel velünk a kapcsolatot!