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.

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 %

 

TPC-H Query Optimization

 

 Zitiervorlage

Schäfer, Fjodor: TPC-H-Benchmark Query Performance Optimierung, www.schefa.com/blog/93 (xx.xx.xxxx)

 

Recent Blog Entries

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.