Jakou používat strukturu tabulek databáze – MySQL – Fórum – Programujte.com

Zdravím, vytvářím databázi pro uchování logu z 20 zařízení, v budoucnosti se plánuje až 50 zařízení. Logy se budou uchovávat po 10 vteřinách, počet sloupců typu float okolo 50. Z důvodu velkého množství dat, se budou po měsíci promazávat a starší přes půl roku mazat úplně. Avšak v první fázi je počítáno s tím, že data budou uchovávána podrobně 3 měsíce, ale jen pro cca 5 zařízení(celkem 777 600 řádků pro jedno zařízení)

Řeším, jestli všechny logy nechat v jedné tabulce, nebo udělat zvlášť tabulku pro každé zařízení. Myslím si že by to mohlo být efektivnější. Budu k tomu mít ještě jednu tabulku kde bude definice zařízení, takže by tam mohla být vazba na konktrétní tabulky a poté tabulka uživatelů, kteří budou moci data prohlížet.

Určitě bude potřeba vytáhnout poslední záznam od každého zařízení do nějakého souhrného přehledu. Nyní mam vše v jedné tabulce a i přes indexy času a čísla zařízení je práce celkem pomalá. Aby taky ne, když obsahuje 2 miliony řádků...

Existuje ještě nějaká jiná možnost jak to řešit?

Pro monitorovani site, teploty pouzivame ve firme zabbix. Netusim, jak to vnitrne resi.
Umi udelat grafy po 10 min a zoomovat to po 30 min, 1h ... Kazdy zoom bych resil extra tabulkou, kde bych vypocital prumer. Hlavne se to pak rychleji nacita.

U tvych zarizeni nevim. 800.000 radku je docela dost a jestli to ma byt casem 50x.
50 sloupcu... Neslo by treba mit tabulku aktualni stav, kam ukladas aktualni data do X sloupcu, zvysujes v jednom sloupci n+1? Az bude n==X, tak zaznam zkopirujes do tabulky2 a v tabulce1 smazes. A ted, otazka je, potrebujes se k tem floatum dostat hned? Nebo by stacilo to ulozit do stringu jako csv. Uvazuji o tom, pridat tam treba dalsi sloupce 50+50+50 (vic radku do jednoho)... aby se zmensil pocet radku. A jestli ty data nevyuzivas nejak aktivne, jestli by bylo ci nebylo lepsi to zapsat jako string. Jako, urcite je asi lepsi to mit ve float sloupcich. Ale nevim, kolik sloupcu sql zkousnou. Nejvic jsem mel v tabulce asi 30. je fakt, ze v programu na vedecke publikace je jich 130 a neni s tim problem.

Cili, asi bych to resil tak, jak to mas vymyslene. A az pokud by nastal nejaky problem, tak to zkusil zmenit.
Jestli s temi daty aktivne pracujes, tak bych si resil bokem jeste tabulku aktualni stav, kde bych udrzoval treba 30 poslednich hodnot. Pocital inserty a pri pocet>Y spustil promazani starsich hodnot (podle casu), treba.

To je dobrý nápad s tou další tabulkou. Tam by prakticky měli být tak půl hodinové záznamy. tj 6x30x50 = 9000 záznamů což by mohlo být OK. Současně data budu ukládat i do tabulek pro každé zařízení zvlášť, kde už bude řádků 6*60*24*30 = 259 200 za měsíc plus 129 600 řádků za tři měsíce. Tam už ale budou oprace s daty méně časté a prakticky vždy bude dotaz jen na jednu tabulku zařízení, nikdy ne více současně. Nyní pokud dělám select dle času a zařízení v jedné tabulce s těmi miliony řádků, tak jsou to zatím rozumné doby v jednotkách vteřin. To na vytažení historického záznamu postačuje.

U mne má každé zařízení svou databázi.  V databázi jsou tabulky pro hodnoty, název měření a poznámku, takže si uživatel může nějaký úsek označit podle svého uvážení. Dále pak ještě tabulka uživatelů pro řízení přístupu a tabulka s nastavením jako barva grafů pro jednotlivé uživatele. Takže ke každému zařízení smí jen registrovaní uživatelé a každý uživatel může mít svoje "barvičky" a jiná nastavení. Celé jsem to zkoušel na dvou systémech s hodnotami po 1 sekundě.

Dobré je na tabulce použít indexování. Při malé hustotě ukládání dat je zpomalení zanedbatelné, zato zrychlení při velkém výběru dat bude citelným přínosem.

Mazání starších dat nedoporučuji. Spíš archivaci tabulek. Třeba jednou za měsíc tabulku administrátor  přejmenuje a vytvoří novou prázdnou. Takto se alespoň administrátor může dostat ke starším datům. Další možnost je aby systém sám vytvářel na každý měsíc novou tabulku (jde na to použít event). Lze si pak pomoci tím, že bude tabulka tabulek obsahující názvy datových tabulek a jejich časový úsek od - do. Postup výběru by pak nejdřív vybral názvy tabulek podle časového úseku (v případě přesahu přes více měsíců by to bylo více tabulek) a z nich se vyberou požadovaná data - spojit data z několika stejných tabulek lze pomocí UNION.

hu

Ještě pozn.:

1. všechna zařízení v jedné tabulce je nešikovné. Dříve nebo později příjde chvíle, kdy bude třeba přidat zařízení - např. koupilo se nové nebo některé zařízení odebrat - např. odstěhováno do jiné laboratoře.

2. předpoklad, že všechna zařízení budou pracovat současně, může být mylný. Některá mohou zůstat vypnutá. Pak budou vznikat sloupce plné NULL a budou jen zabírat místo

