Alacsony memóriaproblémák elhárítása az Azure Database for MySQL-ben – rugalmas kiszolgáló

A következőkre vonatkozik: Azure Database for MySQL – Egykiszolgálós Azure Database for MySQL – Rugalmas kiszolgáló

Fontos

Az önálló Azure Database for MySQL-kiszolgáló a kivonási útvonalon van. Határozottan javasoljuk, hogy frissítsen rugalmas Azure Database for MySQL-kiszolgálóra. További információ a rugalmas Azure Database for MySQL-kiszolgálóra való migrálásról: Mi történik az önálló Azure Database for MySQL-kiszolgálóval?

Annak érdekében, hogy a rugalmas Azure Database for MySQL-kiszolgálópéldányok optimálisan teljesíthessenek, nagyon fontos a megfelelő memóriafoglalás és -kihasználtság. Ha rugalmas Azure Database for MySQL-kiszolgálót hoz létre, a rendelkezésre álló fizikai memória alapértelmezés szerint a számítási feladathoz választott szinttől és mérettől függ. Emellett memória van lefoglalva pufferekhez és gyorsítótárakhoz az adatbázis-műveletek javítása érdekében. További információ: How MySQL Uses Memory.

Vegye figyelembe, hogy a rugalmas Azure Database for MySQL-kiszolgáló a lehető legtöbb gyorsítótár-találat elérése érdekében memóriát használ fel. Ennek eredményeképpen a memória kihasználtsága gyakran a példány rendelkezésre álló fizikai memóriájának 80–90%-át mozgatja. Hacsak nincs probléma a lekérdezési számítási feladat előrehaladásával, ez nem jelent problémát. Előfordulhat azonban, hogy memóriaproblémák merülnek fel, például az alábbiak miatt:

• Túl nagy pufferek konfigurálva.
• A futó optimális lekérdezések részoptimálisak.
• Az illesztéseket végrehajtó lekérdezések és a nagy adathalmazok rendezése.
• Állítsa be egy adatbázis-kiszolgáló maximális kapcsolatait túl magasra.

A kiszolgáló memóriájának nagy részét az InnoDB globális pufferei és gyorsítótárai használják, amelyek olyan összetevőket tartalmaznak, mint a innodb_buffer_pool_size, a innodb_log_buffer_size, a key_buffer_size és a query_cache_size.

A innodb_buffer_pool_size paraméter értéke azt a memóriaterületet határozza meg, amelyben az InnoDB gyorsítótárazza az adatbázistáblákat és az indexhez kapcsolódó adatokat. A MySQL a lehető legtöbb táblával és indexel kapcsolatos adatot próbál elhelyezni a pufferkészletben. A nagyobb pufferkészlethez kevesebb I/O-művelet szükséges a lemezre való átirányításhoz.

Memóriahasználat monitorozása

A rugalmas Azure Database for MySQL-kiszolgáló számos metrikát biztosít az adatbázispéldány teljesítményének méréséhez. Az adatbázis-kiszolgáló memóriakihasználtságának jobb megértéséhez tekintse meg a gazdagép memóriaszázalékát vagy memóriaszázalékát .

Ha azt tapasztalja, hogy a memória kihasználtsága hirtelen megnőtt, és a rendelkezésre álló memória gyorsan csökken, figyelje meg a többi metrikát, például a gazdagép CPU-százalékos értékét, az összes Csatlakozás és az I/O százalékot, hogy megállapítsa, a probléma forrása-e a számítási feladat hirtelen megugrása.

Fontos megjegyezni, hogy az adatbázis-kiszolgálóval létesített minden kapcsolathoz szükség van némi memória lefoglalására. Ennek eredményeképpen az adatbázis-kapcsolatok megugrása memóriahiányt okozhat.

A magas memóriakihasználtság okai

Vizsgáljuk meg a MySQL-ben a magas memóriakihasználtság néhány további okát. Ezek az okok a számítási feladat jellemzőitől függenek.

Az ideiglenes táblák számának növekedése

A MySQL "ideiglenes táblákat" használ, amelyek egy speciális típusú tábla, amely egy ideiglenes eredményhalmaz tárolására szolgál. Az ideiglenes táblák többször is felhasználhatók egy munkamenet során. Mivel a létrehozott ideiglenes táblák helyiek egy munkamenethez, a különböző munkamenetek különböző ideiglenes táblákat tartalmazhatnak. A sok munkamenetet tartalmazó éles rendszerekben, amelyek nagy ideiglenes eredménykészletek fordítását végzik, rendszeresen ellenőriznie kell a globális állapotszámlálót created_tmp_tables, amely nyomon követi a csúcsidőben létrehozott ideiglenes táblák számát. A memóriabeli ideiglenes táblák nagy száma gyorsan alacsony rendelkezésre álláshoz vezethet a rugalmas Azure Database for MySQL-kiszolgáló egy példányában.

