Performance-Problem: lokal schnell, auf Server langsam

[Dave_Bowman]

Hallo,

ich habe vor längerem eine SP geschrieben, die jedoch zu langsam lief auf dem Server. Also habe ich mir die jetzt nochmal vorgenommen und sie auf meinem lokalen Rechner (i5@3,2GHz, 4GB RAM, SSD) optimiert. Das Ergebnis war ok, von ca. 3,5 Sekunden konnte ich die Geschwindigkeit auf ca. 0,8 Sekunden drücken.

Jetzt habe ich das natürlich auch auf dem Live-Server (Phenom II X6 @ 2,8GHz, 8GB RAM, HDD, MySQL auf InnoDB hin konfiguriert) probieren wollen, und stelle enttäuscht fest, dass sich dort die Performance quasi nicht geändert hat, die Abfrage dauert nach wie vor zwischen 3 und 5 Sekunden. Und so richtig viel ist auf dem Server auch nicht los, SHOW PROCESS nach zu beurteilen.

Die Prozedur macht eine Reihe von Auswertungen auf einer MEMORY-Tabelle mit ca. 320.000 Datensätzen (Einsatz von Indizes kaum möglich, da Berechnungen notwendig bei jeder Zeile) und generiert daraus eine Treffermenge von um die 100 Sätzen. Maßgeblich konnte ich lokal die Geschwindigkeit drücken, weil ich die MEMORY-Tabelle optimiert habe, sodass sie jetzt nur noch aus numerischen Werten (sieht man von einem Char(5)-Feld für eine PLZ ab) besteht.

Aber weshalb ist die nach wie vor so langsam auf dem Server? Das einzige, was mir einfällt, ist die SSD, auf der - keine Ahnung - vielleicht temporäre Tabellen angelegt werden müssen für die GROUP BY-Klauseln oder so. Nur wenn das stimmt, weshalb werden die dann auf dem Server nicht im Hauptspeicher angelegt, der sollte doch wohl reichen.

Ich habe auf beiden Rechnern mal FLUSH STATUS; CALL SP; SHOW STATUS; ausgeführt, hier die Ergebnisse (alle fehlenden Einträge waren bei beiden 0):

	Server	lokal
Binlog_cache_use	7	0
Bytes_received	251	253
Bytes_sent	6477	6461
Com_call_procedure	1	1
Com_create_table	2	2
Com_dealloc_sql	1	1
Com_drop_table	2	2
Com_execute_sql	3	3
Com_insert_select	1	1
Com_prepare_sql	3	3
Com_select	1	1
Com_set_option	8	8
Com_show_status	1	1
Com_show_warnings	1	1
Com_stmt_close	3	3
Com_stmt_execute	3	3
Com_stmt_prepare	3	3
Compression	OFF	OFF
Connections	25872	10
Created_tmp_disk_tables	0	0
Created_tmp_files	5	3
Created_tmp_tables	2	2
Flush_commands	7	1
Handler_commit	2	2
Handler_read_first	1	1
Handler_read_key	328977	328314
Handler_read_rnd	200	200
Handler_read_rnd_next	498157	497357
Handler_update	210475	209887
Handler_write	118561	118455
Innodb_buffer_pool_pages_data	133961	1934
Innodb_buffer_pool_pages_dirty	63	21
Innodb_buffer_pool_pages_flushed	9357936	5612
Innodb_buffer_pool_pages_free	123069	4388
Innodb_buffer_pool_pages_misc	5114	206
Innodb_buffer_pool_pages_total	262144	6528
Innodb_buffer_pool_read_ahead	65570	655
Innodb_buffer_pool_read_ahead_evicted	0	0
Innodb_buffer_pool_read_requests	1417810067	3439718
Innodb_buffer_pool_reads	53772	1241
Innodb_buffer_pool_wait_free	0	0
Innodb_buffer_pool_write_requests	97464786	140624
Innodb_data_fsyncs	1799944	2095
Innodb_data_pending_fsyncs	1	0
Innodb_data_pending_reads	0	0
Innodb_data_pending_writes	0	0
Innodb_data_read	2003341312	34820096
Innodb_data_reads	122340	2318
Innodb_data_writes	6054775	4603
Innodb_data_written	65496576	101527040
Innodb_dblwr_pages_written	4678968	2806
Innodb_dblwr_writes	114589	141
Innodb_have_atomic_builtins	ON	ON
Innodb_log_waits	0	0
Innodb_log_write_requests	11006398	20430
Innodb_log_writes	739449	772
Innodb_os_log_fsyncs	794087	845
Innodb_os_log_pending_fsyncs	1	0
Innodb_os_log_pending_writes	0	0
Innodb_os_log_written	1336423936	9541120
Innodb_page_size	16384	16384
Innodb_pages_created	626780	1168
Innodb_pages_read	122140	1895
Innodb_pages_written	4678968	2806
Innodb_row_lock_current_waits	2	0
Innodb_row_lock_time	20661496	0
Innodb_row_lock_time_avg	273	0
Innodb_row_lock_time_max	51448	0
Innodb_row_lock_waits	75516	0
Innodb_rows_deleted	2453741	92
Innodb_rows_inserted	16559872	52014
Innodb_rows_read	1427194360	828826
Innodb_rows_updated	12554937	3400
Innodb_truncated_status_writes	0	0
Key_blocks_not_flushed	0	0
Key_blocks_unused	260799	44216
Key_blocks_used	13147	4152
Key_read_requests	45	0
Key_reads	0	0
Key_write_requests	0	0
Key_writes	0	0
Last_query_cost	220.999000	220.999000
Max_used_connections	50	4
Not_flushed_delayed_rows	0	0
Open_files	84	2
Open_streams	0	0
Open_table_definitions	244	40
Open_tables	242	4
Opened_files	1713197	2327
Opened_table_definitions	4	4
Opened_tables	4	4
Queries	73355656	10528
Questions	3	3
Rpl_status	AUTH_MASTER	AUTH_MASTER
Select_scan	4	4
Slave_running	OFF	OFF
Slow_launch_threads	0	0
Slow_queries	0	1
Sort_merge_passes	4	4
Sort_range	0	0
Sort_rows	200	200
Sort_scan	2	2
Table_locks_immediate	268	7
Table_locks_waited	0	0
Threads_cached	2	1
Threads_connected	50	4
Threads_created	2207	5
Threads_running	6	1
Uptime	638157	22914
Uptime_since_flush_status	4	1

