Leistungseinbruch

Hallo,

meine Datenbank (Tabelle) enthält im Moment ca. 1,1 Mio Datensätze. Ich
benutze ein PHP-Script um in die DB regelmäßig Updates einzuspielen.

Heute habe ich jedoch einen ziemlichen Leistungseinbruch bei der
Updategeschwindigkeit feststellen müssen. In den Statusvariablen haben
sich folgende Werte während des Updatelaufes permanent erhöht:

Handler_read_rnd 428 k
Anzahl der Anfragen, eine Zeile basierend auf einer festen Position zu
lesen. Dieser Wert wird hoch sein, wenn Sie viele Anfragen ausführen,
die erfordern, dass das Ergebnis sortiert wird. Wenn Handler_read_rnd
hoch ist, haben Sie wahrscheinlich viele Anfragen, die MySQL zwingen,
ganze Tabellen zu scannen, oder Sie haben Joins, die Schlüssel nicht
richtig benutzen.

Handler_read_rnd_next 79 M
Anzahl der Anfragen, die nächste Zeile in der Daten-Datei zu lesen.
Dieser Wert wird hoch sein, wenn Sie viele Tabellen-Scans durchführen.
Im Allgemeinen weist das darauf hin, dass Ihre Tabellen nicht korrekt
indiziert sind, oder dass Ihre Anfragen nicht so geschrieben sind, dass
Sie Vorteile aus den Indexen ziehen, die Sie haben.


Bis vor kurzem funktionierte das Script noch wunderbar und im Vergleich
zu jetzt rasend schnell. Es tut im Prinzip nur folgendes:

- Select Datensatz from Tabelle (unter Verwendung von Indizes)
- wenn Datensatz bereits vorhanden ist, aktualisiere diesen
- wenn Datensatz noch nicht vorhanden ist, füge diesen neu ein

Hier ist der wichtigste Teil meiner MySQL-Konfiguration (MySQL 5.0.18).

key_buffer = 768M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 256M
thread_cache_size = 32M
thread_stack = 128K
max_connections = 1000

query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1

log-slow-queries = /var/log/mysql/mysql-slow.log

Der Server verfügt über 2GB RAM. Die betroffene Tabelle hat folgende Größen:
Daten 412.954 KB
Index 482.798 KB

Im Logfile der Slow-Queries sind jetzt auch erstmalig einige
Update-Anweisungen des Updatescriptes erschienen.

Jetzt die Frage: Wie ist der Leistungseinbruch zu erklären? Kann ich
durch Änderung der Systemvariablen etwas verbessern? Oder muss ich
mühsam meine gesetzten Indizes überprüfen (obwohl mit weniger Daten ja
noch alles wunderbar funktioniert hat?).


Gruß,
Stefan

Re: Leistungseinbruch

Hi Stefan

Stefan Gläßer wrote:
> Bis vor kurzem funktionierte das Script noch wunderbar und im Vergleich
> zu jetzt rasend schnell. Es tut im Prinzip nur folgendes:
> - Select Datensatz from Tabelle (unter Verwendung von Indizes)
> - wenn Datensatz bereits vorhanden ist, aktualisiere diesen
> - wenn Datensatz noch nicht vorhanden ist, füge diesen neu ein

Warum verwendest du nicht einen einfachen REPLACE?

> Jetzt die Frage: Wie ist der Leistungseinbruch zu erklären? Kann ich
> durch Änderung der Systemvariablen etwas verbessern? Oder muss ich
> mühsam meine gesetzten Indizes überprüfen (obwohl mit weniger Daten ja
> noch alles wunderbar funktioniert hat?).

Keine Ahnung, sorry.

HTH, Johannes

Re: Leistungseinbruch

Hi Johannes,
> Warum verwendest du nicht einen einfachen REPLACE?

in den Kommentaren auf
http://dev.mysql.com/doc/refman/5.0/en/replace.html steht folgendes:

