Im folgenden Artikel geht es um die Optimierung der 22 Abfragen vom TPC-H-Benchmark, die ich einst im Studium erledigen durfte. Aufgabe war es, eine Leistungsoptimierung durchzuführen und dabei eine möglichst geringe Antwortzeit der einzelnen Queries und der Gesamtdauer aller zu erzielen. Das Benchmark eignet sich dabei als eine gute Gelegenheit, um Aspekte der Optimierung von Datenbanken zu überprüfen.
Leider gibt es immer noch zu wenig Ausarbeitungen im Internet, die sich genau dieser Fragestellung annehmen und auch ihr Vorgehen erklären. Bei einigen Queries ist zwar mit erfahrenem Auge ersichtlich, was gemacht werden könnte und welche Konsequenzen es nach sich zieht, jedoch ist es für Anfänger schwierig, diese Sicht- und Denkweise nachzuvollziehen und damit anzueignen.
Der folgende Text ist ein Abriss der Ergebnisse meiner ursprünglichen Ausarbeitung und betrachtet vornehmlich Maßnahmen, die wirklich signifikante Leistungssteigerungen bewirken (d.i. Index und materialisierte Sicht). Es wird dabei außer Acht gelassen, dass das TPC-H-Benchmark Einschränkungen vorgibt, welche die ein oder andere Maßnahme nicht gestatten würden, da der Zweck des Benchmark auch ein anderer ist. Nichtsdestotrotz soll aber die Funktionalität der Abfragen garantiert werden, sodass bei allen Abfragen die Parameter weiterhin beliebig sein können.
Der zentrale Aspekt, der auch unter realen Umständen immer bedacht werden sollte, ist der des trading space for time. Ich werde die Maßnahmen nur soweit angehen, wie ich meine, dass die Performancesteigerung in einem angemessenen Verhältnis zu zusätzlichen Platzbedarf der Hilfsstrukturen steht.
Technische Umgebung
Die Messungen werde ich erneut, jedoch auf einem anderen Rechner durchführen.
- Betriebssystem ist hierbei eine Windows 10 Partition auf einem iMac i5 8 GB RAM
- 500 MB Testdaten werden mit dbgen.exe generiert
- Datenbanksystem (DBS) ist SQL Anywhere von Sybase
- Datenbank Spezifikationen
- 50 MB (10%) statischer Cache
- Mit Messungen kann man nachweisen, dass es Unterschiede in der Performance zwischen einem dynamischen und statischen Cache gibt, sowie der dazu gewählten Seitengrößen
- 1 GB Vorallokation mit geclusterten Primärschlüsseln
- Theoretisch ist diese Maßnahme nicht notwendig, da die Testdaten alle sortiert sind und somit schon geordnet geladen werden. Vorallokation ist besonders dann wichtig, um einer Fragmentierung vorzubeugen, die bei Aktualisierungen der Datenbank entstehen würde und zu einer Verlangsamung der Operationen führt.
- 4 KB ist die Standardseitengröße, die SQL Anywhere empfiehlt. Tests mit anderen (2, 8, 16, 32) schneiden auch bedingt durch die Cachegröße schlechter ab. Mehr über den Einfluss der Seitengröße kann man in der Dokumentation von SQL Anywhere nachlesen.
- 50 MB (10%) statischer Cache
Ausgangsmessung
Das vorliegende Diagramm veranschaulicht die Antwortzeiten (y) der einzelnen Queries (x).
Um Ausreißer zu vermeiden, wurden die Messungen fünfmal durchgeführt, wobei es zu einer Schwankung von 1,3 % um den Mittelwert kam (was auf zuverlässige Werte schließen lässt). Die obige Abbildung zeigt das beste Ergebnis für alle 22 Abfragen, die in der Summe 369024 ms dauerten (μ : 373 s). Dabei ist ersichtlich, dass Q19, Q18, Q9, Q21, Q15 das größte Potenzial hinsichtlich einer Leistungsoptimierung beherbergen.
Leistungsoptimierung
Bei den 22 Abfragen vom TPC-H-Benchmark handelt es sich um reine Lesevorgänge, sodass Aspekte wie Füllmarken oder Logging in Hinblick auf die Leistungsoptimierung keine Relevanz besitzen. Wichtig werden diese erst, wenn konkrete Änderungen am Datensatz der Datenbank wirksam werden sollen.
Indizes besitzen damit praktisch keinerlei Einschränkung. Einige Attribute sind im Benchmark sogar so gesetzt, dass sie als Index taugen und in der realen Welt nicht (z.B. Mengen). Der Gebrauch von Indizes ist immer dann fraglich, wenn neben der READ-Operation noch CREATE, UPDATE und DELETE vollzogen werden müssen. Indizes gehören zu den Hilfsstrukturen in der Datenbank und müssen, wenn diese geändert wird ggf. aktualisiert werden.
Es macht somit einen großen Unterschied, welche Maßnahmen man speziell in dieser Datenbank einsetzt und welche in der realen Welt brauchbar sind. Die Prämissen sind entscheidend. Die Leistungsoptimierung findet somit nicht unter realen Bedingungen statt.
Query 1
Für das erste Query soll an dieser Stelle auf den Beitrag von Dr. Syed Saif ur Rahman verlinkt werden, der sich dieser Abfrage angenommen hat.
Was ergänzt werden sollte, und was der Autor nicht weiter ausführt, ist warum gerade an dieser Stelle eine materialisierte Sicht geeignet ist. Materialisierte Sichten stellen nicht bloß einen Ausschnitt einer Tabelle bereit, sondern speichern die Zustandswerte, weshalb sie als Cache fungieren. Für Q1 eignet sich eine materialisierte Sicht deshalb, weil eine oder mehrere Aggregatfunktionen an einer Tabelle durchgeführt werden. Um diesen Operationen und Scan-Vorgängen vorzubeugen, die Ergebnisse quasi vor-zurechnen, empfiehlt es sich eine solche Sicht zu verwenden. Das DBS spart sich dadurch einen sequenziellen Lesevorgang der größten Tabelle (lineitem) ein.
CREATE MATERIALIZED VIEW query_1 as (
SELECT l_shipdate, l_returnflag, l_linestatus, SUM(l_quantity) AS SUM_QTY, SUM(l_extendedprice) AS SUM_BASE_PRICE, SUM(l_extendedprice*(1-l_discount)) AS SUM_DISC_PRICE, SUM(l_extendedprice*(1-l_discount)*(1+l_tax)) AS SUM_CHARGE, SUM(l_quantity) AS AVG_QTY_SUM, SUM(l_extendedprice) AS AVG_PRICE_SUM, SUM(l_discount) AS AVG_DISC_SUM, COUNT(*) AS COUNT_ORDER
FROM lineitem
GROUP BY l_shipdate, l_returnflag, l_linestatus
);
REFRESH MATERIALIZED VIEW query_1;
- 6144 ms → ( +352 KB ) → 128 ms
Query 4
In Query 4 wird eine Zählung durchgeführt, wobei die beiden Tabellen lineitem und orders verbunden werden. Anschließend wird eine Auflistung aller Aufträge nach Priorität sortiert, innerhalb eines bestimmten Zeitraums angezeigt. Da es nur 5 Prioritäten gibt, kann man mit einem einfachen Index auf o_orderpriority dem Datenbanksystem etwas entgegenkommen.
CREATE INDEX "idx_query_4" ON "ORDERS" ( "o_orderpriority" ASC);
- 9216 ms → ( +3360 KB ) → 7040 ms
Query 5
Die fünfte Abfrage gibt eine Übersicht der Einnahmen von Nationen einer bestimmten Region. Da hier mehrere Tabellen benötigt werden und eine Aggregat-Funktion durchgeführt wird, kann ähnlich wie bei Q1 mit einer materialisierten Sicht die Tabelle zwischengespeichert werden, welche die Einnahmen vorrechnet. Zu beachten ist, dass der Parameter Nationenname als erstes Attribut aufgenommen werden muss. Da der Wertebereich von n_name zudem recht gering ist, benötigt die geschaffene Sicht recht wenig Platz im Vergleich zu den sechs Tabellen, die es verbindet.
Q1 und Q5 sind gute Beispiele von Anwendungsfällen materialisierter Sichten. Joins und sequenzielle Lesevorgänge sind sehr zeitintensiv. Mit dem Zwischenspeichern von Hilfstabellen kann man mit wenig Platzbedarf viel Zeit sparen.
CREATE MATERIALIZED VIEW query_5 as (
SELECT n_name, o_orderdate, r_name, sum(l_extendedprice * (1 - l_discount)) as revenue
FROM customer, orders, lineitem, supplier, nation, region
WHERE c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
GROUP BY n_name, o_orderdate, r_name
);
REFRESH MATERIALIZED VIEW query_5;
- 8576 ms → ( +1808 KB ) → 128 ms
Query 6 + 14 + 15 + 20
Alle diese Abfragen führen einen sequenziellen Lesevorgang der Spalte l_shipdate in LINEITEM durch. Wenn man zudem die Plananzeige von interactive SQL sich näher betrachtet, und die anderen "Scan-Prädikate" vergleicht, dann sieht man, welche Spalten sonst noch in einen möglichen Index aufgenommen werden sollten. In manchen Queries werden Joins mit den Tabellen PART und SUPPLIER vollzogen, sodass die entsprechendne Fremdschlüssel auf l_shipdate folgen müssen.
Der benötige Index erstreckt sich über 6 Spalten und braucht fast 100 MB. Dabei werden Q6, Q14 und Q15 auf unter 1 s reduziert, wobei Q20 noch etwa 2 s dauert. Will man aber einen 100 MB Index für eine Tabelle, die selbst 370 MB groß ist? Mit Rücksicht auf die Prämissen halte ich das für eine vernünftige Lösung.
CREATE INDEX "idx_query_6_14_15_20" on lineitem ( l_shipdate, l_partkey, l_suppkey, l_quantity, l_extendedprice, l_discount );
- 34560 ms → ( +100296 KB ) → 2944 ms
Query 9
Bei dieser Abfrage werden 6 der 7 Tabellen verbunden, sodass sich die Ursache der langen Antwortzeit erahnen lässt. Joins sind zeitintensiv. Als Lösungsansatz kann eine materialisierte Sicht in Frage kommen, die diesen Verbund irgendwie umgeht. Alternative ist denormalisieren, dh. LINEITEM + ORDERS zu einer Tabelle machen oder PART + PARTSUPP + SUPPLIER etc.
Eine materialisierte Sicht hingegen würde das Ergebnis der Verbundoperation zwischenspeichern. Die Schwierigkeit dabei ist, dass p_name nicht wie n_name in Q5 einen begrenzten Wertebereich hat und ein group by nur unnötige Zeilen erstellt. Die nachfolgende Lösung reduziert zwar die Antwortzeit beachtlich, wobei aber der zusätzliche Speicherbedarf schon fraglich ist. Nichtsdestotrotz soll die halbe Minute Lebenszeit gespart werden, so dass dies auch in die Endmessung aufgenommen werden soll.
CREATE MATERIALIZED VIEW query_9 as (
SELECT p_name, l_extendedprice, l_discount, l_quantity, ps_supplycost, o_orderdate, n_name
FROM part, supplier, lineitem, partsupp, orders, nation
WHERE s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nationkey
);
REFRESH MATERIALIZED VIEW query_9;
- 32128 ms → ( +212368 KB ) → 3328 ms
Query 11
Für Q11 und Q12 sollen recht simple materialisierte Sichten verwendet werden, um einen join der entsprechenden Tabellen zu umgehen. Die Antwortzeiten nach der Optimierung gehen nahe Null.
CREATE MATERIALIZED VIEW query_11 as (
SELECT ps_partkey, n_name, ps_supplycost, ps_availqty
FROM partsupp, supplier, nation
WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey
);
REFRESH MATERIALIZED VIEW query_11;
- 2176 ms → ( +10880 KB ) → 256 ms
Query 12
CREATE MATERIALIZED VIEW query_12 as (
SELECT l_shipmode, o_orderpriority, l_commitdate, l_receiptdate, l_shipdate
FROM orders, lineitem
WHERE o_orderkey = l_orderkey
AND l_commitdate < l_receiptdate
AND l_shipdate < l_commitdate
);
REFRESH MATERIALIZED VIEW query_12;
- 7296 ms → ( +11888 KB ) → 256 ms
Query 18
Hierbei handelt es sich um die zweitlangsamste Abfrage, sodass der folgende Index durchaus seine Berechtigung hat. Das Mengenattribut l_quantity ist eine ganze Zahl von 1 bis 50, sodass es in diesem Fall als Index taugen sollte.
CREATE INDEX "idx_query_18" ON lineitem ( l_orderkey, l_quantity );
- 37760 ms → ( +39976 KB ) → 3072 ms
Query 19
Diese Abfrage hat die längste Antwortzeit und damit die höchste Priorität. Doch bereits ein Index auf den 5x5 großen Wertebereich von p_brand beschleunigt den Query erheblich bei verhältnismäßig sehr geringem Platzbedarf.
CREATE INDEX "idx_query_19" ON "PART" ( "p_brand" ASC);
- 164992 ms → ( +556 KB ) → 6400 ms
Die schnellste Alternative, von der aber aus Platzverbrauchsgründen (190 MB) abzuraten ist, wird in der Endmessung nicht berücksichtigt. Man muss den Join umgehen. Das wird mit einer materialisierten Sicht gemacht, wobei anschließend diese indiziert werden muss, denn es wird immer noch ein sequenzieller Lesevorgang nach p_brand vollzogen.
CREATE MATERIALIZED VIEW query_19 as (
SELECT l_partkey, p_brand, p_container, l_quantity, p_size, l_shipmode, l_shipinstruct, l_discount, l_extendedprice
FROM lineitem, PART WHERE P_PARTKEY = l_partkey
ORDER BY p_brand, l_shipmode, l_extendedprice
);
REFRESH MATERIALIZED VIEW query_19;
CREATE INDEX "idx_query_19" ON query_19( p_brand, l_shipmode, l_extendedprice );
Query 21
Die Herausforderung bei dieser Abfrage ist, dass die größte Tabelle LINEITEM dreimal geholt wird, wobei noch sequenziell überprüft wird, ob l_commitdate größer als l_receiptdate ist. Ein Index auf diese beiden Spalten zusammen mit den Fremdschlüsseln, die für den Join mit SUPPLIER und ORDERS benötigt werden, ist sage und schreibe 57 MB groß und die optimalste Lösung, die ich erblicken kann. Nichtsdestotrotz soll das nicht in die Endmessung aufgenommen werden.
CREATE INDEX "idx_query_21" ON lineitem( l_commitdate, l_receiptdate, l_suppkey, l_orderkey );
Ergebnis Leistungsoptimierung TPC-H-Benchmark
Mit einem Index auf p_brand lässt sich die Gesamtdauer von 370 auf ca. 200 Sekunden reduzieren. Wenn man noch die weiteren Maßnahmen in Betracht zieht (mit Ausnahme von Q19.2 und Q21), dann sind etwa 92 sec drin (mit 0,3% mittlerem Messfehler). Mit den Maßnahmen wurden nicht einmal alle Queries beachtet. Die Abfragen, für welche diese Maßnahmen angewendet wurden, sind von 303 sec auf 24 sec beschleunigt worden, wobei Q21 mit den zahlreichen joins auf LINEITEM langsamer wurde, da das DBS wegen der Maßnahmen hinsichtlich dieser Tabelle mehr überprüft.
Ausgangsbasis | mit Maßnahmen | |
Gesamtdauer | 369024 ms | 91392 ms |
100 % | 24,8 % | |
Datenbankgröße | 608700 KB | 990144 KB |
100 % | 162,7 % | |
Unbeachtete Queries | 66176 ms | 67840 ms |
100 % | 102,5 % | |
Beachtete Queries | 302848 ms | 23552 ms |
100 % | 7,8 % |
Zitiervorlage
Schäfer, Fjodor: TPC-H-Benchmark Query Performance Optimierung, www.schefa.com/blog/93 (xx.xx.xxxx)