[wurzl]

Hier handelt es sich um die ersten Ergebnisse dieses Artikels: MySQL Benchmarking, Stress-Test, Optimierung. Für generelle Fragen und Anregungen bitte auch dort posten.

Um was geht es?
Um meine MySQL-Server zu optimieren, möchte ich zunächst meine Server vergleichen. Dadurch erhoffe ich mir ein paar Antworten zum Thema: "Was bringt eine schnellere CPU. mehr RAM, schnellere Festplatten, anderes Linux, andere Compiler flags."
Dazu gibt es die "MySQL Benchmark Suit", die normalerweise in jeder Installation enthalten ist. Sie eignet sich zum Vergleichen unterschiedlicher Installationen. Es ist KEIN Werkzeug um spezifische Daten oder Bedürfnisse zu testen. Desweiteren ist dieses Tool in Perl geschrieben - Windows-Benutzer werden damit evtl. Probleme haben.

Mitmachen
Ich werde in diesem Thread die Ergebnisse meiner Benchmarks posten. Natürlich ist Jeder dazu eingeladen dies auch zu tun da es zu Vergleichszwecken einfach interessant ist.

Anmerkungen:
- Bei den folgenden Tests ist meist die CPU der Flaschenhals. Es gibt aber auch Tests, die stark auf die Festplatte zugreifen.
- Multi-CPU Server können bei diesem Test nicht berücksichtigt werden, da alle Querys nacheinander ausgeführt werden.
- Die Benchmarks benötigen eine gewisse Zeit und (wie könnte es anders sein) lasten das System aus. Also bitte nachts laufen lassen!
- Änderungen an der my.cnf haben relativ wenig Auswirkungen. Warscheinlich weil es nur 1 Zugriff gleichzeitig ist und die test db nicht wirklich gross ist. Ich werde später noch Stress-Tests durchführen.

Wo sind die Scripte?
Gentoo: /usr/share/mysql/sql-bench/
SuSE: hier muss man das rpm "mysql-bench" installieren
Generell: Einfach die 4.x.x Sources runterladen, entpacken und da ist dann auch schon das "sql-bech" Verzeichnis.

Test Durchführung
Es handelt sich wie gesagt um eine Benchmark Suit. Es gibt also mehrere Tests. Diese können alle zusammen oder einzeln ausgeführt werden.
Ich empfehle die Tests einzeln zu starten, damit das System nicht allzulange beschäftigt ist. Außerdem ist es sinnvoll jeden Test mehrmals (ca. 4x) und möglichst isoliert (nachts?) durchzuführen. Interessant ist dabei nur der beste (schnellste) Durchlauf. Dadurch kann man äußere Einflüsse (andere, böse Benutzer) relativ gut ausschließen.

Starten der Tests

./run-all-tests server=mysql --user=root --password='geheim' --log --fast

Wie bereits erwähnt: Das kann auch mal ne Stunde dauern. Also die Tests lieber einzeln laufen lassen. Dazu aus dem obigen Code einfach das "run-all-tests" mit einem anderen Script aus dem Verzeichnis ersetzen.

Ergebnisse
Die liegen im Verzeichnis "output" (das bewirkt die Option --log). Jeder Test hat eine eigene Datei bekommen, die sich wunderbar mit cat oder less anzeigen lässt.

Falls man alle Tests durchgeführt hat, sollte das Output-verzeichnis in etwas so aussehen:

ATIS-mysql_fast-Linux_2.6.14_gentoo_r5_i686
RUN-mysql_fast-Linux_2.6.14_gentoo_r5_i686
alter-table-mysql_fast-Linux_2.6.14_gentoo_r5_i686
big-tables-mysql_fast-Linux_2.6.14_gentoo_r5_i686
connect-mysql_fast-Linux_2.6.14_gentoo_r5_i686
create-mysql_fast-Linux_2.6.14_gentoo_r5_i686
insert-mysql_fast-Linux_2.6.14_gentoo_r5_i686
select-mysql_fast-Linux_2.6.14_gentoo_r5_i686
transactions-mysql_fast-Linux_2.6.14_gentoo_r5_i686
wisconsin-mysql_fast-Linux_2.6.14_gentoo_r5_i686

