[MEGOLDVA] MySQL tábla partícionálás

Adatbázis: SQL, XML DB

Adva van egy adatbázis aminek a sebességén kellene javítanom.
Van ebben a db -ben néhány olyan tábla melyekben cirka 5 - 8 millió rekord van. Mivel és a hozzájuk tartozó file méret is Gigabyte körüli, szeretném ezeket elpartícionálni, 500 ezer, 1 millio rekordonként.
A MySQL ide vágó doksiját elolvastam ( http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html ).
Viszont az nem egészen tiszta, hogy ha most megcsinálom a partíciókat akkor később, a rekordok szaporodásával ezt meg kell-e csinálnom még egyszer.

Tehát ha most csinálok egy táblából amiben 5 430 789 rekord van, 6 partíciót:

* 0 - 1 000 000 -ig
* 1 000 001 - 2 000 000 -ig
* 2 000 001 - 3 000 000 -ig
* 3 000 001 - 4 000 000 -ig
* 4 000 001 - 5 000 000 -ig
* 5 000 001 - a végéig

akkor a hatodik partíció fog megint hízni akármeddig (erre tippelek a doksi alapján), vagy a hatodik partíció tart a tábla jelenlegi végéig és a később beszúrt adatok egy 7. partícióba kerülnek.

Másik kérdés partícionálhatok-e úgy, hogy azokra a recordokra is megmondom a partíció határokat amik még nincsenek a táblában (jelen példát folytatva 5 - 6 millió, 6 - 7 millió, 7 - 8 millió, 8 - 9 millió, stb)?

  • 6506 megtekintés

( Jason v | 2012. 01. 17., k – 09:06 )

Idevágó indexelés megvan már?
Biztos, hogy az a 5ésfél millió sor nem bontható máshogy tovább?

Mi a lassú, hogy gyorsítani akarod? 2 sec egy sor kiválasztása indexelt oszlop alapján is?
100Hz-en mintavételezed az utolsó 300 sort?

Mivel elolvastad a doksit amit linkeltél, akkor aszerint a less than maxvalue-t fogod használni, majd amikor sok adat begyűlt az utolsó partícióba, alter table és új partíciót adsz hozzá.

De. Használhatsz más szintaxist, amikor azt mondod meg, mi alapján akarsz particionalni, s megadod hany particiot akarsz, annyi reszre osztja a key domainje alapjan. Persze ez kesobb valtoztathato.
http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html
Ugyanakkor el akarod olvasni ezt is: http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

"Idevágó indexelés megvan már?"
Részben igen, részben folyamatban, de a probléma ettől mélyrehatóbb, mivel az alkalmazás ami ezt a db-t használja a rosszul megtervezett lekérdezések miatt sokszor végez full table scan -t, ontja a temp táblákat.

"Biztos, hogy az a 5ésfél millió sor nem bontható máshogy tovább?"
Sajnos nem :(

"Mi a lassú, hogy gyorsítani akarod? 2 sec egy sor kiválasztása indexelt oszlop alapján is?"
Vannak egész egyszerű lekérdezések, pl.: SELECT * FROM nagyon_hosszu_table WHERE valami_egyszeru_feltetel; amik (nyilván más lekérdezések lockjai miatt) 3 - 6 másodpercig tart. Vannak ugyanakkor, olyan query -k amik 35 - 45 másodpercig tartanak

A HASH alapú partícionálás így első ránézésre nem biztos, hogy jó nekem. A maatkit eredményei alapján azt már látom, hogy a jelenlegi kulcsok jó része nem hatásos, vagy egyenesen hibás (ezt persze okozhatja a sok full table scan is). Ezért úgy érzem, nem lesz hatékony a HASH alapú partíció. (javíts ki ha tévedek)

----
올드보이
http://molnaristvan.eu/

Az az 5,5 millió sor nem is olyan sok egyébként. Normálisan meg kellene csinálni az indexeket, illetve faragni kellene mindenféle db paramétereket (query cache, mindenféle buffer és size), illetve ha sok memóriád van, akkor akár a /tmp mehet tmpfs-re is, azzal sok disk io-t meg tudsz spórolni (ha tényleg annyira rosszak a query-k az alkalmazásban, vagy a sok írás miatt nem tudod rommá indexelni).

Minden performace tuning guide úgy indul, hogy tervezd meg normálisan az adatbázist, rakj indexet oda ahova kell, írd meg normálisan a query-ket, az többet ér minden szervertuningnál.

A szervertuning pedig nagy vonalakban úgy néz ki, hogy jókora query cache (bár ez nálad nem játszik), jókora key buffer és minden releváns *_buffer és *_size megnövelése :). Persze nyilván ésszel, és mérsz - módosítasz - mérsz az ajánlott módszer. De semmit konkrétatt nem tudunk se a feladatról se a vasról, így pedig nehéz nekiállni.

Tudni kellene:
- dedikált (mysql only) szerver-e, vagy van rajta komplett lamp stack, esetleg még más is
- mennyi RAM-od van
- milyen storage engine(-ek)

De a legfontosabb: milyen táblastruktúra, milyen query-k, milyen gyakran, slow query log-ot kapcsold be, aztán explain-nel nézd meg őket.

Aha.

