Přidání, úprava a ověření velkého množství dat v databázi

Jan Endel napsal(a):

Počkej, chápu správně že kopíruješ každou minutu databázi v podstatě?

Nestačila by master-slave replika v tomhle případě?

Nene, prostě ze svého serveru vstupuju na jíný a beru si od něj data z celé evropy (v průměru je to 3000 dat) a je to ve formátu JSON.

Jedná se o dopravní hlášení, takže se data rychle updatují, mažou a přidávají se nové, takže do „feedu“ vstupuju každou minutu, abych měl co minutu aktuální data.

V aktuální tabulce, do které data sypu se drží vždy jen aktuální počet (z 99% data nepřesáhnou limit 4000), starší data, které už nejsou ve feedu potom přesouvám do starších tabulek pro pozdější prácí s nimi, aby nebyl problém v rychlosti s prací s těmito daty + přidávání nových atd..

EDIT: na ten druhý server nemám absolutně žádný přístup kromě toho feedu
EDIT2: data jsou rozděleny na bloky, celá evropa má asi 40 bloků (40 feedů, které si musím potahat – použil jsem Guzzle takže celou evropu vytahám a zpracuji za 2 sec) teď už zbejvá jen to nasypat do tý databáze… už jsem zkusil několik způsobů ale všechno je moc pomalý

teď jsem se dostal k bodu, kdy při malém počtu dat mám vše nasypané v DB za 2sec, nojo, ale když je špička, tak to přesahuje i minutu (což ruší další cron), prostě potřebuju ten kód udělat stabilní aby trval třeba MAX 30 sec (prostě co nejmíň). Nechci aby se stávalo to co teď, že jednou to je 12 sekund, podruhé 20, potřetí 1,20min atd

Editoval Томас (13. 9. 2016 10:48)

CZechBoY napsal(a):

No rozhodně rychlý by bylo nedělat 5000 dotazů a třeba jich udělat jen 5 (oracle má limit na 1000 položek v IN). Nebo použít INSERT ON DUPLICATE KEY UPDATE a udělat prakticky 1 dotaz.

Však jsem psal, že on duplicate key update používám a data rozděluji po 300 rows, jenže v 1 jsonu je asi 20 sloupcu, dotaz je neskutečně obrovskej a server to nedává. Jo někdy to trvá 12 sec a super, jenže podruhý to trvá 40 sec nebo i přes minutu. To je příšerný, nevím proč se to děje

CZechBoY napsal(a):

@Томас a bez těch ifů to nejde?

A jak?

dustanu třeba toto:

uuid = 1111, user = karel, lat = 47.04156416, lng = 16.145156
uuid = 2222, user = franta, lat = 46.04156416, lng = 15.145156
uuid = 3333, user = pepa, lat = 45.04156416, lng = 14.145156
uuid = 4444, user = igor, lat = 44.04156416, lng = 13.145156

// + 3000 podobnejch

takže

INSERT INTO table (uuid, user, lat, lng) VALUES
(1111, karel, 47.04156416, 16.145156),
(2222, franta, 46.04156416, 15.145156),
(3333, pepa, 45.04156416, 14.145156),
(4444, igor, 44.04156416, 13.145156)
// jenze v databazi uz mam karla a frantu, a zmenilo se u nich lat i lng
ON DUPLICATE KEY UPDATE
lat = (IF(1111, 47.04156416, IF(2222, 46.04156416, lat))),
lng = (IF(1111, 16.145156, IF(2222, 15.145156, lng)));

• samozřejme update nemuzu dat jen frantu a karla, protoze dopredu nevim, ktere v db uz jsou a ktere ne, takze realne 4 v insertu a 4 v update (akorat realne to neni 4 a 4 ale 3000 a 3000) rozdeleny na 300×300 – což je slušnej mazec, nemyslím, že tohle je správný řešení – víc mi to uškodí než pomůže (velký počet dat – musím posílat do DB mín a tím se takhle ohromnej dotaz musí provádět několikrát) a tak jako tak je dotaz stále obří i když pošlu třeba jen 100 dat

Já tam možnost bez IFů nevidím, ale možná se pletu. (Důležitý je, že tam nemám 4 sloupce, ale 20±).

Editoval Томас (13. 9. 2016 11:40)

jiri.pudil napsal(a):

Použij VALUES()

Jaká je idální počet rows (polí) při použítí:

$this->context->query("INSERT INTO table", [[1],[2],[3]]);

Ví někdo? :-) Pouštím tam 100 polí a kdo by to byl řekl… nějak to trvá.

Mám aplikaci na stejném principu a rozhodně to celé zabal do transakce, u mě to znamenalo zrychlení tak o 200%.

Mysteria napsal(a):

Mám aplikaci na stejném principu a rozhodně to celé zabal do transakce, u mě to znamenalo zrychlení tak o 200%.

Výborně, zkusil jsem se vyvarovat tomu šílenému dotazu a peru natvrdo všechno do databáze pomocí transakcí, pro insert je to zatím v pořádku – 1000 záznamů (46–60s) nyní 16 sec MAX a to tam je ještě započítaný čas něčeho jiného, takže bezva. + tak velký počet insertů tam nikdy nebude, protože se dělají průběžně, spíš ty updaty se dějí, tak uvidímě

Tak teď už jen rozjet update pod transakcema a doufám, že to bude svištět jak po másle. :-D

Tak, běží to překvapivě dobře, kód:

