10 GB-s rosszul tervezett db vegigjarasa

Adatbázis: SQL, XML DB

Hello!

Van egy kis problemank. Adott egy ~10 GB-s adatbazisunk (nem sajat), amin vegig kellene futni, es kidumpolni egy fileba azoknak az oszlopoknak a neveit, amelyekben nincs mas adat, csak NULL, 0 vagy '', tehat nem hasznaljak. Utana a filebol kiolvasva egy masik scripttel ALTER TABLE var1 DROP COLUMN var2-t vegrehajtani mindegyiken. Az se lenne talan gond, ha egyenesen dropolnank, de jo lenne, ha az is meg lenne, mit dobunk ki belole. Nincs indexeles, van vagy 2 000 000 sor meg 4000 oszlop. Nemelyik tablan akar 50-70 masodpercig is eltart egy query.

Valakinek valami otlet, hogy lehetne megoldani?

  • 2482 megtekintés

( andrej_ v | 2011. 01. 26., sze – 15:40 )

Milyen dbms-ben van ez, mert ettől nagyban függ a megoldás.

( pts | 2011. 01. 26., sze – 15:49 )

Egyszerű megoldás, ha írsz egy PHP, Python, Perl, Ruby vagy hasonló nyelven egy szkriptet, ami SELECT * FROM TablaNev -et nyom minden táblára, és soronként minden mezőre megnézi, hogy az 0, NULL vagy ''-e. ha egy mező minden sorban ilyen értékű, akkor kiírja a mező (oszlop) nevét (vagy sorszámát). Ennek a szkriptnek a kimenetéből már lehet generálni néhány ALTER TABLE TablaNev DROP COLUMN OszlopNev parancsot, amit már le lehet futtatni.

Lehet, hogy ez a külső szkript túl lassú lesz 10 GB adatra. Ha így van, akkor írhatsz néhány tárolt eljárást (stored procedure), amely közvetlenül a MySQL szerveren fut. Nem tudom, hányszor gyorsabb a tárolt eljárás a külső szkriptnél, a szorzó 0.5 és 100 között akárhol lehet.

A COUNT nem jo InnoDB eseten, mert lassu. En valami olyasmivel probalkoznek, hogy: 


function cleandb($db) {
  $res = mysql_query("SELECT TABLE_NAME AS table, COLUMN_NAME AS column FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$db'");

  while ($row = mysql_fetch_object($res)) {
    $exists = mysql_fetch_assoc(db_query("SELECT 1 FROM {$row->table} WHERE {$row->column} IS NOT NULL AND $column <> 0 AND $column <> ''"));
    if (!array_shift($exists)) {
      db_query("ALTER TABLE $table DROP COLUMN $column");
    }
  }
}

Érdemes megmérni, hogy a SELECT * vagy a SELECT DISTINCT OszlopNev a gyorsabb. A bemenettől függően akármelyik is lehet a gyorsabb. A SELECT DISTINCT OszlopNev végigolvassa a táblafájlt minden oszlopra. Ha 100 oszlop van, akkor 100-szor olvassa végig, ami lassabb lehet a SELECT *-nál.

oké, a nézeteltérés oka az, hogy bennem fel sem merült, hogy valaki egy két millió soros táblát egyben kikérjen a mysql-től és azt beleborítsa egy scriptbe.

Abban igazad van, hogyha egyszer futtatod le a select *-ot, az látatlanban gyorsabb lesz, mint minden mezőre külön egy distinct.

Viszont akkor sokkal több időd elmegy arra, hogy írj egy scriptet, ami minden mezőt párhuzamosan kotorászik végig, hogy mi van benne. A fejlesztés, debuggolás, futtatás valószínűleg több időt fog elvinni, mintha írsz egy kőegyszerű scriptet és feleslegesen megdaráltatod egy kicsit a mysql-t.

C#-ban kb. 1-1.5 órás meló lenne összedobni a logikát. Lehet nagyon erőforrás barátra írni, memória igénye nulla, procsszor igénye szintén kevés. Az idő 90%-a kb. egy menetben való adatok felolvasásának IO igénye lenne.

Ráadásul indexek sincsenek, ami szintén érv a teljes felolvasás mellett.)(Az egész db-t csak egyszer kell felolvasni.)

Ez valoban elkepzelheto.

