[mySQL] Welke High Availability oplossing

[humbug]

Ik begin te merken dat ik door de bomen het bos niet meer kan zien. Dus dan wordt het tijd om eens te kijken wat andere mensen ervan denken. Ik ben simpel gezegd op zoek naar een goede oplossing om te zorgen dat een database 24x7 beschikbaar is.

Even wat achtergrond. Wij hebben een OLTP systeem wat +/- 500 inserts per seconde op een mysql database doet. Hiernaast worden er nog wat rapporten op de database gedraaid maar uiteindelijk hebben we ongeveer 80% inserts/updates tegenover selects. Deletes doen we niet aan Het resultaat is dan ook dat de database met grofweg 500 GB tot 1TB per jaar groeit

Transacties verliezen betekent geld verliezen, dus moeten we dit voorzien van een HA oplossing.

Wat research heeft de volgende 4 methoden voor HA opgeleverd
1. MySQL replicatie (desnoods met MMM) Deze oplossing lijkt met name geschikt voor systemen die veel reads doen en in mindere mate geschikt voor systemen waarbij veel writes plaatsvinden. Met name het asynchrone karakter en de issues met synchronisatie lijken er voor te zorgen dat dit niet echt optimaal gaat werken
2. DRBD. Dit is een speciaal filesysteem wat blocks kopieert naar een tweede server. Dit is synchroom en het tweede filesysteem is dus altijd exact gelijk aan het eerste. Dit lijkt redelijk aan te sluiten bij wat ik ermee wil bereiken, maar er wordt wel gewaarschuwd dat de recovery tijd bij een failure uren kan duren als de database hersteld moet worden.
3. MySQL met data op een gedeelde partitie (mbv red hat cluster) Dit lijkt vrij dicht in de buurt van de DRDB oplossing te zitten met dit verschil dat de data zelf maar 1 keer moet worden opgeslagen.
4. MySQL Cluster. Dit lijkt zeer snel maar lijkt enorme hoeveelheden geheugen te vereisen Nu is het wel zo dat er tegenwoordig een optie is voor "MySQL Cluster Disk Data Tables" maar over de impact op performance is verdacht weinig informatie te vinden.

(Voor de geinteresseerde, de geheugeneisen voor een MySQL cluster Data Node)
Database Size * Replicas * 1.25 (data redundancy + indexes drive overall memory requirement) / number of data nodes.
Dus bij 3 servers moeten alle servers 4,17TB aan intern geheugen hebben. Houden we het geheugen beperkt tot 192GB per server moeten er 65(!) servers komen. Dit lijkt niet de ideale oplossing. Echter als het geheugengebruik dmv DiskDataTables drastisch kan worden ingeperkt is het wel een vorm van "automatische sharding" wat op zich wel interessant kan zijn om de performance te kunnen blijven verbeteren.


In mijn ogen lijken de eerste en vierde optie dus niet echt optimaal voor een database met veel schrijfacties en grote hoeveelheden data. De middelste twee opties lijken redelijk te kunnen uitpakken maar mocht de data corrupt raken ben je de data gelijk kwijt.

Wat zijn jullie ervaringen met deze vier manieren om een database van HA te voorzien?

[ Voor 0% gewijzigd door humbug op 21-08-2011 10:19 . Reden: Spelling ]

[voodooless]

Heb je ook al gekeken naar andere oplossingen? MySQL is niet de enige SQL database. En vergeet ook niet dat er andere no(n)SQL oplossingen zijn die voor dergelijk grote datahoeveelheden geschikt zijn en vaak ingebouwde clustering, sharding, redundancy en scalability hebben. De vraag is altijd: wat doe je met je data? Vast niet alleen inserten

[cariolive23]

