Dotaz: efektivne bash mysql krmitko

[Loso]

Ahojte,

snazim sa efektivne nakrmit mysql tabulku plnymi cestami k suborom pomocou bash scriptu (cesty obsahuju aj medzery).

Funkcne riesenie sa vsak tvari hrozne pomaly (aj ked vynecham mysql riadok, tak pocitadlo ide ako sopel po smirgli); Prosim o napady na moznu optimalizaciu nasledovnej kostry bash kodu.

PS: So suborom s cestou $i sa v scripte aj pracuje vzhladom na kontent, takze vygenerovat staticky list ciest pomocou find a uploadnut ho do mysql nie je mozne.

LISTDIR=/adresar/plny/suborov;
COUNTER=0;
IFS='
'

find $LISTDIR -type f | while read i; do
#    echo "insert into....$i" | mysql ....;
    COUNTER=`expr $COUNTER + 1`;
    echo "$COUNTER";
    echo "$i";
done

---

---

Odpovědi

[Kit]

find "$LISTDIR" -type f -exec echo "INSERT INTO ... {} ...;" \; | mysql ...

[Loso]

tato konstrukcia mi napadla tiez, i ked vo forme -exec ./script.sh {} \; Problem je bohuzial v tom, ked nazov suboru obsahuje medzeru, apostrof, nedajboze retazec "drop oblubene data;" vtedy treba znova nestastne IFS ... a este treba sem-tam pustit trebaz mp3info ak sa jedna o media file.

[Kit]

To je takový problém dát kolem {} uvozovky a ve skriptu ta data ošetřit?

[Loso]

tie retazce musia byt tak ci tak osetrene, to rozoberat nejdem (az take jednoduche to pri niektorych kreativnych menach nie je), ale staci si dat do prazdneho scriptu iba pocitadlo a ls -l na $@ a rychlost celeho find ide do teplych krajin... to je TO co riesim (forkuje sa zakazdym cely bash skript). V kazdom pripade dakujem za snahu...

[Kit]

V takovém případě bych se asi vykašlal na Bash a napsal bych si celý skript v PHP nebo Pythonu, kteří už disponují veškerým potřebným arzenálem včetně odolného klienta pro MySQL.

[Šangala]

Příloha:

• kuk.bash (1155 bytů)

Dal jsem pryč výpis (bo to je jistě brzda), dopřebastlil(je to jen na odzkoušení) a výsledky z přílohy mám takovéto:

TEST >time ./kuk.bash 1
COUNT(*)
0
COUNT(*)
38711

real	5m15.801s
---------------------

TEST >time ./kuk.bash 10
COUNT(*)
0
COUNT(*)
38711

real	0m35.810s
---------------------

TEST >time ./kuk.bash 100
COUNT(*)
0
COUNT(*)
38711

real	0m34.757s
---------------------

TEST >time ./kuk.bash 1000
COUNT(*)
0
COUNT(*)
38711

real	6m7.704s
---------------------

Třeba to pomůže.

Trochu to zabije COUNTER=`expr $COUNTER + 1`; z 5:15 to dá 6:10
opravou na COUNTER=$(($COUNTER + 1)) z 5:15 to dá 5:15

Jen projetí ve smyčce se ti to nepotáhne jako sopel, zesoplí se to tím co tam ještě děláš :)

[Loso]

mudry google poradil, ze cela ne-efektivita vspociva hlavne v tom, ze read "prehladava" relativne dlhy retazec byte-po-byte, kym nenatrafi v relativne dlhom stringu (full cesta k suboru) na nastavene IFS... => v mojom pripade teda cez bash pouzitelna cesta nevedie. Dakujem.

[Šangala]

Popis dobrý, přívlastkům nevěřím, nemyslím si, že toto je zásadní problém, pokud se od toho nechce mega-souborů/minutu a délka cest není 2042znaků ;). Ale netvrdím, že to máš dělat v bashi…

[Kit]

Zkusil jsem to udělat v Pythonu, zvládá to nové řádky i apostrofy v názvech

import os
import MySQLdb

db=MySQLdb.connect('localhost', '', '', 'test')
db.query("CREATE TABLE soubor(nazev text);")
curs=db.cursor()
for line in os.popen('find "/adresar/plny/suborov" -print0').read().split('\0'):
    curs.execute("INSERT INTO soubor VALUES (%s)", (line,))

db.commit()

[Loso]

hezky, ja som po zisteni, v com to viazne, vsadil na PHP, namiesto find sa ako velmi efektivna osvedcila konstrukcia (mozno posluzi na inspiraciu):

