MySQL optimalizálás webmestereknek!

A MySQL adatbázis teljesítmény optimalizálása:.
A teljesítménynövekedést kiszolgáló oldalon leginkább a gyorsító tárak növelésével tudjuk elérni, ezért fontos, hogy legyen elegendő szabad memóriánk a gépünkben.
Az optimális beállítások megtalálásához igen hasznosak a MySQL optimalizálást segítő scriptek, ezek közül én hármat használok, a három együttes eredményére alapozva állítom be a megfelelő paramétereket a mysql szerver konfigurációjában.
Akkor nézzük is meg az első ilyen scriptet, amely a mysqltunner.pl névre hallgat.
Először is le kell töltenünk a gépünkre:

wget http://blog.mtommy.info/downloads/mysqltunner.pl

Majd adjunk rá futtatási jogot:

chmod 770 mysqltunner.pl

Most már le tudjuk futatni az optimalizáló scriptet:

./mysqltunner.pl

Ekkor megfogja tőlünk kérdezni a MySQL szerver eléréséhez szükséges felhasználónevet és jelszót.
Itt a root felhasználót adjuk meg és a hozzá tartozó jelszót.

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

Ha megadtuk a hozzáférési adatokat, akkor lefut a script és az ábrán látható kimenethez hasonló eredményt fog adni.

Az script kimenete 5 részre osztható fel, melyek az alábbiak:

• General Statistics: Általános statisztika a mysql szerverről
• Storage Engine Statistics: Statisztika a adatbázis tároló motorokról
• Security Recommendations: Biztonsági ajánlások
• Performance Metrics: Teljesítmény adatok, ez lesz számunkra az egyik fontos rész
• Recommendations: Ajánlások a beállítások módosítására, ez a másik fontos rész számunkra

