Large database

Soocam se s problemom velikega stevila rekordov. Bazo imam sicer mysql, v bazi je trenutno cez 16mio zapisov kjer se shranjujejo
eventi. Te rabim hraniti za 5 let. Vse sicer spila super, vendar hocem zaradi velike kolicine podatkov stare podatke
arhivirati v mesecne tabele, vendar ko na bazi 1mio rekordov naredim delete za dolocen mesec, mi nabije cpu do neskoncnosti
in produkcija ne more vec teci normalno. Ne myisam ne innodb ne delata kot bi morala. Studiram sicer o migraciji na postgres,
vendar to pomeni kup dela na aplikacijah. Ena resitev je da ze produkcija pise v mesecne tabele, kjer bo potem velikost cca 400k rekordov.
Tuki je spet problem, ker se v roku enega tedna posamezen event lahko poupdejta z dolocenimi podatki in moram potem iskati po mesecnih tabelah.
Ma kdo kako iskusnjo v tej smeri ? (oracle,mssql itd ne pride v postev)

Select in insert ni panike, ko je treba brisati 200.000 rekordov za dolocen mesec, takrat so problemi, ker mora indexe brisati in potem se defragrmentirati
plac v index fajlih. Ko to dela, zaklepa bazo in posledicno ostali procesi ki selektirajo in insertirajo trajajo predolgo za real-time operacije.
Se poskusil tudi z delete quick in delete low_priority pa je dosti podobno. Nekje vem da sem cital, da ima mysql kar probleme ko enkrat naraste cez mio rekordov.

Ja, delete je "drag", ker mora skrbeti za redo loge in podobno...
Če moraš brisati večino podatkov je morda bolje, da recorde, katere ne želiš pobrisati, preneseš v začasno (ali drugo) tabelo, nato narediš TRUNCATE, nato shranjene podatke preneseš nazaj. Če pa nimaš constraintov ali kakšnih drugih ovir, enostavno dropneš originalno tabelo in preimenuješ tisto začasno.

Mysql 5.1? Potem table partitioning. Narediš particije..., skopiraš v prave particije.. na koncu narediš še truncate glavne tabele...

overlord ti je dal dober predlog

sicer pa je odvidno kako uporabljas stare arhive...

jaz bi recimo imel loceni tabeli med aktivnimi zapisi (tiste po katerih dejansko isces, popravljas in brises) in arhivnimi zapisi... ki jih samo kopicis in se ne spreminjajo ali brisejo.....seveda, ce se ti to splaca....

ce pravis, a imas za shranjevati 5 let podatkov, in da recimo aktivno delas s podatki v zadnjem letu, bi jaz za arhiv naredu tabelo tipa acrhive in nabil notri podatke... tiste aktivne podatke pa bi pravilno poindeksiral...

in seveda VSE operacije opravljal na TMP "kopiji" te tabele, kjer bi drzal notri le tista polja, ki jih potrebujes ...recimo ce dejansko uporabljas polja id, datum, naslov, status, bi vse ostale odstranil.... opravis potrebne manipulacije....in ko naredis vse selecte/edite etc... spremembe uredis na pravi tabeli

za brisanje pa uporabi spet pomozno tmp tabelo, kamor shranjujes IDje pobrisanih rekordov...seveda ce to ne pocnes s kakim drugim orodjem (PHPjem ali cem drugim... pol to naredi raje v kako array spremenljivko)

ma openx je tako krs izdelek, da je joj... fasada je kul, tudi prej bila...tehnicno je pa podn...

Problem pri Mysqlu je, da takoj, ko brišešš več kot 10% zapisov v tabeli ne uporabi indeksa ampak gre delat full table scan. DELETE FROM foo WHERE id IN (SELECT id FROM foo WHERE blalbla);

zato pa uporabis le index na id in naredis delete za vsakega posebej
DELETE FROM table WHERE id = 1;
DELETE FROM table WHERE id = 2;
DELETE FROM table WHERE id = 3;
DELETE FROM table WHERE id = 4;

etc...cim delas razlicne druge operacije na ogromnih tabelah pa to ne gre!

ze to se pozna
DELETE FROM table WHERE id < 5;


zato pa sem predlagal... vse manipulacije naj dela v TMP tabelah, kjer uporablja tiste res nujne fielde in pravilno poindeksirane... pa seveda je zlo pomemben tip fielda.... da ne uporabis int(16) za vrednosti od 1-10 ali text za polje z 1 znakom...


odvidno je tudi kolko spomina ima na razpolago... tmp tabelo lahko da komplet v spomin (memory storage engine) in z njo tam manipulira....

Če prav razumem ti hraniš podatke o neki eventih, se pravi je trenutek nastanka vedno "sedaj", nikoli ne dobiš podatka za prihodnost.
Kaj ko bi mel za vsako leto svojo tabelo? Brisanje 5 let starih zapisov tako zgleda kot sprememba SQLa in drop tastare tabele.
Selecti ti pa ne češejo po eni tabeli ampak po 5ih sistemsko neodvisnih. Če maš dobre omejitve (po letu) lahko še dodatno omejiš iskanje.
Oracle temu pravi particioniranje ampak ga lahko precej simpl spelješ avtomatsko.

Če MySQW slučajno pozna koncept VIEWa, to postane še mal bol simpl.

Poldi112 je 30. okt 2010 ob 10:47 izjavil:

Čisto informatovno, a zna kdo povedati, kako bi se v takih primerih obnesel postgres?

ma za osnovne funkcije bi bil pr vlkih DBjih za odtenek hitrejsi...

sicer pa je mySQL kar se tice featurjev dokaj zmanjsal razliko in ponuja vse tisto kar je prej krasilo in razlikovalo postgres (stored procedures, views, transactions, etc)

No če boš po enga in enega brisal jih obvezno zapakiraj v transakcijo. Čeprav sem še vedno mnenja, da se da to izredno lepo rešiti s particijami. Vse kar boš rabil popravljat je koda za izbris.. kjer narediš truncate najstarejše tabele. Sam bi naredil Tabele za 5*12mesecev... potem pa samo mesečno delaš truncate...
Kar se tiče selectov, jih delaš direktno na glavno tabelo, postgresql je tako pameten, da ve v katero tabelo mora iti delat select, upaj, da je enako z mysql.