MySQL Performance enttäuschend

[toom]

Ich teste meine MySQL Datenbank auf Geschwindigkeit und versuche meine Tabellen zu optimerieren bzw. die ganze Datenbank.

Ich habe ein kleines Testprogramm geschrieben, das die Dauer von verschiedenen Abfragen misst.

Ich habe ein Tabelle ParkingPlace. Die Tabelle hat unter anderem folgende Felder:

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| active               | bit(1)       | NO   |     | NULL    |                | 
...
| ownerId              | bigint(20)   | NO   |     | NULL    |                | 
...
| rasterId             | int(11)      | NO   | MUL | NULL    |                | 
...
| trustable            | bit(1)       | NO   |     | NULL    |                | 
+----------------------+--------------+------+-----+---------+----------------+

Auf der Tabelle wird folgende Abfrage durchgeführt:

Hibernate: 
    select
        parkingpla0_.id as id1_,
        .../*alle anderen Spaltennamen */,
        parkingpla0_.trustable as trustable1_
    from
        ParkingPlace parkingpla0_ 
    where
        parkingpla0_.rasterId=? 
        and parkingpla0_.active=? 
        and parkingpla0_.trustable=?

Ein "show index from parkingplace" liefert:

+--------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| parkingplace |          0 | PRIMARY                        |            1 | id          | A         |       20000 |     NULL | NULL   |      | BTREE      |         | 
| parkingplace |          1 | RasterId_Active_Trusable_Index |            1 | rasterId    | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| parkingplace |          1 | RasterId_Active_Trusable_Index |            2 | active      | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| parkingplace |          1 | RasterId_Active_Trusable_Index |            3 | trustable   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |

Soweit so gut. Ich habe die Abfrage in zwei Fällen getestet.
Fall A ohne Index "RasterId_Active_Trusable_Index" ergibt sich folgende Leistung:

10000 queries auf 10000 parkingplaces -> 3m 54sec
10000 queries auf 20000 parkingplaces -> 7m 54sec
10000 queries auf 30000 parkingplaces -> 11m 30sec
10000 queries auf 40000 parkingplaces -> 16m 37sec

Fall B nun mit dem entsprechenden Index

10000 queries auf 10000 parkingplaces -> 1m 42sec
10000 queries auf 20000 parkingplaces -> 2m 00sec
10000 queries auf 30000 parkingplaces -> 4m 48sec
10000 queries auf 40000 parkingplaces -> 6m 30sec

Was mich hier verwirrt ist die Tatsache, dass mit einem Index die Dauer der Abfragen zunimmt bei steigender Anzahl an Einträgen. Ehrlich gesagt, das ist ja auch zu erwarten, aber mit kommt diese Steigerung der Dauer der Abfragen doch ziemlich groß vor. Ich hatte erwartet, dass die Dauer der Abfragen annähernd konstant bleibt (Außerdem finde ich die Leistungssteigerung jetzt auch nicht soo überwältigend).

Was ist Eure Meinung dazu?
Habe ich vielleicht was vergessen was man noch verbessern könnte?

 

[toom]

Der Index wird durch MySQL in Form eines B-Baumes realisiert. Ein kurzer Blick in Wikipedia hat ergeben, dass das Finden eines Elements im B-Baum maximal log Schritte benötigt.

Eine kleine Beispielrechnung (grob)
10000 Elemente: log(10000) = 4 Faktor 1
20000 Elemente: log(20000) = 4,3 Faktor 1,075
30000 Elemente: log(30000) = 4,47 Faktor 1,1175
40000 Elemente: log(40000) = 4,6 Faktor 1,15

Wenn man das jetzt umlegt auf meine Messungen, dann sollte die Steigerung in der Zeit eigentlich so aussehen:
10000 queries auf 10000 parkingplaces -> 1m 42sec
10000 queries auf 20000 parkingplaces -> 2m 00sec (sollte sein 1m42s * 1,075 = 1m 49s)
10000 queries auf 30000 parkingplaces -> 4m 48sec (sollte sein 1m42s * 1,1175 = 1m 53s)
10000 queries auf 40000 parkingplaces -> 6m 30sec (sollte sein 1m42s * 1,15 = 1m 57s)

Man sieht also ganz klar, dass das Wachstum recht niedrig sein sollte. Trotzdem ist das Wachstum in meinen Fall der Datenbank ziemlich schlecht im Vergleich zu meiner Rechnung.

Irgendwas scheint nicht zu passen.

 

[Roger Wilco]

MySQL :: MySQL 5.1 Reference Manual :: 12.3.2 EXPLAIN Syntax
MySQL :: MySQL 5.1 Reference Manual :: 7.2.1 Optimizing Queries with EXPLAIN

 

[toom]