A MySQL-ben az ideiglenes táblaméretet két paraméter értékei határozzák meg, az alábbi táblázatban leírtak szerint.

Paraméter	Ismertetés
tmp_table_size	A belső, memórián belüli ideiglenes táblák maximális méretét adja meg.
max_heap_table_size	Megadja, hogy a felhasználó által létrehozott MEMÓRIAtáblák maximális mérete növekedjen.

Megjegyzés:

Egy belső, memórián belüli ideiglenes tábla maximális méretének meghatározásakor a MySQL a tmp_table_size és max_heap_table_size paraméterekhez beállított értékek közül az alacsonyabb értéket veszi figyelembe.

Javaslatok

Az ideiglenes táblákkal kapcsolatos kevés memóriaproblémák elhárításához vegye figyelembe az alábbi javaslatokat.

• A tmp_table_size érték növelése előtt ellenőrizze, hogy az adatbázis megfelelően van-e indexelve, különösen az illesztésekben részt vevő és műveletek szerint csoportosított oszlopok esetében. A megfelelő indexek használata az alapul szolgáló táblákon korlátozza a létrehozott ideiglenes táblák számát. A paraméter és a max_heap_table_size paraméter értékének növelése az indexek ellenőrzése nélkül lehetővé teszi, hogy a nem hatékony lekérdezések indexek nélkül fussanak, és a szükségesnél több ideiglenes táblát hozzanak létre.
• Hangolja a max_heap_table_size és tmp_table_size paraméterek értékeit a számítási feladat igényeinek megfelelően.
• Ha a max_heap_table_size és tmp_table_size paraméterekhez beállított értékek túl alacsonyak, előfordulhat, hogy az ideiglenes táblák rendszeresen kiömlik a tárolóba, ami késést eredményez a lekérdezésekben. A lemezre kiömlő ideiglenes táblákat a globális állapotszámláló created_tmp_disk_tables segítségével követheti nyomon. A created_tmp_disk_tables és created_tmp_tables változók értékeinek összehasonlításával megtekintheti a létrehozott belső, lemezen lévő ideiglenes táblák számát a létrehozott belső ideiglenes táblák teljes számával.

Táblagyorsítótár

Többszálas rendszerként a MySQL a táblafájl-leírók gyorsítótárát tartja karban, így a táblák egymástól függetlenül több munkamenettel is megnyithatók. A MySQL némi memóriát és operációsrendszer-fájlleírót használ a táblagyorsítótár karbantartásához. A table_open_cache változó határozza meg a táblagyorsítótár méretét.

Javaslatok

A táblagyorsítótárral kapcsolatos kevés memóriaproblémák elhárításához vegye figyelembe az alábbi javaslatokat.

• A table_open_cache paraméter az összes szál nyitott tábláinak számát adja meg. Az érték növelése növeli a mysqld által igényelt fájlleírók számát. A globális állapotszámláló opened_tables állapotváltozójának ellenőrzésével ellenőrizheti, hogy növelnie kell-e a táblagyorsítótárat. Növelje ennek a paraméternek az értékét növekményesen, hogy megfeleljen a számítási feladatnak.
• A table_open_cache túl alacsony beállítása miatt a rugalmas Azure Database for MySQL-kiszolgáló több időt tölthet a lekérdezésfeldolgozáshoz szükséges táblák megnyitásával és bezárásával.
• Ha ezt az értéket túl magasra állítja, az több memória használatát okozhatja, és a fájlleírókat futtató operációs rendszer elutasított kapcsolatokhoz vagy a lekérdezések feldolgozásának sikertelenségéhez vezethet.

Egyéb pufferek és a lekérdezési gyorsítótár

A kevés memóriával kapcsolatos problémák elhárításához használhat még néhány puffert és egy gyorsítótárat a megoldás érdekében.

Nettó puffer (net_buffer_length)

A nettó puffer mérete az egyes ügyfélszálak kapcsolati és szálpuffereinek mérete, és a max_allowed_packet megadott értékre nőhet. Ha egy lekérdezési utasítás nagy, például az összes beszúrás/frissítés nagyon nagy értékkel rendelkezik, akkor a net_buffer_length paraméter értékének növelése segít a teljesítmény javításában.

Illesztés puffere (join_buffer_size)

Az illesztés puffere akkor van lefoglalva a táblasorok gyorsítótárazásához, ha egy illesztés nem tud indexet használni. Ha az adatbázis számos illesztéssel rendelkezik indexek nélkül, érdemes lehet indexeket hozzáadni a gyorsabb illesztéshez. Ha nem tud indexeket hozzáadni, fontolja meg a join_buffer_size paraméter értékének növelését, amely meghatározza a kapcsolatonként lefoglalt memória mennyiségét.

Rendezési puffer (sort_buffer_size)

