Mysql lekérdezés belassul, beáll

Adatbázis: SQL, XML DB

Adva van egy suse 10.3 64bit, core2quad, 4GB ram, mysql 5.0.45.

A következő a probléma: a mysqlen másodpercenként kb 135-500 lekérdezés/sec fut. a max terhelésen kb 500. időközönként előfordul, hogy egy egy lekérdezés beáll a mysqlbe, vagy nagyon lassan fut le, ez 10-15 sec, vagy meg se várom, hogy lefusson, mert mögötte beáll a többi. van hogy 300-400 sec-ig fut, ekkor már kilövöm. ilyen kveri áll be hogy: update table set mezo=mezo+1 where id='valami', egy 100.000 soros táblán, vagy 45.000 soron egy egyszerű select. ugyanakkor olyan lekérdezések is beállnak, amik máskor szempillantás alatt lefutnak. a beállt lekérdezések státusza: sending data, copying to tmp table, update, removing tmp, szóval a létező összes állapotba beáll. a load ez egekbe ugrik, volt 400-as is, alapbol 3-5. van hogy a mysql 4 napig "hibátlanul" fut, majd naponta két ilyen beállás is bejön. nem reprodukálható, különböző kverik állnak be, még azt se lehet mondani, hogy egy kveri a hibás. myisam táblák.
itt a my.cnf:
max_connections = 300
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
max_heap_table_size = 64M
tmp_table_size = 96M
query_cache_type = 1
query_cache_size = 32M
thread_concurrency = 8
join_buffer_size = 32M

de már mindenféle variációt kipróbáltam.

viszonylag sok a tmp table is, azt akarom, hogy csinálok egy 100-200mbos ramdriveot, és oda rakom a tmpdir-t. de most hiába van megadba a my.cnfbe a /var/mysql tempnek, a mysql a #sql_aaaa_1.MYD MYI fájlokat továbbra is a /var/lib/mysql-be szorja, ez a datadir egyébként. van erre valami oka, vagy mi?

valaki esetleg találkozott már ilyennel? mit kéne állítani, vagy mi a rossz?

köszönöm a segítséget.

  • 2528 megtekintés

( petya | 2009. 01. 19., h – 16:09 )

MyISAMnel az update table lockkal jar, ha mar nagyon keves update muveleted van megeri InnoDB-t hasznalni (ott row level locking van, es nagyon jol lehet tuningolni). A ramdrive a tmp dirnek ertelmetlen. A temp tablakat ugy csinalja a mysql, hogy megnezi, hogy mekkora kell, majd megnezi a temp_buffer_size vagy valami hasonlo valtozot. Ha belefer, akkor a temp tabla MEMORY engine-nel jon letre, ha nem, akkor MyISAM-mel. MyISAM-et alapvetoen en csak javareszt read only/keves insert tablara hasznalnek. Az update table lockkal jar, ha pedig delete van, az meg rosszabb. Ilyenkor "lyukas" lesz a tabla, es az elkovetkezendo insertek is table lockkal fognak jarni. Ha random updateknel all be a mysql, akkor valoszinu locking parad van.

jó, de a selectek miért állnak be, mikor nincs előtte semmi, ami lockolna. mytop egész nap megy, folyamatosan nézem, és a 45.000 soros táblánál egytáblás select beáll. mondjuk azt, hogy a updatet megoldottam egy másik táblával, delayed insert, és időnként update. ez tán még jobb is mint az innodb.

Ha valami ir egy MyISAM tablat, akkor az lockolodik olvasasra is, te az elso selectet fogod latni a sorban. A mytopot futtatni egesz nap elegge overheaddy, helyette en azt csinalnam, hogy ne kapjon annyi connectiont a production user, mint a max, es beallaskor belepnek (roottal pl, neki lesz szabad connectionje), es nyomnek egy show full processlistet. Az, hogy a 400as load ellenere be tudsz lepni szinten lock parara utal. A mysqlperformanceblog.com nagyon jo olvasnivalo a temaban, MyISAM-et ok is csak javareszt read-only tablakra ajanlanak.

jó, felejtsük el, hogy insert és/vagy update van a táblákon. nincs. készítettem 3 képet, megpróbálom azzal elmagyarázni, hogy mizu:

a db futott innodbvel is kb 2 percig, éles üzem idején voltam olyan bátor, és átraktam. ez lett az eredménye(pár napja):

http://dev2.hu/gumiszoba/hup3_innodb.png

