Partitionierung großer Tabellen

Hallo NG,

ich habe hier bei einigen Webanwendungen teilweise Tabellen von knapp
3GB Größe. Auf einem ordentlich ausgestattetem Server läuft auf SuSE
Linux 9.3 eine MySQL 5.0.18 for i686 Installation.

Nun würde ich aus verständlichen Performancegründen gerne die großen
Tables partitionieren. Da das aber ein Livesystem ist, möchte ich
keine Experimente mit Betas machen.

- Wer hat denn Erfahrungen mit Partitioning unter MySQL 5.1.19.0? Ist
das da wirklich stable?
- Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?


Vielen Dank für Eure Hilfe!
Dennis

Re: Partitionierung großer Tabellen

Am 12.06.2007 15:14 schrieb Dennis Winter:
> Hallo NG,
>
> ich habe hier bei einigen Webanwendungen teilweise Tabellen von knapp
> 3GB Größe. Auf einem ordentlich ausgestattetem Server läuft auf SuSE
> Linux 9.3 eine MySQL 5.0.18 for i686 Installation.
>
> Nun würde ich aus verständlichen Performancegründen gerne die großen

*gibt* es denn Performanceprobleme?

> Tables partitionieren. Da das aber ein Livesystem ist, möchte ich
> keine Experimente mit Betas machen.
>
> - Wer hat denn Erfahrungen mit Partitioning unter MySQL 5.1.19.0? Ist
> das da wirklich stable?
> - Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?

Das dürfte von den Daten und den Abfragen abhängen. Du kannst nach
Jahreszahlen partitionieren oder nach Postleitzahlen, vielleicht auch
nach Schuhgrößen.

Re: Partitionierung großer Tabellen

On 12 Jun., 15:24, Christian Kirsch wrote:
> *gibt* es denn Performanceprobleme?

Ja, die gibt es. In unregelmässigen Abständen hängt sich MySQL bei
vielen Anfragen auf. Nach einem Restart des Dämons (wenn es klappt,
andernfalls Reboot) läuft alles wieder ordentlich. Nur ist dann
gelegentlich die eine oder andere große Tabelle gecrasht. Ein Repair
dauert bei diesen Tabellen dann bis zu 25 Min.

> > - Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?
>
> Das dürfte von den Daten und den Abfragen abhängen. Du kannst nach
> Jahreszahlen partitionieren oder nach Postleitzahlen, vielleicht auch
> nach Schuhgrößen.

Da meine Elemente keine Schuhe haben, wie wäre es mit dem
Primärschlüssel? Also einem Autoinkrement-Wert?

Re: Partitionierung großer Tabellen

Am 12.06.2007 15:40 schrieb Dennis Winter:
> On 12 Jun., 15:24, Christian Kirsch wrote:
>> *gibt* es denn Performanceprobleme?
>
> Ja, die gibt es. In unregelmässigen Abständen hängt sich MySQL bei
> vielen Anfragen auf. Nach einem Restart des Dämons (wenn es klappt,
> andernfalls Reboot) läuft alles wieder ordentlich. Nur ist dann
> gelegentlich die eine oder andere große Tabelle gecrasht. Ein Repair
> dauert bei diesen Tabellen dann bis zu 25 Min.
>

Und die fraglichen Anfragen hast Du Dir schon mit EXPLAIN angesehen,
es gibt keine brauchbare Fehlermeldung etc.?

Was Du da beschreibst, hört sich jedenfalls weniger nach einem
Performance- als nach einem Stabilitätsproblem an. Dem sollte man
vielleicht auf den Grund gehen.

>>> - Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?
>> Das dürfte von den Daten und den Abfragen abhängen. Du kannst nach
>> Jahreszahlen partitionieren oder nach Postleitzahlen, vielleicht auch
>> nach Schuhgrößen.
>
> Da meine Elemente keine Schuhe haben, wie wäre es mit dem
> Primärschlüssel? Also einem Autoinkrement-Wert?
>

