MySql update egyik gépen nagyon lassú

Van egy update ami több mint 2 millió rekordot változtat meg. A fejlesztői gépemen olyan lassan fut, hogy ki sem bírom várni a végét. Egy órányi futás után kilövöm. Az egyik VPS-en meg 3 perc alatt lefut ugyanazon az adatbázison. 

Holott a fejlesztői gép jóval erősebb mint a VPS. Mi lehet ennek az oka? Egyáltalán hol kezdjek el keresgélni?

Hozzászólások

Szerkesztve: 2021. 01. 15., p - 07:52

Explain mit mond rá? Valóban egyforma a két adatbázis, indexek, stb?

Erdemes megpróbalni hogy mennyi adat fut le pár mp alatt, mondjuk 20e sor módosítása megy-e?

Szeeintem memória és writebuffer méret eltérés lesz a két gép között. My.cfg mennyire azonos?

Ekkora differencia nem jön ki hdd vs. ssd vagy ram sebesség miatt. 

A két adatbázis ugyan az max a rekordok számában tér el pár tízererrel. 

Explain ahol lassú 20 47 282 rekord:

Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+-----------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                | key                   | key_len | ref       | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+-----------+--------+----------+-------------+
|  1 | SIMPLE      | BF    | NULL       | index | PRIMARY,IDX_BIZONYLAT_FEJ_01 | IDX_BIZONYLAT_FEJ_01  | 302     | NULL      | 121392 |   100.00 | Using index |
|  1 | SIMPLE      | BTIP  | NULL       | ALL   | NULL                         | NULL                  | NULL    | NULL      |     11 |    10.00 | Using where |
|  1 | UPDATE      | BT    | NULL       | ref   | FK_BIZONYLAT_TETEL_01        | FK_BIZONYLAT_TETEL_01 | 4       | BDM.BF.ID |     16 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+-----------+--------+----------+-------------+

Explain ahol gyors 2 062 984 rekord:

Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+--------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                | key                   | key_len | ref                      | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+--------------------------+--------+----------+-------------+
|  1 | SIMPLE      | BF    | NULL       | index | PRIMARY,IDX_BIZONYLAT_FEJ_01 | IDX_BIZONYLAT_FEJ_01  | 302     | NULL                     | 122661 |   100.00 | Using index |
|  1 | SIMPLE      | BTIP  | NULL       | ALL   | NULL                         | NULL                  | NULL    | NULL                     |     11 |    10.00 | Using where |
|  1 | UPDATE      | BT    | NULL       | ref   | FK_BIZONYLAT_TETEL_01        | FK_BIZONYLAT_TETEL_01 | 4       | TESZT.BF.ID              |     17 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+------------------------------+-----------------------+---------+--------------------------+--------+----------+-------------+

Mindkettő default telepítés semmi nem lett rajtuk konfigurálva.

Annyi "érdekesség" még, hogy maga a fejlesztői gép is érezhetően lassabb lesz a update kilövése után is és le sem tudott állni szabályosan a rendszer. Reset után elég sok iő kellet, hogy magához térjen. egy select count(*) from bizonylat_tetel 10-20 s-ig tartott a szokásos x ms helyett, és kellett egy OPTIMIZE TABLE (kb negyed óráig futott a >2 mill. rekordos táblán) és utána megint jó lett a lekérdezés sebessége.

Ref oszlopban más DB van megadva. Nem lehet hogy az egyik gépen az egy másik adatbázis, a fejlesztőin pedig egy távoli DB?

"maga a fejlesztői gép is érezhetően lassabb lesz a update kilövése után": akkor ott valami háttértár dolog lesz, valami lassulás. Nincs ott (is) valami network cucc? Helyi gép ez?

"OPTIMIZE TABLE": simán lehet hogy NAGYON sok törölt rekordod volt benne, mert nem drop table hanem delete from-mal ürítetted ki sokszor egymás után. Optimize table előtt ezek csak jelölt törölt sorok, azokon fizikailag át kell mennie. SHOW PROCESSLIST is mutatja, hogy lassan halad az update, mert sok a törölt rekord. A SELECT(1) (select * helyett; ugyanolyan gyors, csak így szebb) is azért lehetett lassú, mert sok benne a törlés.

OPTIMIZE után most rendben fut az UPDATE? Pgsql-nél a vacuum való erre, itt OPTIMIZE (ami innodb esetén =recreate), ami rendbe teszi a dolgokat, néha kell futattni. Én prodban is futtatom rendszeresen, mert jobb hetente futtatni mind évente :)

Ref oszlopban más DB van megadva. Nem lehet hogy az egyik gépen az egy másik adatbázis, a fejlesztőin pedig egy távoli DB?

Mindkettő helyi adatbázis, csak a fejlesztői gépen 'TESZT' az adatbázis neve a VPS-en meg "BDM".

akkor ott valami háttértár dolog lesz

Erre már gondoltam én is. Ezt hogy tudnám kideríteni? Egyébként más téren nem tapasztaltam a napi munka során lassulást. Annyi, hogy sok adatbázist droppoltam el a fejlesztői gépen, az update megfuttatás előtt és ami furcsa (vagy lehet nem furcsa, csak az én tudásom nem megfelelő ), hogy ha df -el nézek egy tárhely kihasználtságot akkor 5,4 Gb szabad helyet mutat. A midnight commander is a bal alsó sarokban annyit ír, viszont ha sudo-val root-ként futtatom az mc-t akkor 17 Gb szabad helyet ír, a df root-ként futtatva ugyan úgy 5,4 GB-ot.

