MySQL replication bevindingen

Trouwe .plan freaks zullen gemerkt hebben dat er al enkele weken geen .plannetjes zijn geweest omtrent ons server park. Vaak betekent geen nieuws goed nieuws, zo ook in dit geval. Sinds de laatste werkzaamheden aan het serverpark zijn er namelijk nauwelijks meer problemen geweest, waardoor er weinig nieuws was te melden. Toch zijn er wat ontwikkelingen gaande waarover ik jullie wil informeren en die met name interessant zijn voor de tweakers die zelf gebruik maken van MySQL als database server.

MySQL replication

MySQL beschikt sinds versie 3.23.15 over ondersteuning voor one-way replication. Replication maakt het mogelijk om databases realtime te synchroniseren over een cluster van database servers. In het geval van one-way replication moeten alle updates naar één master server gestuurd worden, waarna de wijzigingen door de master worden doorgegeven aan de slave servers. MySQL ondersteunt meerdere slaves per master in een ster-topologie, maar er zijn ook mensen die er in zijn geslaagd om slave servers te daisy-chainen (slave is master van een andere slave) en circulaire replication (elke server in de ring is zowel slave als master) aan de praat te krijgen. Dit laatste werkt alleen als er geen inserts worden gedaan op tabellen met auto-increment kolommen. MySQL kent namelijk geen logica die het verstrekken van auto-increments op meerdere masters regelt.

Replication ken enerzijds gebruikt worden om de redundancy te verbeteren en anderzijds om de load te verdelen over meerdere servers. In het geval van one-way replication wordt de bottle-neck bepaald door de snelheid waarmee de master de updates en inserts kan verwerken en de slaves de master kunnen bijhouden. Bij het bekende scenario van een website zal het aantal wijzigingen in de database relatief laag zijn, zodat de bottleneck vooral wordt bepaald door de snelheid waarmee de selects verwerkt kunnen worden door de database servers. Een derde handige toepassing van replication is het vereenvoudigen van backup procedures. MySQL zet bij het draaien van een backup een lock op de tabel die op dat moment wordt gedumpt. Dit resulteert nog wel eens in langdurige table locks en daardoor uitvloeiende ongemakken bij gebruikers. Door een backup te maken vanaf een replication slave ondervinden gebruikers van de master server geen hinder van de backup. Ook kan replication goed gebruikt worden voor het realtime bijhouden van een off-site backup. De verbinding tussen de master server in de co-locatie ruimte en de lokale backup server kan met SSH beveiligd worden.

De database is veruit de zwakste schakel in het hosting raderwerk van Tweakers.net gebleken. Als bezoekers heb je dit met enige regelmaat aan den lijve kunnen ondervinden. In het afgelopen half jaar hebben we een heel scala aan problemen voorbij zien komen, variërend van een simpel gebrek aan capaciteit tot thread-limieten in de Linux kernel. Sinds onze verhuizing naar TrueServer en de ingebruikname van de tweede database server (Apollo) is de betrouwbaarheid van onze database backend aanmerkelijk verbeterd. Dit komt deels omdat de load per database server veel lager is, maar ook vooral omdat de servers veel optimaler geconfigureerd zijn.

Tweakers.net is bijna volledig database-driven en dat betekent dat we voor onze content vrijwel geheel afhankelijk zijn van de database server. Is de server stuk, dan kunnen wij niet meer dan een gecachede error pagina laten zien. Tweakers.net is bovendien erg dynamisch, waardoor de databases vrijwel continu aan veranderingen overhevig zijn. Het regelmatig kopiëren van een backup naar een tweede standby database server zou weinig effect sorteren, omdat de backup server altijd out-of-date zou zijn. Alleen een goed werkende replication functionaliteit van de database server kan ons hierbij helpen. Tot voor kort ontbrak het ons aan een tweede database server en bovendien leek de replication functionaliteit bij eerdere tests behoorlijk onbruikbaar te zijn.

Sinds de verhuizing naar TrueServer, nu bijna een maand geleden, zijn de databases van Tweakers.net en GoT verdeeld over twee servers. Met alleen een verdeling van de databases en de load over meerdere servers zou de database server echter in stand blijven als single point of failure. Daarom was ik erg benieuwd naar de mogelijkheden van MySQL replication. Sinds drie weken wordt hiervan gebruik gemaakt, zowel voor load balancing als het verhogen van de redundancy.

Op dit moment worden alleen de databases op Artemis gerepliceerd door Apollo, met uitzondering van de Fokforum database die niet werkt met replication vanwege een vermoedelijk probleem met temporary tables in de search engine. Load balancing wordt bereikt door de SQL queries voor de pagina's op Tweakers.net random in een verhouding van 70/30 te verdelen over Artemis en Apollo. Redundancy kan simpel gerealiseerd worden door een paar extra regeltjes code in het connect scripts dat er voor zorgt dat een connectie wordt gemaakt met de slave zodra de master down is. Hierbij moet je wel rekening houden dat de MySQL connect funcite van PHP nogal onhandig omgaat met dode servers die wel zouden moeten bestaan. In dat geval blijft het script oneindig hangen. Dit probleem is op te lossen door een check met fsocksopen op de poort van de MySQL server voor het aanroepen van de connect functie.

