Schreib-/Lesezugriffe und Locks

Hi,

ich betreibe Blogscout.de und habe da mit einer Problematik zu
kämpfen, bei der ich einige Lösungsansätze kenne, aber teilweise nicht
Zeit und Geld habe, um alle auszuprobieren. Außerdem kann es ja auch
immer noch sein, dass ich ein paar Lösungen übersehe.

Bei Blogscout.de handelt es sich unter anderem um einen Counter-Dienst
für Blogs.

Im Moment kommen täglich etwa 900.000 Blogaufrufe in die Datenbank
(wobei ich aber auch immer nur die letzten 2 Tage vorhalte, die
Tabelle ist also nie größer als etwa 2 Mio Datensätze, aber das wird
auch noch wachsen). Die Tabelle ist aktuell 350 MB groß und hält zur
Zeit 1 Mio Zeilen.

Desweiteren gibt es einige weitere Tabellen, die Statistikdaten
enthält. Eine Tabelle mit jetzt 3,2 Mio Einträgen und aktuell 937 MB
enthält jede aufgerufene Seite aller Blogs. Die Tabelle wird benötigt,
um einer Logfile-Zeile eine ID der sogenannten Blogeinträge-Tabelle
zuzuordnen. Dieses wird wiederum benötigt, um Statistiken auf Ebene
der Blogeinträge anzubieten.

Es gibt eine ganze Reihe von Statistiktabellen auf Blog und
Blogeintragebene (ja, die Daten sind redundant, aber sonst gäbe es
noch mehr Probleme mit der Geschwindigkeit), mit 1,5 bis 4 Mio
Einträgen und Größen von 290 bis 350 MB.

Soviel zu den Rahmenbedingungen, nun also zu den Problemen: die
Statistiktabellen und die Tabelle mit den Blogeinträgen werden immer
wieder gelockt, weil gleichzeitig schreibende und lesende Zugriffe
passieren. So kommen immer wieder neue Blogbeiträge hinzu und
gleichzeitig werden immer wieder Daten aus dieser Tabele benötigt um
die Blogeinträge anzuzeigen. Bei den Statistiken ist es das selbe: den
ganzen Tag über werden diese aktualisiert und auch immer wieder aus
ihnen gelesen.

Ein Problem ist sicherlich, dass der DB-Server nur mit 1 MB bestückt
ist. Der Server wird bei Hetzner betrieben, ich könnte für mehr Geld
also auch bessere Hardware bekommen. Ich möchte das Geld aber nicht
unnötig ausgeben, weil vielleicht noch sehr viel andere Dinge
verbessert werden können. Trotzdem wäre es gut, eure Einschätzung zu
erhalten: wenn ich von einem DS 3000 (http://hetzner.de/
rootserver.html) umsteigen würde, welchen würdet ihr empfehlen? Würde
die Verdopplung des Speichers spürbar was bringen? Macht es Sinn, auf
DualCore und 2 GB statt 1GB umzusteigen? Ich bin mir sicher, dass das
stark von den Faktoren abhängt, warum genau die Anfragen im Moment
gelockt werden. Da kann ich gerne Informationen zu nachliefern. Eure
Erfahrungswerte interessieren mich aber auch unabhängig von der
konkreten Situation.

Alle Tabellen sind im Moment MyISAM, MySQL 5.0.x kommt zum Einsatz.
Ich hatte auch schon einmal InnoDB für die Tabellen ausprobiert, die
immer gelockt werden, weil InnoDB Row-Locking erlaubt, im Gegensatz zu
Table-Locking bei MyISAM. Das Ergebnis war aber katastrophal, weil ich
zwar keine Locks mehr hatte, die Zugriffe aber so deutlich langsamer
wurden, dass ich es nicht mehr schaffte, die damals nur 400.00 Zeilen
pro Tag zu bearbeiten. Diese Lösung muss also leider verworfen werden.

Als nächstes dachte ich daran, eine Replikation der Datenbank
aufzusetzen und die Lesenden Zugriffe nur von dort zu machen. Das wäre
von der Applikation her gut zu realisieren und wenn dann nicht immer
die absolut neusten Zahlen zu sehen wären, wäre das auch nicht so
schlimm.
Ich habe selbst noch nie eine Replikation aufgesetzt, kenne mich
deshalb nicht mit dem Verhalten aus, kam aber zu folgendem Schluß: der
"Empfänger" der Replikation (also der Client) macht ja genau das, was
der Master auch tut. D.h. er führt auch alle Schreibprozesse aus,
womit ich im Endeffekt wieder auf die Locking-Problematik stosse, die
ich jetzt auch habe, oder? Der einzige Vorteil wäre, dass keine
konkurrierenden Schreibprozesse mehr passieren, weil die Repliaktion
ja jeden Befehl einzeln ausführt. Könnte das eventuell schon
ausreichen, um das Problem einzudämmen?