Warum nicht. Andererseits: Warum? Ich habe gerade mal bei Oracle
nachgelesen. Die haben, wenn ich das richtig verstanden habe, drei
Verfahren: "partition by range", bei dem Du selber die Wertebereiche
einer Spalte vorgibst, anhand dann die Tabelle zerlegt wird. Oder
"Partition by hash". In diesem Fall legst Du die Anzahl der
Partitionen und die Spalte fest und Oracle benutzt eine Hashfunktion
auf der angegebenen Spalte, die die Daten möglichst gleichmäßig auf
die Partitionen verteilt. Als drittes dannList-Partitionierung, wenn
man Enumerations oder ähnliches hat.

Was ich bislang darüber gelesen habe, ging in der Regel von einer
semantischen Bedingung aus (also *nicht* dem PK) - sowas wie eine
Tabelle pro Jahr oder eine pro PLZ-Bereich oder so. Wenn natürlich
Deine Anfragen alle ohnehin den PK im WHERE benutzen, dann kannst Du
auch danach partitionieren. Wenn es aber um andere Kriterien geht,
dann bringt die Partitionierung nach dem PK m.E. wenig.

Re: Partitionierung großer Tabellen

On 12 Jun., 15:57, Christian Kirsch wrote:
> Und die fraglichen Anfragen hast Du Dir schon mit EXPLAIN angesehen,
> es gibt keine brauchbare Fehlermeldung etc.?

Nein. Das ist ein ausgefeiltes Tool mit mehreren Entwicklern dran. Bis
ich mich da reingefuchst habe dauert das zu lange. Als die Tabellen
noch kleiner waren ging alles wie am Schnürchen...

> Was Du da beschreibst, hört sich jedenfalls weniger nach einem
> Performance- als nach einem Stabilitätsproblem an. Dem sollte man
> vielleicht auf den Grund gehen.

Habe ich getan und keine brauchbare Fehlermeldung und damit keine
Quelle gefunden ausser, dass die Kiste performancemässig nicht mehr
mitkommt. Wenn es soweit ist, schießt die Prozessorlast für MySQL in
die Höhe, sodass der Kernel nicht mal mehr Logfiles schreibt. Das is
halt Essig fürs debuggen...

> > Da meine Elemente keine Schuhe haben, wie wäre es mit dem
> > Primärschlüssel? Also einem Autoinkrement-Wert?
>
> Warum nicht. Andererseits: Warum? Ich habe gerade mal bei Oracle
> nachgelesen. Die haben, wenn ich das richtig verstanden habe, drei
> Verfahren: "partition by range", bei dem Du selber die Wertebereiche
> einer Spalte vorgibst, anhand dann die Tabelle zerlegt wird. Oder
> "Partition by hash". In diesem Fall legst Du die Anzahl der
> Partitionen und die Spalte fest und Oracle benutzt eine Hashfunktion
> auf der angegebenen Spalte, die die Daten möglichst gleichmäßig auf
> die Partitionen verteilt. Als drittes dannList-Partitionierung, wenn
> man Enumerations oder ähnliches hat.
>
> Was ich bislang darüber gelesen habe, ging in der Regel von einer
> semantischen Bedingung aus (also *nicht* dem PK) - sowas wie eine
> Tabelle pro Jahr oder eine pro PLZ-Bereich oder so. Wenn natürlich
> Deine Anfragen alle ohnehin den PK im WHERE benutzen, dann kannst Du
> auch danach partitionieren. Wenn es aber um andere Kriterien geht,
> dann bringt die Partitionierung nach dem PK m.E. wenig.

Okay, ich habe meine Schuhgrößen gefunden. Wenn eine Tabelle
partitioniert ist, ist das allerdings für die weitere Verwendung der
bisherigen Queries nicht relevant, oder?

Ist die Partitionierung nach Schuhgrößen eigentlich dynamisch? Sprich,
wenn eine neue Schuhgröße dazukommt wird automatisch eine Partition
hierfür angelegt.

Sorry, das klingt jetzt etwas so, als wäre ich zu doof
http://dev.mysql.com/doc/refman/5.1/de/partitioning.html zu lesen.
Aber wirklich durchsteigen tu ich im Moment dort noch nicht.

Re: Partitionierung großer Tabellen

Am 12.06.2007 16:15 schrieb Dennis Winter:

>
> Okay, ich habe meine Schuhgrößen gefunden. Wenn eine Tabelle
> partitioniert ist, ist das allerdings für die weitere Verwendung
> der bisherigen Queries nicht relevant, oder?
>