A rendezési puffer bizonyos ORDER BY és GROUP BY lekérdezések rendezési feladatainak végrehajtására szolgál. Ha a GLOBÁLIS ÁLLAPOT megjelenítése kimenetben másodpercenként több Sort_merge_passes jelenik meg, fontolja meg a sort_buffer_size érték növelését az ORDER BY vagy a GROUP BY műveletek felgyorsítása érdekében, amelyek nem javíthatók a lekérdezésoptimalizálás vagy a jobb indexelés használatával.

Kerülje a sort_buffer_size érték tetszőleges növelését, hacsak nem rendelkezik másra utaló kapcsolódó információkkal. A puffer memóriája kapcsolatonként van hozzárendelve. A MySQL dokumentációjában a Kiszolgálórendszerváltozók című cikk azt ismerteti, hogy Linuxon két küszöbérték, 256 KB és 2 MB van, és hogy a nagyobb értékek használata jelentősen lelassíthatja a memóriafoglalást. Ennek eredményeképpen kerülje a sort_buffer_size érték 2 M-en túli növelését, mivel a teljesítménybírság meghaladja az előnyöket.

Lekérdezési gyorsítótár (query_cache_size)

A lekérdezési gyorsítótár a lekérdezési eredményhalmazok gyorsítótárazásához használt memóriaterület. A query_cache_size paraméter határozza meg a lekérdezési eredmények gyorsítótárazására lefoglalt memória mennyiségét. Alapértelmezés szerint a lekérdezési gyorsítótár le van tiltva. Emellett a lekérdezési gyorsítótár elavult a MySQL 5.7.20-es verziójában, és a MySQL 8.0-s verziójában törlődik. Ha a lekérdezési gyorsítótár jelenleg engedélyezve van a megoldásban, a letiltás előtt ellenőrizze, hogy nincsenek-e rá támaszkodó lekérdezések.

Puffergyorsítótár találati arányának kiszámítása

A puffergyorsítótár-találatok aránya fontos a rugalmas Azure Database for MySQL-kiszolgálói környezetben annak megértéséhez, hogy a pufferkészlet képes-e befogadni a számítási feladatokra vonatkozó kéréseket, vagy sem, és általános szabályként célszerű mindig 99%-nál nagyobb pufferkészlet-gyorsítótár-találati arányt beállítani.

Az Olvasási kérelmek InnoDB pufferkészletének találati arányának kiszámításához futtassa a GLOBÁLIS ÁLLAPOT megjelenítése parancsot a "Innodb_buffer_pool_read_requests" és a "Innodb_buffer_pool_reads" számlálók lekéréséhez, majd az alábbi képlet használatával számítsa ki az értéket.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Consider the following example.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

A fenti értékeket használva az InnoDB pufferkészlet találati arányának kiszámítása az olvasási kérelmekhez a következő eredményt adja:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Az utasítások puffergyorsítótár-találati arányának kiválasztása mellett a DML-utasítások esetében a háttérben történik az írás az InnoDB pufferkészletbe. Ha azonban olvasásra vagy lap létrehozására van szükség, és nem érhetők el tiszta lapok, akkor meg kell várni, hogy a lapok először ki legyenek ürítve.

A Innodb_buffer_pool_wait_free számláló megszámolja, hogy hányszor történt ez. Innodb_buffer_pool_wait_free 0-nál nagyobb erős jelzés, hogy az InnoDB pufferkészlet túl kicsi, és növelni kell a pufferkészlet méretét vagy a példányméretet az adatbázisba érkező írások elhelyezéséhez.

Javaslatok

• Győződjön meg arról, hogy az adatbázis elegendő erőforrással rendelkezik a lekérdezések futtatásához. Előfordulhat, hogy időnként fel kell skáláznia a példány méretét, hogy több fizikai memóriát kapjon, hogy a pufferek és a gyorsítótárak elférjenek a számítási feladat számára.
• Kerülje a nagy vagy hosszú ideig futó tranzakciókat, ha kisebb tranzakciókra bontja őket.
• A "Host Memory Percent" riasztásokkal értesítéseket kaphat, ha a rendszer túllépi a megadott küszöbértékeket.
• A lekérdezési teljesítmény Elemzések vagy Azure-munkafüzetek használatával azonosíthatja a problémás vagy lassan futó lekérdezéseket, majd optimalizálhatja őket.
• Éles adatbázis-kiszolgálók esetén rendszeres időközönként gyűjtse össze a diagnosztikát, hogy minden zökkenőmentesen fusson. Ha nem, elháríthatja és megoldhatja az ön által azonosított problémákat.

További lépések

Ha társválaszt szeretne keresni a legfontosabb kérdésekre, vagy fel szeretne tenni vagy megválaszolni egy kérdést, látogasson el a Stack Overflow webhelyre.