Die einzigen großen Unterschiede sehe ich im InnoDB-Block, weiß aber nicht, ob diese Werte beim FLUSH vielleicht gar nicht zurückgesetzt werden. Ich kann das nicht richtig deuten.

Kann mir jemand sagen, woran es liegt, dass mein Entwicklungsrechner mit einer 0815-MySQL-Konfiguration so viel schneller bei der Abarbeitung einer SP ist als ein Datenbank-Server (Query-Cache ist bei beiden ausgestellt)?

Ich bin für jeden Rat oder Hinweis dankbar ...


Dave

[defo]

Die aufgelisteten Werte sagen mir wenig, da ich nicht mit mySQL arbeite.
Immerhin, Dein Prod Server hat offenbar etwas mehr zu tun und vlt nicht genug RAM für die Aufgabe. Sortierungen gehen dann z.B. auf das Filesystem..
Ich würde über debug log der SP erstmal feststellen, welche OPs auf beiden System die Zeitfresser sind. Die SQL Statements dazu dann auf Ihren Ausführungsplan untersuchen lassen und entsprechend optimieren.

[Dave_Bowman]

Hallo defo,

was meinst Du mit debug log? Mir ist da nichts bekannt, ich finde da auch nichts weiter. Ich kann zusätzliche Zeiten mit ausgeben in den Zwischenschritten, nur nutzen die unter MySQL nicht so viel, da sie nur sekundengenau und nicht millisekundengenau sind.

Und die Statements bzw. Operationen, die länger brauchen, habe ich identifiziert ... und - so dachte ich - optimiert. Wie gesagt, ich bin lokal bei ca. 0,8 Sekunden. Und dieselbe Prozedur mit denselben Tabellen (sind nur zwei MEMORY-Tabellen) brauchen dann eben auf dem Server so viel länger.

Was kann ich tun? Was meinst Du mit debug log?

Dave

[defo]

Ich meinte Du kannst in Deiner SP Zwischenzeiten rausschreiben. Dass es diese Genauigkeitsprobleme gibt, war mir nicht bekannt. Es gibt aber sowas wie fn microseconds(timestamp), hab ich mal gesehen.
Wenn Du Dir sowieso sicher bist, welche Statements fragwürdig sind, dann muss dort eben direkt der Ausführungsplan analysiert werden. Das erscheint mir vielversprechender, als "auf Verdacht" die Basistabelle zu verschlanken (Was bei dem Volumen natürlich nicht verkehrt sein kann).

[Dave_Bowman]

Hi defo,

Danke für Deine Nachricht.
Dass man da mit einer Funktion auch Millisekunden messen kann, habe ich auch gelesen. Nur es nicht ganz verstanden, und soweit ich es mitbekommen habe, waren das auch Funktionen für Unix-Systeme, wir betreiben MySQL unter Windows. Irgendwie überstieg das meinen Horizont, ich habe keine Ahnung von Perl-Scripten, Unix-Programmierung oder was auch immer das war.

Und wie gesagt, optimiert sind die Tabellen eigentlich. Und auch das SQL. Ich hätte sonst keine 0,8 Sekunden auf meinem Rechner. Unter anderem habe ich Koordinaten in der MEMORY-Tabelle, und die Ergebnisse sind nach Entfernung sortiert. Das heißt, dass ich da auch nicht so viel mit Indizes reissen kann, da ja die Entfernung für jede Zeile berechnet werden muss. Aber ganz allgemein bin ich ja mit der Abarbeitunggeschwindigkeit zufrieden - auf meinem Rechner. Oder nimmst Du an, dass der Ausführungsplan auf meinem Rechner ein anderer ist als auf dem Live-System? Wie gesagt, gleiche Tabellen, gleiche Prozedur.

