Topic: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Tokia situacija:
mySQL 5.1.54
Yra didelė lentelė (~2Gb, ~19M įrašų).
Lentelėje nuolat naudojamos INSERT ir SELECT užklausos (UPDATE nebūna niekada)
šiuo metu tipas MyISAM (kaip ir visų kitų lentelių DB)

Kas keletą dienų reikia paleisti užklausą kuri ištrina nuo 50 000 iki 200 000 eilučių.
Trynimas vyksta pagal tam tikrą indeksinį lauką (bet ne pagrindinį)
Kol kas tiesiog leidžiama užklausa DELETE ROM lentele WHERE indexas IN (5,6,7,8.... )
Problema ta kad užklausos vykdymas net šalinant pagal indeksą vyksta 4-5 valandas.

Gal būtų kokių patarimų? Galbūt reiktų pakeisti lentelės tipą?
Dedikuotas serveris tad įvairius mySQL nustatymus taip pat galima keisti.

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Kažkas blogai.

Kiek ID paprastai būna nurodyta, IN() sąlygoje?

Aš pabandyčiau daryti taip: turėčiau susikūręs specialią lentelę, kurioje būtų surašyti reikiami trinti ID. Tarkim prieš trinimo operaciją, ta lentelė yra išvaloma (TRUNCATE) ir į ją suINSERTinami nauji ID.

Turint tokią lentelę galima daryti trinimą naudojant lentelių sujungimą išvengiant IN() operatoriaus. čia aišku su sąlyga, kad tas ID abiejuose lentelėse tikrai yra indeksas ir jis yra naudojamas. (nes gali būt, kad pas tave jis nenaudojamas, o naudojamas kažkuris kitas, indeksas, kuris nepadengia būtent to ID).

Ar naudojamas indeksas, ir kaip naudojamas, gali pasižiūrėt DELETE komandą pakeitęs į EXPLAIN EXTENDED SELECT, pvz.:

EXPLAIN EXTENDED SELECT * FROM lentele WHERE indexas IN (5,6,7,8.... )

Kaip trinti iš vienos lentelės, bet atrenkand duomenis pagal kitos lentelės ID: http://dev.mysql.com/doc/refman/5.0/en/delete.html

Taip pat yra svarbu, kad tu naidoji MyISAM, kuriame realiai operacijos užrakina (LOCK) visą lentelę, todėl jeigu pas tave toje lentelėje trinimo metu vyksta intensyvus skaitymas tai tas stipriai prisideda prie operacijos lag'o. Reikėtų paeksperimentuoti, kiek tokiu atveju padėtų InnoDB naudojimas, kur UPDATE/DELETE rakina konkrečias eilutes.

Dar kitas svarbus dalykas - kiek iš viso tu toje lentoje turi index'ų. Blogai jeigu daug. Nes trinimo metu visi indeksai yra modifikuojami. Idealiu atveju turėtų būti vienas indeksas (pvz PRIMARY), pagal kurį tu atlieki trinimo operaciją.

MySQL serverio konfigūracija irgi turi įtakos, bet čia jau sekantis aiškinimosi/konfigūravimo žingsnis  po schemos/užklausų opetimizavimo.

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Aš tai keisčiau tipą į InnoDB ir visą trynimą atlikčiau transakcijoje, kiek teko eksperimentuoti (ten buvo INSERT, bet manau rezultatai gali būti panašūs) tai viskas vykdavo nepalyginamai greičiau, tikrai ne 2 ar 3 kart greičiau. Tokiu atveju indeksas atnaujinamas tik baigus transakciją. Dėl selektų tai galima pasirinkti kokį nors kitą nei default ISOLATION LEVEL, priklausomai nuo poreikių ir situacijos. Tuomet selectų nestabdys vykdoma transakcija.

MongoDB Certified Developer
MongoDB Certified DBA
Zend Certified Engineer

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

IN sąlygoje nurodau 10000-40000 ID
Anksčiau atrodo buvo kiekvienam ID atskira užklausa tai trukdavo keletą kartų ilgiau.
Galvoju gal bandyti skaidyti į didesnį užklausų skaičių ir po mažiau ID :)
Pats indexas tikrai naudojamas.

Iki šiol tos užklausos metu serverį uždarydavome, tad tuo metu ir SELECT komandų, kurios galėtų apsunkinti nebūdavo.
Dabar yra noras kiek įmanoma labiau sutrumpinti darbo laiką, kad vartotojai kuo mažiau pastebėtų šiuos procesus. Tuo metu panašių užklausų leidžiama keliasdešimt su skirtingomis lentelėmis :)

O indeksų toje lentelėje yra neišvengiamai daug.
Data       782.8     MiB
Index     1,800.6     MiB
Total             2,583.4     MiB
Bet čia jau nieko pakeisti negaliu, nes iš lentelės duomenis imami įvairiais skirtingais aspektai.

o tai IN() performancas blogesnis negu jungiant su kita lentele JOIN`ais?
Dėkui už patarimus, reikės viską išbandyti :)

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Na daug indeksų gerai selectams, tačiau peilis insertams, updeitams ir deletams. čia manau gali praversti ir lentelių struktūros pakeitimai, nes man kažkaip atrodo, kad čia ne viskas gerai...
Gal gali pateikti lentelės vaizdą ir uždėtus indeksus?

MongoDB Certified Developer
MongoDB Certified DBA
Zend Certified Engineer

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Visos lentelės nelabai schemos nelabai galiu pateikti.
Indeksai yra sukurti būtent įvairioms SELECT užklausoms, nes be šių indeksų jos labai pasunkėtų.
Iš esmės pagrinde vyksta SELECT užklausos, tačiau dėl indeksų jos visos yra greitos ir jokių problemų nesudaro.
UPDATE nevyksta niekada.
INSERT  vyksta kiekvieną dieną, tačiau užklausų skaičius nedidelis. Tiesiog insertinami duomenys gan didelėmis porcijos kas tam tikrą laiką.
Kas tam tikrą periodą ~1 mėnesį nebereikalinga informacija yra išvaloma. Būtent čia ir yra didžiausios problemos.

Dabar bandau pakeisti lentelės tipą, naudoti tranzacijas ir nenaudoti IN (arba skaidyti užklausą į daugiau mažesnių užklausų ir naudoti tai mažesnėmis porcijomis)
Galbūt viso to ir užteks :)

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

Nepamiršk parašyt rezultatų (-;

Re: Didelėje lentelėje reikia ištrinti didelį kiekį įrašų.

na IN() naudoti tikrai neapsimoka, JOIN`inant kitą lentelę viskas pagreitėja bent dvigubai.

O innodb ir tranzakcijas ko gero pabandysiu geriausiu atveju rytoj, nes vieną lentelę pakeisti į innodb lokaliai jau trunka nuo pat ryto ir neaišku kada baigsis :)