A törölt állományok valós helyfoglalása nem minden esetben szűnik meg azonnal, sokszor a törölt állományt addig haszáló process újraindítása szükséges, hogy elengedje a file handle-t és az FS tényleg szabadként tudja használni a helyet. Én hirtelen nagyra nőtt log állományokkal járok rendszeresen így.

Egyébként IOPS különbséget dd-vel vagy fio-val tudsz mérni, 4k vagy 8k blokkmérettel tesztelj DB jellegű munka céljából, lehetőleg nagy ismétlésszámmal (hogy az össz. mozgatott adatmennyiség minimum a memóriaméret kétszerese legyen az adott gépen). Ezzel kiderülhet, ha durva különbség van a két gép diszk alrendszer teljesítménye között. Én simán el tudom képzelni, hogy a VPS olyan tárolóra ír, ami írási gyorstáras, és jóval nagyobb az írási teljesítménye emiatt (a desktop-ban hiába van SSD, 4k random írásban azok sem jeleskednek, csak egy sima HDD-hez mérten jobbak).

A művelet közben terhelési mutatókat kellene nézni a desktop gépen. CPU, diszk IO, várakozási sor. Gondolom helyben fut minden, így hálózat nem releváns. Ha minden alacsony, akkor valamire várakozik sokat a DB motor, ha meg valamelyik fent van az égben, akkor az a szűk keresztmetszet (de ez triviális, nem azért írom). Én arra tippelek, hogy nem lesznek magasak a mutatók, hanem valamire várakozás van sokszor, sok ideig.

Nem mondom nagy százalékra, hogy lehet ekkora óriási különbség, de ki sem zárnám teljesen.

Nem ennyire drasztikusan, de hasonló lassulást tapasztaltunk az egyik SQL szerverünkön, amikor feltettük az egyes Spectre CPU-bug foltokat, majd jelentős gyorsulásokat tapasztaltunk, amikor megjöttek a hozzá passzoló új CPU microcode-ok. Még azt is el bírom képzelni, hogy a két gép közötti CPU microcode eltérés okoz ekkora eltérést, vagy éppen a VPS hypervisora kioptimalizál valamit, amit a másik gép nem.

Azt tudjuk, hogy a lassú gépben milyen CPU van? (Ha Intel Core-i sorozat, akkor hanyadik generációs?)

Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
CPU(s):              4
On-line CPU(s) list: 0-3
Thread(s) per core:  2
Core(s) per socket:  2
Socket(s):           1
NUMA node(s):        1
Vendor ID:           GenuineIntel
CPU family:          6
Model:               60
Model name:          Intel(R) Core(TM) i5-4300M CPU @ 2.60GHz
Stepping:            3
CPU MHz:             973.556
CPU max MHz:         3300,0000
CPU min MHz:         800,0000
BogoMIPS:            5188.17
Virtualization:      VT-x
L1d cache:           32K
L1i cache:           32K
L2 cache:            256K
L3 cache:            3072K
NUMA node0 CPU(s):   0-3
Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm cpuid_fault epb invpcid_single pti ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid xsaveopt dtherm ida arat pln pts md_clear flush_l1d

Az adatbázis típusok ugyanazok ( MyISAM vs InnoDB, etc ) ?

itt többet számít az iops, mint a cpu... a fejlesztői gépben ssd van?

a vps az milyen vps? használhat-e host io cache-et pl.

Még nem volt, ezért laikusként bedobom a mysqltunert. Hátha kiszúr valamit.

Ha csinálsz egy másik komolyabb műveletet, annak az időigénye is sokkal nagyobb a fejlesztői gépen?

Mondjuk egy tök új táblába 1M sor adat betöltése.

Ha az is lassabb akkor valószínű tényleg egy globális "izé" okozza a gondot, ha nem lassabb akkor jobban körbe kell nézni a problémás táblát.

Gábriel Ákos

Ha "lebutítom" az updatet, hogy csak az az egy tábla szerepel benne aminek az értékét kell updatelni, kiveszem a másik két táblát akkor az pár perc alatt lefut.

Több hasonló updatet kell most írni egy jövőbeni funkció miatt, ami ezt a táblát érint. Van ami gyorsan lefutott a fejlesztői gépen van ami nem. Ami lassan ment az újonnan megírt updatek közül a fejlesztőin, ott az indexekkel játszadozva (a meglévő indexek közül nem az optimálisat használta így meg kellett adni neki egy hint-el hogy mit használjon) sikerült a fejlesztőin is lefuttatni. De ettől függetlenül érdekes, hogy mi okozza ezt a nagy különbséget főleg az első update-nél amivel előjött ez a hiba. Ott az indexekkel már nem nagyon lehet bűvészkedni. 3 tábla vesz részt a játékban egy "fej" és egy "tétel" tábla, ott adottak a primary és foreign key "indexek",  a harmadik táblában meg csak 11 rekord van. Éppenséggel megpróbálhatom majd azt is indexelni, de ha az a 11 rekord okozza ezt a problémát, akkor ... morcos leszek.

Meg kellene próbálni több gépen is megfuttatni ugyanezzel az adatbázissal az updatet ha több másik gépen is megfelelő sebességgel fut akkor legalább már be van határolva hogy nálam van valami gond ...

ANALYZE TABLE table_name;

meg index eldobás - újból létrehozás már gondolom megvolt. (rebuild index kézzel :) )