Please note that REPLACE INTO is a much slower performer than an UPDATE
statement. Keep in mind that a REPLACE INTO requires a test on the keys,
and if a matching unique key is found on any or all columns, a DELETE
FROM is executed, then an INSERT is executed. There's a lot of
management of rows involved in this, and if you're doing it frequently,
you'll hurt your performance unless you simply cannot do with any other
syntax.

Wenn dem in MySQL 5.0.18 noch immer so ist, bleibe ich lieber bei meinen
Update-Anweisungen.


Gruß,
Stefan

Re: Leistungseinbruch

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Re: Leistungseinbruch

Stefan Gläßer wrote:
> Hallo,
>
> meine Datenbank (Tabelle) enthält im Moment ca. 1,1 Mio Datensätze. Ich
> benutze ein PHP-Script um in die DB regelmäßig Updates einzuspielen.
>
> Heute habe ich jedoch einen ziemlichen Leistungseinbruch bei der
> Updategeschwindigkeit feststellen müssen.

Zahlen? Wie schnell vorher, wie schnell nachher?

> In den Statusvariablen haben
> sich folgende Werte während des Updatelaufes permanent erhöht:
>
> Handler_read_rnd 428 k
> Handler_read_rnd_next 79 M

Das sind Zähler. Interessant wäre die Veränderung dieser Werte, während
dein Skript läuft. Noch interessanter wären generelle Aussagen zur
Auslastung der Maschine. CPU-, RAM-, Plattenaktivität.

> Bis vor kurzem funktionierte das Script noch wunderbar und im Vergleich
> zu jetzt rasend schnell.

Wenn sich sonst nichts geändert hat (hat es?) könnte die Größe deiner
Tabelle einen kritischen Punkt erreicht haben. Letzteres ist aber
erstens unwahrscheinlich und würde zweitens auch eher zu einem
schleichenden Rückgang der Geschwindigkeit führen.

Vielleicht ist es ja was triviales: kaputter Index? Was sagt CHECK
TABLE? Oder ist das Datenfile fragmentiert? Wird es nach OPTIMIZE
TABLE besser?

> - Select Datensatz from Tabelle (unter Verwendung von Indizes)
> - wenn Datensatz bereits vorhanden ist, aktualisiere diesen
> - wenn Datensatz noch nicht vorhanden ist, füge diesen neu ein

Schlechte Vorgehensweise. Mein Vorschlag:

1. UPDATE Datensatz
2. Wenn Fehler (Datensatz existiert nicht), dann INSERT

Oder (besser) gleich INSERT ... ON DUPLICATE KEY UPDATE ...

Trotzdem wäre es interessant, bei welchem der drei Schritte denn nun
die meiste Zeit draufgeht? Und hat sich vielleicht der Mix UPDATE /
INSERT gegenüber vorher verändert? INSERT ist u.U. viel schneller als
UPDATE.

> key_buffer = 768M
> Der Server verfügt über 2GB RAM. Die betroffene Tabelle hat folgende Größen:
> Daten 412.954 KB
> Index 482.798 KB

Das ist soweit ganz in Ordnung. Der Index paßt komplett in den
key_buffer, alle Index-Operationen sollten also schnell gehen.
Wenn du viele UPDATEs machst, bekommst du natürlich heftig I/O
auf dem Datenfile.

> Jetzt die Frage: Wie ist der Leistungseinbruch zu erklären?

Zu wenig Information.

> Oder muss ich
> mühsam meine gesetzten Indizes überprüfen (obwohl mit weniger Daten ja
> noch alles wunderbar funktioniert hat?).

UPDATE/INSERT werden durch Indizes möglicherweise langsamer (INSERT
immer, UPDATE vor allem, wenn indizierte Spalten verändert werden).


XL

Re: Leistungseinbruch

Andreas Kretschmer wrote:
>> Please note that REPLACE INTO is a much slower performer than an UPDATE
>> statement. Keep in mind that a REPLACE INTO requires a test on the keys,
>> and if a matching unique key is found on any or all columns, a DELETE
>> FROM is executed, then an INSERT is executed.