Danke für den Tipp.

Wenn ich es abfrage erhalte ich

mysql> explain select * from parkingplace where rasterid=184 and active=true and trustable=true;
+----+-------------+--------------+--------+--------------------------------+------+---------+------+------+-------+
| id | select_type | table        | type   | possible_keys                  | key  | key_len | ref  | rows | Extra |
+----+-------------+--------------+--------+--------------------------------+------+---------+------+------+-------+
|  1 | SIMPLE      | parkingplace | system | RasterId_Active_Trusable_Index | NULL | NULL    | NULL |    1 |       | 
+----+-------------+--------------+--------+--------------------------------+------+---------+------+------+-------+

Das heißt doch, dass hier ein Index benutzt wird. Trotzdem halte ich die Leistung für extrem schlecht und widerspricht krass dem logarithmischen Wachstum das ein B-Baum erfüllen soll. Da muss irgendwas anderes noch nicht stimmen.

 

[Roger Wilco]

Das heißt doch, dass hier ein Index benutzt wird.

Nein. Das heißt es genau nicht. Lies nochmal MySQL :: MySQL 5.1 Reference Manual :: 7.2.1 Optimizing Queries with EXPLAIN. Darin wird die Bedeutung der einzelnen Spalten im Detail erklärt.

 

[toom]

Hoppla, ich habe aus versehen wohl die falsche Ausgabe kopiert und eingefügt, als ich explain ausgeführt habe. Ich habe eigentlich folgende Ausgabe erhalten:

mysql> explain select * from parkingplace where rasterid=200 and active=true and trustable=true;
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+-------+
| id | select_type | table        | type | possible_keys                  | key                            | key_len | ref               | rows | Extra |
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+-------+
|  1 | SIMPLE      | parkingplace | ref  | RasterId_Active_Trusable_Index | RasterId_Active_Trusable_Index | 6       | const,const,const |    3 |       | 
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+-------+
1 row in set (0.03 sec)

Die Leistung ist nach wie vor schlecht

 

[touchstone]

Eigentlich sieht das alles gut aus, der korrekte Key wird genommen, und key_len=6 sollte eigentlich bedeuten dass der komplette Key verwendet wird (4+1+1 Bytes). Eigentlich sollte so eine Abfrage mit ref=const sofort (in weniger als einer Sekunde) erledigt sein.

Um das Problem einzugrenzen könntest Du mal folgendes ausprobieren:

• Den Key ganz löschen und schauen ob die Zeiten gleich bleiben oder noch länger werden.
• Die BIT Spalten auf TINYINT(1) ändern und den Index neu erstellen. (BIT ist noch nicht solange dabei wie die anderen Datentypen, vielleicht gibt es damit ein Problem. Normalerweise würde dann aber EXPLAIN nicht anzeigen dass der Index verwendet wird)

 

[Mario]

Unabhängig vom Index würde ich noch folgendes in Betracht ziehen: Das gesamte Resultset, welches durch eine SQL Abfrage zurückgeliefert wird, landet soweit ich weiß im Arbeitsspeicher. Und dieser will/muß ja auch verwaltet werden. Soll heißen, je größer das Resultset ist, um so mehr ist MySQL bzw. das Betriebssystem mit verwalten des Speichers beschäftigt.

Deshalb wird z.B. eine Abfrage auf alle Spalten einer Tabelle, wie

SELECT * FROM ....

ab einer bestimmten zurückgelieferten Datenmenge immer langsamer sein als eine Abfrage auf nur (als Beispiel) eine Spalte, wie

SELECT spalte1 FROM ...

Zumindest meine Erfahrung bei größeren Datenmengen. Wobei das aber letztendlich doch sehr stark von der aktuellen Konfirguration des MySQL Demons abhängt und natürlich auch vom zur Verfügung stehenden Arbeitsspeicher.

Ich denke mal auf einem dedizierten Datenbankserver spielt so was dann doch eine untergeordnete Rolle. Wenn überhaupt.

 

[toom]

• Den Key ganz löschen und schauen ob die Zeiten gleich bleiben oder noch

Das habe ich doch schon gemacht. Ein paar Beiträge früher in diesem Thread, dort stehen die Ergebnisse einmal mit und einmal ohne Index.

@Mario
Ich habe die Tests auf meinem Rechner Core2Duo mit 2GB Ram ausprobiert, ich denke das sollte reichen, aber es könnte natürlich sein, dass man noch was in irgendeiner Konfigurationsdatei einstellen kann, was von Haus aus nicht eingestellt ist.

Fazit (Wenn keiner eine weitere Idee hat): Auf jeden Fall meiner Meinung nach zu langesam! Skaliert auf keinen Fall gemäß log obwohl es ein B-Baum ist.