$inserts = [];
$this->database->beginTransaction();
foreach($rows as $row)
{
     $ex = $this->database->table("..")->where(["id"=>$row->id])->fetch();
     if(!$ex){
          $inserts[] = $row;
     }else{
          $this->database->table("..")->where(["id"=>$row->id])->update([]);
     }
}

$this->commit();

// tady je guzzle na tahani adres podle souradnic - proto neinsertuju hned, museji se jeste asynchrone natahat ty adresy

$this->database->beginTransaction();
$this->database->query("INSERT INTO ..", $inserts);
$this->database->commit();

Běži to opravdu hezky, na tento script posílám data po dvou tisících, někdy je to 800, někdy 3600 atd. A celej script se provede do 6ti-15sekund, vytahani dat, posladni a ulozeni do db + update

Nojo, ale jeden problem tam přecijenom je, 1 z XX pokusů script už nejede 6sec ale třeba 1,2 min… úplně náhodně – vypadá to nějak takhle:

6s
5s
12s
13s
9s
8s
6s
12s
56 s
1,2 MIN
6s
5s
…

někdy jsou třeba 3 přes minutu za sebou a pak je to zas 6sec… moc to nechápu, někdo zasvěcený do databázi trochu víc možná bude vědět? Počet dat by měl chodit pořád stejný – s rozdílem treba 100 takže by to nemělo být tím, kolik tam cpu dat – pořád chodí v tuto hodinu okolo 2300–2500 dat, není to že jendou prijdou 3 a podruhé 3000 – proto jsem dost zmaten, možná to bude jinými crony, které běží (zkusím je vypnout), ale pochybuju – to jsou rychlovky a časy se neshodují

Result se mi nevrací žádný proste "", takže to je vždy timeout error (zkusím nastavit větší timeout, třeba to nějakej error vyplivne?)

Mysteria napsal(a):

A tyhle časy jsou jenom čistě databáze? Protože taky můžeš mít problém se získáváním samotných dat.
Já mám stejný princip, z externích stránek nejdřív vytáhnu JSON a XML data a ty pak zpracovávám do databáze.
Chce to vědět časy jednotlivých úkonů, aby se ti totiž nestalo třeba to, že ti to přes minutu netrvá proto, že je pomalej zdroj dat a né databáze. :)

U mě se totiž čas od času stane, že je nějakej zdroj dat nedostupnej a způsobovalo to tyhle prodlevy, protože třeba 2 minuty se čekal prostě jenom na stažení dat a s databází to nemělo nic společnýho.

Hele, mrknu na to a udělám důkladnej benchmark (nepřišlo mi to důležitý, protože přes guzzle jsem měl data vždy do 2–4sec MAX), tak jsem časy spojil do jednoho a už mi to píše ten kompletní (vzhledem k tomu, že jsem nevěděl, že tam je ta chybka…)

Tak moment, ono chvíli bude trvat než to udělám a taky než ta chyba vyskočí znova, pak to sem hodím

suwer napsal(a):

Томас napsal(a):

Tak, běží to překvapivě dobře, kód:

$inserts = [];
$this->database->beginTransaction();
foreach($rows as $row)
{
     $ex = $this->database->table("..")->where(["id"=>$row->id])->fetch();
     if(!$ex){
          $inserts[] = $row;
     }else{
          $this->database->table("..")->where(["id"=>$row->id])->update([]);
     }
}

$this->commit();

// tady je guzzle na tahani adres podle souradnic - proto neinsertuju hned, museji se jeste asynchrone natahat ty adresy

$this->database->beginTransaction();
$this->database->query("INSERT INTO ..", $inserts);
$this->database->commit();

Znova, proc nechces pouzit INSERT … ON DUPLICATE KEYS s VALUES() ktere jsou presne pro tyhle pripady? Asi bych chapal to asynchronni dotazeni adresy, nicmene ty ji zjistujes jenom pri insertu a pri updatu uz ne?

Tohle jsem měl předtím, problém byl s taháním adresy, protože tahat naráz 2500 adres, mno nevím… :-) ano, adresy jenom u přidání, takhle vytáhnu 30 adres a hotovo, 2500 to je moc, musel jsem to předtím vypnout, strašný zdržení

všechno už loguju, teď čekám, až to zase spadne…

stažení dat z externích zdrojů: 3s
ten cyklus s updatama + check:  8s
tahání adres:					1s
insert: 	                    2s

timeout jsem navysil a dal ho taky logovat, tak snad mi tam hodi nejakej error kdyztak, do ted byl result prazdnej a ted uz musim jen cekat nez to spadne…

Tak mám výsledky, už to spadlo a nejsem teda moc potěšenej, je to jak říká @Mysteria zdržuje to tahání adresy, jednou to je za 0–1 sec a pak jednou yahoo praskne v bedně a trvá to dlouho jak blázen… takže tady je nejspíš zakopanej pes, vypnu to a uvidíme…

Když potřebuješ mít script co nejrychlejší, tak můžeš poslat sql v jednom dotazu, ne?

$sql = '';
$params = [];
for($i = 0; $i < 5000; $i++)
{
	$sql .= 'REPLACE INTO tbl (id, value) VALUES(?, ?);';
	$params[] = $i;
	$params[] = $val;
}
$this->database->query($sql, $params);

Editoval Antik (14. 9. 2016 19:09)

@Томас normálně to zabal do jedný transakce, ale koukám že ti tam chybí rollback při chybě…

$this->db->beginTransaction();

try {
	//cosi
} catch(PDOException $e) {
	$this->db->rollBack();

	throw $e; // pošlu to dál
}

$this->db->commit();