Még mielőtt elkezdenénk soronként ki elemezni a script eredményét, fontos megjegyeznem, hogy a script futtatásai között a MySQL szervert ajánlott 1-2 napig "járatni".
Nos akkor nézzük meg a script milyen információkat is ad nekünk.
General Statistics rész:
[--] Skipped version check for MySQLTuner script:
Kihagyja a scriptünk verzió ellenőrzését, ez nekünk jelenleg lényegtelen
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10
Megmutatja milyen MySQL verziót futtat a gépünk.
[OK] Operating on 64-bit architecture
Az operációs rendszerünk 64 bites, ez azért fontos, hogy kezelni tudja a 4GB-nál több memóriát, amire szükségünk lehet ha gyorsítani szeretnél az SQL szerverünket.
Storage Engine Statistics rész:
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Megmutatja, hogy melyik tároló motor van engedélyezve a szerveren. A + -al jelöltek engedélyezve vannak a mínusz jellel jelöltek pedig nem.
[--] Data in MyISAM tables: 194M (Tables: 1555)
[--] Data in InnoDB tables: 160M (Tables: 367)E két sor statisztikát ad tároló motoronként, hogy mennyi adatot tárol az adatbázisba, illetve azt hány táblában.
Security Recommendations rész:
[OK] All database users have passwords assigned
Megmutatja, hogy az összes adatbázis felhasználónak van e jelszava
Performance Metrics rész:
[--] Up for: 17d 20h 24m 54s (37M q [24.323 qps], 1M conn, TX: 101B, RX: 6B)
[--] Reads / Writes: 77% / 23%
E két sor általános statisztikát ad pl. a szerver uptime-ról, írások olvasások megoszlásáról.
[--] Total buffers: 898.0M global + 4.6M per thread (100 max threads)
Memória használatról ad információt ez a sor, 898 MB-ot használ globálisan a mysql szerver, és erre még rá jön szálanként 4.6 MB memória használat. Így a szerver maximum kihasználtság esetén 898 MB + 100 x 4.6 MB- ot használhat, amit a következő sorban látunk is kiszámolva:
[OK] Maximum possible memory usage: 1.3G (16% of installed RAM)
[OK] Slow queries: 3% (1M/37M)
Ez a bejegyzés megmutatja, hogy a lekérdezések hány % lassú lekérdezés, amelyeknek egy a konfigurációban megadott időnél tovább tart a futása. Ezt ajánlott minél kisebb értéken tartani, illetve bekapcsolni az ilyen lassú kérések naplózását. Majd a naplózót lekérdezéseket optimalizálni.
[OK] Highest usage of available connections: 20% (20/100)
Ebben a sorban láthatjuk, hogy a maximális kapcsolatok számát hány % ban használtuk ki a legmagasabb kihasználtságkor. Figyeljünk, hogy mindig maradjon tartalék kapcsolat.
[OK] Key buffer size / total MyISAM indexes: 128.0M/44.4M
A kulcsok gyorsítótárának a méretét mutatja, illetve annak a kihasználtságát az összes index/kulcshoz viszonyítva. Jelenleg 44.4MB-ot használ a 128MB gyorsítótárból. Memória mennyiségünktől függően állítsuk be, minél magasabb Key buffer hit rate-ot próbáljunk meg elérni.
[OK] Key buffer hit rate: 99.9% (6M cached / 3K reads)
A kulcs gyorsítótárnak a találati arányát mutatja, azaz az összes kulcsnak hány % -át tudja a gyors cache (gyorsítótár)-ből olvasni.
[OK] Query cache efficiency: 81.4% (23M cached / 28M selects)
Lekérdezés gyorsítótár hatásfokát láthatjuk, érdemes minél magasabban tartani, pl. a query cache növelésével, vagy a gyorsítótár töredezettségének megszüntetésével.
[OK] Query cache prunes per day: 0
Itt azt tudjuk meg, hogy hány adatot/lekérdezést kellet eldobnunk a query cache-ből, mert nem fértek el benne, azaz megtelt vagy töredezett a cache. Legjobb, ha ez a szám 0.
[OK] Temporary tables created on disk: 24% (48 on disk / 200 total)
Azt mutatja, hogy az ideiglenes táblák, hány százalékát készíti a lemezre, ezt érdemes alacsonyan tartani, mert jobb ha a memóriában készíti el ezeket és onnan olvassa, jelentősen gyorsabban.
Viszont a String és Blob mezőket tartalmazó táblákat csak a lemezen tudja tárolni, úgy hogy ha sok ilyen táblát használunk akkor ez a % viszonylag magasan fog maradni.
[OK] Thread cache hit rate: 96% (2 created / 63 connections)
Szálankénti gyorsítótár találati aránya. Próbáljuk meg magasan tartani.
[OK] Table cache hit rate: 24% (47 open / 195 opened)
Tábla cache találati aránya, ha túl kevés, akkor emelhetjük a tábla gyorsítótárat, viszont arra ügyelnünk kell, hogy ekkor általában emelnünk kell a mysql szerver által használható fájlok számát is.
Ennek a kihasználtságát mutatja a következő sor:
[OK] Open file limit used: 9% (94/1K)
[OK] Table locks acquired immediately: 100% (66 immediate / 66 locks)
Majd az utolsó sor ebben a részben a táblazárolásokat mutatja.
Recommendations rész:
A script ajánlatokat tesz a MySQL szerver konfigurációjának a változtatására, amikkel növelhető a teljesítménye. Ez is két részből áll, egy általános részből, illetve egy ténylegesen az adott konfigurációs változó értékének a beállítására vonatkozó javaslat.
pl. egy javaslat az optimalizálásra:
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 2.0M, or always use indexes with joins)
Ami azt javasolja hogy emeljük meg a lekérdezések gyorsítótárát 128MB fölé, illetve szintén emeljük a join-oknál használt gyorsítótárat, vagy mindig használjunk indexeket.
Ebben a cikkben ennyit akartam bemutatni, a következő cikkben megnézzünk még egy optimalizáló scriptet, illetve lehet kitérek, hogy hogyan is lehet ezeket a javasolt módosításokat elvégezni a MySQL szerver alatt.

A következő script a tuning-primer.sh névre hallgat.

Weboldal ahonnan beszerezhető: https://launchpad.net/mysql-tuning-primer

Kezdésként töltsük le a gépünkre a tuning-primer-t:

wget http://launchpad.net/mysql-tuning-primer/trunk/1.6-r0/+download/tuning-primer.sh

Majd adjunk rá futtatási jogot:

chmod 770 tuning-primer.sh

Majd futassuk le a scriptet:

./tuning-primer.sh

A másik scripthez hasonlóan, itt is meg kell adnunk az elején a mysql kapcsolódáshoz szükséges adatokat, ezeket megpróbálja automatikusan megtalálni, ha nem sikerül neki, akkor a következőhöz hasonló képet fogunk kapni, illetve néhány kérdést.

A   Would you like to provide a different socket?: [y/N] kérdésre válaszoljunk N-el.

