Dedicated server met database interne mirror?

[ScuL]

Hi,

Vond het lastig om de juiste categorie te kiezen dus hoop dat ik op de juiste plek zit.

Ik heb een dedicated server die draait op CentOS met daarop de diverse webapplicaties (apache,mysql,proftpd) het geheel gemanaged door Plesk.

Heb onlangs een backup script gebouwd dat dagelijks de gehele site dmv rsync met mijn thuisserver (via Cygwin) synchroniseert.
Heb echter een probleempje met de databases.

Ik heb 4 databases in mysql op de server draaien, 1 daarvan is meer dan 1.5GB, de overigen zijn tussen de 50 en de 200MB.
Het backup script draait in het holst van de nacht een mysqldump. Bij de kleiner databases duurt dit tussen de 30 en 60 seconden en is er minimale downtime. Bij de database van 1.5GB duurt dit vrij lang (zeker 5 minuten misschien meer) en gedurende die tijd is de website offline.
Ook lukt het af en toe niet om de gehele dump uit te voeren met als gevolg:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `phpbb_posts` at row: 1320824

Heb hier wat onderzoek naar gedaan en het blijkt dat het mogelijk is mysql te spiegelen.
Wat ik nu wil weten:

- optie 1: is het mogelijk om lokaal een gekloonde database te hebben die live wordt bijgewerkt?
vb. database: dbnaam1 en dbnaam1bkp
Zodra er een INSERT statement uitgevoerd wordt op dbnaam1 wordt dat tegelijkertijd uitgevoerd op dbnaam1bkp?
- optie 2: is het mogelijk dit gestaggerd te doen?
(alle SQL routines van db1 over 24 uur worden in een script gestopt en dat wordt in het holst van de nacht uitgevoerd op dbnaam1bkp zodat beide databases gesyncht zijn?)
- optie 3: het enige wat ik heb kunnen vinden dat hier dichtbij komt is een mysql replicate op een externe server. is dit verplicht? kan het ook lokaal? ik heb geen externe mysql server, tenzij ik het op mijn thuisservertje installeer met alle gevolgen van dien.

Advies is bijzonder welkom

[Keiichi]

Je kunt een master-slave setup maken. Je kunt vanuit de slave backups maken waarbij je alles kan locken en de master gewoon beschikbaar blijft.

De fout tijdens het dumpen kan ik niet veel over zeggen, misschien dat de log van mysql hier wat meer inzicht in kan geven.

[ScuL]

Keiichi schreef op vrijdag 31 januari 2014 @ 13:09:
Je kunt een master-slave setup maken. Je kunt vanuit de slave backups maken waarbij je alles kan locken en de master gewoon beschikbaar blijft.

De fout tijdens het dumpen kan ik niet veel over zeggen, misschien dat de log van mysql hier wat meer inzicht in kan geven.

Ja dat was ook mijn insteek, als ik een mirror heb van het origineel kan ik de mirror gerust locken en dumpen terwijl de master actief blijft voor gebruikers.

Mijn vraag is alleen hoe ? En of dit kan op dezelfde server of dat ik hier 2 servers voor nodig heb.

[Fish]

Het hoeft geen externe te zijn he, maar gewoon naar een andere instantie repliceren moet al werken natuurlijk. google -> http://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

Als ik die error bekijk kapt de sql server de verbining af. zit er geen timeout op ?

[ Voor 15% gewijzigd door Fish op 31-01-2014 13:11 ]

[ScuL]

fish schreef op vrijdag 31 januari 2014 @ 13:11:
Het hoeft geen externe te zijn he, maar gewoon naar een andere instantie repliceren moet al werken natuurlijk. google -> http://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

Als ik dat vlug bekijk maak ik er uit op dat je dan dus mysqld 2x moet draaien opdezelfde server?
Ik heb nu al een gemiddelde CPU load van 250% het lijkt me dat 2x mysqld draaien de performance niet ten goede komt.

Je kunt niet 1 mysqld instance tegelijk naar 2 db files laten schrijven/lezen?

[ Voor 84% gewijzigd door ScuL op 31-01-2014 13:18 ]

[DukeBox]

Waarom niet gewoon --lock-tables=false / --single-transaction ?

[ScuL]

DukeBox schreef op vrijdag 31 januari 2014 @ 13:49:
Waarom niet gewoon --lock-tables=false / --single-transaction ?

Kun je wat meer uitleg geven. Wat ik uit de documentatie opmaak is dat de tabellen dan niet gelockt worden? Maar wat gebeurt er dan op het moment dat een user een post plaatst terwijl de dump aan de gang is?

Moet er bij zeggen dat dat op dit moment niet mogelijk is, zodra mysqldump draait trekt ie de CPU plat en zijn alle websites down.

Ook is het zo dat soms de mysqldump slaagt en soms niet. (hangt denk ik van serverload af)