[defo]

Ja, ich nehme an, dass es Unterschiede gibt. Faktisch gibt es ja andere Laufzeiten. Ich bin in mySQL nicht wirklich fit. Daher weiß ich nicht, wie der Optimizer dort arbeitet. Von Oracle kenne ich es so, dass nicht nur das Statement, die Tabellen und Indexstruktur berücksichtigt wird, sondern auch das Volumen und die Inhalte.
Die letzten beiden Aspekte sind offensichtlich dynamisch und werden von System zyklisch oder manuell zusammengestellt. Sind diese Werte veraltet oder existieren gar nicht, kommt der Optimizer zu falschen oder ungünstigen Ergebnissen.
Langer Rede kurzer Sinn: Lass Dir auf beiden Systemen den Ausführungsplan ausgeben und Du hast schwarz auf weiß, ob es Unterschiede gibt. Wenn nicht, kannst Du die Stelle abhaken und Dich auf Systemparameter Tuning oder andere Flaschenhälse konzentrieren, ansonsten musst Du Deine Optimierungen optimieren.

[Dave_Bowman]

Also, ich bin mit meinem Latein am Ende.

An der SSD bzw. HDD wird's wohl auch nicht liegen, denn gebe ich SHOW STATUS LIKE 'Created\_tmp\_%'; vor und nach dem Aufruf der SP auf beiden Rechnern ein, sehe ich, dass er nichts auf Platte schreibt, zwei temp. Tables anlegt und auf dem Server bis zu 7, lokal 3 temp. Files erstellt.

Ich habe mir jetzt mal ein exemplarisches SELECT aus der SP rausgegriffen. Deren Ausführungspläne sind lokal und auf dem Server identisch:
1, SIMPLE, smf, ALL, , , , , 328836, 100.00, Using temporary; Using filesort
Er kann keine Indizes nutzen aus den genannten Gründen. Auf dem Server benötigt die Ausführung zwischen 0,6 und 0,85 Sekunden, lokal braucht er lediglich 0,2 Sekunden. Allein bei diesem SELECT werden bei beiden 1 temp. Table angelegt und lokal 2 temp. Files und auf dem Server 4.

Entweder der Server ist deutlich langsamer als mein lokaler Rechner (was ich nicht glaube, zumindest nicht 50% langsamer), oder es liegt irgendwie an den temp. Files. Weshalb das bei einem einzelnen SELECT deutlich mehr sind, weiß ich nicht, wodurch das beeinflusst wird, auch nicht. Der Server hat natürlich ein wenig mehr zu tun als mein Rechner, aber ob sich das dann so massiv auf die Performance auswirkt ... ich weiß nicht.

[defo]

Vielleicht solltest Du mal in der Richtung prüfen:

MEMORY-Tabellen speichern ihren Inhalt im Arbeitsspeicher, eine Eigenschaft, die sie mit internen Tabellen gemeinsam haben, die der Server bei der Verarbeitung von Anfragen nebenbei anlegt. Die beiden Tabellentypen unterscheiden sich jedoch darin, dass MEMORY-Tabellen im Gegensatz zu den internen Tabellen nicht von Speicherkonvertierung betroffen sind:

Wenn eine interne Tabelle zu groß wird, konvertiert der Server sie automatisch in eine Festplattentabelle. Deren maximale Größe wird durch die Systemvariable tmp_table_size festgelegt.

MEMORY-Tabellen werden nie in Festplattentabellen konvertiert. Um sicherzustellen, dass Sie nicht versehentlich den gesamten Arbeitsspeicher benutzen, können Sie die Systemvariable max_heap_table_size so einstellen, dass auch MEMORY-Tabellen einem Größenlimit unterliegen. Für einzelne Tabellen können Sie auch in der CREATE TABLE-Anweisung die Tabellenoption MAX_ROWS-Tabelle setzen.

Der Server benötigt genug Arbeitsspeicher, um alle MEMORY-Tabellen zu pflegen, die zur selben Zeit gebraucht werden.

aus
http://dev.mysql.com/doc/refman/5.1/...ge-engine.html

[Dave_Bowman]

Ich glaube, dass der Server einfach viel langsamer ist. Mittlerweile denke ich nicht mehr, dass es an temporären Dateien resp. Tables oder dem Speichermanagement von MySQL liegt. Ich habe einen schnöden CPU-Benchmark mal laufen lassen (Super Pi) und mal drei verschiedene Läufe gemacht:

1. lokal: 2,76 sek / Server 9,39 sek
1. lokal: 5,84 sek / Server 24,88 sek
1. lokal: 12,75 sek / Server 50,32 sek

Ich denke mal, das ist die Erklärung. Ich verstehe zwar nicht, weshalb der so langsam ist, bei dem Prozessor sollte doch mehr zu erwarten sein, aber es ist nun mal so. Ist ein gehosteter Server bei 1&1, aber wenn ich mich recht erinnere, ein dedizierter. Sind da solche Werte normal?

Danke, defo, für Deine Hilfe und Dein Engagement.

Dave