Ha tudod, mik az alap lekérdezések, akkor explain plan alapján próbáld ki extra indexekkel.
Tudod módosítani egyáltalán a progit? Hogypl a lekérdezéseket abban kicsit atird.

A 35-45 masodperces lekerdezes igen gyanus, plane ha gyakori futasra van szanva, nem lehet, hogy az app hazon belul vegez extra muveleteket amit sql-ben is meg lehetne csinalni? (pl. group by-ozas az app-ban belul, amihez leszivja a fel tablat feleslegesen)

A lockolas is lehet neha felesleges :-)

Es lehet, hogy a particionalas nem is megoldas erre, ha szarok a selectek.

Az EXPLAIN az jó ötlet!
A témán rajta van egy programozó is.

A group by már most is szőrös dolog.

Ami eddig megvolt:

  • tegnap délben bekapcsoltam a slow logot 2s -es idővel
  • a keletkezett logot oda adtam a maatkit mk-query-digest -nek
  • futtattam check, optimize, analyze table -t

Ami még lesz:

  • a maatkit mk-query-profiler segítségével elemzem a kulcsokat és a kulcsok hatékonyságát
  • csinálok jmeter tesztet annak érdekében, hogy tudjak sebességet statisztikázni
  • csinálok partíciókat
  • várk minden javaslatot, ötletet

----
올드보이
http://molnaristvan.eu/

( Jason v | 2012. 01. 17., k – 10:19 )

Srácok előttem: túl hosszú a kommentem, amiben benne vannak a válaszok?:-)

( Vudumen | 2012. 01. 17., k – 13:19 )

Keves az info ahhoz, hogy ez 100% bizonyossaggal kijelentheto legyen de szerintem a particionalassal nem fog gyorsulni az alkalmazasod. A lekerdezeseket kell atnezni es a hozzajuk tartozo indexeket. Raadasul ez nem is nagy adatmennyiseg, gyanitom, hogy az egesz elfer buffer poolban (felteve, hogy InnoDB-rol beszelunk, MyISAM eseten a table lockok rontjak valoszinuleg a teljesitmenyt). Ha mar particionalsz akkor a metodika kivalasztasanal (range, hash, etc.) szinten a lekerdezeseket kell figyelembe venni, hogy lehetoseg szerint a parition pruning minnel hatekonyabban utilizalodjon.

( Kayapo | 2012. 01. 18., sze – 08:20 )

Nos közben megszületett a megoldás:
Mint az oly sokszor most is bebizonyosodott, hogy amit egy programozó elront azt egy rendszergazda nem valószínű, hogy tudja kompenzálni. (Meg az is, hogy ha kakából építesz várat a decemberi hidegben, az tavasszal rád fog dőlni)

Először is a maatkit nyújtotta eszközök nagyszerűek ahhoz, hogy megtaláld egy sql alapú alkalmazás gyenge pontjait.
A my.cnf -ben beállítottam, hogy azok a lekérdezések amelyek 2másdpercnél hosszabb ideig futnak, vagy nem használnak indexeket kerüljenek be a slow query log -ba:

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Ezt így hagytam egy 24 órán keresztül. Az összegyűlt logot azután az mk-query-digest -nek adtam oda:

mk-query-digest mysql-slow.log > maatkit.mk-query-digest.out

Az eredményül kapott statisztikából kiválogattam a query -ket és ezeket az mk-query-profiler -el tovább elemeztem

mk-query-profiler --database hiper_optimise -umaatkit -pAo0lkuseZ91 -h localhost --tab hiper_test2.sql > mk-query-profiler-report.csv

Ez továgg árnyalja a képet és ki, azáltal, hogy eredményez egy táblázatot amelyben látható, melyík lekérdezés hány sort és mekkora adatmennyiséget érint.

A lekérdezések működésének elemzésére ezután az EXPLAIN -t lehet használni. Ha sokat látod a "Using filesort" -ot az jót nem jelent, mivel ilyenkor temp file -t csinál a mysql.

Partícionálás:
Az látszik, hogy ez még nem tökéletes a mysql -ben. Még nem létező id -re nem lehet partíciót definiálni. Ha nem integer az id akkor lehet/kell a HASH partícionálást használni. A partícionálással 5-7% futásidőt lehet spórolni (optimálisabb query-kel gondolom többet)

Köszönöm mindenkinek a segítséget, ha esetleg valamit nem írtam le, kérdezzetek nyugottan!

----
올드보이
http://molnaristvan.eu/

A lekérdezések működésének elemzésére ezután az EXPLAIN -t lehet használni. Ha sokat látod a "Using filesort" -ot az jót nem jelent, mivel ilyenkor temp file -t csinál a mysql.

Ennek a neve eleg megteveszto, ez csak azt jelenti, hogy a rendezeshez nem hasznal indexet. Ha a sort bufferbe belefer a rendezendo set, akkor csak azt hasznalja, ha pedig eleg a memoria alapu temp tabla, akkor pedig azt. SHOW GLOBAL STATUS-bol a sort merge passes-t tudod nezni, hogy mennyi olyan tortenik, amikor nem eleg a sort buffer, tuzoltas jelleggel a sort buffert ilyenkor feljebb lehet emelni ha tudod.