3. pokud je více zařízení, mohou pracovat asynchronně, je tedy problém aby ze všech zařízení přišla hodnota se stejným časem.

4. Pro nejnovější historii by možná bylo šikovnější view. Při členění na měsíce však bude nejnovější historie zbytečný luxus, práce se samotnou tabulkou bude dostatečně rychlá.

Body 1 - 3 jsou zkušenosti z našich laboratoří.

hu

#1 Pavelv
Především je chybou mít v tabulce 50 sloupců jenom kvůli počtu zařízení. 5 sloupců by mělo stačit.

50 sloupců je proto, že je 47 měřených parametrů. Tři sloupce navíc jsou čas, identifikátor primárního klíče, kód zařízení.

#7 Pavelv
Aha, takže to máš zřejmě správně.

Byl bych rád, jen pořád nevím co s těma záznamy. Třídění každého zařízení do zvláštní databáze mi už přijde zase moc hardcore. Je z hlediska práce s daty nějaký rozdíl když budu mít 50 databází a v každé 3 tabulky, než jednu databázi se 150 tabulky? Z hlediska správy a chyb to bude asi vhodnější rozdělit. Je nějaké obecné či morální doporučení, kolik by měla databáze obsahovat tabulek, tabulka řádků a řádek sloupců?

Zatím to totiž mám rozmyšlený pro jednu databázi. V ní 50 tabulek pro každé zařízení zvlášť s cca 50 sloupci a 500 tisíci řádky. Další tabulka s 10 tisíci řádky a 50 sloupci pro kopii dat všech zařízení za posledních půl hodiny. Záznamy starší než 1 měsíc budu ukládat do společné tabulky pro všechna zařízení v měsíčním intervalu. Po pěti letech by zde mohlo být 3000 řádků a 20 sloupců. Seznam uživatelů bude umístěn v další tabulce, předpoklad 70 řádků. Vztah M:N mezi uživateli a zařízeními bude řešit tabulka přístupů. Zde bude asi 1000 řádků a minimálně sloupce (uživatel_id, zařízení_id). Úroveň oprávnění jednotlivých uživatelů bude řešeno přes cca 7 skupin(řádků) v další tabulce. K tomu počítám ještě 3 tabulky pro logování chybových stavů. Další možná přibudou při vývoji. Odhadem tak v databázi bude 60 tabulek. Je to takto řešitelné a správné?

Samostatné databáze jsem zvolil:

1. jsem omylný pitomec, mít hromadu tabulek od všeho možného v jedné db není přehledné. Stačí chvilka nepozornosti a pozměním co jsem pozměnit neměl. V samostatné databází jsou v ohrožení jen tabulky jednoho zařízení. Obnova ze zálohy u menšího celku je jednodušší a má menší riziko chyb. Navíc je rychlejší.

2. Databáze mají stejnou strukturu. Odpadá mi nutnost pro každý systém vymýšlet unikátní jména tabulek, upravovat pro ně SQL query - v každém dotazu bych musel nějak dosadit správné jméno tabulky a nesplést se. Při programování a tvorbě konfigurace systému je to hromada práce navíc. U samostatných db stačí změnit jednu query - tu která nastaví používanou databázi. Opět to omezuje riziko chyb.

hu

#9 Pavelv
Jednu databázi se třemi tabulkami nebo 50 databází také po třech tabulkách. Záleží jen na tom, zda budeš potřebovat pracovat současně s daty různých zařízení. Zcela samostatné zařízení si zaslouží vlastní databázi.

Naopak 150 tabulek pro 50 zařízení v jedné databázi považuji za nepřijatelné řešení.

Databáze s třemi tabulkami klidně bude, ale bude mít 50x více řádků. A to bude problém, tudíž asi znám odpověď na moji otázku :-)

#1 Pavelv

To se mi nezdá že by to mělo být tak pomalé, zkus kouknout jaký plán se pro ten tvůj dotaz použije.(A pozor na to že plán se může lišit pokud zadáš SQL dotaz přímo v nějaké konzoli, nebo když použiješ parametrický dotaz v kódu)

Pokud budeš mít index (IDzarizeni, Čas), v tomto pořadí - ne opačně, a budeš mít správně dotaz,
tak musí být odpověď na aktuální data dannéhop zařízení téměř okamžitá.(pokud má db dost paměti na to udržet index/y v paměti).
Uznávám že je to trochu atyp index, ale pro tenhle typ dotazů je to skoro nutnost. Pokud tedy nechceš mít extra tabulku jen na aktuální data jak už tu někdo psal.

Také zkontroluj že máš dobře statistiku nad indexama, MySql moc neznám ale většinou je třeba čas od času aktualizovat.

Pokud bys chtěl data z jednotlivých zařízení od sebe oddělit do "jednotlivých částí" kvůli optimalizaci, tak zkus juknout na partitioning. Je to trochu složitější a jsou i nějaká omezení ale mělo by to dělat co chceš, a přitom pro dotazy by se to stále mělo tvářit jako jedna tabulka (ale osobně jsem to nepoužil).
Nejdříve bych se ale zkusil obejít bez partitioning-u, těch dat co píšeš zas tak moc není aby to nezvládl běžný desktop stroj.

Jediné čeho bych se trochu dlouhodobě bál bude čištění db po smazaných řádkách, a údržbě indexů.Může trvat, pokud je hodně dat. Případně řešit partitioningem. Ale to je třeba ozkoušet jak se MySql chová, jak říkám moc ji neznám, ale těch dat není tak moc.

#1 Pavelv
Nestačil by pro tyto účely RRDtool?