foreach (new RecursiveIteratorIterator(new RecursiveDirectoryIterator('/adresar/plny/suborov',RecursiveDirectoryIterator::FOLLOW_SYMLINKS)) as $fullfilepath)
  {
      $pathparts = pathinfo($fullfilepath);
....

z "15 bash minut" som sa dostal na "36 php sekund" (teda ak nehladam v pripade mp3 suborov ID3tags, nechcekujem duplicitne subory, ktore uz v DB su, apod.), takze spokojnost, uz len odladit zvysok aktivit. No nic, cas ist spat. Dobru noc a vdaka.

[Kit]

Pro 60K souborů jsem se s Pythonem dostal pod 10 sekund.

Proti duplicitám pomůže UNIQUE.

[Šangala]

I v Bash-i výše to vypadá při 60k, tak na 54sec (na Core2Duo) jedním & a jedním wait lze ještě nějakou sekundičku nahnat.

[Kit]

Také jsem to zkoušel na Core2Duo / 2 GHz. Úzkým hrdlem bude zřejmě filesystém, který mám zaplněný jen na 20 %.

[Šangala]

¡Ha! já na 2.4GHz. Tuším i jak to zrychlit i Bash-i, ale chtěl jsem si jen ověřit, že když se to trochu upraví(, a část té úpravy lze použít v jakémkoliv jazyku jako možnost zrychlení), že by to mělo být rychlejší než ten sopel na šmirglu :)
Sem ten find pustil předem, aby cache eliminovala vliv FS (bo s tím člověk moc nepohne).

[Kit]

V tom Bahi by mohl být problém s řetězením proměnné "sql". Proto má pro 1000 položek výrazně delší časy. Pro takové akce se mi osvědčilo pole, na které pak udělám případný map a následně reduce (join). Tím se mi vyřeší i ten nadbytečný oddělovač.

[Šangala]

To je zásadní místo (ke zrychlení) různými způsoby
Jen multi-insert má vždy zlom (aspoň co mám na MySQL zkušenost), kdy to jde do kytek, takže bez profilování to nelze takto jednoznačně označit za důvod i když je to velmi pravděpodobné.

[Kit]

Koukám, že ten klient MySQL se v Basi spouští tolikrát, kolik je SQL dotazů. V tom Pythonu ho spouštím jen 1× a dotazy do něho cpu jedním datovým proudem.

Možná by se to dalo upravit nějak takto:

find ... | while ...
   echo "insert ..."
done | mysql ...

[Šangala]

Kouzlo (na MySql) je v multi insertu a je putna jak se použije, pokud se vkládá velké množství řádků tak samostatné inserty jsou výrazně pomalejší než jeden multi insert, ale má to určité zlomové body, dle nastavení serveru a výkonu, protože multi insert je jeden dotaz a když je moc velký, tak to analýza zabije a i prostor pro mezi-oddych SQL serveru přichází za moc dlouho.

Tady jsem to upravil na na první pohled divně, na druhý lehce pochopitelné:

echo -n "('$xy')" >> /dev/shm/kuk
...
mysql -u test -ptest test < /dev/shm/kuk

Tedy eliminace spojováni v bash-i přes soubor, ale v paměti:

TEST >time ./kuk.bash 10
COUNT(*)
0
COUNT(*)
38712

real	0m33.569s
----------------------

TEST >time ./kuk.bash 100
COUNT(*)
0
COUNT(*)
38712

real	0m7.211s
----------------------

TEST >time ./kuk.bash 1000
COUNT(*)
0
COUNT(*)
38712

real	0m5.419s
----------------------

TEST >time ./kuk.bash 10000
COUNT(*)
0
COUNT(*)
38712

real	0m6.777s

A jsem s 60k v bash-i pod 9 sec, kazí mi to jen ty 0.4GHz

[Kit]

Tak tohle už je super výsledek. Jenom škoda, že se tímto způsobem nedají zpracovat parametrizované dotazy. Jak si třeba poradí s apostrofem v názvu souboru?

[Šangala]

Nijak :(. Daný úkol by se asi měl řešit jinak, nějak tak jak nabízíš níže. Nebo ve vhodnějším jazyku a i se správným konektorem na db, kde se to vyřeší nebo se použijí jiné fce. Vložil jsem se do debaty jen proto, že v bash-i někdy dělám idiotiny ať už omylem nebo záměrně, a nějakými drobnostmi se pak výkon líší i ve stonásobcích, tak jsem chtěl zkusit jak to tedy je. Prostě čím výš (jazykem) si, tím víc musíš vědět nebo vyzkoušet pokud chceš výkon či šetrnost :).