Aangezien MySQL uitsluitend ondersteuning heeft voor one-way replication blijft overlast onvermijdelijk als de master down is. Het posten van reacties zal dan bijvoorbeeld niet mogelijk zijn. Wel behoudt Tweakers.net in ieder geval zijn content en dat is al veel beter dan een situatie waarbij alleen een error pagina getoond kan worden, ook al staat die error pagina vol met mooie BMW's . Gelukkig heeft de noodzaak van replication zich nog niet bewezen sinds het in gebruik werd genomen. De database servers draaien al drie weken zonder problemen en de MySQL server op Artemis heeft inmiddels een uptime van 15 dagen bereikt, iets wat we al heel lang niet meer hebben mogen meemaken. In die 15 dagen heeft Artemis meer dan 125 miljoen queries uitgevoerd. En hoewel Artemis het sinds de komst van Apollo minder druk heeft, verwerkt hij tijdens de topuren nog steeds een respectabele 150 queries per seconde.

De betrouwbaarheid van MySQL replication bleek bij eerdere tests op Artemis en Athena nogal problematisch te zijn. Dit was vooral te wijten aan het feit dat de snapshots werden gemaakt met mysqldump op een draaiende database server. Het tarren van de data directories terwijl de server offline is, blijkt veel beter te werken. Inmiddels draait replication al 15 dagen onafgebroken zonder veel problemen. Al met al kunnen we hier dik tevreden over zijn . De goede werking van replication betekent tevens dat MySQL een belangrijk streepje voor heeft op Postgres en SAP DB. Deze twee open source DBMS'en moeten het nog zonder replication functionaliteit stellen.

Tot slot nog een twee tips voor mensen die zelf aan de slag willen gaan met MySQL replication. Voordat je replication pratend kunt maken, zul je een snapshot van de databases op de master moeten kopiëren naar de slave. De veruit beste methode om dit te doen is het bouwen van een tarbal op de master (ik ga voor het gemak uit van een Unix-achtig systeem). Dit zal wat overlast veroorzaken omdat de master voor enkele minuten platgegooid moet worden, maar het werkt veel beter dan een online mysqldump. In dat geval zou je alle tabellen gedurende de dump moeten locken om verzekerd te zijn van een backup die consistent is in de tijd. Het kopiëren van tarretjes tussen servers is geen probleem zolang hetzelfde OS gebruikt worden.

Ten tweede is het uitermate belangrijk dat je ten alle tijde voorkomt dat er buiten de master om wijzigingen worden gemaakt in de gerepliceerde databases op de slaves. Dit is vooral belangrijk op tabellen met auto-increment kolommen, omdat inserts in deze tabellen tot gevolg kunnen hebben dat de replication stopt met een 'duplicate entry'-error. Vaak is de enige oplossing dan het maken van een nieuw snapshot op de master. Snapshotjes aanleggen doe je bij voorkeur in de nacht als de overlast het minst is. Ik heb al meerdere keren mijn wekker moeten zetten voor deze handeling en daarom zie ik m'n replication niet graag kapot gaan . De eenvoudigste manier om te voorkomen dat er op de slave per ongeluk wijzigingen worden gemaakt is het connecten met een aparte MySQL user die alleen leesrechten heeft op de replication tabellen.

Reacties (39)

Nog wta info: volgens een posting van Sasha Pachev (MySQL developer) op de MySQL mailinglist staat 3.23.40 op het punt van uitkomen. In deze versie wordt een belangrijke replication bug gefixt:

"Hello, everyone:

Good news - I was able to repeat and fix the bug that several people have reported but were not able to repeat. No wonder - even after I noticed the
problem in the source, it took some creative work to be able to write a test case for it. ( We try to write a test case for every bug we find, even if we find it just by visual inspection).

The symptom of the bug was that you would get a message in the slave error log about the lost connection due to a low setting of max_allowed_packet when max_allowed_packet setting was not the problem at all.

The fix is to upgrade to 3.23.40, which should be out within 24 hours ( this is why I have not bothered to make a patch, if somebody really cannot wait,
let me know)."

Hmm... mooi systeem met one-way replication. Als nu je master dood gaat, kun je dan niet stiekum van die slave een master maken en later de boel weer fixen?

Dat zou in principe met wat kleine wijzigingen in de config (my.cnf) gedaan kunnen worden, maar het moet wel handmatig. Je moet dan wel een nieuw snapshot maken omdat de binlog op de nieuwe master niet op dezelfde tijd zal beginnen als de master offline ging. In de praktijk heb je er niet zoveel aan, tenzij er sprake is van lange downtime. Bij de meeste problemen kan de master binnen enkele minuten weer online.