Falls hier jemand aus dem Raum Köln/Bonn mitliest, setze ich mich auch
gerne mal gemeinsam mit der Person an den Server, um detailliertere
Informationen geben zu können. Ansonsten bin ich auch gerne bereit,
weitere Daten zu Konfiguration, etc. preiszugeben.

Viele Grüße,
Dirk

Re: Schreib-/Lesezugriffe und Locks

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Schreib-/Lesezugriffe und Locks

Hallo Andreas,

On 17 Mrz., 15:28, Andreas Kretschmer
wrote:
> begin Dirk Olbertz wrote:

> Man müßte sich halt mal anschauen, was da konkret das Problem ist.
> Explain ist Dir schon bekannt? Allerdings ist MySQL wohl nicht in der
> Lage, pro Select und Table mehr als einen Index zu nutzen. Aber ein paar
> Millionen Zeilen sind nicht wirklich viel.

Explain ist mir bekannt, ja, obwohl ich zugeben muss, dass ich nicht
immer 100% immer weiß, was mir die Ausgabe gerade sagen will. Und das
ganz besonders im Zusammenhang mit der Annahme, die Du da auch machst.
Nämlich dass MySQL evtl. nicht in der Lage ist, mehr als einen Index
pro Anfrage zu benutzen.

Wenn das wirklich so ist, müsste ich ja nur einen Index pro WHERE-
Clause anlegen. Das liesse sich auf jeden Fall machen. Aber ich habe
keine Ahnung, ob das nun wirklich ein Gerücht ist, oder tatsächlich so
ist.

Vielleicht kann ja hier jemand noch aufklärend einspringen.

Viele Grüße,
Dirk

Re: Schreib-/Lesezugriffe und Locks

Dirk Olbertz writes:

> Ein Problem ist sicherlich, dass der DB-Server nur mit 1 MB bestückt
> ist. Der Server wird bei Hetzner betrieben, ich könnte für mehr Geld
> also auch bessere Hardware bekommen. Ich möchte das Geld aber nicht
> unnötig ausgeben, weil vielleicht noch sehr viel andere Dinge
> verbessert werden können. Trotzdem wäre es gut, eure Einschätzung zu
> erhalten: wenn ich von einem DS 3000 (http://hetzner.de/
> rootserver.html) umsteigen würde, welchen würdet ihr empfehlen? Würde
> die Verdopplung des Speichers spürbar was bringen? Macht es Sinn, auf
> DualCore und 2 GB statt 1GB umzusteigen? Ich bin mir sicher, dass das
> stark von den Faktoren abhängt, warum genau die Anfragen im Moment
> gelockt werden. Da kann ich gerne Informationen zu nachliefern. Eure
> Erfahrungswerte interessieren mich aber auch unabhängig von der
> konkreten Situation.

Mit 1 MB ist sicher 1 GByte gemeint?

vmstat -n 1 gibt dir möglicherweise einen Hinweis wie die Auslastung
auf dem Gerät aussieht.

Re: Schreib-/Lesezugriffe und Locks

"Dirk Olbertz" wrote:
>
> ich betreibe Blogscout.de und habe da mit einer Problematik zu
> kämpfen, bei der ich einige Lösungsansätze kenne, aber teilweise nicht
> Zeit und Geld habe, um alle auszuprobieren. Außerdem kann es ja auch
> immer noch sein, dass ich ein paar Lösungen übersehe.
....

> Soviel zu den Rahmenbedingungen, nun also zu den Problemen: die
> Statistiktabellen und die Tabelle mit den Blogeinträgen werden immer
> wieder gelockt, weil gleichzeitig schreibende und lesende Zugriffe
> passieren.

Kannst du dazu genauere Zahlen nennen? Z.B. liefert dir SHOW GLOBAL
STATUS LIKE 'Com_%' die Anzahl der Aufrufe der verschiedenen State-
ments. Zusammen mit der Uptime (SHOW GLOBAL STATUS LIKE 'Uptime')
könntest du daraus mal die durchschnittliche Rate für SELECT, INSERT,
UPDATE, DELETE bekommen.

Weit besser ist natürlich, wenn du das etwas genauer protokollierst.
Vermutlich hast du ja auch irgendwelche Cronjobs oder generell Zeiten,
in denen das Lastverhalten "speziell" ist.

Vermutlich werden deine Tabellen häufiger geschrieben als gelesen.
Deswegen ist

> Alle Tabellen sind im Moment MyISAM,