> Klar, ne. Wenn Du das mit PHP nachbaust, ist das gaaanz dolle schneller
> als wenn das direkt die DB macht.

[ ] du hast den von dir gequoteten Text gelesen und mit dem im OP
beschriebenen Vorgehen verglichen

ein UPDATE von Applikationsseite ist i.a. schon schneller als ein
Server-internes DELETE&INSERT

--
Hartmut Holzgraefe, Senior Support Engineer .
MySQL AB, www.mysql.com

http://www.mysql.com/support/

Re: Leistungseinbruch

Hallo Andreas,

> Klar, ne. Wenn Du das mit PHP nachbaust, ist das gaaanz dolle schneller
> als wenn das direkt die DB macht.

Ich habe es jetzt mal testweise auf Replace into geändert. Hat auch
erstmal funktioniert. Allerdings möchte ich meinen Primärschlüssel
erhalten, was hier aber nicht geklappt hat. Alle Datensätze wurden
erfolgreich aktualisiert, wobei aber jeder Datensatz eine neue ID
(Primärschlüssel) erhalten hat.

Kann man dieses Verhalten noch beeinflussen?

Stefan

Re: Leistungseinbruch

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Re: Leistungseinbruch

Hallo Axel,

> Zahlen? Wie schnell vorher, wie schnell nachher?

Lässt sich leider nur sehr schlecht messen. Es wurde aber definitiv
langsamer.

> Das sind Zähler. Interessant wäre die Veränderung dieser Werte, während
> dein Skript läuft. Noch interessanter wären generelle Aussagen zur
> Auslastung der Maschine. CPU-, RAM-, Plattenaktivität.

7520 aktualisierte Datensätze

Zähler vorher: Handler_read_rnd 660k Handler_read_rnd_next 108M
Zähler nachher: Handler_read_rnd 664k Handler_read_rnd_next 109M

CPU-Auslastung während des Updates: 10% id 90% wa
RAM-Auslastung während des Updates: 15MB frei, kein Swapping
Plattenaktivität: Wie mess ich denn das?

Beim ausführen des Updatescriptes haben sich die Zähler permanent
erhöht. Außerdem hatte ich nach ca. 15 Minuten ein
Load von ca. 2.0, 1.5, 1.0

Nach ein paar Minuten pegelt sich der Load dann wieder ein bei ca.
0.08, 0.05, 0.00
D.h. der Datenbankserver hat quasi kaum etwas zu tun.

> Wenn sich sonst nichts geändert hat (hat es?) könnte die Größe deiner
> Tabelle einen kritischen Punkt erreicht haben. Letzteres ist aber
> erstens unwahrscheinlich und würde zweitens auch eher zu einem
> schleichenden Rückgang der Geschwindigkeit führen.

Das Script hat sich nicht geändert.

> Vielleicht ist es ja was triviales: kaputter Index? Was sagt CHECK
> TABLE? Oder ist das Datenfile fragmentiert? Wird es nach OPTIMIZE
> TABLE besser?

Nein es wird leider nicht besser.

> Schlechte Vorgehensweise. Mein Vorschlag:
>
> 1. UPDATE Datensatz
> 2. Wenn Fehler (Datensatz existiert nicht), dann INSERT

Das werde ich mal probieren.

> Oder (besser) gleich INSERT ... ON DUPLICATE KEY UPDATE ...

Wie funktioniert das genau? Die Tabelle hat einen Primärschlüssel auf
der ID und einen UniqueKey bestehen aus einem INT(3) und einem
VARCHAR(30). Wenn die INT(3)+VARCHAR(30) Kombination in der Updatedatei
vorkommmt, soll der Datensatz aktualisiert werden. Kommt er nicht vor,
soll er neu eingefügt werden.