Die Frage verstehe ich nicht. Meinst Du "Ich kann die Queries weiter
so benutzen wie bisher" (Antwort: Ja) oder meinst Du irgendwas anderes?

> Ist die Partitionierung nach Schuhgrößen eigentlich dynamisch?
> Sprich, wenn eine neue Schuhgröße dazukommt wird automatisch eine
> Partition hierfür angelegt.
>

Ich vermute: Nein. Denn die Partitionierung dürfte ein einmaliger
Prozess sein (in CREATE/ALTER TABLE). Du kannst natürlich einen
INSERT- bzw. UPDATE-Trigger schreiben, der nachguckt, ob "eine neue
Schuhgröße" hinzugekommen ist und dann bei Bedarf eine neue Partition
anlegt.


Um so etwas ähnliches geht es in dem iX-Artikel zu Partitionierung in
PostgreSQL
http://www.heise.de/kiosk/archiv/ix/07/04/141_Teile_und_herr sche


> Sorry, das klingt jetzt etwas so, als wäre ich zu doof
> http://dev.mysql.com/doc/refman/5.1/de/partitioning.html zu lesen.
> Aber wirklich durchsteigen tu ich im Moment dort noch nicht.
>

Entscheidend dürften diese beiden Stellen sein:

> Die Partitionierungsimplementierung in MySQL 5.1 befindet sich noch
> in der Entwicklung und ist noch nicht bereit für
> Produktionsumgebungen. Etwas Ähnliches gilt für den Inhalt dieses
> Kapitels: Manche der hier beschriebenen Features sind in
> Wirklichkeit noch gar nicht implementiert, und andere funktionieren
> noch nicht ganz so wie beschrieben.

Das hört sich in meinen Ohren nicht so an, als ob man jetzt schon eine
wichtige Anwendung darauf aufsetzen möchte.

Vielleicht doch erstmal Fehlersuche? Wenn's die MySQL-Logs nicht
bringen, vielleicht die des Web-Servers, wenn es eine Web-Anwendung
ist? Ich gestehe, dass ich den Verdacht habe, Eure Queries könnten
nicht so prima sein, wie sie sein sollten (fehlende Indizes, ein
vergessenes WHERE, was ein Kreuzprodukt auslöst oder so...). Denn von
Stabilitätsproblemen, wie Du sie beschreibst, hört man hier fast nie.

Re: Partitionierung großer Tabellen

On 12 Jun., 16:26, Christian Kirsch wrote:
> Die Frage verstehe ich nicht. Meinst Du "Ich kann die Queries weiter
> so benutzen wie bisher" (Antwort: Ja) oder meinst Du irgendwas anderes?

Neinein, hast genau richtig verstanden! :)

> > Ist die Partitionierung nach Schuhgrößen eigentlich dynamisch?
> > Sprich, wenn eine neue Schuhgröße dazukommt wird automatisch eine
> > Partition hierfür angelegt.
>
> Ich vermute: Nein. Denn die Partitionierung dürfte ein einmaliger
> Prozess sein (in CREATE/ALTER TABLE). Du kannst natürlich einen
> INSERT- bzw. UPDATE-Trigger schreiben, der nachguckt, ob "eine neue
> Schuhgröße" hinzugekommen ist und dann bei Bedarf eine neue Partition
> anlegt.

Guter Tip, danke!

> [...]
>
> Vielleicht doch erstmal Fehlersuche? Wenn's die MySQL-Logs nicht
> bringen, vielleicht die des Web-Servers, wenn es eine Web-Anwendung
> ist? Ich gestehe, dass ich den Verdacht habe, Eure Queries könnten
> nicht so prima sein, wie sie sein sollten (fehlende Indizes, ein
> vergessenes WHERE, was ein Kreuzprodukt auslöst oder so...). Denn von
> Stabilitätsproblemen, wie Du sie beschreibst, hört man hier fast nie.

Gut, dann schau ich mal ob ich die letzten Queries vor dem heutigen
Crash irgendwo für ein EXPLAIN rausbuddeln kann.

