MySQL - jedna velká tabulka, nebo mnoho malých

[prezek]

Zdravím,
potřebuju sbírat data a pak zpracovávat. Dejme tomu, že mám 100 robotů, kteří se kteří občas navštíví nějakou z 20 místností. Každý pohyb a každá operace, kterou provedou v místnostech se v místnosti detekuje a zaznamenává. Dat bude přibližně okolo milionu řádků na každou místnost. Někde více, někde méně. Při zpracování se může hledět na to, co dělal konkrétní robot v určitém časovém intervalu, nebo na to, co se dělo v konkrétní místnosti v určitém časovém intervalu.
Je lepší mít jednu velkou tabulku (cca 20 mil. řádků) pro všechny místnosti a uživatele? Nebo data rozdělit do 120ti tabulek a každá záznam mít v příslušné tabulce robota a v tabulce místnosti.

Předem dík za rady, Petr

[Reklama]

[asd]

Predpokladam, ze pak budes delat nejakou analytiku, tak se podivej jak se designuje analyticke prostredi, datove sklady, popripade zapremyslej nad tabulkou kteran je column oriented (sloupcove orientovana? Je to spravny preklad?)

[Hidden]

20.000.000 záznamů není pro databázi žádná hrůza. Místo sekání tabulek na menší bych se zamyslel nad datovými typy a indexy.
Jelikož s návrhem DB evidentně nemáš zkušenosti (no offense), doporučuju nevymýšlet si vlastní problémy a prostě to naprototypovat.

1. Insertovat všechno do jedné tabulky a přímo nad ní dělat ty analytické dotazy.
2. Vyhovuje? Problem solved. Moc pomalé? Zamysli se nad datovými typy a indexy.
3. Pořád pomalé? Uprav tabulku pro rychlé inserty (tzn. žádné indexy, integritní omezení atd.) a pro analýzu si ty data dávkově přelij do jiné tabulky, s více vyhovující strukturou. Na podobném principu fungují datové sklady a běžně se takhle zpracovávají TB dat.

Dělení těch samých dat mezi více tabulek je nesmysl. Pokud nad tabulkou nemáš indexy, triggery atd., je rychlost insertování téměř konstantní ať je tabulka velká jak chce.

Ze zvědavosti se klidně podívej na ty column-based batabáze, jak radí asd ale reálně si myslím, že řešíš nesmysly. Ty tři body, které jsem uvedl, by ti měly stačit.

[to_je_jedno]

Souhlas s hidden. Jinak zalezi kde spatrujes problem jestli v zapisu nebo cteni. Pripadne by mohlo byt reseni MongoDB apod. 20M radku neni zadna velka veda.

[prezek]

S návrhem DB opravdu nemám zkušenost, nesetkal jsem se s tím ani ve škole (mám elektro).

V databázi bude několik tabulek, pro běžnou práci budou data zpracovávána už při příjmu. Občas ale bude možná potřeba analyzovat data podle požadavků a toho se trochu bojím.

Zápis musí být rychlý, pokud jsem si hrál s indexy nad kombinacemi sloupců (to je asi blbost, že?), tak se zápis zpomalil z několika tisíc řádků/s na pár řádků/s a to není přípustné.  Čtení podle požadavků (co se dělo v pátek od 10:00 do 15:00 v místnosti ZÁCHOD, co dělal robot R2D2 ve středu apod.) by mělo být ideálně v řádu do 500 ms, teď to trvá několik sekund (při 10e6 řádcích).

Díky za rady, zůstanu tedy u velké tabulky a budu optimalizovat dotazy a indexy.

[Reklama]

[Kit]

Zápis musí být rychlý, pokud jsem si hrál s indexy nad kombinacemi sloupců (to je asi blbost, že?), tak se zápis zpomalil z několika tisíc řádků/s na pár řádků/s a to není přípustné.  Čtení podle požadavků (co se dělo v pátek od 10:00 do 15:00 v místnosti ZÁCHOD, co dělal robot R2D2 ve středu apod.) by mělo být ideálně v řádu do 500 ms, teď to trvá několik sekund (při 10e6 řádcích).

Zkus počet indexů zredukovat na jeden nebo dva - mohou mít složené klíče.

[j]

Kazdej index zrychluje cteni a zpomaluje zapis. Vic tabulek ma smysl vyhradne pro cteni. Zapremejslej o tom, kolik zapisu a kolik cteni budes generovat. Pokud to ma byt vicemene nejaky log, ze ktereho mozna sem tam bude nekdo chtit ziskat nejaky data, tak bych to ladoval do jedny tabulky bez indexu, a z ni to prelejval cas od casu (denne ...) do tabulky se stejnou strukturou ale uz treba s nejakym indexem.

20M neni moc, ale ...

[Filip Jirsák]

Podle vašeho popisu soudím, že ty záznamy mají stejnou strukturu - pak je samozřejmě lepší mít je v jedné tabulce. Pokud je objem dat velký, řeší se to prostředky databáze pomocí tzv. partitions - podle vámi definovaných pravidel si databáze sama rozdělí velkou tabulku na několik menších. Vy s ní dále můžete pracovat jako s jednou velkou tabulkou, ale pokud se dotazujete na data, která leží pouze v jedné nebo několika partition (a databáze to z vašeho dotazu pozná), bude pracovat jenom s příslušnými částmi.

[Hidden]