Plusz gyorsitas (legalabbis a DB->script IO igenyen), ha eloszor lekered select *-gal mondjuk az elso 1000 sort (vagy valamennyit, ami elenyeszo a teljes DB-hez kepest), es rogton kiszorod azokat az oszlopokat, amik biztosan kellenek. Utana a select mar nem a teljes DB-n futna, hanem csak a gyanusan ures oszlopokra.
PHP-ban van mar nehany DB piszkalo scriptem, az 1 ora jo felso becsles fejlesztesi idore, de inkabb nehanyszor 10 perc. Ekkora DB-re nagyjabol a futasidovel osszemerheto, utana 1 ejszaka alatt mindenkeppen lefut (az alterrel egyutt).

Ha konnyen programozhato DB (eddig csak Postgreshez irtam tarolt eljarasokat), akkor valoszinuleg megsporolhato a fejlesztesi es a futasido nagy resze. De egy egyszerhasznalatos kodra nem erdemes uj nyelvet tanulni.

--
Auto correct can go straight to He'll.

( mgrincs | 2011. 01. 26., sze – 16:21 )

Azt nem tudom a MySQL mit csinal, de egy par DBMS igen furan kezeli a NULL ertekeket, ezert a distict, es az aggregat fuggvenyek nem feltetlenul adnak jo valaszt.
Szerintem sincs egyszerubb megoldas, mint tenyleg soronkent vegignezni.

Igazabol, azt sem tudom mit varsz egy ilyen "clean-up"-tol, szerintem az esetlegesen felszabadulo hely miatt nem eri meg, es valoszinuleg gyorsabb sem lesz az adatbazis tole. Akkor meg minek?

( r1pp3rj4ck | 2011. 01. 26., sze – 22:26 )

meg van egyebkent, csinaltunk egy stored procedure-t, ami a parameterkent megkapott tablanev alapjan kidobalja a felesleges cuccokat, csak meg nem futtattuk le, mert bugos, azt holnap javitjuk :D

irtam egy shell sciptet, ami mysql-bol lekeri a tablakat, bedumpolja egy fileba, atirja SQL syntaxre, hogy hivja meg a stored procedure-t az adott parameterekkel, aztan lefuttatja azt is, az is ledumpolodik egy fileba, aztan vegrehajtja a dropokat. termeszetesen torlodnek a felesleges fileok, es egy masik fileba kiirja az uptimeot az elejen es a vegen. Szoval lehet tippelni, mikorra fejezi be :)

( Jason v | 2011. 01. 27., cs – 18:13 )

miert nem tesztek fel indexet? par ora alatt (najo, ejjel) letrejon, majd bazigyorsan le tudtok kerdezgetni.

--
"SzAM-7 -es, tudjátok amivel a Mirage-okat szokták lelőni" - Robi.

Szerintem egy tárolt eljárással lehetne legegyszerübben. MySQL-ben nem igazán vagyok otthon, de Oracle alatt tuti így csinálnám.

logikája:
ciklus oszloponként
select count(oszlopnév) ha érték nem 0 vagy NULL
ha count(oszlopnév nulla) oszlop törlése
ciklus vége

pénteken mentés, hétvégére ráengeded, hétfőn vagy örülsz vagy visszatöltessz...
--
Kis problémából egy kis munkával nagy problémát lehet gyártani. Ha valami müxik ne b***tasd :)
Uriember az, aki nem beszél a Windows-ról, pedig tudna...

Igaz, bár azt irta újratervezik az adatbázist, akkor vélhetően normalizálnak is kicsit. A teljes beolvasás mint módszer itt szerintem nem érdemes, csak feleslegesen bonyolítaná a dolgot, mert valamilyen nyelven irt logika kellene hozzá, ami már eleve sebességcsökkenés, hibalehetőség stb. Amúgy erre a logika:
logikai tipusú tömb, oszlopszám elemmel, init értékek hamisak
Ciklus végig a táblán
Belső ciklus a mezőkön, ha adott mező értéke nem 0 vagy NULL tömb adott elemét igazra állítjuk
ciklus vége
ciklus vége

ciklus a tömb elemein, ha az érték hamis adott oszlopot törölni

Tárolt eljárással pár sorból egyszerűen és tisztán megoldható. Adatmanipulációt érdemes mindig tárolt eljáráson/triggeren keresztül megoldani, biztosabb és átláthtóbb
--
Kis problémából egy kis munkával nagy problémát lehet gyártani. Ha valami müxik ne b***tasd :)
Uriember az, aki nem beszél a Windows-ról, pedig tudna...