[Kit]

Řešení v Pythonu jsem popsal výše a ošetřil jsem i podivné znaky v souborovém systému včetně kódování Češtiny. Mám to ve funkčním stavu, jen nemám ošetřeny výjimky. To si už každý může udělat podle svých potřeb.

[Kit]

Multiinsert se sice nevyskytuje např. v SQLite, ale tam se dá dosáhnout téhož, pokud se hromada insertů uzavře do transakce. Pokud by v MySQL fungovaly transakce, tak by se to také dalo použít.

[Šangala]

Transakce v MySQL fungují, ale to na výkon nemá vliv (multi vs. samostatné inserty), nikdy jsem to nijak nezkoumal přesně proč, ale MySQL multi-insert výrazně zvyšuje rychlost, řekl bych že se to prostě jednou analyzuje připraví a naráz zapíše jako blok.
Několikrát jsem dělal aplikace co zapisovali statísíce, až miliony záznamů (v transakcích po blocích) a pokud ta data byla prostá relativně jednoduchá, tak tímto způsobem, to bylo suveréně nejrychlejší úložiště (i s InnoDB) na import ve srovnání s PostgreSql a M$SQL( a skopičiny na M$SQL abych z toho aspoň něco výtáhl a vlezl se predikovatelně do časového okna nikomu nepřeji) a hezky to i napomáhá (multi-insert) přirozenému paralelnímu zpracování na jednom stroji (což tedy mohou být i před-generované samostatné insert-y). Hloupé na tom je že se no musí trochu poladit kolik v dávce na daných datech a nastavení serveru.

[Loso]

krmitko pre "glorifikovanu file library" mi bezi doma na xtreameri s Atom D525 + 4GB RAM, kde som z cisteho zufalstva, podmieneneho hlavne pomalym FSB, radsej posadil cele mysql do ram disku a adresar so subormi je na ext4 WD 500GB Black 16MB cache, uloha optimalizacie vspocivala, hlavne, pokusit sa vyhnut while read (ono spominane nestastne citanie zoznamu po riadku kvoli medzeram v nazvoch) dalsie tasks, typu seds na krmenie premennych pre jednotlive stlpce su pri tom uvodnom spomaleni zanedbatelne, osobne mi je srdecne jedno ci nieco bezi 35 alebo 38 minut, hlavne, ked vidim ze find mi je schopny list stringov ciest dodat za 0.5 sekundy, o5 vdaka za ostatne postrehy. Aktualne to bezi nieco cez 10 minut, aj to len kvoli brzde, ked ide3tag objekt prechadza cely subor, ci tam nahodou tag nejaky tag predsa len nie je......(riesenim bude prazdne tags povyrabat, alebo pamatat si dalsi stlpec, ze proste onen file tag nema...)

[Kit]

Pokud to potřebuješ zpracovat přes id3tag, tak proč to neuděláš rovnou?

find -print0 | xargs -0 id3tag

[Loso]

lebo sa snazim to pustat nad celym fs, kde nie su len media files, ale aby mi fulltext search v terene vedel hladat podla mien adresarov, pripon, mien suborov a aj v id3 tagoch. Dalsim krokom na vylamanie zubov budu pdfs...

[Kit]

Myslel jsem to jinak. Výstup z find poslat do DB přefiltrovaný přes id3tag. Aby se insertovaly všechny informace, nejen název souboru.

[Loso]

Vidim, ze uz je vyreseno, nicmene presto jedna poznamka... bash neni optimalni uplne na vsechno. To je jako pocitat MP3 v bashi. Nebo renderovat 3D scenu arduinem. Na tohle je dobre si udelat treba krmitko v Ccku, zkompilovat a klidne i ridit bashem, odkoukavat stav, zobrazovat, ale delat to radek po radku v bashi neni moc rozumne.

[Kit]

Obávám se, že Cčko se na takovou utilitu nehodí. Je to prostě overkill a ladil bych to ještě teď. Skriptovací jazyky byly vyvinuty právě pro takové účely. V Pythonu jsem to měl slepeno za pár minut, úzkým hrdlem je souborový systém. Pro automatizovaný běh bych jen přidal ošetření výjimek.

Napadlo mě i rychlé řešení pro Bash, ale v tuto chvíli je to už asi zbytečné řešit.