16 sec tmp table másolás, látszik ,hogy 2 secig nem futott le az első kveri, a többi utána beállt oda a többi, lejártak ugyanis a dblayerben cachek, mindegyik bekérdezett... lentebb látszik egy insert into nowview , most épp 2755 sor van benne, látszik, hogy előtte nincs select, ami azt a táblát fogná, miért fut akkor 4 secig, egy pár byteos insert?

http://dev2.hu/gumiszoba/hup2.png
ez már myisam. a usersonline tábla most épp 3608 soros, csak insert van rajta, meg delete, oldalletöltésenként insert, delete 10 percenként, dblayeres select cachelve, szoval azon az egytáblás kverin mi fut 16 másodpercig? memória tábla, mivel sokat használt, pár bájtos igazán, mégis beáll, semmire nem vár.... namost ezt egyszer adta, azóta sem állt be itt. tehát eddig ezt egyszer produkálta.

http://dev2.hu/gumiszoba/hup1.png

az első kveri egy teljesen másik adatbázishoz tartozik, (63 sec), removing tmp, a 3 last_search-es megint egyfajta db, egytáblás lekérdezés, az 5. closing table-s meg egy 3. db. tehát betorlodik a kveri, utána senki semerre.

sürű updates dolog nincs, a selectek állnak be, látszik semmi nem lockol. mert az ugye megint egy dolog, hogy el van írva egy kveri, és mindig másodpercekig fut, és mögéáll a többi, de legalább lefut, mégha lassan is. de mindig másik kveri áll be... :( vagy esetleg máshogy kérdezve: mitől állhat be egy 4000 soros táblán időnként egy select?

de lenne is sűrű update, akkor is: mitől fut egy egyszerű számlálónövekedés percekig, ha nincs előtte semmi?

@petya: mtoppal lesem, mikor kezd beállni. mert ha elszalad a ló, másik konzolba megy az apache stop majd mysql restart, majd apache start. de ez így komolytalan nagyon... :D egyébként percekig jelentkezne be, ráadásul a nagios se jut akkor időhöz, ergó összeomlás után percekkel ha kapok smst, de lehet azt se, mert apostfix is ezt a mysqlt használja... a blogon sem találtam, hogy mit kéne állítani....

egyébként a képtelenség.hu alatti vasról van szó....

Ismét megfogtam egy beállást:

http://dev2.hu/gumiszoba/beallas.png

last_search tábla 21632 soros, és mögé beállva olyan kveri, aminek köze nincs a táblához. láthato, hogy 1 nap 1 percet bírt kereken. nincs csúcsterhelés, ezek a kverik egyébként leszaladnak rendesen.

ja, load: 156

nincs. néztem közben a iostat -m 1 -gyel a diskeket, de semmi.

közben a tmp dir-es hibára megvan a megfejtés. bug.

http://bugs.mysql.com/bug.php?id=30287

namost egyébként lehet, hogy ettől van, hogy a /var/lib/mysql -be, a datadirbe szemetel, ami ugyanaz a disk, nyilván. de ez eddig fel se nagyon tünt. lehet a disk nem tud kiszolgálni? gyanus mondjuk, hogy ennyi ideig...

lett frissítve 5.0.67-re. ez már veszi a tmpdir változót, és betoltam ramba (tmpfs) az ideiglenes táblákat. a load láthatoan lentebb ment. node, hogy ne legyen bodottá, azota kétszer is beállt, egyszer "sending data" egyszer pedig "closing tabla" state-ben. más más kveri és database. segíthete az, hogy pl 4 óránként tolor rá egy flush query cache; flush tables; cront? pici lélegzetvételhez jut...

tehát nem az a baj véleményem szerint, hogy myisam, és hogy lockol, mert legutóbb nem volt egy locked state sem... sending data, vagy closing tables volt....

+1

A nem-determinisztikus beallas tipikus tunete az osztott eroforrast hasznalo programoknak. Elvileg deadlockot nem okozhatna query, de az implementacio meg siman okozhat iszonyatos lassulast: vannak esetek amikor arra optimalizalnak, hogy a lock ugyis hamar szabadda valik, de ha meg sem, akkor a sok-sok busy-waiting spinlock jocskan megfoghatja a gepet. Erre utal a magas load is.

Szerintem ilyen esetben a queryket kellene optimalizalni, valamint olyan DB enginet valasztani a tablakhoz, amik hatekonyabban kezelik a lockokat. Valamint ha nincs ra szukseg, csokkenteni a transaction isolation levelt.

--
The Net is indeed vast and infinite...
http://gablog.eu