> Trotzdem wäre es interessant, bei welchem der drei Schritte denn nun
> die meiste Zeit draufgeht? Und hat sich vielleicht der Mix UPDATE /
> INSERT gegenüber vorher verändert? INSERT ist u.U. viel schneller als
> UPDATE.

Nein das ist konstant geblieben.

> UPDATE/INSERT werden durch Indizes möglicherweise langsamer (INSERT
> immer, UPDATE vor allem, wenn indizierte Spalten verändert werden).

Es werden definitiv bei jedem Update/Insert die Indizes verändert.


Gruß,
Stefan

Re: Leistungseinbruch

Andreas Kretschmer schrieb:
> begin Hartmut Holzgraefe schrieb:
>
>>Andreas Kretschmer wrote:
>>
>>>>Please note that REPLACE INTO is a much slower performer than an UPDATE
>>>>statement. Keep in mind that a REPLACE INTO requires a test on the keys,
>>>>and if a matching unique key is found on any or all columns, a DELETE
>>>>FROM is executed, then an INSERT is executed.
>>
>>>Klar, ne. Wenn Du das mit PHP nachbaust, ist das gaaanz dolle schneller
>>>als wenn das direkt die DB macht.
>>
>>[ ] du hast den von dir gequoteten Text gelesen und mit dem im OP
>> beschriebenen Vorgehen verglichen
>>
>>ein UPDATE von Applikationsseite ist i.a. schon schneller als ein
>>Server-internes DELETE&INSERT
>
>
> Sagen wir mal so:
>
> Ein 'REPLACE INTO' hat PostgreSQL nicht, hier würde man sich so behelfen
> wie im Beispiel ganz unten auf
> http://developer.postgresql.org/docs/postgres/plpgsql-contro l-structures.html.

Sollen wir jetzt hier erstmal die halbe Doku von PostgreSQL durchlesen?
>
> Und da vermute ich mal, daß man daß nicht schneller bekommt, wenn man
> versucht, das außerhalb der DB mit PHP oder wie auch immer nachzubilden.

Du hast leider REPLACE INTO und dessen Vorgehensweise und die
Vorgehensweise des OP nicht verglichen. Sonst würdest du sehen, dass es
in dem einen Fall ein DELETE und ein INSERT und in dem anderen Fall nur
ein UPDATE stattfindet. Letzteres ist auf jeden Fall von Vorteil.

Und das Post von Hartmut hast du ignoriert ... wieso?

Grüße

Dirk

Re: Leistungseinbruch

Stefan Gläßer schrieb:
> Hallo Andreas,
>
>> Klar, ne. Wenn Du das mit PHP nachbaust, ist das gaaanz dolle schneller
>> als wenn das direkt die DB macht.
>
>
> Ich habe es jetzt mal testweise auf Replace into geändert. Hat auch
> erstmal funktioniert. Allerdings möchte ich meinen Primärschlüssel
> erhalten, was hier aber nicht geklappt hat. Alle Datensätze wurden
> erfolgreich aktualisiert, wobei aber jeder Datensatz eine neue ID
> (Primärschlüssel) erhalten hat.
>
> Kann man dieses Verhalten noch beeinflussen?

Dieses Verhalten kenne ich von REPLACE INTO nicht. Aus meiner Sicht
kannst du es nur falsch verwendet haben. Selbstverständlich bleiben PK's
voll und ganz erhalten. Zeig mal bitte das Create-Statement der Tabelle
und einen beispielhaften REPLACE-Befehl. Danke

Grüße

Dirk

Re: Leistungseinbruch

Axel Schwenke schrieb:
>
>>key_buffer = 768M
>>Der Server verfügt über 2GB RAM. Die betroffene Tabelle hat folgende Größen:
>>Daten 412.954 KB
>>Index 482.798 KB
>
>
> Das ist soweit ganz in Ordnung. Der Index paßt komplett in den
> key_buffer, alle Index-Operationen sollten also schnell gehen.

