Forum » Programiranje » [MariaDB] INSTERT INTO z vmesnimi commit-ti per n rows?
[MariaDB] INSTERT INTO z vmesnimi commit-ti per n rows?
HotBurek ::
Dobro jutro.
Evo, toktrat imam pa sledečo situacijo. SQL skripta naredi select iz izvorne tabele, potem group by, ter na koncu še insert into v ciljno tabelo.
Primer:
In zadeva faila, ker prej zmanjka diska (/tmp/neki..).
Sumi, da skrpita dela v dveh korakih. V prve koraku zloži vse skupaj in shrani v temp fajle (in že tu faila), v drugem pa bi naredila commit vsega v tabelo.
Kako bi lahko naredil, da bi skripta delala commit na recimo vsakih 1000 row-ov? Ali pa še kakšne druge opcije...
p.s.: Če v štartu ne uporabim "SET autocommit = 0;", dokaj kmalu dobim sledeč error: SQL Error [1206] [HY000]: (conn=34) The total number of locks exceeds the lock table size
p.s.: Prav tako sem poizkusil SELECT INTO OUTFILE '/tmp/group_by.csv' in isto faila zaradi premalo diska.
Evo, toktrat imam pa sledečo situacijo. SQL skripta naredi select iz izvorne tabele, potem group by, ter na koncu še insert into v ciljno tabelo.
Primer:
SET autocommit = 0; LOCK TABLE `source_table` READ, `destination_table` WRITE; INSERT INTO `destination_table` SELECT `columns` FROM `source_table` GROUP BY `some_stuff` ORDER BY `some_key` ASC; COMMIT; UNLOCK TABLE; SET autocommit = 1;
In zadeva faila, ker prej zmanjka diska (/tmp/neki..).
Sumi, da skrpita dela v dveh korakih. V prve koraku zloži vse skupaj in shrani v temp fajle (in že tu faila), v drugem pa bi naredila commit vsega v tabelo.
Kako bi lahko naredil, da bi skripta delala commit na recimo vsakih 1000 row-ov? Ali pa še kakšne druge opcije...
p.s.: Če v štartu ne uporabim "SET autocommit = 0;", dokaj kmalu dobim sledeč error: SQL Error [1206] [HY000]: (conn=34) The total number of locks exceeds the lock table size
p.s.: Prav tako sem poizkusil SELECT INTO OUTFILE '/tmp/group_by.csv' in isto faila zaradi premalo diska.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
- spremenilo: HotBurek ()
WizzardOfOZ ::
fetch vseh vrstic potem pa števec in ga z vsakim insertom povečuješ, na določeno število pa potem narediš kommit.
pa še zaklepat ne rabiš tabele.
tebi sedaj zaklene tabelo on hoče vse inserte narest, na koncu pa kommitat. seveda ne gre, ker imaš pravice, da narediš nekje 18000 vrstic, potem pa mu zmanjka prostora v tempu in se sesuje sql.
pa še zaklepat ne rabiš tabele.
tebi sedaj zaklene tabelo on hoče vse inserte narest, na koncu pa kommitat. seveda ne gre, ker imaš pravice, da narediš nekje 18000 vrstic, potem pa mu zmanjka prostora v tempu in se sesuje sql.
Milčinski je napisal butalce kot prispodobo in ne kot priročnik!!!
Svuda u svijetu ima budala ali je izgleda kod nas centrala!!!
Svuda u svijetu ima budala ali je izgleda kod nas centrala!!!
HotBurek ::
Evo, sem naredil funkcijo, notri pa FETCH cursor.
Prvi runtest, ki je poizkušal vnesti vse row-e, je failal (disk se je zafifal).
Potem sem pa naredil tako, da procedura (oz. loop) vnese samo prvih 10 row-ov. In to sem testiral na manjši izvorni tabeli, za končno (insert) tabelo sem uporabil isto. Tako da vem, da dela pravilno.
No, in tudi to je fail-al. In to mi zdej ni jasno, zakaj oz. kaj se dogaja.
Na disku je 36GB prostora.
Najprej SELECT zbere podatke iz izvornih tabele, naredi group by in jih zloži skupaj v temp fajl /tmp/#sql-temptable-abc12-34-d.MAD. Ta fajl vsakič med izvajanjem procedure raste in zraste na 11GB, vse skupa tajra kakšnih 30~60 minut.
Potem pa se v naslednjem koraku zelo hitro zafila disk (spremljam z df -h). In tu sem mislil, da dela commit v tabelo. No, sedaj sem (kot je napisano zgoraj) zmodificiral, da se vnese zglolj 10 vrstic.
Skratka, 11GB file v /tmp/ folderju se kreira, vedno pride do iste velikosti. Tega sem tudi pogledal (tail filename), in vidim vsebino, ki je rezultate SELECT-a. Do tu je vse ok.
A ta drugi del, ko zafila preostalih 25GB prostora na disku, kljub temu, da je funkcija omejena na INSERT zgolj 10-tih row-ov (testirano na manjši tabeli), mi pa ni jasno. Na koncu v tabelo ne vnese niti enega samega row-a.
Je tudi zamudno testira vsako spremembo, ker traja 1h za en test. Krneki.
Prvi runtest, ki je poizkušal vnesti vse row-e, je failal (disk se je zafifal).
Potem sem pa naredil tako, da procedura (oz. loop) vnese samo prvih 10 row-ov. In to sem testiral na manjši izvorni tabeli, za končno (insert) tabelo sem uporabil isto. Tako da vem, da dela pravilno.
No, in tudi to je fail-al. In to mi zdej ni jasno, zakaj oz. kaj se dogaja.
Na disku je 36GB prostora.
Najprej SELECT zbere podatke iz izvornih tabele, naredi group by in jih zloži skupaj v temp fajl /tmp/#sql-temptable-abc12-34-d.MAD. Ta fajl vsakič med izvajanjem procedure raste in zraste na 11GB, vse skupa tajra kakšnih 30~60 minut.
Potem pa se v naslednjem koraku zelo hitro zafila disk (spremljam z df -h). In tu sem mislil, da dela commit v tabelo. No, sedaj sem (kot je napisano zgoraj) zmodificiral, da se vnese zglolj 10 vrstic.
Skratka, 11GB file v /tmp/ folderju se kreira, vedno pride do iste velikosti. Tega sem tudi pogledal (tail filename), in vidim vsebino, ki je rezultate SELECT-a. Do tu je vse ok.
A ta drugi del, ko zafila preostalih 25GB prostora na disku, kljub temu, da je funkcija omejena na INSERT zgolj 10-tih row-ov (testirano na manjši tabeli), mi pa ni jasno. Na koncu v tabelo ne vnese niti enega samega row-a.
Je tudi zamudno testira vsako spremembo, ker traja 1h za en test. Krneki.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
HotBurek ::
Sedajle sem pognal še en test.
Naredil sem, da sem na vhodu (SELECT) omejil na 20 row-ov (LIMIT 20), ter pustil counter v LOOP proceduri, ki omeji prvih 10 vnosov in potem naredi LEAVE loop.
In kaj se je zgodil? Fail. Spet se je zafilal disk. Tega res ne razumem...
Naredil sem, da sem na vhodu (SELECT) omejil na 20 row-ov (LIMIT 20), ter pustil counter v LOOP proceduri, ki omeji prvih 10 vnosov in potem naredi LEAVE loop.
In kaj se je zgodil? Fail. Spet se je zafilal disk. Tega res ne razumem...
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
kunigunda ::
Nared najprej
EXPLAIN INSERT INTO `destination_table`
SELECT `columns`
FROM `source_table`
GROUP BY `some_stuff`
ORDER BY `some_key` ASC;
da vids kva ti bo delu
EXPLAIN INSERT INTO `destination_table`
SELECT `columns`
FROM `source_table`
GROUP BY `some_stuff`
ORDER BY `some_key` ASC;
da vids kva ti bo delu
HotBurek ::
Takole se izriše:
id|select_type|table|type|possible_keys |key |key_len|ref |rows |Extra | --+-----------+-----+----+----------------------------+-------+-------+--------------------+--------+--------------------------------------------+ 1|SIMPLE |pa |ALL |PRIMARY,pages_schema_int_IDX| | | |36396564|Using where; Using temporary; Using filesort| 1|SIMPLE |ac |ref |PRIMARY |PRIMARY|74 |database_crawl.pa.id|22 |Using index |
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
kunigunda ::
Probi zdej fielde, k jih mas v group by, dati v secondary key (alter table ... add key (...)), lahko potem se enkrat naredis explain (npr samo selecta), ampak mislm da bi resilo ta problem.
HotBurek ::
Sem naredil index, ki združuje id (pogoj v JOIN-u) ter schema_int (to je edino, kar je v WHERE pogoju).
Pognal EXPLAIN in je isto:
Mislim, da bi moral bolj it v smer, kaj in kako se odvija vpisovanje podatkov. Sedaj imam omogočen logging, kjer se vsak ukaz vpiše v log file.
In bom poglnal, to kar sem do sedaj imel in da vidim, kaj se zgodi, ko se temp file zbilda...
Pognal EXPLAIN in je isto:
id|select_type|table|type|possible_keys |key |key_len|ref |rows |Extra | --+-----------+-----+----+-------------------------------+-------+-------+--------------------+--------+--------------------------------------------+ 1|SIMPLE |pa |ALL |PRIMARY,pages_id_schema_int_IDX| | | |37539507|Using where; Using temporary; Using filesort| 1|SIMPLE |ac |ref |PRIMARY |PRIMARY|74 |database_crawl.pa.id|22 |Using index |
Mislim, da bi moral bolj it v smer, kaj in kako se odvija vpisovanje podatkov. Sedaj imam omogočen logging, kjer se vsak ukaz vpiše v log file.
In bom poglnal, to kar sem do sedaj imel in da vidim, kaj se zgodi, ko se temp file zbilda...
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
DamijanD ::
koliko je pa velika baza? Ker 36GB proste placa za server, kjer imaš 1GB bazo je ogromno; ampak, če je baza pa velika 500GB, je pa 36GB praktično nič...
HotBurek ::
Baza ima skupno 72GB, od tega ima tabela, iz katere berem, nekje 500M record-ov in skupno ~40GB.
V vmesnem času sem spremenil SELECT, ki zajame podatke do te mere, da več ne uporablja JOIN. Pa v config-u sem odstranil nekatere nastavitve, ki sem jih prej dodal.
Da omenim dve nastavitvi:
Prva bi naj bila ON že po defaultu, kar sem tudi videl, ko sem gledal vsebino v /var/lib/mysql/database_1/.
A ta druga nastavitev, kompresija, pa ni ON po defaultu.
No, in sedajle je query končno in uspešno naredil INSERT. YES!
Bom bil napisal, da sumim kompresijo (nastavljeno na najbolj trdo nastavitev, `PAGE_COMPRESSION_LEVEL`=9) in da ta porabi vse resurse.
Grem potestirat original query in brez kompresije, da vidim, če je to to.
V vmesnem času sem spremenil SELECT, ki zajame podatke do te mere, da več ne uporablja JOIN. Pa v config-u sem odstranil nekatere nastavitve, ki sem jih prej dodal.
Da omenim dve nastavitvi:
innodb_file_per_table=ON innodb_compression_default=ON
Prva bi naj bila ON že po defaultu, kar sem tudi videl, ko sem gledal vsebino v /var/lib/mysql/database_1/.
A ta druga nastavitev, kompresija, pa ni ON po defaultu.
No, in sedajle je query končno in uspešno naredil INSERT. YES!
Bom bil napisal, da sumim kompresijo (nastavljeno na najbolj trdo nastavitev, `PAGE_COMPRESSION_LEVEL`=9) in da ta porabi vse resurse.
Grem potestirat original query in brez kompresije, da vidim, če je to to.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
HotBurek ::
Evo, kot kaže, je bila rešitev v tem, da sem SELECT spremenil tako, da sem odstranil JOIN. To sem lahko naredil, ker je v "autocomplete" tabeli 'id' zapisan v taki obliki, da se iz njega da dobit 'shop_id' (uporabil v GROUP BY) in tako nisem rabil uporabit JOIN-a z "main" tabelo, kjer je 'shop_id' izvorno shranjen.
Verjetno bom v naslednji verziji naredil manjši rewrite in dodajal shop_id tudi v "autocomplete" tabelo, s tem se bo ta podatek podvajal, a bo zato query, ki agregira podatke tabele "autocomplete", šel skoz.
Kind of solid.
Verjetno bom v naslednji verziji naredil manjši rewrite in dodajal shop_id tudi v "autocomplete" tabelo, s tem se bo ta podatek podvajal, a bo zato query, ki agregira podatke tabele "autocomplete", šel skoz.
Kind of solid.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
kunigunda ::
Ja to pa nisi povedu da mas join, tud union bo vedno temp tabelo naredu. Ce ti ni vrstni red inserta pomemben, daj
pri selectu raje ORDER BY NULL, potem ne bo filesorterja uporablju (ki je default pri group by) in bo posledicno hitrejse.
pri selectu raje ORDER BY NULL, potem ne bo filesorterja uporablju (ki je default pri group by) in bo posledicno hitrejse.
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | Mariadb (InnoDB) istočasni insert v tabelo iz različnih procesov (strani: 1 2 )Oddelek: Programiranje | 7354 (3712) | 2g00d4u |
| » | Kako pohitrit build-anje index za autocomplete?Oddelek: Programiranje | 1074 (606) | DamijanD |
| » | [SQL] Pohitritev izpisaOddelek: Programiranje | 2915 (1814) | kuall |
| » | SQL vprasanje (strani: 1 2 )Oddelek: Programiranje | 8369 (5048) | BivšiUser2 |
| » | PostgreSQL pomočOddelek: Programiranje | 2511 (2004) | Mato989 |