Die Datei RUN-mysql_fast-Linux_2.6.14_gentoo_r5_i686 beinhaltet eine Zusammenfassung aus der ich auch die Daten entnehme, die ich hier posten werde.

Was bedeuten die vielen Zahlen?
Hier mal mein Output für den connect-Test:

Testing server 'MySQL 4.0.24/' at 2006-03-12 16:43:57

Testing the speed of connecting to the server and sending of data
Connect tests are done 10000 times and other tests 100000 times

Testing connection/disconnect
Time to connect (10000:sad: 11 wallclock secs ( 6.28 usr  0.72 sys +  0.00 cusr  0.00 csys =  7.00 CPU)

Test connect/simple select/disconnect
Time for connect+select_simple (10000:sad: 12 wallclock secs ( 6.62 usr  0.82 sys +  0.00 cusr  0.00 csys =  7.44 CPU)

Test simple select
Time for select_simple (100000:sad:  5 wallclock secs ( 1.92 usr  1.01 sys +  0.00 cusr  0.00 csys =  2.93 CPU)

Test simple select
Time for select_simple_cache (100000:sad:  5 wallclock secs ( 1.94 usr  1.01 sys +  0.00 cusr  0.00 csys =  2.95 CPU)

Testing connect/select 1 row from table/disconnect
Time to connect+select_1_row (10000:sad: 14 wallclock secs ( 7.17 usr  0.84 sys +  0.00 cusr  0.00 csys =  8.01 CPU)

Testing select 1 row from table
Time to select_1_row (100000:sad:  9 wallclock secs ( 2.52 usr  1.31 sys +  0.00 cusr  0.00 csys =  3.83 CPU)

Time to select_1_row_cache (100000:sad:  7 wallclock secs ( 2.24 usr  1.22 sys +  0.00 cusr  0.00 csys =  3.46 CPU)

Testing select 2 rows from table
Time to select_2_rows (100000:sad: 10 wallclock secs ( 2.48 usr  1.63 sys +  0.00 cusr  0.00 csys =  4.11 CPU)

Test select with aritmetic (+)
Time for select_column+column (100000:sad:  9 wallclock secs ( 2.27 usr  1.25 sys +  0.00 cusr  0.00 csys =  3.52 CPU)

Testing retrieval of big records (65000 bytes)
Time to select_big_str (10000:sad: 18 wallclock secs ( 8.29 usr  1.77 sys +  0.00 cusr  0.00 csys = 10.06 CPU)

Total time: 100 wallclock secs (41.73 usr 11.58 sys +  0.00 cusr  0.00 csys = 53.31 CPU)

"wallclock secs": Gesamtzeit für die Ausführung
"usr, sys, cursr, csys": Overhead des Clients

Der interessante Wert errechnet sich also aus wallclock minus Overhead.
Bei meiner "Total time" entspricht das 46,69 Sekunden.

[wurzl]

Server 1:
CPU: Athlon XP 2400+
RAM: 1024MB
HD: 4x160GB ATA 5400rpm
RAID: 5 (software)

System:
Linux: Gentoo
Kernel: 2.6.14
Perl: 5.8.7
MySQL: 4.0.24
Auslastung: so gut wie idle

Benchmark:
alter-table: Total time: 84 wallclock secs ( 0.04 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.06 CPU)
ATIS: Total time: 13 wallclock secs ( 2.92 usr 1.29 sys + 0.00 cusr 0.00 csys = 4.21 CPU)
big-tables: Total time: 13 wallclock secs ( 3.57 usr 3.04 sys + 0.00 cusr 0.00 csys = 6.61 CPU)
connect: Total time: 100 wallclock secs (41.73 usr 11.58 sys + 0.00 cusr 0.00 csys = 53.31 CPU)
create: Total time: 783 wallclock secs ( 3.89 usr 0.67 sys + 0.00 cusr 0.00 csys = 4.56 CPU)
insert: Total time: 850 wallclock secs (242.19 usr 60.59 sys + 0.00 cusr 0.00 csys = 302.78 CPU)
select: Total time: 397 wallclock secs (32.76 usr 5.83 sys + 0.00 cusr 0.00 csys = 38.59 CPU)
transactions: Failed
wisconsin: Total time: 2 wallclock secs ( 0.94 usr 0.25 sys + 0.00 cusr 0.00 csys = 1.19 CPU)

Bemerkung:
Das sieht doch vorerst ganz in Ordnung aus. Nur die selects finde ich etwas lahm.

[wurzl]

Server 2:
CPU: Celeron 1200mHz
RAM: 256MB
HD: 40GB ATA 5400rpm
RAID: nö

System:
Linux: Gentoo
Kernel: 2.6.15
Perl: 5.8.7
MySQL: 5.0.18
Auslastung: keine (heheh, firewall dichgemacht)

Benchmark:
alter-table: Total time: 70 wallclock secs ( 0.22 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.26 CPU)
ATIS: Total time: 47 wallclock secs ( 6.81 usr 0.46 sys + 0.00 cusr 0.00 csys = 7.27 CPU)
big-tables: Total time: 53 wallclock secs (11.85 usr 1.80 sys + 0.00 cusr 0.00 csys = 13.65 CPU)
connect: Total time: 331 wallclock secs (135.22 usr 23.72 sys + 0.00 cusr 0.00 csys = 158.94 CPU)
create: Total time: 644 wallclock secs (18.95 usr 2.36 sys + 0.00 cusr 0.00 csys = 21.31 CPU)
insert: Total time: 3272 wallclock secs (992.89 usr 81.22 sys + 0.00 cusr 0.00 csys = 1074.11 CPU)
select: Total time: 1061 wallclock secs (103.77 usr 8.39 sys + 0.00 cusr 0.00 csys = 112.16 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 5 wallclock secs ( 1.53 usr 0.15 sys + 0.00 cusr 0.00 csys = 1.68 CPU)


Bemerkung:
Aua!
Sollte hier etwa das MySQL5 schuld sein?
Ist die MySQL Config schrott?
Ist MySQL 5.x wirklich noch unstable?
Ist die Kiste einfach zu schäbig?
Ich werde mal das 4.1.x MySQL ausprobieren.

[wurzl]

Server 3:
CPU: Athlon XP 2600+
RAM: 512MB
HD: 80GB ATA 5400rpm
RAID: nö

System:
Linux: SuSE
Kernel: 2.4.21
Perl: 5.8.1
MySQL: 4.0.15 MAX
Auslastung: keine

Benchmark:
alter-table: Total time: 17 wallclock secs ( 0.06 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.07 CPU)
ATIS: Total time: 16 wallclock secs ( 5.95 usr 0.81 sys + 0.00 cusr 0.00 csys = 6.76 CPU)
big-tables: Total time: 13 wallclock secs ( 4.03 usr 1.82 sys + 0.00 cusr 0.00 csys = 5.85 CPU)
connect: Total time: 94 wallclock secs (36.79 usr 8.84 sys + 0.00 cusr 0.00 csys = 45.63 CPU)
create: Total time: 116 wallclock secs ( 3.99 usr 0.55 sys + 0.00 cusr 0.00 csys = 4.54 CPU)
insert: Total time: 792 wallclock secs (290.99 usr 36.35 sys + 0.00 cusr 0.00 csys = 327.34 CPU)
select: Total time: 403 wallclock secs (36.59 usr 3.72 sys + 0.00 cusr 0.00 csys = 40.31 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 4 wallclock secs ( 2.17 usr 0.18 sys + 0.00 cusr 0.00 csys = 2.35 CPU)

Bemerkung:
Sieht gut aus - liegt es an der MAX db?

[wurzl]

Server 4:
CPU: Opteron 146
RAM: 2048MB
HD: 2x120GB SATA 7200rpm
RAID: 1

System:
Linux: Gentoo
Kernel: 2.6.15
Perl: 5.8.7
MySQL: 4.1.14
Auslastung: ein paar User

Benchmark:
alter-table: Total time: 47 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.05 CPU)
ATIS: Total time: 17 wallclock secs ( 1.97 usr 0.90 sys + 0.00 cusr 0.00 csys = 2.87 CPU)
big-tables: Total time: 11 wallclock secs ( 3.06 usr 2.01 sys + 0.00 cusr 0.00 csys = 5.07 CPU)
connect: Total time: 47 wallclock secs (16.23 usr 6.53 sys + 0.00 cusr 0.00 csys = 22.76 CPU)
create: Total time: 90 wallclock secs ( 0.69 usr 0.17 sys + 0.00 cusr 0.00 csys = 0.86 CPU)
insert: Total time: 625 wallclock secs (145.40 usr 41.45 sys + 0.00 cusr 0.00 csys = 186.85 CPU)
select: Total time: 248 wallclock secs (14.09 usr 3.88 sys + 0.00 cusr 0.00 csys = 17.97 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 2 wallclock secs ( 0.88 usr 0.48 sys + 0.00 cusr 0.00 csys = 1.36 CPU)

Bemerkung:
Schön flott

[wurzl]

Server 5:
CPU: Celeron 2000MHz
RAM: 256MB
HD: 40GB ATA 5400rpm
RAID: nö

System:
Linux: SuSE
Kernel: 2.6.9
Perl: 5.8.3
MySQL: 4.0.18
Auslastung: nö

Benchmark:
alter-table: Total time: 35 wallclock secs ( 0.26 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.32 CPU)
ATIS: Total time: 46 wallclock secs (19.44 usr 2.06 sys + 0.00 cusr 0.00 csys = 21.50 CPU)
big-tables: Total time: 23 wallclock secs ( 6.51 usr 4.24 sys + 0.00 cusr 0.00 csys = 10.75 CPU)
connect: Total time: 484 wallclock secs (179.27 usr 48.01 sys + 0.00 cusr 0.00 csys = 227.28 CPU)
create: Total time: 239 wallclock secs (17.65 usr 4.14 sys + 0.00 cusr 0.00 csys = 21.79 CPU)
insert: Total time: 3374 wallclock secs (1567.13 usr 167.58 sys + 0.00 cusr 0.00 csys = 1734.71 CPU)
select: Total time: 1136 wallclock secs (160.34 usr 17.08 sys + 0.00 cusr 0.00 csys = 177.42 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 7 wallclock secs ( 4.29 usr 0.41 sys + 0.00 cusr 0.00 csys = 4.70 CPU)

Bemerkung:
Aua!

[zefix]

Gibts das mysql-dings auch für Debian?

[wurzl]

Bestimmt! ich weiss blos nicht, wo die Scripte liegen. Aber /usr/share ist auf jedenfall eine gute Idee.

Im Zweifelsfall tuts ein "locate test-connect" auch.

cu.. wURzL

[Junior]

Tolle Arbeit, wurzl. Vielen Dank!

Ganz lieben Gruss

Junior

[Christoph Neuroth]

Mandriva/Mandrake: Paket MySQL-bench, Verzeichnis /usr/share/sql-bench

Schade, dass das Script es unbedingt erfordert, dass MySQL-Passwort auf der Kommandozeile einzugeben - dieses ist, solange der Test läuft, für alle lokal angemeldeten User einsehbar - ausserdem bleibt es im Klartext in der Shell-History stehen - also: Finger weg auf Produktivsystemen
Sollte mal jemand nen Bugreport gegen MySQL filen, aber ich bin noch zu faul und brauch erstmal nen Kaffee ;) Bis meine Tanja mit dem Test fertig ist, dauerts eh noch ne Weile, Ergebnisse werden nachgereicht ;)

Server 1:
CPU: 2* Pentium II / 266 MHz
RAM: 440 MB RAM
HD: 14 GB IDE
RAID: haha ;)
(Das erklärt den PayPal-Link inder Signatur, denke ich :D)

System:
OS: Mandriva GNU/Linux
Kernel: 2.6.12(-15mdksmp)
Perl: perl-5.8.7
MySQL: 4.1.12
Auslastung: 1000 Unique Visitor im Monat oder so ;)