Nechceš nám ten projekt trochu přiblížit? Takhle se těžko radí. Ti roboti plní databázi tisíci záznamy/sec v reálném čase? To budeš mít asi brzo více, než 20M záznamů, ne? Nebo běží jen nějakou část dne a následně do DB ty záznamy nasypou dávkově? A ty dotazy následně děláš jen nad tou jednou tabulkou? Žádné vazby? Co je vlastně výstupem? Pokud zmíněný robot R2D2 insertuje třeba 2000 záznamů/sec a chci se podívat, co dělal v pátek v místnosti ZÁCHOD od 10:00 do 15:00, vrátí mi dotaz 36000000 záznamů. K čemu je to dobré?

[prezek]

Pokud to ma byt vicemene nejaky log, ze ktereho mozna sem tam bude nekdo chtit ziskat nejaky data...

jj, přesně tak, tato tabulka slouží jako log, ze kterého možná párkrát za rok někdo bude něco potřebovat. Ze začátku častěji (já - pro kontrolu, jestli všechno běží tak, jak má). Data pro běžné zobrazení budou zpracována při příjmu a ukládána do jiných tabulek (aktuální stav místnosti, počet robotů v místnosti, počet návštěv místnosti...)

Ti roboti plní databázi tisíci záznamy/sec v reálném čase?

Teď plním tabulku náhodnými daty, potom budou "místnosti" předávat data 1-2x za sekundu, pokud v ní je "robot". Těchto "místností" je 20, takže je možné, že během některé vteřiny bude cca 50 požadavků o zápis, pokud se nahromadí data, tak více. Při špatně zvoleném indexu to nestíhá 50 zápisů za sekundu a při špatně položeném dotazu bez indexů trvá dlouho vygenerování dat a v té době je ještě více zpomalen zápis.

Co je vlastně výstupem?

Výstupem může být třeba animace, kudy se robot pohyboval, jakou rychlostí, kdy se rozsvítilo, kdy se spláchlo a kdy zhaslo.

[Ovrscout]

Ohledně rychlosti vkládání.
Zkus poladit jak často se data commitují a způsob insertu(např parametrizované query/inserty).
Pokud je to po každém řádku (auto)commit tak to může být velmi pomalé.Vkládání více řádek "najednou" by to mnělo zrychlit, zvláště pokud tě brzdí indexy, nebudou se tak často aktualizovat a pojede to lépe.
Zkus commitovat po co největších blocích je přijatelné. Např pokud ti v komunikaci přijde 100 "řádek " tak alespoň těch 100řádek najednou, lepší jsou tisíce/desetitisíce řádek ale záleží jak máš udělanou aplikaci, nebo jestli třeba můžeš udělat to že budeš commitovat každých x řádek nebo třeba po nějakém čase atd. Záleží také na požadavcích na spolehlivost, odolnost na výpadky, zda jsou přijaté/zapsané řádky potvrzovány v komunikaci zpět do zařízení atp.

Jinak viz rady ostatních.

[lenochware]

Ukládal bych pohyby robota do binárního souboru, do databáze pouze nějaké globálnější informace, podle toho, co budeš chtít zjišťovat, třeba jestli robot navštívil nějakou místnost...

[kojot4]

No tak já jsem pracoval s tabulkami cca. 20 mil. záznamů v MySQL a můžu potvrdit, že to nefunguje zrovna moc optimálně - indexy v tomhle případě sice pomůžou, ale i tak kolikrát nějaký i celkem jednoduchý dotaz trvá několik vteřin.

Důležitý asi nebude jenom typ databáze, ale i disk, určitě u velkého množství záznamů bych zvolil SSD, ta databáze ve výsledku stejně bude mít při tomhle objemu pár desítek gigabajtů, což se tam vejde.

Další věc jsou různé statistiky. Já jsem to řešil tak, že jsem ty statistiky průběžně generoval do jiné tabulky, a pak jsem promazával tu hlavní, kam se zapisovalo. Můžeš například vydumpovat data do určitého ID, ty zakomprimovat, zazálohovat a smazat s tabulky (samozřejmě co si potom vyjedeš statistiky co potřebuješ).

Mírné navýšení výkonu MySQL také provede příkaz Optimize table, ale zamyká tabulky, což je trošku problém.

V každém případe určitě použij SSD disky!

[Tuxik]

Na SSD se vykašli, při této velikosti se to celý vejde do paměti každé moderní chytré elektroniky (včetně vyhřívanýho prkýnka na záchodě). Ale vážně... co potřebuješ za řešení? Nejlepší ať to stojí, co to stojí, nebo nějaký domácí zadarmo, ideálně aby DB běžela na starým PC po dědovi, nebo jak? A musí to být MySQL?

[Filip Jirsák nepřihlášený]

No tak já jsem pracoval s tabulkami cca. 20 mil. záznamů v MySQL a můžu potvrdit, že to nefunguje zrovna moc optimálně - indexy v tomhle případě sice pomůžou, ale i tak kolikrát nějaký i celkem jednoduchý dotaz trvá několik vteřin.

To svědčí spíš o neznalosti toho, jak databáze fungují. Index není žádná magická hůlka, že byste přidal index a zrychlily se tím všechny dotazy. Pomůže to jedině tehdy, pokud pro daný dotaz máte nad příslušnou tabulkou index, který výrazně omezí počet získávaných záznamů. Když budete mít třeba dotaz na průnik tří podmínek, pro každou podmínku zvlášť budete mít index, ale žádná z těch podmínek neomezí množinu záznamů výrazně, budou vám ty indexy k ničemu. (Samozřejmě pokud nepoužíváte specializované indexy, třeba fulltextové, které jsou udělané tak, aby bylo možné dělat průnik indexů.)