Túlzott MySQL szerverterheléssel szembesülni, vagy néhány lekérdezés észrevétele túl sokáig tart? Így kezdje el a MySQL teljesítményproblémáinak kivizsgálását.
Mielőtt elkezdené, tisztában kell lennie azzal, hogy minden kiszolgálónak és adatkészletnek megvan a maga teljesítményproblémája. A cikk célja, hogy általános útmutatást adjon a kiszolgáló konfigurációjának ellenőrzéséről és az egyes lekérdezésekről a rejtőzködő problémák ellen.
A szerver terhelésének kezelése
A legnyilvánvalóbb jelzés arra, hogy valami nincs rendben, gyakran egy általános lassulásból ered. Ha tartósan magas erőforrás-kihasználtságot tapasztal, a MySQL konfigurációs fájljának módosítása optimálisabb beállításokhoz vezethet.
A kiinduláshoz jó hely a futás MySQLTuner. Ez a szkript automatikusan felméri a MySQL szervert 300 lehetséges teljesítménymutató alapján. Készít egy listát azokról a javaslatokról, amelyek segítségével többet hozhat ki a környezetéből.
A MySQLTuner Perl szkriptként van terjesztve, ezért a Perl-t telepíteni kell a rendszerére. A MySQLTuner letöltéséhez és futtatásához használja az alábbi parancsokat:
wget http://mysqltuner.pl/ -O mysqltuner.pl chmod +x mysqltuner.pl ./mysqltuner.pl --host 127.0.0.1 --username root --pass mysql-password
A kapcsolat szintaxisa hasonló a mysql parancssori kliens. Csatlakozzon, mint a root felhasználót, hogy a szkript teljes hozzáféréssel rendelkezzen a szerveréhez.
A MySQLTuner hasznos, mert értékelése a következőkön alapul a ti szerver tárhely a ti adatkészlet. Ennek ellenére a kimenet csupán szuggesztív – nem minden ajánlásnak lesz hatása, és egyesek valóban csökkenthetik a teljesítményt.
A MySQLTuner egy csak olvasható szkript. A szerver beállításait nem változtatja meg. Ha elfogad egy javaslatot, manuálisan frissítenie kell a jelzett változót a MySQL konfigurációs fájljában. A fájl helye az operációs rendszer terjesztésétől függ. A közös helyek közé tartozik /etc/mysql/my.cnf és /etc/mysql/mysql.conf.d/mysqld.cnf.
Egy változó megváltoztatása után indítsa újra a MySQL szervert:
sudo /etc/init.d/mysql restart
Most hagyja el a szervert, hogy egy ideig normálisan működjön. Ezután újra futtathatja a MySQLTunert a szerver teljesítményének átértékeléséhez. Javasolhatja ugyanannak a változónak a másik módosítását. Végezze el a változtatásokat, de ügyeljen az összes beállítás egyensúlyára. Nem feltétlenül tudja minden változót a javasolt értékre állítani anélkül, hogy új javaslatok jelennének meg. A MySQLTuner kimenete azt tanácsolja, hogy hagyja el a szervert 24 órán keresztül, hogy pontos értékelési adatokat kapjon.
A pufferméretek testreszabása
A puffer és a gyorsítótár méretének megváltoztatása jelentős teljesítményjavulást eredményezhet. A MySQL alapértelmezés szerint viszonylag kicsi pufferméret, ami nagyobb terheléseknél nem fog jól működni. Az értékeket a MySQL konfigurációs fájlba írja be K, M, vagy G a tárolóegység megjelölésére (pl 512M jelentése 512 megabájt).
innodb_buffer_pool_size: Alapszabályként ez általában a rendelkezésre álló memória 70-80% -ára van beállítva. Meghatározza az InnoDB táblákba történő lekérdezések puffereléséhez használt készlet méretét. Próbáljon meg legalább ekkora lenni, mint az adatkészlet teljes mérete, feltéve, hogy elegendő memória áll rendelkezésre.innodb_buffer_pool_instances: 1 és 64 közötti érték, amely meghatározza a működni kívánt InnoDB pufferkészletek számát. A puffertárban tárolt minden oldal véletlenszerűen van hozzárendelve az egyik példányhoz. Nagyobb számú példával javulhat az egyidejűség.innodb_log_file_size: A maximális méret a naplófájlok újbóli elvégzése naplócsoportban. Ezeket a fájlokat az összeomlás helyreállítása során használják a hiányos tranzakciók helyreállítására. A nagyobb értékek javítják a teljesítményt, de összeomlás esetén hosszabb helyreállítási időt okoznak.key_buffer_size: Ez hasonló ainnodb_buffer_pool_sizede a MyISAM táblákhoz használják. Ne feledje, hogy ha kizárólag a MyISAM vagy az InnoDB táblákat használja, akkor ennek megfelelően kell beállítania a releváns változót, és a másikat viszonylag kis méretre kell cserélnie, például32M. Ellenkező esetben pazarolná a RAM-ot, ha nagy pufferterületet biztosítana egy fel nem használt táblatípus számára.join_buffer_size: Ez beállítja az index nélküli csatlakozásokhoz használt puffer méretét. A puffer méretének növelése felgyorsítja a lekérdezéseket, amelyek nem indexelt összekapcsolásokat használnak. Túl magasra állítása memóriaproblémákhoz vezethet, mivel a táblák közötti minden egyesítéshez csatlakozási puffert osztanak ki. Több tábla közötti összetett összekapcsoláshoz több pufferre lesz szükség, mindegyikjoin_buffer_sizekapacitás, ami gyorsan sok RAM-ot emészthet fel. Az alapértelmezett érték:256K.sort_buffer_size: Tetszikjoin_buffer_size, de alkalmazható válogató műveletek segítségévelfilesort. A magasabb értékek felgyorsíthatják a nagy eredményhalmazok rendezését, de veszélyeztethetik a memóriahasználat növekedését egy nagyon aktív szerveren.
Mindig értékelnie kell az esetleges változásokat az adatkészlet méretével és a szerver hardveres erőforrásaival összhangban. Ha ezeket az értékeket túl alacsonyra állítja, az hatással lesz a lekérdezés teljesítményére, míg fordítva, ha túl magasra állítja, túlzott memóriahasználathoz vagy akár memória kimerüléséhez vezethet. A MySQLTuner figyelmeztetéseket ad ki, ha az Ön konfigurációja veszélyezteti az összes rendelkezésre álló rendszermemória elfogyasztását.
Lassú lekérdezések elemzése
A lassú lekérdezési napló engedélyezése betekintést nyújt a rosszul teljesítő lekérdezésekbe. Ezt megteheti egy MySQL shell munkamenetből:
sudo mysql SET GLOBAL slow_query_on = "On"; SET GLOBAL slow_query_log_file = "/slow-query.log"; SET GLOBAL long_query_time = 5;
Ez a konfiguráció naplóz minden olyan kérdést, amelynek futtatása öt másodpercnél hosszabb ideig tart /slow-query.log. Rendszeresen vizsgálja meg ezt a fájlt a régóta futó lekérdezések azonosításához.
Miután megtalált egy problémás lekérdezést, használhat egy EXPLAIN nyilatkozatot, hogy betekintést nyerjen a lassulás okaiba. A lekérdezés előtagja a EXPLAIN és futtassa a parancsot MySQL héjban. Majd táblázatos kimenetet kap bemutatva, hogy a MySQL hogyan tervezi végrehajtani a lekérdezést.
A EXPLAIN A kimenet tartalmazza az elérhető indexek, a felhasznált kulcsok és az értékelt rekordok számát. Tolmácsolás EXPLAIN az adat maga a téma. Az egyes mezők jelentésével kapcsolatos részletes útmutatás a MySQL dokumentáció.
Könnyebb lehet a MySQL Workbench használatával egy EXPLAIN grafikusan. Ez elősegítheti az adatbázis-motor szemléletének megjelenítését az eredményhalmaz lekérésével kapcsolatban. A MySQL Workbench alkalmazásban nyomja meg a Ctrl + T billentyűkombinációt egy új lekérdezés fül megnyitásához. Írja be a lekérdezést, és nyomja le a Ctrl + Alt + X billentyűkombinációt annak lekérdezésként történő végrehajtásához EXPLAIN (Nem kell hozzáadnia a EXPLAIN előtag manuálisan.). Az eredmények ablaktáblán megjelenik a vizuális végrehajtási terv, amely kiemeli az érintett műveleteket.
Az indexek szerepe
Fontos, hogy az adatkészlet megfelelő indexeket tartalmazzon. Az indexek helyes használata jelentősen növeli a lekérdezés teljesítményét.
SELECT * FROM users WHERE Email = 'example@example.com';
Ennek a lekérdezésnek tartalmaznia kell egy indexet a users.Email terület. Index nélkül a MySQL-nek teljes tábla vizsgálatot kell végeznie, ami minden rekord lassú vizsgálatát eredményezi.
Az index segítségével az adatbázis-motor sokkal gyorsabban képes azonosítani a rekordokat. Ezt úgy hozza létre, hogy létrehoz egy új adatstruktúrát, amely a mező értékét és a forrásrekord mutatóját tartja. Ezután a mutatókat úgy lehet rendezni, hogy a MySQL egyenesen a releváns adatokra ugorjon.
Index hozzáadásához egy meglévő mezőhöz használja a ADD INDEX nyilatkozat azzal ALTER TABLE:
ALTER TABLE my_table ADD INDEX my_index (my_field);
Ezután futnia kell OPTIMIZE TABLE my_table a meglévő adatok indexeléséhez és a lekérdezési statisztikák újraszámításához.
Ha több mezővel dolgozik, létrehozhat egy fedő index. Ez egy olyan index, amely az összes mezőt magában foglalja.
SELECT * FROM my_table WHERE x = 1 AND y = 2 ORDER BY z; ALTER TABLE my_table ADD INDEX covering_index (x, y, z);
Fedő indexek használata esetén a mezők sorrendje számít. Ha lekérdezte WHERE z = 1 ORDER BY x, a fent létrehozott indexet nem használnánk.
Győződjön meg arról, hogy a mezőket a WHERE vagy JOIN ... ON a záradékokat index fedi. A nem indexelt mezők lekérdezése gyorsan teljesítmény szűk keresztmetszetévé válhat. Legyen óvatos az indexeléssel kapcsolatban minden mező azonban – ha soha nem fogja lekérdezni azt a mezőt, akkor az index felesleges ennek a feje fölött továbbra is a MySQL-nek kell fenntartania.
Az indexeletlen lekérdezések naplójának engedélyezésével azonosíthatja azokat a lekérdezéseket, amelyek számára előnyös lenne egy index. Kövesse a fenti utasításokat a lassú lekérdezési napló engedélyezéséhez. Ezután futnia kell SET GLOBAL log_queries_not_using_indexes = "On" egy MySQL héjból. Ez elindítja a hiányzó indexekkel rendelkező lekérdezések naplózását a lassú lekérdezési naplóba. A nem indexelt lekérdezések akkor is bekerülnek, ha nem haladják meg a beállított lassú lekérdezési időt.
Következtetés
A MySQL teljesítményének javításához nincs mindenki számára megfelelő megközelítés. A szükséges lépések a kiszolgáló erőforrásaitól, az adatkészlet méretétől és a gépen futó egyéb terhelések okozta erőforrás-verseny szintjétől függenek.
Nem szabad figyelmen kívül hagynia az alkalmazási réteget sem – lehet, hogy nem a MySQL okozza a teljesítményproblémákat. Ellenőrizze a kód lekérdezésének módját. Ha túl sok a hurok, például egy lekérdezést használ az N + 1 rutinban, akkor a kód újrafeldolgozása sokkal hatásosabb lehet, mint a MySQL szerver mikromenedzselése.
