• Sie befinden sich hier: Startseite > SQL-Backstube > MySQL Stored Procedure - Performance und Versionsprobleme

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

01.05.2011: MySQL Stored Procedure - Performance und Versionsprobleme

MySQL hat bisher nicht den besten Ruf was die Performance von Stored Procedures angeht. Bereits 2008 war dies ein Thema in einem Blogeintrag[1]. Darin wurde die Fibonacci Formel[2] mit einer Stored Function nachgebildet. Das Ergebnis war: PHP ist mit einer ähnlichen Funktion 10x schneller als die MySQL Stored Function. Hat MySQL inzwischen nachbessern können?

Aus dem genannten Blog-Eintrag geht leider nicht hervor, um welche MySQL Version es sich handelt. Aber inzwischen sind ja einige Jahre ins Land gegangen und Zeit für einen erneuten Test.

Getestet mit folgendem Betriebssystem:

* Windows 7 Pro 64Bit (Version 6.1 Build 7600)

Getestet mit folgenden Datenbanken:

* mysql-essential-5.1.55-winx64
* mysql-5.5.9-winx64
* mysql-5.6.2-m5-winx64

MySQL Stored Function

Listing 1:

1. /* Code stammt von Link[1] */
2. DELIMITER $$
4. CREATE FUNCTION fibonacci(n INT)
5. RETURNS DOUBLE
6. NO SQL
7. BEGIN
8. DECLARE f1, result DOUBLE DEFAULT 0.0;
9. DECLARE f2 DOUBLE DEFAULT 1.0;
10. DECLARE cnt INT DEFAULT 1;
11. WHILE cnt <= n
12. DO
13. SET result = f1 + f2;
14. SET f1 = f2;
15. SET f2 = result;
16. SET cnt = cnt + 1;
17. END WHILE;
18. RETURN result;
19. END$$
21. DELIMITER ;
23. /* Function aufrufen */
24. SELECT BENCHMARK(100, fibonacci(40000));

PHP Fibonacci-Skript ausführen als Referenzzeit:

Listing 2:

1. Benötigt mit der PHP Version 5.3 (32Bit)
2. ...\php-5.3.6-Win32-VC9-x86>php test_fib.php 40000 100
3. 0.98547005653381 Sekunden

Test mit MySQL 5.1.55

Beginnen wir mit der MySQL Version 5.1.55 und testen drei Durchläufe mit dem BENCHMARK() Befehl.

Listing 3:

1. 1) SELECT BENCHMARK(100, fibonacci(40000));
2. 2) SELECT BENCHMARK(100, fibonacci(10000));
3. 3) SELECT BENCHMARK(100, fibonacci(1000));
5. mysql> select version();
6. +------------------+
7. | version() |
8. +------------------+
9. | 5.1.55-community |
10. +------------------+
11. 1 row in set (0.00 sec)

Jeder der drei BENCHMARK() Aufrufe aus Listing 3: wird nun ausgeführt.

Listing 4:

1. mysql>SELECT BENCHMARK(100, fibonacci(40000));
2. +----------------------------------+
3. | BENCHMARK(100, fibonacci(40000)) |
4. +----------------------------------+
5. | 0 |
6. +----------------------------------+
7. 1 row in set (9.36 sec)

Listing 5:

1. mysql>SELECT BENCHMARK(100, fibonacci(10000));
2. +----------------------------------+
3. | BENCHMARK(100, fibonacci(10000)) |
4. +----------------------------------+
5. | 0 |
6. +----------------------------------+
7. 1 row in set (2.40 sec)

Listing 6:

1. mysql>SELECT BENCHMARK(100, fibonacci(1000));
2. +----------------------------------+
3. | BENCHMARK(100, fibonacci(1000)) |
4. +----------------------------------+
5. | 0 |
6. +----------------------------------+
7. 1 row in set (0.30 sec)

Listing 4: zeigt es schon deutlich. Die MySQL Function benötigt mit 9,36 Sekunden wieder ca. 10x so lange wie das PHP Skript, um die Zahlen zu berechnen. So betrachtet hat sich nicht viel geändert. Können es die neuen Versionen besser?

Test mit MySQL 5.5.9

Weiter geht es mit der MySQL Version 5.5.9. Auch hiermit testen wir drei Durchläufe mit dem BENCHMARK() Befehl.

Listing 7:

1. 1) SELECT BENCHMARK(100, fibonacci(40000));
2. 2) SELECT BENCHMARK(100, fibonacci(10000));
3. 3) SELECT BENCHMARK(100, fibonacci(1000));
5. mysql> select version();
6. +-----------+
7. | version() |
8. +-----------+
9. | 5.5.9 |
10. +-----------+
11. 1 row in set (0.00 sec)

Listing 8:

1. mysql>SELECT BENCHMARK(100, fibonacci(40000));
2. ERROR 1690 (22003): DOUBLE value is out of range in '(f1@1 + f2@3)'

Listing 9:

1. mysql>SELECT BENCHMARK(100, fibonacci(10000));
2. ERROR 1690 (22003): DOUBLE value is out of range in '(f1@1 + f2@3)'

Listing 10:

1. mysql>SELECT BENCHMARK(100, fibonacci(1000));
2. +----------------------------------+
3. | BENCHMARK(100, fibonacci(1000)) |
4. +----------------------------------+
5. | 0 |
6. +----------------------------------+
7. 1 row in set (0.28 sec)

Uups, was ist das. Die MySQL Version 5.5.9 steigt mit einem SQL-Fehler (ERROR 1690) aus. Ist dies ein Bug?

Test mit MySQL 5.6.2-m5

Zum Schluß noch die neue MySQL BETA Version 5.6.2-m5. Auch hiermit testen wir drei Durchläufe mit dem BENCHMARK() Befehl.

Listing 11:

1. 1) SELECT BENCHMARK(100, fibonacci(40000));
2. 2) SELECT BENCHMARK(100, fibonacci(10000));
3. 3) SELECT BENCHMARK(100, fibonacci(1000));
5. mysql> select version();
6. +-----------+
7. | version() |
8. +-----------+
9. | 5.6.2-m5 |
10. +-----------+
11. 1 row in set (0.00 sec)

Listing 12:

1. mysql>SELECT BENCHMARK(100, fibonacci(40000));
2. ERROR 1690 (22003): DOUBLE value is out of range in '(f1@1 + f2@3)'

Listing 13:

1. mysql>SELECT BENCHMARK(100, fibonacci(10000));
2. ERROR 1690 (22003): DOUBLE value is out of range in '(f1@1 + f2@3)'

Listing 14:

1. mysql>SELECT BENCHMARK(100, fibonacci(1000));
2. +----------------------------------+
3. | BENCHMARK(100, fibonacci(1000)) |
4. +----------------------------------+
5. | 0 |
6. +----------------------------------+
7. 1 row in set (47.35 sec)

Oha, was ist das? Die MySQL Version 5.6.2-m5 steigt ebenfalls mit einem SQL-Fehler (ERROR 1690) aus. Zudem läuft die dritte Abfrage zwar durch, aber mit welcher Zeit: 47,35 Sekunden!

Vergleichstest abgebrochen...

Nachdem die neueren MySQL-Version einen DOUBLE value is out of range in '(f1@2 + f2@4)' Fehler ausgeben, muss der Test leider abgebrochen werden. Immerhin bei der dritten Variante mit fibonacci(1000) liefern alle drei Versionen ein Ergebnis.

Listing 15:

1. 5.1.55 - 1 row in set (0.30 sec)
2. 5.5.9 - 1 row in set (0.28 sec)
3. 5.6.2-m5 - 1 row in set (47.35 sec)

Da die 5.6.2-m5 ja noch eine Beta-Version ist, wird der gewaltige Zeit-Unterschied hoffentlich mit diversem Debug-Code zusammenhängen, der bis zur GA Version entfernt ist.

Ergebnis

Nach wie vor ist PHP bei diesem Beispiel ca. 10x schneller als eine MySQL Stored Function (v5.1.55). Derzeit noch unklar ist mir das Problem mit dem "DOUBLE value is out of range" der MySQL Versionen 5.5.9 und 5.6.2-m5.

UPDATE 03.05.2011

Der von den neueren MySQL Versionen gemeldete "DOUBLE value is out of range.." ist korrekt. Die berechnete Zahl übersteigt den gültigen Wertebereich von DOUBLE. Eigentlich hätte die Version 5.1.55 oder vorher auch diesen Fehler melden müssen. Hier haben die überarbeiteten Fehlerroutinen der Stored Routines (siehe SIGNAL etc.) ein gutes Werk getan.

Links zum Thema

[1] http://mtocker.livejournal.com/45222.html
[2] http://de.wikipedia.org/wiki/Fibonacci-Folge

https://twiedmann.de