mysql storage engine valasztas

Adatbázis: SQL, XML DB

A kovetkezo problemara keresek esszeru megoldast:

adott egy script, ami maillog-ot olvas File::Tail-lel, es a feldolgozott infokat adatbazisba tolja (az adatok jellege szerint) kb. 3 db (indexelt) tablaba. Worst case tablankent havi 10M rekord gyulne ossze az adatbazisban.

A kerdes az, hogy milyen storage engine-t valasszak, ha keresni is kell benne, tovabba naponta 1x torolni az 1 honapnal regebbi rekordokat, es mindezt ugy, hogy ne szakadjon bele se a gep ill. se a mysql demon a csomo insert-be, ill. a select-ek is ertelmes sebesseggel fussanak le?

Az 'archive' engine nagyon megtetszett, csak egy feature-t nem tud: delete, ami azert fajo hiany erre a feladatra...

Ha csak jobbat nem mondtok, akkor marad az 'archive' storage engine ugy, hogy naponta uj tablat keszitek pl. table_YYYYMMDD, es ami nem kell, azt eldobom drop-pal. De ez azert nem tul elegans hackeles...

Es meg egyet nem tud: indexeket, ami azt is jelenti, hogy az archive engine-t el kell felejteni...

  • 1638 megtekintés

( uid_194 | 2011. 05. 17., k – 16:51 )

Egyenkent insertelve irgalmatlanul lassu lesz, storage enginetol fuggetlenul. Vagy csinalj bulk insertet (erre van vagy 3 megoldas), vagy HandlerSocket neki, es ugy bulk insert. Onnantol az insert gyors lesz, a gep sem szakad bele, es a select is ertelmes sebesseggel futna le.

Ha egyenkent insertelsz, folyamatosan, relative nagy sebesseggel, akkor szerintem enginetol fuggetlenul lassu lesz es leterhel. (memory-only engine(ke)t nem szamitva :P)

--
|8]

( petya | 2011. 05. 17., k – 16:59 )

Ha InnoDB-n vagy, akkor viszonylag sok lehetoseged van az iras tuningolasara, akar a durability rovasara is. Alapbol, ha gyors inserteket szeretnel, akkor megfelelo log file es log buffer meret kell neked (checkpoint age-et monitorozd). Ha a durability rovasara is mehet a dolog, akkor innodb_flush_logs_at_trx_commit.

Naponta egyszeri torles: ha ezt egy nagy tranzakcioban csinalod, akkor az undo mereted meg fog noni, ezt le kell bontani sok kis tranzakciora, szerencsere ez viszonylag egyszeruen megoldhato.

Ha MyISAM-et hasznalsz, akkor a torles miatt concurrent_insert=2, es neha rebuildelned kell a tablaid, ha nem akarod, hogy vegtelen sok storaget megegyen, ez 1 gep eseten offline muvelet. Egyebkent a delete-k miatt lyukas lesz a MyISAM tabla, es amig azokat be nem tomod az insertek is table lockolni fognak.

Ha az archive-hoz ragaszkodsz, akkor a torlest meg tudod oldani datum alapjan range partitioning-gel, es alter table drop partition-nal torolsz, ezt a megoldast barmelyik storage engine-nel hasznalhatod. Ha InnoDB-nel teszed ezt, es viszonylag nagy buffer poolod van, akkor ez eltarthat valameddig, hiszen ilyenkor az InnoDB a buffer poolbol kiveszi azokat a page-eket, amik a torolt tablahoz (particiohoz tartoznak).

Ha InnoDB-n vagy

meg sehol nem vagyok, ezert e topik :-)

Alapbol, ha gyors inserteket szeretnel, akkor megfelelo log file es log buffer meret kell neked (checkpoint age-et monitorozd).

Hmmm, mondjuk egy innodb_log_buffer_size=8M es innodb_log_file_size=32M parositast azert kiprobalok majd (innodb_buffer_pool_size=256M mellett)

Naponta egyszeri torles: ha ezt egy nagy tranzakcioban csinalod

Ugy erted, hogy egy 'delete from ... where ts < timstamp' formaju utasitast begin + commit koze tegyek? De azt olvastam, hogy az innodb-ben delete helyett gyorsabb a drop table, vagy meg inkabb a lentebb emlitett drop partition. A myisam akkor kiesett a valasztasbol. A tablak rebuild-elese nem jarhato ut.

Nem ragaszkodom amugy az archive-hoz, sot az kifejezetten hatrany, hogy indexeket nem tamogat, mert akkor barmilyen select gyakorlatilag egy full table scan-t eredmenyez. Viszont a lekerdezesek sokkal ritkabbak, mint az insert-ek. De elriaszt az 'archive' storage-tol az, hogy egy 10M rekordot tartalmazo tablaban vajon meddig tart egy select lefuttatasa? Ill. ha jol ertem a particonalast, es sikerul emberi meretu pl. 100k rekord particiokat kialakitani, akkor csak 100k rekordot fesul at egy select-hez, igaz?

Mindenkinek kipostázzák az alkotmányt

Log file es log buffer: meg ezek is eleg kis ertekek. Ha InnoDB-t hasznalsz, akkor akkor lesznek gyorsak az irasaid in general, ha az adatbazisod belefer a buffer poolba (valojaban eleg csak annak az adatnak, amit hasznalsz is).

Kerdes: ha elcrashel az adatbazisod, akkor megengedheto az, hogy elveszits par masodpercnyi adatot? Ha most myisam-et hasznalsz, akkor amugy is igy van:).

Archive: csak akkor nem lesz full scan a select, ha partitioning function alapjan selectelsz. Ha az alapjan, akkor lenyegeben csak a particion lesz full scan, ezt explain partitions-zal tudod megnezni. MySQL-ben a particiok valojaban tablak.

Egy tranzakcioban: ugy ahogy irod, a delete egy tranzakcio lesz. Ha viszont delete from ... where ts < timestamp limit 5-ot csinalsz pl, akkor kevesebb ideig fog futni a tranzakcio, kevesebb rekordot lockol, kevesebb undot general, azt kell vizsgalni, hogy mikor toroltel kevesebb, mint 5 elemet, mert akkor abba lehet hagyni. Ezt remelkul meg lehet irni barmiben.

Log file es log buffer: meg ezek is eleg kis ertekek. Ha InnoDB-t hasznalsz, akkor akkor lesznek gyorsak az irasaid in general, ha az adatbazisod belefer a buffer poolba (valojaban eleg csak annak az adatnak, amit hasznalsz is).

A innodb_log_buffer_size-ra a gyari doksi ir 1-8 MB-ot, mig a innodb_log_file_size-ra 1 - 0.5 * innodb_buffer_pool_size MB erteket. Amugy 1 rekord ~500 byte, igy 300k rekord elvileg 150 MB, ami belefer a jelenlegi 256 MB-os innodb_buffer_pool_size-ba. Btw. ha innodb lesz belole, es szinten particionalnam, akkor eleg lenne egy particionak belefernie?

Kerdes: ha elcrashel az adatbazisod, akkor megengedheto az, hogy elveszits par masodpercnyi adatot?

meg. Ebben az esetben a log gyujto script sem kepes sql-be tolni az adatot, es egyelore nem is akarok obenne bufferelni, amig helyre nem all a kapcsolat az sql szerverrel.

Viszont amit meg be kene tudni loni, az az egy ~300k rekord meretu particio full table scan ideje 'archive' storage eseten. Mert ha 1-2 sec-nel jelentosen tobb, akkor az elvezhetetlen felhasznaloi elmenyt nyujt.

Mindenkinek kipostázzák az alkotmányt