Majd a következő kérdésre Y-al:

Do you have your login handy ? [y/N]:

Ezek után meg kell adnunk a mySQL kapcsolódáshoz szükséges felhasználó nevet és jelszót.
Javasolt a root felhasználót megadni, hogy a script hozzá férjen a rendszer paraméterekhez is.

Majd ha az ezt követő kérdésre igennel válaszolunk, akkor ezeket az adatokat elmenti egy helyi my.cnf fájlba, így legközelebb már nem kérdezi meg őket:

Would you like me to create a ~/.my.cnf file for you? [y/N] : Y

Ha sikerült megfelelően beállítanunk a kapcsolatot a mySQL szerverrel akkor lefut a script elég sok eredményt adva.

Nézzük csak milyen hasznos információkat tud szolgáltatni számunkra a tunning-primer.sh mysql optimalizáló script.

Menjünk végig kisebb részenként a rengeteg információn.

Az elején egy általános statisztikát add nekünk, hogy milyen MySQL verziónk van, az mennyi ideje fut, az összes lekérések számát illetve az aktuálisan futó szálakat is megmondja.

Majd a lassú sql kérésekről add információt, hogy minden rendben van-e, ha esetleg nincs akkor javaslatot is tesz a javításukra.

A következő pont a bináris log.
Ellenőrzi, hogy használjuk-e vagy sem. Ha engedélyezzük akkor a bináris log segítségével lépésről-lépésre való vissza állítást tesz lehetővé a mysql szerver.

Következő résznél a szálakról kapunk információt.

thread_cache_size: mekkora a szál gyorsító tárunk
thread_cached: hány szálunk van a gyorsító tárban

Majd a kapcsolatok számáról kaphatunk bővebb információt:

Megtudhatjuk, hogy egyszerre hány kapcsolatot tud fogadni a mysql szerverünk, abból mennyit használunk jelenleg, mennyi volt a maximális kihasználtság az indítása óta és ez hány %-os kihasználtságot jelent. Ha túl kevés szabad kapcsolatunk lenne, akkor itt kapni fogunk egy figyelmeztetetést, hogy állítsuk magasabbra a maximális kapcsolatok számát.

Ezek után az InnoDB tároló motor paramétereiről kapunk információt, ha használjuk ezt a motort akkor ez a rész is fontos lesz számunkra.

Következő résznél a memória használatról kapunk képet.
Itt amire érdemes figyelnünk az a Configured Max Memory Limit, az itt látható értéket foglalhatja le a MySQL szerver maximálisan.

Key buffer résznél, az kulcsok gyorsító tár kihasználtságáról kapunk információt.
Ügyeljünk arra, hogy mindig legyen szabad kapacitás, már ha a memóriánk mérete engedi.

Következő rész a Query Cache, amely a lekérések gyorsító tárának állapotáról árulkodik.

Itt is fontos, ha módunk van rá legyen szabad kapacitása. (Query cache Memory fill ratio legyen 90% alatt).

Majd a Sort Operations rész következik, ahol a rendezéshez szükséges gyorsító tárról kapunk információkat.

Ezt követi a JOINS rész, ahol, ahogy a neve is mutatja a joinolt lekérdezésekhez használt gyorsító tárról kapunk információkat.

Az Open Files Limit résznél, meg tudhatjuk, hogy maximálisan hány fájlt nyithat meg egyszerre a mysql szerverünk. Ez a beállítás a tábla gyorsító tárazás miatt fontos. Ezt majd a következő résznél látjuk, viszont fontos hogy a megnyitható fájlok száma mindig több legyen mint a tábla cache, az ajánlás szerint akár 2-3-szor is.

A Table Cache résznél a tábla gyorsító tárról kapunk információkat, azaz az épp használatban lévő megnyitott táblákat is gyorsító tárazzuk a gyorsabb elérés érdekében.
Fontos hogy a table_open_cache legyen több mint az open tables, azaz a gyorsító tár legyen nagyobb a megnyitott táblák számánál, hogy mind beleférjen. Illetve ahogy említettem az open_files_limit pedig legyen több a table_open_cache –nél, különben hibás működés fog fellépni a mysql szerverünknél.

A következő rész a Temp Tables, azaz az ideiglenes táblák.
Ezeket is érdemes a memóriában tárolnunk a lemez helyett, viszont a BLOB és a TEXT mezőket a nem fix méret miatt kénytelen a lemezen tárolni. Így mindig lesz olyan temp tábla ami a lemezen fog tárolódni, de ezeket próbáljuk minimálisan tartani, a temp table cache emelésével.

