MySQL optimalizálás – 1. rész

Ezzel a cikkel egy néhány részes sorozatot indítok útjára, mely a MySQL adatbázis teljesítmény optimalizálásáról fog szólni.

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.

Hivatalos weboldala: http://mysqltuner.pl

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.

Ahonnan ez a cikk származik és ahol még több hasonló témájú cikket találsz: http://blog.mtommy.info

Hozzászólás beküldése

| Download Free Ringtones for Verizon Online. | Thanks to Highest CD Rates, Ally Bank Rates and Binaural & Isochronic Beats