Wij zijn overgestapt van MySQL naar PostgreSQL om minimaal 1500 transacties (+/- 20 INSERTs + een tiental SELECT's) per seconde uit te voeren met 100 concurrent users. MySQL kon de concurrency onmogelijk aan (vanaf 10 concurrent users ging het bergafwaards) en kreeg steeds grotere problemen met rapportages op grote hoeveelheden data die niet in RAM pasten. Daarnaast begonnen we steeds meer nieuwe SQL-behoeftes te krijgen, bv. window functions, die niet door MySQL werden ondersteund. SQL Server was geen optie, Oracle te duur en dus kwamen we automatisch op PostgreSQL uit. Op dit moment is de I/O de beperkende factor, de processors en RAM kunnen naar ons idee nog wel betere performance leveren.

Wij gebruiken PostgreSQL versie 9.0 i.c.m. pgPool op een RHEL 5 omgeving.

MySQL is voor ons geen optie meer wanneer het aankomt op betrouwbaarheid, functionaliteit of performance.

[JaQ]

cariolive23 schreef op zondag 21 augustus 2011 @ 17:49:
Wij zijn overgestapt van MySQL naar PostgreSQL om minimaal 1500 transacties (+/- 20 INSERTs + een tiental SELECT's) per seconde uit te voeren met 100 concurrent users.

Hoe pijnlijk was die overstap?

[DukeBox]

Ik gebruik al enige jaren MySql cluster met disk data tables. Zolang het voornamelijk inserts hebt dan kan
een eenvoudige raid10 set met 4 hdd's en een goede controller gemakkelijk 500 inserts per sec.

Het ook sterk afhankelijk van de hoeveelheid indexen die er zijn én die je 'raakt' bij een insert (oftewel, meten is weten).

Voorheen gebruikte ik alleen mirroring, sinds afgelopen jaar ben ik wel overgestapt op een raid 10 set met de indexen op SSD omdat ik tegen problemen met doorloop tijd begon aan te lopen wanneer er ook zware read query's werden uitgevoerd. Dit versnelde het proces aanzienlijk.

Ik doe er ze in bulk (update), maar dan wel ieder uur zo'n 1.7 miljoen in 12 min met diverse uniq indexen. met daar boven op ook nog continue reads.

Overigens doe ik dit als sinds 1999 dus je kan een beetje inschatten hoeveel data dat bij elkaar is
Daarnaast gebruik ik ook nog mirroring naar een offsite locatie als hot standby.

[ Voor 33% gewijzigd door DukeBox op 21-08-2011 18:56 ]

[Varienaja]

We gebruiken bij ons op het werk Postgres met een DRBD device, waarop Postgres z'n tabellen opslaat. Tevens hebben we een pacemaker/heartbeat cluster opgetuigd, zodat een 'standby' server automatisch de taken van een eventueel stervende actieve server overneemt.

Erg vaak hebben we de replicatie van DRBD niet in werking gezien, maar als het uren duurt heb je hoogstwaarschijnlijk de standaard replicatiesnelheid niet overschreven met iets zinnigs. Standaard is die snelheid 250kb/sec, maar wij zetten 'm op 40% van onze 10GBit verbinding tussen de twee Servers. Een replicatie is dan in een oogwenk voorbij (één en ander natuurlijk afhankelijk van het aantal blocks dat opnieuw gesynct moet worden).

Kortom: Heartbeat/Pacemaker met DRBD is een fijne combo. Helemaal sinds je 't grafisch kunt bedienen met de Management console.

[cariolive23]

JaQ schreef op zondag 21 augustus 2011 @ 18:32:
[...]

Hoe pijnlijk was die overstap?

Dat heeft geen pijn gedaan, of je moet doelen op de bugs in de MySQL-queries die we tegenkwamen... Het importeren van een paar TB aan data kost wel wat tijd, maar dat weet je vooraf.

Wij hebben de boel vooral veel eenvoudiger kunnen maken. Vele beperkingen van MySQL waar workarounds voor waren bedacht, konden nu met eenvoudige SQL worden opgelost. De bugs in de GROUP BY-queries waren nog het lastigste, PostgreSQL vergeeft geen enkele fout. Corrupte data was ook nog een uitdaging, er waren problemen met corrupte utf8-karakters im de MySQL-database die moesten worden opgelost, maar ook dat is geen rocket science.

Zorg wel voor een bruikbare configuratie, dus niet de standaard configuratie, en zorg er voor dat je begrijpt hoe MVCC werkt in PostgreSQL waarom het gebruik van transacties ook zorgt voor een betere performance. EXPLAIN is ook een essentieel onderdeel, je moet begrijpen waarom de database bepaalde keuzes maakt. Met die informatie kun je de beste indexes gaan kiezen en zo de performance optimaliseren. Ga wel testen met een grote database, 64GB RAM en een database van een paar GB, dat gaat altijd snel (dankzij cache) en is dus niet realistisch.

Wanneer je de database voor jou laat werken, hoef jij je niet meer druk te maken over de betrouwbaarheid en performance.

Wij hadden het oude systeem in 3 maanden herschreven voor PostgreSQL, getest (functioneel en performance) en gemigreerd. Met MySQL hadden we in 3 maanden onmogelijk een vergelijkbare performance, betrouwbaarheid en/of functionaliteit gekregen.

Binnenkort gaan we een test doen met PostgreSQL versie 9.1 die synchrone replicatie kent, dat is iets wat we nu nog missen.

[JaQ]

cariolive23 schreef op zondag 21 augustus 2011 @ 20:03:
Dat heeft geen pijn gedaan, of je moet doelen op de bugs in de MySQL-queries die we tegenkwamen...

Waar ik op doel is: over gaan naar een ander RDBMS is niet zo simpel als het lijkt. Zelf geef je dat ook al aan. Je hebt het al snel over een flink ontwikkel-traject met navenante kosten. SQL is nou eenmaal niet portable. Je moet een gedeelte van je code herschrijven, of op zijn minste controleren. Daarnaast moet je nieuwe kennis op doen van een nieuw RDBMS, die zijn eigen kuren heeft. Om nog maar te zwijgen over de meer fancy HA/DR features.

Naar mijn onbescheiden mening vallen 99% van de database-prestatie-problemen uit in twee oorzaken:
1. slechte code
2. niet voldoende I/O's kunnen maken voor de gevraagde workload.

(overigens zijn er over het algemeen ook 2 antwoorden op performance vraagstukken: 1. "it depends" 2. waarom zou je dat willen?)

Over het algemeen lost over gaan naar een ander RDBMS niets op, tenzij je overgaan naar een nieuw RDBMS ziet als je code opnieuw bekijken (want daarmee pak je punt 1 aan). Structurele ontwerpfouten los je (meestal) niet op met aan andere engine.

Niet voldoende I/O's is soms ook code gerelateerd, maar heeft ook nogal eens te maken met matige hardware. In mijn ervaring (die zeker niet maatgevend is) heeft vaak te maken met non-enterprise spullen. Je kan nou eenmaal niet verwachten dat je consumer diskje dezelfde prestaties geeft (op lange termijn) als een enterprise-grade SAN (waarmee ik ook niet wil zeggen dat je altijd maar de duurste storage moet kopen). Soms heeft het te maken met configuratiefouten, maar dat kom ik in mijn werk steeds minder tegen.

Wellicht ben ik beperkt in mijn beeld van dit soort problemen, ik houd mijn enkel met Oracle bezig.

---

@ts: Je hebt natuurlijk naar deze link gekeken? (welke versie gebruik je eigenlijk?)

Overigens: 500 inserts van 2 integers is iets anders dan 500 inserts van 25 kolommen waarvan 1 blob.... en zo kan ik nog een aantal vragen bedenken, bijvoorbeeld: wat bedoel je echt met 24*7?, heb je kennis van mysql replicatie, clustering, clustered filesystems, linux&HA in huis?, waar zit je huidige bottleneck in je systeem?, moeten je rapportages op life data?, heb je budget?, etc etc. Kortom: denk je al in techniek, terwijl je functionele vraag nog niet rond is?

[humbug]

voodooless schreef op zondag 21 augustus 2011 @ 11:53:
Heb je ook al gekeken naar andere oplossingen? MySQL is niet de enige SQL database. En vergeet ook niet dat er andere no(n)SQL oplossingen zijn die voor dergelijk grote datahoeveelheden geschikt zijn en vaak ingebouwde clustering, sharding, redundancy en scalability hebben. De vraag is altijd: wat doe je met je data? Vast niet alleen inserten

Natuurlijk hebben we ook naar andere oplossingen gekeken. Sterker nog, we doen hetzelfde al op Oracle en DB2. Punt is dat het prijsniveau van die oplossingen redelijk hoog liggen zodra je serieus aan de gang gaat. In dit project zijn we dus aan het kijken of het ook wat goedkoper kan

Wat we met de data doen is in feite simpel. In feite inderdaad "niets". Dit systeem koppelt het systeem van klanten A,B en C aan de systemen van klanten D,E en F en staat 20 uur per dag verkeer tussen die klanten uit te wisselen. De inserts zijn eigenlijk niet veel meer dan een grote logfile Natuurlijk er worden ook rapportages op de data gedraaid maar dat gebeurd in het window van 4 uur dat de systemen bij de klanten stil liggen. Weggooien van oude data is natuurlijk prettig maar dat kan sowieso niet binnen de periode van 5 jaar omdat anders klant A zijn record lastig kan matchen met die van partij D en er ook en soms na langere tijd weer een bericht terug moet (Wat dus 1 read is en weer wat writes ) Daar doen we dus eigenlijk inderdaad "niets" mee. Zie het dus voornamelijk als een leuke log file.

[humbug]

Varienaja schreef op zondag 21 augustus 2011 @ 19:12:
We gebruiken bij ons op het werk Postgres met een DRBD device, waarop Postgres z'n tabellen opslaat. Tevens hebben we een pacemaker/heartbeat cluster opgetuigd, zodat een 'standby' server automatisch de taken van een eventueel stervende actieve server overneemt.

Erg vaak hebben we de replicatie van DRBD niet in werking gezien, maar als het uren duurt heb je hoogstwaarschijnlijk de standaard replicatiesnelheid niet overschreven met iets zinnigs. Standaard is die snelheid 250kb/sec, maar wij zetten 'm op 40% van onze 10GBit verbinding tussen de twee Servers. Een replicatie is dan in een oogwenk voorbij (één en ander natuurlijk afhankelijk van het aantal blocks dat opnieuw gesynct moet worden).

Kortom: Heartbeat/Pacemaker met DRBD is een fijne combo. Helemaal sinds je 't grafisch kunt bedienen met de Management console.

Uren duren is niet de replicatie, maar het herstellen van de database bestanden na een crash. Het kan zijn dat postgreSQL dat wat beter aanpakt dan mySQL met innoDB. In theorie kun je bij een crash met mySQL binnen een minuut weer online zijn. In praktijk kan dat wel eens wat langer duren.

[humbug]

@ts: Je hebt natuurlijk naar deze link gekeken? (welke versie gebruik je eigenlijk?)

Overigens: 500 inserts van 2 integers is iets anders dan 500 inserts van 25 kolommen waarvan 1 blob.... en zo kan ik nog een aantal vragen bedenken, bijvoorbeeld: wat bedoel je echt met 24*7?, heb je kennis van mysql replicatie, clustering, clustered filesystems, linux&HA in huis?, waar zit je huidige bottleneck in je systeem?, moeten je rapportages op life data?, heb je budget?, etc etc. Kortom: denk je al in techniek, terwijl je functionele vraag nog niet rond is?

Ja die link ken ik. En je hebt gelijk iets meer details kunnen handig zijn

Op dit moment draaien we mySQL 5.5 aangezien dat laatste stabile release is.
We doen over het algemeen inserts in meerdere tabellen met 20 velden waarbij de lengte van een insert +/- 1KB is. Geen blobs, wel varchars maar ook die zijn <100 bytes.
24x7 betekent voor ons 20 uur data van een groep klanten naar andere systemen doorsturen en in een 4 uur window resultaten terugsturen en backups doen. De kennis van MySQL replicatie en clustering is redelijk beperkt (vandaar ook dit topic) maar Linux, clustered filesysteem en generic database HA is er wel voldoende (zoals gezegd, doen we dit kunstje al op andere databases)
De weinige rapportages die op deze data gedraaid moeten worden worden gescheduled om in de 4 "rustige" uren gedraaid te worden.
Tenslotte is de vraag wat de bottleneck wordt moeilijk te beantwoorden. De kunst is de kosten van het platform te verlagen en tegelijkertijd nog de minimale requirements te blijven halen. We hebben hier redelijk zware hardware staan waarop de troughput fluitend gehaald wordt, maar de bedoeling is dus met een flink lager budget te gaan werken bij nieuwe productiesystemen.

De functionele vraag is gelukkig wel rond als ook het budget.

[kwaakvaak_v2]

Percona heeft volgens mij een aantal oplossingen voor mysql, waaronder een hot backup oplossing. Die gebruik ik nu zelf ook voor een forum met een db van 1.2Gb en de gebruikers hebben er nauwelijks last van als ik overdag een snapshot maak. Tuurlijk is het wat traag omdat de snapshots fysiek op dezelfde machine worden gemaakt. En percona advieseert eigenlijk om een master -> slaves oplossing te hebben en de snapshots op één van die slaves te maken.

[RobIII]

humbug schreef op maandag 22 augustus 2011 @ 08:15:
[...]

humbug schreef op maandag 22 augustus 2011 @ 08:23:
[...]

humbug schreef op maandag 22 augustus 2011 @ 08:49:
[...]

Gebruik de edit knop ( ) als je iets toe te voegen hebt; je topic herhaaldelijk omhoogschoppen is niet nodig en die melding staat er niet voor niets:

[voodooless]

Momenteel gebruiken we zelf ook, net als anderen hierboven al doen PostgreSQL. In piek doen we ruim 600 transacties met zo'n 10 tot 20 inserts per stuk (met daarachter nog wat triggers om automatisch partitioning te doen). Dat gaat op zich best rap.

[JaQ]

humbug schreef op maandag 22 augustus 2011 @ 08:49:
Ja die link ken ik. En je hebt gelijk iets meer details kunnen handig zijn

humbug schreef op maandag 22 augustus 2011 @ 08:49:
We doen over het algemeen inserts in meerdere tabellen met 20 velden waarbij de lengte van een insert +/- 1KB is. Geen blobs, wel varchars maar ook die zijn <100 bytes.

Dus 500 * 1kb + indexen + logging is ongeveer zo'n 1.5 MB aan schrijfacties per seconde. Stel dat je erg ruim neemt dan kom je op 2 MB, dat is nou niet echt schokkend te noemen. Zelfs over netwerk moet je dat kunnen halen (NFS / iSCSI) qua I/O's.

humbug schreef op maandag 22 augustus 2011 @ 08:49:
24x7 betekent voor ons 20 uur data van een groep klanten naar andere systemen doorsturen en in een 4 uur window resultaten terugsturen en backups doen.

Die 20 uur, is dat data ophalen en inladen? Of staat de data klaar en heb je 20 uur om die data te laden? En heb je het dan echt over 36.000.000 inserts? (20*3600*500), oftewel zo'n 34 TB aan data? Gaat het echt om deze hoeveelheid?

Het lijkt er op dat je enkel een backup kan maken door een replica bij te houden. Wil je ook nog iets dat op PITR lijkt? (kan mysql dat?) Een backup naar tape is in ieder geval out of the question met 34TB per dag.

Moet je echt 24*7 online zijn voor de verwerking, of mag je best een uurtje plat (als je die 36 mio inserts maar haalt?).

humbug schreef op maandag 22 augustus 2011 @ 08:49:
De weinige rapportages die op deze data gedraaid moeten worden worden gescheduled om in de 4 "rustige" uren gedraaid te worden.

Tsja, 1 aggregatie op 34 TB en je 4 uur is een flink eind onderweg . Het is in ieder geval out of the question dat je zoveel data in 4 uur naar een ander storage device kan kopieren. Again: dat wordt een replica.

humbug schreef op maandag 22 augustus 2011 @ 08:49:
Tenslotte is de vraag wat de bottleneck wordt moeilijk te beantwoorden.

Als ik het zo lees is de bottleneck snel gevonden: kosten van storage. Bij de kosten van zoveel groei valt al het andere in het niets (zelfs als je Oracle gebruikt ).

humbug schreef op maandag 22 augustus 2011 @ 08:49:
De functionele vraag is gelukkig wel rond als ook het budget.

Aj, je budget staat al vast, maar je hebt nog geen idee of dat gaat passen in je technische oplossing (want die zoek je nog, toch?) Dat kan vervelend worden mocht blijken dat je een 10Gb lijn tussen 2 locaties nodig hebt en deze niet in je budget past?

Zelf heb ik nog nooit op iets gewerkt dat 34TB per dag groeit. Momenteel werk ik aan een content systeem dat momenteel 28TB bevat en een TB per maand groeit (en dat vind ik al groot ). Daar heb ik veel vraagstukken opgelost door middel van Oracle technologie: Oracle EE 11.2, ASM external redundancy, extended RAC over 2 locaties (aantal km uit elkaar), DataGuard (physical stand-by) naar een ander extended RAC, advanced compression & deduplicatie op secure files (=blobs) en partitioning. En dan nog wat trucjes met stats, sql-profiles, etc. Dit alles om te zorgen dat:
- zero-unplanned-downtime (ieder component kan uitvallen zonder verstoring)
- een volledige backup terug gezet kan worden binnen 2 uur (bijvoorbeeld door logische corruptie door een DBA ) met 0 dataverlies
- de performance op orde is voor zo'n 1500 requests per seconde (zowel zoektochten naar specifieke content als het ophalen van de content uit de database): de huidige load
- de performance op orde blijft tot zo'n 4500 requests per seconde (hoop ik )

Er missen nog een paar dingen (moet nog gebouwd worden):
- De stand-by omgeving moet naar een ander type hardware + storage (om firmware bugs te voorkomen)
- Derde stand-by naar derde locatie, voor dataprotectie.

Past zo'n soort investering in je budget, of zit je toch aan een iets bescheidener oplossing te denken?

[ACM]

JaQ schreef op maandag 22 augustus 2011 @ 21:15:
Dus 500 * 1kb + indexen + logging is ongeveer zo'n 1.5 MB aan schrijfacties per seconde. Stel dat je erg ruim neemt dan kom je op 2 MB, dat is nou niet echt schokkend te noemen. Zelfs over netwerk moet je dat kunnen halen (NFS / iSCSI) qua I/O's.

Je kan niet zomaar losse io-operaties naar MB/sec vertalen en het dan weer IOps noemen 500 IOps is veel voor een gewone harddisk. Uiteraard kan vaak bij een raid-opstelling de controller een groot deel van de kleine IOps vertalen naar een kleiner aantal grotere IOps. En ook het groeperen van meerdere inserts in transacties kan uitmaken.
Een groei van 0.5-1TB/jaar zorgt er ook gelijk voor dat SSD nog niet echt heel bruikbaar is (althans, dat hangt natuurlijk van het budget af ), hooguit als disks voor het actief beschreven gedeelte. Door de boel te partitioneren (ook wel sharding genoemd als het over verdelen naar losse systemen gaat) kan je er wellicht voor zorgen dat je altijd maar een beperkt deel van de database echt beschrijft en je op die manier wel (kosten)effectief SSD's kan inzetten.

Wil je ook nog iets dat op PITR lijkt? (kan mysql dat?)

Met de binlogfunctionaliteit - die ook al gebruikt wordt voor de asynchrone backups - schijn je dat te kunnen bereiken.

[igmar]

humbug schreef op zondag 21 augustus 2011 @ 10:12:
Wat research heeft de volgende 4 methoden voor HA opgeleverd
1. MySQL replicatie (desnoods met MMM) Deze oplossing lijkt met name geschikt voor systemen die veel reads doen en in mindere mate geschikt voor systemen waarbij veel writes plaatsvinden. Met name het asynchrone karakter en de issues met synchronisatie lijken er voor te zorgen dat dit niet echt optimaal gaat werken

Vergeet dit. De MySQL synchronisatie is ronduit ruk, vooral in een master-master opstelling. In ons geval was de lag in sommige gevallen 30 seconden voordat de andere server de data pas zag. Niet bruikbaar, op een failover opstellig na.

2. DRBD. Dit is een speciaal filesysteem wat blocks kopieert naar een tweede server. Dit is synchroom en het tweede filesysteem is dus altijd exact gelijk aan het eerste. Dit lijkt redelijk aan te sluiten bij wat ik ermee wil bereiken, maar er wordt wel gewaarschuwd dat de recovery tijd bij een failure uren kan duren als de database hersteld moet worden.

Euh, nee. Het is een replicating block device. Het heeft totaal geen idee van het concept 'filesysteem'. Er is dus ook normaliter maar 1 writable server, de rest zijn read-only kopieen. Multiple masters kan wel, maar je FS moet daar wel geschikt voor zijn. ext[234], xfs, etc vallen daar allemaal buiten.

Wat zijn jullie ervaringen met deze vier manieren om een database van HA te voorzien?

Overstappen op een andere database MySQL is niet geschikt voor dit soort zaken, en goede cluster filesystemen zijn er (nog) niet echt. Ik zou overstappen op postgresql.