Ich kenne solche Probleme auch nicht von MySQL. Zu Anfang war die Swap-
Partition des Servers etwas zu klein geraten, aber dieses Problem ist
auch schon lange behoben. Schaumer mal...

Vielen Dank für Deine Zeit! Bin ein gutes Stück weitergekommen! Wenn
ich den Fehler gefunden habe poste ich das Ergebnis hier.


Viele Grüße
Dennis

Re: Partitionierung großer Tabellen

On 12.06.2007 15:57, Christian Kirsch wrote:
> Am 12.06.2007 15:40 schrieb Dennis Winter:
>> On 12 Jun., 15:24, Christian Kirsch wrote:
>>> *gibt* es denn Performanceprobleme?
>> Ja, die gibt es. In unregelmässigen Abständen hängt sich MySQL bei
>> vielen Anfragen auf. Nach einem Restart des Dämons (wenn es klappt,
>> andernfalls Reboot) läuft alles wieder ordentlich. Nur ist dann
>> gelegentlich die eine oder andere große Tabelle gecrasht. Ein Repair
>> dauert bei diesen Tabellen dann bis zu 25 Min.
>>
>
> Und die fraglichen Anfragen hast Du Dir schon mit EXPLAIN angesehen,
> es gibt keine brauchbare Fehlermeldung etc.?
>
> Was Du da beschreibst, hört sich jedenfalls weniger nach einem
> Performance- als nach einem Stabilitätsproblem an. Dem sollte man
> vielleicht auf den Grund gehen.
>
>>>> - Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?
>>> Das dürfte von den Daten und den Abfragen abhängen. Du kannst nach
>>> Jahreszahlen partitionieren oder nach Postleitzahlen, vielleicht auch
>>> nach Schuhgrößen.
>> Da meine Elemente keine Schuhe haben, wie wäre es mit dem
>> Primärschlüssel? Also einem Autoinkrement-Wert?
>>
>
> Warum nicht. Andererseits: Warum? Ich habe gerade mal bei Oracle
> nachgelesen. Die haben, wenn ich das richtig verstanden habe, drei
> Verfahren: "partition by range", bei dem Du selber die Wertebereiche
> einer Spalte vorgibst, anhand dann die Tabelle zerlegt wird. Oder
> "Partition by hash". In diesem Fall legst Du die Anzahl der
> Partitionen und die Spalte fest und Oracle benutzt eine Hashfunktion
> auf der angegebenen Spalte, die die Daten möglichst gleichmäßig auf
> die Partitionen verteilt. Als drittes dannList-Partitionierung, wenn
> man Enumerations oder ähnliches hat.

Wenn ich mich richtig erinnere, kannst du sogar über mehrere Spalten hashen.

> Was ich bislang darüber gelesen habe, ging in der Regel von einer
> semantischen Bedingung aus (also *nicht* dem PK) - sowas wie eine
> Tabelle pro Jahr oder eine pro PLZ-Bereich oder so. Wenn natürlich
> Deine Anfragen alle ohnehin den PK im WHERE benutzen, dann kannst Du
> auch danach partitionieren. Wenn es aber um andere Kriterien geht,
> dann bringt die Partitionierung nach dem PK m.E. wenig.

Ich bin mir nicht sicher, was du mit "semantischer Bedingung" meinst.
Partitionierung ist i.d.R. dafür da, große Datenmengen handhabbar zu
halten. Da geht es um das physische Schema vs. dem logischen Schema
(das ändert sich durch Partitionierung ja auch nicht). Oracle kann z.B.
partition pruning, d.h., wenn der Optimizer feststellt, dass die
gefragten Daten nur in n Partitionen sein können, schaut er auch nur die
an. Ein weiterer Vorteil ist i.d.R., dass man ganze Partitionen droppen
kann, was viel schneller geht, als die Datensätze zu löschen.

Das Anlegen (und manchmal auch Wegschmeißen) von Partitionen ist i.d.R.
Wartungsarbeit. Wenn man nach Zeitstempeln partitioniert, macht es auch
Sinn, einen cron-Job darauf anzusetzen, der einmal pro Tag / Woche /
Monat eine neue Partition erzeugt. Bei Hash-Partitionierung (in Oracle)
kann man keine neuen Partitionen hinzufügen, wenn ich mich recht
erinnere. Das macht auch keinen Sinn, wenn man sich mal überlegt, wie
Hash-Partitionierung arbeitet. :-)