Anmerkung: Hierbei ist aber zu beachten, dass zumindestens zu dem
Zeitpunkt keine anderen Tabellen verwendet werden dürfen. Also gar nix
anderes, wenn möglich.

Grüße

Dirk

Re: Leistungseinbruch

Stefan Gläßer schrieb:

>
> Jetzt die Frage: Wie ist der Leistungseinbruch zu erklären? Kann ich
> durch Änderung der Systemvariablen etwas verbessern? Oder muss ich
> mühsam meine gesetzten Indizes überprüfen (obwohl mit weniger Daten ja
> noch alles wunderbar funktioniert hat?).

Auch wenn es wahrscheinlich nichts bringt, würde ich mal den explain
anschaun und evtl. den Daemon dazu zwingen bestimmte Indizes zu nutzen.
Evtl. meint er, dass bei der Menge der Daten und irgendwelche
Sonnenflecken besser keine Indeizes mehr verwenden soll.

Grüße

Dirk

Re: Leistungseinbruch

=?ISO-8859-1?Q?Stefan_Gläßer?= wrote:
> Hallo Axel,
>
>> Zahlen? Wie schnell vorher, wie schnell nachher?
>
> Lässt sich leider nur sehr schlecht messen. Es wurde aber definitiv
> langsamer.

Wenigstens eine Schätzung? Statt 20 Minuten jetzt 25? Oder dauert es
doppelt so lange, zehnmal so lange?

>> Das sind Zähler. Interessant wäre die Veränderung dieser Werte, während
>> dein Skript läuft. Noch interessanter wären generelle Aussagen zur
>> Auslastung der Maschine. CPU-, RAM-, Plattenaktivität.
>
> 7520 aktualisierte Datensätze
>
> Zähler vorher: Handler_read_rnd 660k Handler_read_rnd_next 108M
> Zähler nachher: Handler_read_rnd 664k Handler_read_rnd_next 109M

Hmm. 4K wahlfreies Lesen, 1M Rows gesamt Lesen. Also im Schnitt scans
über jeweils 250 Rows. Kommen zu den 7520 "aktualisierten" Datensätzen
auch noch nicht-aktualisierte dazu? Wieviele SQL-Statements waren das
insgesamt?

> CPU-Auslastung während des Updates: 10% id 90% wa

Du hast ganz klar ein I/O-Problem. Deine CPU verbringt 90% ihrer Zeit
damit, auf I/O zu warten. Gibt es sonst Auffälligkeiten? Hast du ins
syslog und die Kernel-Messages (dmesg) geschaut?

> RAM-Auslastung während des Updates: 15MB frei, kein Swapping

Wenn du 750MB key_buffer konfiguriert hast, sollten ca. 1GB für den
Kernel-Pagecache übrig sein. Das ist eigentlich perfekt so.

> Plattenaktivität: Wie mess ich denn das?

man iostat

und wenn du schon mal dabei bist: man memstat, man sar

Vielleicht gefällt dir auch mein Monitoring-Tool:
http://24days.de/~schwenke/asing/

> Beim ausführen des Updatescriptes haben sich die Zähler permanent
> erhöht. Außerdem hatte ich nach ca. 15 Minuten ein
> Load von ca. 2.0, 1.5, 1.0
>
> Nach ein paar Minuten pegelt sich der Load dann wieder ein bei ca.
> 0.08, 0.05, 0.00
> D.h. der Datenbankserver hat quasi kaum etwas zu tun.

Das würde ich als typischen "erst geht alles aus dem Cache, dann läuft
der voll und ich muß auf I/O warten" Fall ansehen.

Allerdings paßt das nicht zu deinen Zahlen. Mit 400MB Datenfile und
400MB Index, paßt das beides problemlos ins RAM.

>> INSERT ... ON DUPLICATE KEY UPDATE ...
>
> Wie funktioniert das genau?

So wie es im Handbuch steht? Gibts seit 4.1.


XL