Failsafe replication (waarbij de slave automatisch de taak van de master kan overnemen bij onbereikbaarheid van de master) staat op de todo voor MySQL 4.0.x.

De goede werking van replication betekent tevens dat MySQL een belangrijk streepje voor heeft op Postgres en SAP DB. Deze twee open source DBMS'en moeten het nog zonder replication functionaliteit stellen.

http://www.pgsql.com/download/

Ik zie daar toch echt een replication server bijstaan hoor...

Maar dat is nog een vaag dingetje. Er staat niets over in de documentatie. Uit een usenet post in comp.databases.postgresql.hackers:

After looking briefly at the the RServ 0.1 replication software we decided it was not an option (for now) (it had serious problems in our tests, there is no documentation available and inquiries about it to PosgreSQL, Inc. remain unanswered unfortunately).

Maar dat is nog een vaag dingetje.

En? Dat is die van MySQL toch ook uiteindelijk... alleen als je aan een aantal beperkende voorwaarden voldoet wil het werken. En als je er niet aan voldoet krijg je ook serious problems.

Uh, rare redenatie: heel MySQL is vaag, ook al zijn er docs.

Maar je zegt dat PostgreSQL het *zonder* replication moet stellen en dat klopt gewoon niet. De mate van vaagheid en het al dan niet aanwezig zijn van docs hebben daar niets mee te maken.

Er is in ieder geval documentatie en er zijn mensen die het gebruiken.

Verandert dat iets aan het niet kloppen van je uitspraak?

Het is dus geen vaag dingetje want ze hebben docs . De rest kun je zelf uitfrutselen.

De beste manier van leren is gewoon doen.

om iemand te quoten:

[quote]
over UFO's is ook documentatie en er zijn mensen die zweren er door misbruikt te zijn
[quote]

Ik heb niks begrepen van het in depth gedoe, maar 'k vind het wel leuk dat er ook eens feedback gegeven wordt over wat er aan de hand is zonder dat er problemen zijn. Dank hiervoor.

Kort samengevat: MySQL replication werkt en de database servers hebben al 3 weken geen probs meer .

En tweakers gaan natuurlijk geen versimpelde bewoordingen gebruiken. In de eerste plaats omdat ze daar te lui voor zijn, in de tweede plaats omdat je dan de kans loopt om zelf als newbie gezien te worden .

godzijdank staat er in de roadmap van mysql dat er vanaf versie 4.0 een auto-change master feature ingebouwd zal zijn waardoor, mocht de master eruit knallen, een willekeurige slave de master taken overneemt. dan wordt het eindelijk echt betrouwbaar.

enig probleem is dat er nu dus wel iets aan de hand is...
databees probbies op got ofzo...
2 minuutjes later ligt de hele page plat...

inplaats van BMW's zijn paar leuk diertjes ook leuk
http://athena.tweakers.net/~femme/pics/

Madelinde en Fleur zeker?
Of bedoelde je Roos?

autokonijn

Roos is kewl

Zitten echt hele mooie foto's tussen Femme, is dat allemaal rond je huis?
echt koel!!

De meeste wel (o.a. 9 mei helemaal). Verder nog wat pics van het kasteel van Ruurlo en het tussenliggende bos.

Gelukkig een .plan over servers! Ik kreeg al ontwenningsverschijnselen

Weer een goed verhaal Femme! Ga zo door!

Enne: Komt er nou nog een keer voor de liefhebbers een verhaaltje over de server-history van t.net? Ik heb namelijk een hogere .plan-dosis nodig merk ik

En afkicken is ook geen goed idee, gezien de zelfmoordneigingen die je er als .plan-junkie van kunt krijgen In memoriam: H. Brood

Ik moet nog drie pagina's finishen (waarvan de meeste tekst er al is) en typo check doen.

Hey das mooi man
TIA

Je tweakotine is anders niet zo hoog.

Interessant stukje dit!
Dit had ik namelijk net nodig om een beetje tot een keuze te kunnen komen

Oja, ook fij dat het goed gaat op t.net!

MySQL uptime op Artemis is nu 20 dagen, 3:50. In die tijd kreeg-ie 164 miljoen queries voor z'n snuitert.

Eindelijk weer een .plan, ik snakte er weer naar .

Maar het is mooi om te horen / zien dat het allemaal lekker loopt bij t.net, we hebben tenslotte slechtere tijden meegemaakt en daar blijken we toch wel een beetje overheen te zijn gekomen en dat voelt denk ik heerlijk voor de crew, of nie?

Er staat me een bijbelverhaal bij waarin er na 7 goede jaren 7 slechte jaren komen, laat het voor t.net alsjeblieft net andersom gaan .

Keep Up The Good Work Crew!

Misschien moeten we het maar als een goed teken zien als er lang geen .plan verschijnt...

Goed werk lui !

Bedankt voor het delen van jullie ervaringen mbt replication. Tegelijkertijd nog wat dingen geleerd ook. Helder verhaal. Thanks!