Még egy fontos rész a Table Locking, azaz a tábla zárolások.
Ha egy táblát több szál próbálja meg elérni írásra egy időben, abból zárolási probléma adódik, azaz az egyik szálnak várakoznia kell, amíg a másik be nem fejezi a tevékenységét.
A MyIsam adatbázis motor táblánkénti zárolást végez, míg az InnoDB pedig soronkéntit, azért ha túl sok zárolási problémánk lenne, akkor érdemes megpróbálni átállni InnoDB-re.

A beállításokat a MySQL szerver konfigurációs állományában tudjuk megtenni, ami a my.cnf névre hallgat. Ez Debian/Ubuntu operációs rendszeren az /etc/mysql könyvtár alatt található.

Nyissuk meg ezt az állományt:
nano /etc/mysql/my.cnf

Kezdjük a sort a lassú kérések naplózásának bekapcsolásával:
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

Nézzük mit jelenteken a fenti sorok:
log_slow_queries: Ezzel kapcsoljuk be a naplózást, illetve megadjuk, hogy az hova történjen
long_query_time: Példánkban a 10 másodpercnél tovább futó sql lekérdezések számítsanak lassúnak
log_queries-not-using-indexes: Azokat a lekérdezéseket is naplózzuk, melyek nem használnak indexeket.

Bináris naplózás (Binary log):
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

server-id: Adunk a gépünknek egy azonosított, ez replikációnál lehet fontos, ahol nem lehet két egyforma nevű gép
log_bin: bináris naplózás bekapcsolása és a napló megadása
expire_logs_days : Hány napig őrizze meg a naplót
max_binlog_size: Mekkora lehet a napló maximális mérete

Szálak gyorsító tára (Thread Cache):
thread_cache_size  = 8
thread_stack = 192K

thread_cache_size: Szál gyorsító tár mérete
thread_stack: Egy adott szálhoz tartozó memória mérete

Maximális kapcsolatok (Max Connections):
max_connections=100
wait_timeout=100

max_connections: Egyidejűleg ennyi kapcsolatod tud fogadni az sql szerver
wait_timeout: Ennyi idő után elbontja a kapcsolatot, és felszabadítja az erőforrásokat

Kulcs gyorsítótár (Key Buffer):
key_buffer_size=128M

key_buffer_size: A kulcs gyorsítótár méretét tudjuk megadni

Lekérdezés gyorsítótár (Query Cache):
query-cache-type  = 1
query_cache_size  = 256M
query_cache_min_res  = 1K
query_cache_limit = 24M

query-cache-type: A gyorsítótárazás bekapcsolása
query_cache_size: A gyorsítótár mérete
query_cache_min_res: A minimális foglalási egység, ha ennél kisebb a lekérdezés eredménye akkor is ekkora helyet fog lefoglalni neki a memóriában.
query_cache_limit: Az ennél nagyobb méretű eredményt adó lekérdezések nem lesznek gyorsító tárazva.

Join gyorsítótár (Join Buffer):
join_buffer_size = 2M

Joint tartalmazó lekérdezések gyorsítótár mérete, ezt ajánlott 2M vagy alatta tartani.

Megnyitott fájlok:
open_files_limit = 32768

Ennyi fájlt nyithat meg maximálisan a mysql szerver, fontos hogy nagyobb legyen a table_cache –nél. Viszont figyelni kell, hogy az operációs rendszer által megnyitható fájlok számánál viszont kevesebb.

Tábla gyorsítótár (Table Cache):
table_cache = 16384
table_definition_cache = 2048

table_cache: Tábla gyorsítótár mérete, ennyi tábla kerülhet a cache –be
table_definition_cache: Tábla definíció gyorsítótár mérete

Ideiglenes tába gyorsítótár (Temp Table):
tmp_table_size          = 256M

Az ideiglenes tábla gyorsítótár mérete.

Ezek lennének a főbb beállítások. A helyes beállításokat sok tesztelés után lehet elérni, de utána is folyamatosan ellenőrizni kell őket. Ha változik az adatbázis terhelés, új adatbázisok kerülnek be a rendszerbe, akkor szinte biztos, hogy hozzá kell majd nyúlnunk ezekhez a beállításokhoz.