[ Voor 22% gewijzigd door ScuL op 31-01-2014 14:45 ]

[Kees]

ScuL schreef op vrijdag 31 januari 2014 @ 14:41:
[...]


Kun je wat meer uitleg geven. Wat ik uit de documentatie opmaak is dat de tabellen dan niet gelockt worden? Maar wat gebeurt er dan op het moment dat een user een post plaatst terwijl de dump aan de gang is?

Met --single transaction komt die dan niet in de dump, maar word hij wel doorgevoert (als je innodb gebruikt, bij myisam werkt dat niet)

Overigens is inderdaad de beste oplossing om een master-slave setup te maken en dan de slave stil te zetten, backup te maken en verder te gaan. Dat kan met twee instanties op 1 host (de tweede instantie zal als het goed is geen lees-werk doen, dus is een stuk minder cpu-hongerig). Maar let er wel op dat het geheel wel zwaarder word want elke schrijfactie doet hij dan minimaal 3x (master files, binlog, slave files) dus hoe goed dat gaat werken weet ik niet.

Overigens vind ik je cpu% nu al wel aan de hoge kant. Ik zit met een db van ~250G (op een 2x quadcore + 256G geheugen) en ~800q/s met pieken tot 5kq/s op ongeveer 4% (~64% op 1 core) cpugebruik, dus het kan mischien ook nuttig zijn om je dbserver iets te upgraden.

Overigens, met de backup; zorg er wel voor dat je --quick en --extended-insert gebruikt, dat kan nogal schelen in de backuptijd (en scheelt enorm in de restoretijd)

[DukeBox]

Sterker nog, ik vind CPU extreem hoog.. ik draai heel wat databases voor de verschillende projecten in /5, een dump van de databases (variërend in groote van 1,5GB tot 122GB) is binnen 15 min. gedaan. Daarbij zie ik CPU niet verder komen dan 5 a 7% en dat op een intel atom D2700.

[ScuL]

Hmm, de server is een Dual Core 2GHz met 2GB RAM. Bij normaal gebruik (users op de fora) schommelt de CPU tussen de 60 en 100% (soms erover op <30 secs).

Ik heb de opties -q en -e (quick en extended-insert) ingeschakeld, kijken wat voor impact dit vannacht heeft.

Bedankt voor de uitleg trouwens Kees ik stel het enorm op prijs om antwoord te krijgen van de databasebeheerder van Tweakers

Zit nu te twijfelen of ik het risico moet nemen om een master-slave opstelling op de server te installeren en daarmee nog verder de resources dicht te knijpen, of dat ik een mySQL servertje moet opzetten op m'n thuisserver en daar dan naar toe replicate..

[DukeBox]

Wat draai je er dan op dat je zoveel cpu load hebt van MySQL ? Een site als t.net kan ik me wel wat bij voorstellen maar om hoeveel query's per sec. gaat het eigenlijk ? Zijn die allen zo CPU intensief ?

Ik verwerk zelf zo'n 2,4 miljoen insert/update quert's per uur op voornamelijk tabellen met een uniq index over 3 kolommen.. (en de tabellen hebben een inhoud van 0,5 tot 380 miljoen records) dit zie ik bijna niet terug in CPU gebruik.

Wat ik zelf doe bij grote databases (omdat het restoren extreem lang duurt in mijn geval) is wekelijks een file kopie en dagelijks de bin log's overhalen. Deze kan ik veel sneller restoren dan een mysql import.

[ Voor 55% gewijzigd door DukeBox op 31-01-2014 18:48 ]

[ScuL]

Dit is de algehele load niet alleen die van mySQL, vergeef me maar op sommige aspecten van linux ben ik nog een n00b. Ik heb geen idee hoe ik moet kijken welke processen de load veroorzaken of hoe ik kan zien hoeveel queries er per uur worden uitgevoerd

[DukeBox]

Ah.. ik dacht al

Er zijn heel wat tooltjes om de load te zien, standaard laat top de huidige load zien van je processen.

[ScuL]

Ik heb het nu 2 nachtjes laten lopen met de quick & extended insert en het tijdverschil is fenomenaal

database1 (9MB gecomprimeerd):
30/01 - 1 minuut
31/01 - 2 minuten
01/02 - <1 minuut
01/02 - <1 minuut

database2 (285MB gecomprimeerd):
30/01 - 25 minuten
31/01 - 35 minuten
01/02 - 7 minuten
02/02 - 4 minuten

database3 (9MB gecomprimeerd):
30/01 - 2 minuten
31/01 - 3 minuten
01/02 - <1 minuut
02/02 - <1 minuut

database4 (1MB gecomprimeerd):
30/01 - 1 minuut
31/01 - 2 minuten
01/02 - <1 minuut
02/02 - <1 minuut