Optimierung
Im Folgenden finden Sie Hinweise darauf, wie Sie Ihre Datenbank-Queries optimieren, Bottlenecks erkennen oder Ihre Datenbank-Struktur verbessern können.
Für MariaDb werden vom Hersteller Anleitungen zur Optimierung von Serverbetrieb, Datenstruktur und Queries bereitgestellt. Ebenso für PostgreSQL; dort werden Performance-Tips angeboten. Da der Serverbetrieb bei beiden Serversystemen beim RRZ liegt, haben Nutzer keinen Zugriff auf die Konfiguration der Server, können aber die Hinweise zur Datenstruktur in ihrer Datenbank und bei der Erstellung von Queries beachten. Da auf den Datenbank-Servern des RRZ eine Vielzahl völlig verschiedener Datenbanken und Anwendungsfälle gehostet werden, können die Server nicht auf einen besonderen Fall abgestimmt konfiguriert werden. Beachten Sie dazu auch die Hinweise zu Quotierung, Zugriffs- und Funktionsbeschränkungen.
Das RRZ kann Ihnen im Rahmen des Betriebs der Datenbankserver Informationen bereitstellen, die Ihnen helfen können, Probleme zu erkennen. Wenden Sie sich dazu bitte an die RRZ-Serviceline und geben Sie den Namen der Datenbank und die dazugehörige Kennung an. Im einzelnen sind das:
- Slow-Log: Das RRZ kann auf Anfrage Informationen bereitstellen, welche Queries länger als 10 Sekunden zur Beantwortung benötigten.
- Queries ohne Nutzung von Indices: Das RRZ kann auf Anfrage Informationen sammeln und bereitstellen, welche Queries keine Indices verwendeten (nur für MariaDb).
- SQL-Errors: Das RRZ kann auf Anfrage Informationen sammeln und bereitstellen, welche Queries auf einen Fehler liefen.
- Die wirkliche Datenbankgröße (s.u.).
Hier ein Beispiel, dass Ihnen zeigt, wie aus einem gefundenen Eintrag in der Slow-Log-Datei des MariaDb-Servers die Datenstruktur einer Tabelle verbessert werden kann. Dazu dienen die Anweisungen explain select (zeigt, welche Datentypen an einer Query beteiligt sind) und procedure analyse() (zeigt, ob sich ein Datentyp besser anders darstellen läßt).
Der Slow-Log-Eintrag:
# Time: 161121 15:03:15
# User@Host: *******[*******] @ zd-dbproxy-2.rrz.uni-hamburg.de [134.100.55.69]
# Thread_id: 631826 Schema: mydatabase QC_hit: No
# Query_time: 5.464161 Lock_time: 0.000096 Rows_sent: 1 Rows_examined: 343320
use mydatabase;
SET timestamp=1479736995;
SELECT COUNT(DISTINCT `resourcemiscId`) AS `count` FROM `mytable1_resource_misc` LEFT JOIN `mytable1_resource_creator` AS `mytable1_c` ON `mytable1_c`.`resourcecreatorResourceId` = `resourcemiscId` LEFT JOIN `mytable1_creator` ON `creatorId` = `mytable1_c`.`resourcecreatorCreatorId` WHERE (`resourcecreatorCreatorId` = '5304' OR `creatorSameAs` = '5304');
explain select anwenden:
explain SELECT COUNT(DISTINCT `resourcemiscId`) AS `count` FROM `mytable1_resource_misc` LEFT JOIN `mytable1_resource_creator` AS `mytable1_c` ON `mytable1_c`.`resourcecreatorResourceId` = `resourcemiscId` LEFT JOIN `mytable1_creator` ON `creatorId` = `mytable1_c`.`resourcecreatorCreatorId` WHERE (`resourcecreatorCreatorId` = '5304' OR `creatorSameAs` = '5304');
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | mytable1_resource_misc | index | NULL | resourcemiscCollection | 5 | NULL | 111510 | Using index |
| 1 | SIMPLE | mytable1_c | ref | resourcecreatorResourceId | resourcecreatorResourceId | 4 |mydatabase.mytable1_resource_misc.resourcemiscId | 1 | |
| 1 | SIMPLE | mytable1_creator | eq_ref | PRIMARY | PRIMARY | 4 | mydatabase.mytable1_c.resourcecreatorCreatorId | 1 | Using where |
3 rows in set (0.00 sec)
Es wird also in der Query das Feld resourcemiscCollection aus der Tabelle mytable1_resource_misc als Index verwendet. Das ist schon mal gut, aber läßt sich der Index optimieren? Dazu dient
procedure analyse():
select resourcemiscCollection from mytable1_resource_misc procedure analyse();
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype |
| mydatabase.mytable1_resource_misc.resourcemiscCollection | 176 | 1806 | 3 | 4 | 0 | 12247 | 304.6646 | 261.2061 | SMALLINT(4) UNSIGNED |
1 row in set (0.05 sec)
Damit wäre der beste Datentyp für den Index SMALLINT(4) UNSIGNED. Ist resourcemiscCollection anders definiert, z.B. als INT(11), würde die Query nicht mit der maximal möglichen Geschwindigkeit abgearbeitet werden.
Datenbankgröße
Bei MariaDb (MySQL) kann es inbesondere bei Verwendung der (voreingestellten) Storage-Engine InnoDB zu einer Fragmentierung des Speicherplatzes für die betreffenden Tabellen kommen. Dadurch belegt eine Datenbank dann mehr Speicherplatz im Dateisystem als von MariaDb angezeigt. Damit kann es sein, daß die Speicherplatzquotierung für Datenbanken aktiv wird und die Datenbank für einen schreibenden Zugriff sperrt, obwohl Größe der Datenbank eigentlich noch unter der Quote liegt.
Mit dem Kommando
SELECT table_schema as "Datenbank-Name",
ROUND( SUM( (data_length + index_length)/1024/1024), 2)
as "Datenbank-Größe (MB)" FROM information_schema.tables
where table_schema = 'mydatabase';
können Sie Datenbankgröße der Datenbank mydatabase bestimmen (in MB) - aber nur die Größe des eigentlichen Datenbestandes, nicht den Fragmentierungs-Overhead. Denn können nur die Datenbank-Administratoren des RRZ bestimmen. Wir können Ihnen aber auf Anfrage mitteilen, wie groß die wirkliche Speicherbelegung Ihrer Datenbank ist.
Wir empfehlen deshalb, bei Überschreitung einer Quote nicht gleich eine Vergrößerung zu beantragen, sondern zunächst einen eventuell vorhandenen Fragmentierungs-Overhead von InnoDB-Tabellen zu beseitigen. Das geht mit dem Kommando OPTIMIZE TABLE.