Da wir hier keine Infos über die Daten haben, ist es schwer zu einer
Partitionierungsstrategie zu raten. Dafür sollte man die Verteilung der
Daten sowie CRUD-Operationen berücksichtigen.

Ciao

robert

Re: Partitionierung großer Tabellen

Robert Klemme schrieb:

> Ich bin mir nicht sicher, was du mit "semantischer Bedingung" meinst.

"inhaltlich", im Gegensatz zu "formal", wenn Du so willst. Also "Jahr"
statt "Primary key". Letzterer ist ja in der Regel irgendwas
synthetisches, das keinerlei Bedeutung hat.

Re: Partitionierung großer Tabellen

[posted & mailed]

Das hier könnte noch interessant sein:

http://datacharmer.blogspot.com/2007/06/use-51-partitions-in -production-today.html

Re: Partitionierung großerTabellen

Dennis Winter wrote:
> Habe ich getan und keine brauchbare Fehlermeldung und damit keine
> Quelle gefunden ausser, dass die Kiste performancemässig nicht mehr
> mitkommt. Wenn es soweit ist, schießt die Prozessorlast für MySQL in
> die Höhe, sodass der Kernel nicht mal mehr Logfiles schreibt. Das is
> halt Essig fürs debuggen...

Was sagt mysqladmin processlist in diesem Augenblick? Reicht es, wenn Du
evtl. die fragliche Connection killst? Was sagen iostat und vmstat in
derartigen Momenten?

Tendenziell klingt es fuer mich, als waere da ein ganz anderes Problem
(irgendwelche Queries, die den kompletten Datenbestand in temporary tables
sortieren und Dir das IO-Subsystem damit plattmachen, o.ae.), dass durch d=
ie
Partitionierung sich nicht ploetzlich magisch loest, sondern eher noch
bloeder werden koennte, weil ich mir schon vorstellen kann, dass JOINs etc=
..
dann teurer sind.

Re: Partitionierung grosser Tabellen

Dennis Winter wrote:
>
> ich habe hier bei einigen Webanwendungen teilweise Tabellen von knapp
> 3GB Größe. Auf einem ordentlich ausgestattetem Server läuft auf SuSE
> Linux 9.3 eine MySQL 5.0.18 for i686 Installation.
>
> Nun würde ich aus verständlichen Performancegründen gerne die großen
> Tables partitionieren.

Partitionierung löst Performanceprobleme nur in wenigen Spezialfällen.
Im wesentlichen dann, wenn
- kein Index benutzt werden kann
- eine Operation sich nur auf eine (oder einige wenige) Partition(en)
einer Tabelle beschränkt.

Wenn - wie du später schreibst - die Datenbank erst langsam wurde als
nennenswert Daten darin lagen, ist es wahrscheinlich daß die Queries
einfach nur keine Indexe nutzen und deswegen langsamer werden.

Es ist dann wesentlich sinnvoller, Indexe hinzuzufügen (und notfalls
Queries so umzuformulieren, daß sie die auch benutzen können) als die
Daten - anscheinend auch noch recht planlos - zu partitionieren.

> Da das aber ein Livesystem ist, möchte ich
> keine Experimente mit Betas machen.
>
> - Wer hat denn Erfahrungen mit Partitioning unter MySQL 5.1.19.0? Ist
> das da wirklich stable?

Die ganze 5.1 Release ist Beta. Mehr muß man dazu eigentlich nicht
sagen. Schau dir halt auf bugs.mysql.com an, ob da Bugs dabei sind,
die dich beißen könnten. Über bisher unbekannte Bugs kann man natur-
gemäß noch nichts sagen...

> - Wie gehe ich eine Partitionierung der Großen Tabellen richtig an?

Zuerst mal müßtest du analysieren, welche Queries genau langsam sind
und warum. Danach könnte man überlegen, ob partitionieren da hilft.

Mit dem Ansatz aus Giuseppes Blog (der Link den Christian gepostet hat)
kannst du ja recht einfach mal probieren, ob 5.1 mit Partitionen über-
haupt etwas bringt.


XL