Igen, ujratervezzuk az egeszet, csak arra kell az egesz cucc, hogy atlassuk ezt az egesz szart, mert eles db-t nem latjuk, csak egy masolatunk van. A cucc egyebkent MSSQL volt, es ASP.NET, mi PHP+MySQL-t irunk helyette. Nem tudom, kik voltak ezek a barmok, akik ezt terveztek, de szerintem le kellene loni oket egy desert eagle-vel.

( Summer6529 | 2011. 01. 28., p – 00:40 )

Azt esetleg lehet tudni, hogy mire használták ezt a táblát? :-)

Abból következtetve, hogy MySQL, nem lehet valami "enterprise" alkalmazás, vagyis a vas, amin fut, sem lehet egy erőgép, vagyis egy-egy lekérdezés eddig is iszonyat ideig tarthatott. Illetve akkor feltételezem, hogy ebbe a táblába csak insertáltak ( lehet, hogy azért nincs index sem ).

Én mindenesetre kipróbálnám azt, hogy mennyi idő az, hogy felrakok indexet 5 oszlopra és aztán mennyi idő alatt fut le az 5 select. Azt kellene megnézni, hogy ez gyorsabb-e, mint index nélkül a select.

A fájlba kidumpolással nem próbálkoznék, szerintem a mySQL gárdája gyorsabb lekérdező algoritmust ír, mint te fogsz :-) ( bár index nélkül, ki tudja... ).

Én mindenesetre kipróbálnám azt, hogy mennyi idő az, hogy felrakok indexet 5 oszlopra és aztán mennyi idő alatt fut le az 5 select. Azt kellene megnézni, hogy ez gyorsabb-e, mint index nélkül a select.
mondjuk hozza kell tenni, hogy az index az a random select-ektol sem ved, tehat ugy is tud relative latvanyosan lassu lenni sok egymas utani select hogyha indexelve van es a tabla relative nagy (nem fer bele a memoriaba/cache-be). de ettol fuggetlenul persze kvazi muszaj, mert igy meg a szekvencialis select-ek is lassuak lesznek.

( micacica | 2011. 01. 28., p – 06:56 )

0. Ellenőrizd le, hogy nem MRG csinálta az eredeti db-t. Ha igen, válts céget.
1. Gyűjtsd össze, hogy milyen adatokra van szükséged.
2. Tervezd meg az új adatbázisodat.
3. Csinálj adatátemelő scripteket.
4. Teszteld az átemelő scripteket ~100 rekordon
5. Tervezz ellenőrző algoritmusokat, amik az átemelést csekkolják.
6. Emeld át az adatokat + teszteld az átemelés helyességét.

( xclusiv v | 2011. 01. 28., p – 13:05 )

Szia,

Írták a select * dolgot, ne!
Írták a distinct-et, ne!
Írták a select count(*)-ot, ne!

Kell egy egyszerű script vagy program, ami a következő-t csinálja
külső ciklus: show tables;ennek az eredményén fogsz végigmenni a belső ciklussal. Hogy tömbbe rakod, vagy magát a result-ot használod részletkérdés
belső ciklus: show columns from XXX, ahol a XXX a tábla neve a belső ciklusból. Ezeken a mezőkön fogsz végigmenni a query-vel
query: select count(YYY) from XXX where YYY is not null and YYY <> '' and YYY <> 0
Ha itt nullát kapsz, akkor ez egy törölhető oszlop, akár itt is mehet az alter table drop column, vagy kiiratod, h tábla-mező törölhető.
Ha nem nulla, akkor van benne érték.

Ez most csak fejből meló mellett pár perc alatt, tesztelni nincs időm, h jó-e.

x

szerk.: ha nem egy, hanem több adatbázis is van, akkor kell még egy legkülső ciklus is, show databases, esetleg show databases like...

SP-t kicsit bonyolultabb hegeszteni, másrészt meg azt én a gyakran használt funkciókra használom, ezt a vackot pedig egyszer kell helyre tenni imho.

A sebességkülöngség szerintem minimális, a dbms dolgozik az indexeletlen vackon, az adatátvitel meg minimális. Meg kiirathatod, hogy ezt csinálom, itt tartok, aztán látod, h fut a csík :)

A reconnect szintén nem nagyon lesz, legalábbis én még nem tapasztaltam, h hosszú query-k esetén bontaná a kapcsolatot a vas.