eher nicht so toll.

Wie du ja schon weißt, können MyISAM-Tabellen nur entweder gelesen
*oder* geschrieben werden. Das wird genau dann zum Problem, wenn eine
Art Queries lange dauert.

> Ich hatte auch schon einmal InnoDB für die Tabellen ausprobiert, die
> immer gelockt werden, weil InnoDB Row-Locking erlaubt, im Gegensatz zu
> Table-Locking bei MyISAM. Das Ergebnis war aber katastrophal, weil ich
> zwar keine Locks mehr hatte, die Zugriffe aber so deutlich langsamer
> wurden, dass ich es nicht mehr schaffte, die damals nur 400.00 Zeilen
> pro Tag zu bearbeiten. Diese Lösung muss also leider verworfen werden.

Das würde ich so nicht unterschreiben. Wenn MyISAM wegen Locking-
Konflikten zu langsam wird, sollte InnoDB schneller sein. Allerdings
verzeiht InnoDB einige Sünden nicht, die MyISAM egal sind (z.B.
PRIMARY KEYS die was anderes sind als INTs). Außerdem will InnoDB
etwas RAM haben für innodb_buffer_pool_size. Mit den Defaults rennt
es nur recht gemächlich.

> Als nächstes dachte ich daran, eine Replikation der Datenbank
> aufzusetzen und die Lesenden Zugriffe nur von dort zu machen. Das wäre
> von der Applikation her gut zu realisieren und wenn dann nicht immer
> die absolut neusten Zahlen zu sehen wären, wäre das auch nicht so
> schlimm.
> Ich habe selbst noch nie eine Replikation aufgesetzt, kenne mich
> deshalb nicht mit dem Verhalten aus, kam aber zu folgendem Schluß: der
> "Empfänger" der Replikation (also der Client) macht ja genau das, was
> der Master auch tut. D.h. er führt auch alle Schreibprozesse aus,
> womit ich im Endeffekt wieder auf die Locking-Problematik stosse, die
> ich jetzt auch habe, oder?

Replikation bringt nur dann wirklich etwas, wenn deine Daten read-
mostly sind. Ich würde mal sagen so ab 80% Lesen, 20% Schreiben.


IMNSHO hast du zwei Möglichkeiten, fortzufahren:

1. du optimierst deine Queries, so daß MyISAMs Table-Locking kein
Problem mehr ist. Gefühlsmäßig sollten sich deine Queries alle
gut über Indizes beschleunigen lassen. Auf jeden Fall solltest
du mal eine Weile (mindestens einen Tag) das slow Query Log
schreiben lassen und dir vor allem alle Queries loggen lassen,
die keinen Index benutzen.
Danach dann mit EXPLAIN analysieren und evtl. Indizes nachrüsten.
Dabei aber daran denken, daß ein Index zwar gut für die Lese-
Performance ist, jedoch schlecht fürs Schreiben!

2. du probierst nochmal InnoDB. Das bringt natürlich nur etwas,
wenn das Problem tatsächlich das Locking ist. In diesem Fall
sollten wir auch mal deine Tabellen und my.cnf begutachten.
Auch wenn du den InnoDB-Weg gehst, ist 1. natürlich trotzdem
nicht verkehrt.


Wenn du deinen Server upgraden willst: alle Datenbanken brauchen RAM!
Im Zweifelsfall also lieber mehr RAM als eine schnellere CPU.


XL

Re: Schreib-/Lesezugriffe und Locks

> Wenn das wirklich so ist, müsste ich ja nur einen Index pro WHERE-
> Clause anlegen. Das liesse sich auf jeden Fall machen. Aber ich habe
> keine Ahnung, ob das nun wirklich ein Gerücht ist, oder tatsächlich so
> ist.

Das ist in der Tat korrekt. Hat eine Tabelle mehrere Indize, dann wird
immer nur einer genutzt (explain sagt dir welcher). Deshalb: auch mal an
kombinierte Indize denken und evtl. nie genutzte entfernen.

Re: Schreib-/Lesezugriffe und Locks

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Schreib-/Lesezugriffe und Locks

MicSch wrote:
>> Wenn das wirklich so ist, müsste ich ja nur einen Index pro WHERE-
>> Clause anlegen. Das liesse sich auf jeden Fall machen. Aber ich habe
>> keine Ahnung, ob das nun wirklich ein Gerücht ist, oder tatsächlich so
>> ist.
>
> Das ist in der Tat korrekt. Hat eine Tabelle mehrere Indize, dann wird
> immer nur einer genutzt

Das ist *nicht* mehr korrekt.
Insbesondere nicht für den OP, der ja MySQL 5.0 im Einsatz hat.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html


XL