Dotaz: MariaDB/MySQL partitioning a diskové oddíly

[BigWrigley]

Zdravím,

sháním nějaký tip nebo radu ohledně partitioningu v MariaDB vs. diskové oddíly na na serveru. Mám DB s jednoduchými shodnými tabulkami, do které denně přibude cca 20 milionu řádků. Za den je to podle použitého engine cca 43GiB (InnoDB), 17 GiB (InnoDB compressed), 18.5GiB (Aria) dat. Během práce DB vzniká cca 30 GiB dat binárních logů za hodinu. Masivně se updatuje a insertuje.

Na serveru HP DL580 G9 mam 4x 400GB SSD a 4x 900GB HDD na P440 Smart Array, server ma 256GB RAM. Hledám optimální způsob využití diskového prostoru. Zatím mam range partitioning po dnech spolu se subpartitions podle key. To samo o sobě funguje dobře. Stačí každý den vytvořit novou partition a dropnout staré. Momentálně jsou SSD disky v RAID 1+0, což dělá cca 716 GiB místa pro data a logy databáze. HDD mám také RAID1+0.

Potřeboval bych nějak využít volný prostor na SSD a HDD tak, aby ideálně partitions se starými daty byly na HDD a nově vznikajicí na SSD. Při definici partitions se dá říct, kde data fyzicky budou, ale změnit to po naplnění partition jde jen za cenu přesunu dat a to trvá i hodinu. MySQL umí v posledních verzích general tablespaces, ale přesun dat mezi nimi je jak se zdá také za cenu rebuildu partition. Což dost nasazení partitioningu v mém případě dost omezuje.

Zatím nejnadějnější se mi jeví nasadit lvmcache. Část SSD kapacity použil jako tu cache pro HDD RAID a zbytek pro binární logy DB. Ale je to tak trochu skoda tech SSD. Ve finále by DB měly být dvě a měla by mezi nimi běžet Master-Slave replikace.

Díky předem za nápady.

A.

Odpovědi

[On]

MySQL Fabric?

[dustin]

Nezmiňuješ druhou stranu - využití těch dat: čtení, vyhledávání, archivace, zálohování, k čemu ta data vlastně slouží. To mi přijde dost podstatná informace pro správné rozhodnutí.

30GB binlogů za hodinu (předpokládám row-based) obsadí do 24 hodin celé to SSD pole. Kratší bych je asi nedával, až padne slave, po vypršení binlogů by se pak asi těžko při takové zátěži bez výpadku masteru obnovoval od nuly.

[BigWrigley]

Data slouží pro účely zákaznické podpory a občasný troubleshooting problémů síťového charakteru. Rád bych zde popsal víc, ale bohužel nemohu. Ale není to mission critical systém, bez kterého firma nemůže jet. Výpadek dat zde není kritická záležitost, prostě jen nebudou k dispozici určité informace o provozu v síti. Čtení těch dat (ext. klienty) není kritická zálezitost. Resp. je to tak, že klienti vyhledávají záznamy z 90% podle indexovaných položek. V podstatě ta nejdůležitější data jsou tak tři týdny až měsíc stará, pak jejich význam rapidně klesá, ale je dobré je mít. Ještě k té struktuře dat. Nejvíce se pracuje s jednou tabulkou o cca 1 milionu řádků, tu aplikace čte a aktualizuje daty v reálném čase a průběžně se z ní odstraňují neaktuální data, která padají do toho partitioningu.

Master-Slave replikaci jsem uvažoval použít tak, že by se zapisovala aplikace do jednoho ze serveru a zák. podpora by používala repliku. Resp. původně jsem chtěl mít synchronní replikaci (Galera Cluster), ale ten to nestíhá - synchronní operace mezi nody (jsou na různých lokalitách) zpomalují operace tak, že data už nestíhají být v reálném čase zapisována. DB dělá cca 25-30k řádkových operací/s.

Jde o upgrade existujícího řešení na starém hw. Data se nearchivují ani nezálohují.

[dustin]

Dobré, že už s tím máte delší zkušenost. Ten stávající HW to stále utáhne? Kolik tedy těch dat nyní je, pokud je neodmazáváte? Vychází mi to cca 600 mil. řádků/600GB každý měsíc, to docela roste, za rok třeba 7TB.

Píšeš, že se zpětně dělají v té DB změny. Jak hluboko do minulosti? Může na produkčním serveru zůstávat jen část dat a zbytek se kumulovat pro R/O čtení pro potřeby zpětného dohledávání na nějakém sekundáru s velkým polem z rotačních disků? Třeba pokud by ty odlité tabulky na masteru byly jen read/only, šlo by použit row-based replikaci a ignorovat při ní delety. Tím by na masteru zůstávala jen část dat, pravidelně tam data odmazávat, zatímco replikací by se plnila obluda na slavu pořád dál. Navíc by slave mohl mít více indexů třeba pro pokrytí těch 10% případů hledání, které jinak mohou trvat pěkně dlouho...

To by pak byly klasické append logy do DB, to již bude mnohokrát vyřešené. Podobné řešení používáme, jen bez replikace (přesouváme párkrát za den skriptem, ale by nestíhalo stálý tok 30k řádků/s) a dat je jen cca 600 mil. všech řádků slave a 50 mil. latest řádků master, tedy mnohem menší než váš případ.

[BigWrigley]

Stávajici HW je tak tak na hraně a nevhodným dotazem se to dokáže položit. Problémem je hlavně i/o. Jakmile to vyleze na nějaká 3-4%, přestane to být ukládání realtime a je konec. V DB máme cca 1.7 miliardy řádků a ma to asi 700 GiB. Což je asi 3.5 měsíce. Je to udělané tak, že se partitioning dělá skriptem - vytváří se každý den nová tabulka a ta z předchozího dne se zakomprimuje (MyISAM). To je docela zajímavá feature MyISAM tabulek, protože pak je tabulka už jen readonly a nemůže se při pádu DB poškodit (tedy ve smyslu nekorektního uzavření) a přitom z ní DB může stále číst. Celé to běží na 2x X5560 2.8GHz a 32 GB RAM, pole je RAID 1+0 HDD.

Zkusím se zamyslet na tím, že bych měl rychlého mastera s menšími SSD disky a slave s HDD (diky za tip). Pro současné prohledávání obojího by se dal použít třeba engine FEDERATED.

[dustin]

Kdyby se použila replikace, byly by na slavu defacto všechny řádky (jen se zpožděním replikace, což by při rozumném HW nebylo mnoho) a pro hledání by nebylo potřeba federated. Výhodou je, že se to při zpoždění replikace nijak nerozsype, pokud je na masteru dost prostoru (a nakonfigurovaného času) pro binlogy.

IMO by rozumný HW řadič s baterkou zálohovanou 512MB keší (dnes za pár desítek dolarů např. zde + nová baterka) měl takový replikační tok na slavu utáhnout na SAS 10-15k HDD, třeba 6 disků v 1+0 plus jeden hotspare. A kdyby měl slave dost RAM, můžeš dát mysql dost paměti pro keše indexů a i to hledání by mohlo svištět. PC3-10600R do o generaci starších HP/DELL je velice levná, 16x8GB za cca 220 USD - tipuju si, že v tom 2 x X5560 budete mít tu samou, je to stejná generace. Píši to na workstation s 2x X5570 a 72GB téhle PC3-10600R, vyšla celá bez disků na cca 11k Kč (samozřejmě bez NBD záruky, ale zase se do rozpočtu vešla další náhradní ve skladu).