Donnerstag, Mai 21, 2015

Dom Brooks über Plan-Stabilität

Dom Brooks zählt zu den Blog-Autoren, die sich am intensivsten mit Fragen der Stabilität von SQL-Ausführungsplänen beschäftigt haben. In Strategies for Minimising SQL Execution Plan Instability liefert er einen recht umfassenden historischen Überblick zur Entwicklung des Optimizers und jener Ergänzungen, die eingeführt wurden, um seine Informationsgrundlagen und Entscheidungsmöglichkeiten zu erweitern. Dabei gilt natürlich, dass die größere Flexibilität und die Einführung adaptiver Strategien dazu führen, dass die Stabilität der Optimizer-Entscheidungen geringer wird. Die grundlegende Frage dabei ist: "Do you want the Optimizer to try to get a better execution plan sometimes? If the answer is yes, then you have to accept that it will get it wrong from time to time." Um die Stabilität von Plänen wieder zu erhöhen, wurden im Laufe der Zeit diverse Hilfsmittel eingeführt, vor allem SPM (SQL Plan Management) in 11g, die es erlauben, die Planauswahl des Optimizers zu kontrollieren und der Autor erläutert ausführlich, welche Möglichkeiten in diesem Zusammenhang existieren und wo ihre Einschränkungen liegen. Insgesamt liefert der Artikel eine ziemlich vollständige Liste der im Rahmen der Fragestellung der Stabilität/Instabilität von Plänen relevanten Faktoren.

Mittwoch, Mai 20, 2015

postgres Ressourcen

Mit den Links zu den - in der Regel sehr lesenswerten - Artikeln des dbi-services Blog habe ich manchmal nicht allzu viel Glück, weil sie bisweilen statt beim gewünschten Artikel auf der Latest Entry Seite ankommen (was laut Franck Pachot ein Joomla-Problem sein könnte - und demnächst erledigt sein sollte, da ein Umzug nach wordpress geplant ist). Trotzdem versuche ich es ein weiteres Mal mit einer Verlinkung: diesmal auf den Artikel "What will be coming with the next major version of postgresql?" von Daniel Westermann, der die einschlägigen Ressourcen zur postgres-Weiterentwicklung zusammenfasst, darunter vor allem auch:
Persönlich verfolge ich nur die Blogs, in denen letztlich alles interessanten Entwicklungen angesprochen werden.

Montag, Mai 18, 2015

Upsert in postgres

In meiner universitären Vergangenheit hätte ich es vielleicht "ein Desiderat der Forschung" genannt: ein Kommando, mit dessen Hilfe sich in postgres ein INSERT oder UPDATE in Abhängigkeit davon durchführen lässt, ob ein gegebener Schlüssel bereits in der Zieltabelle vorliegt. Diese in anderen Datenbanken als MERGE oder UPSERT bezeichnete Funktionalität ist seit vielen Jahren die wahrscheinlich größte Lücke im ansonsten sehr mächtigen SQL-Dialekt von postgres. Mit Release 9.5 wird diese Lücke nun offenbar endlich geschlossen - wobei sich die postgres-Entwickler dafür entschieden haben, die Option als Ergänzung des INSERT-Kommandos zu implementieren:
INSERT INTO ... VALUES ...
   ON CONFLICT
   DO UPDATE SET ...
Ein paar einführende Erläuterungen zum Verhalten liefern unter anderem Craig Kerstiens und Michael Paquier, vor allem aber Hubert Lubaczewski, der - wie üblich - die umfangreichste Untersuchung liefert und dabei auch die Performance betrachtet.

Dienstag, Mai 12, 2015

Degenerierte Indizes

Seit ich begonnen habe, mich mit Datenbanken zu beschäftigen, lautet eine der populärsten Fragen in Oracle-Foren: mit welcher Häufigkeit soll ich meine B*Tree-Indizes neu aufbauen lassen? Zu behaupten darüber hätten Kriege stattgefunden, wäre vielleicht ein wenig übertrieben - aber heftige Auseinandersetzungen waren es allemal. Inzwischen ist dieser Konflikt weitgehend zur Ruhe gekommen, was vermutlich damit zusammen hängt, dass die Antwort eigentlich unstrittig ist: Oracles Implementierung von Indizes macht Rebuilds weitgehend unnötig - sie sind nur für seltene Fälle mit besonderen Bedingungen für die zugehörigen DML-Operationen relevant. Richard Foote hat sich schon vor mehr als zehn Jahren in Oracle B-Tree Index Internals: Rebuilding The Truth mit den Aussagen der Befürworter von regelmäßigen Rebuild-Operationen auseinander gesetzt und diese widerlegt. Trotzdem findet man den Vorschlag solcher Rebuilds auch noch in jüngeren Publikationen, wobei die Formulierungen allmählich den Bereich unfreiwilliger Komik erreichen, was David Aldridge auf Richards Footes Webseite kommentierte:
Interesting quote from Page 728 of that book: “Even if index rebuilding were to be proven as [sic] a useless activity, the Placebo effect on the end users is enough to justify the task.”. It opens up a whole new class of performance tuning … perhaps it could be called “Faith-based Tuning”.
Jonathan Lewis schrieb dazu: "If the only effect is a placebo effect couldn’t you just tell the users that you had rebuilt the indexes rather than actually doing it?"

So viel zur Geschichte - wobei es sich aus meiner Sicht um Ausschnitte aus dem Kapitel zur Auseinandersetzung zwischen wissenschaftlicher und mythischer Welterklärung handelt. Aber der eigentliche Anlass für diesen Eintrag war ein anderer: Mohamed Houri hat gerade einen Artikel zu seiner Verwendung des Scripts Index Sizing von Jonathan Lewis veröffentlicht, mit dessen Hilfe er einen jener seltenen degenerierten Indizes identifizieren konnte, die tatsächlich von einem Rebuild profitieren - alles deutlich komprimiert werden können. Letztlich leistet das Skript dabei nicht mehr (und nicht weniger), als die tatsächliche Größe eines Index mit der angesichts des Datenvolumens erwartbaren Minimalgröße zu vergleichen. Mohamed ergänzt dieses Ergebnis in seinem Artikel um eine Visualisierung der durch die Funktion sys_op_lbid ermittelten Anzahlen von Index-Einträgen pro Leaf-Block und diese Darstellung zeigt, dass es neben einer großen Zahl gut gefüllter Blocks (mit 422 Einträgen) eine noch größere Anzahl relativ schwach gefüllter Blöcke (mit 113 Einträgen) gibt. In seinem Kommentar zum Artikel erinnert Jonathan Lewis wiederum daran, dass hier womöglich das bekannte Problem der Vervielfältigung von ITL Einträgen im Spiel ist, das in solchen Fällen regelmäßig aufzutreten pflegt.

Vielleicht hätte ich mir (und meinen vorgestellten Lesern) den historischen Einstieg auch ersparen können, aber offenbar habe ich inzwischen das Alter erreicht, in dem man beginnt, von den alten Zeiten zu schwadronieren, in denen alles besser/schlechter/anders gewesen ist.

Freitag, Mai 08, 2015

Optimizer-Transformationen: Predicate Pushing in Oracle und Postgres

Das in der Praxis am häufigsten verwendete Verfahren der Query-Performance-Optimierung ist vermutlich in allen RDBMS immer noch das der Umformulierung: da es in SQL für so ziemlich jede Fragestellung mehrere Lösungsmöglichkeiten gibt, kann man in aller Regel versuchen, eine andere syntaktische Variante zu wählen - und hoffen, dass der Optimizer dafür einen effektiveren Plan findet. Ich will dieses Verfahren nicht grundsätzlich negativ bewerten: es kann ohne Zweifel seine Erfolge vorweisen und es gibt RDBMS, bei denen die Instrumentierung so mangelhaft ist, dass man mit ihrer Hilfe nicht allzu viele Hinweise auf die eigentlichen Probleme der Ausführung erhält. Die meisten RDBMS besitzen allerdings inzwischen eine gute oder sogar sehr gute Instrumentierung, die eine exaktere Analyse von Zugriffsproblemen gestattet.

Darüber hinaus sind die Optimizer der meisten RDBMS selbst dazu in der Lage, eine Query intern in eine semantisch äquivalente Form umzuwandeln, die sich leichter optimieren lässt. Eine relativ einfache Option im Rahmen solcher Transformationen ist das Predicate Pushing, das eine einschränkende Bedingung, die für eine View (bzw. Inline-View) angegeben wird, an die in der View enthaltenen Sub-Queries überträgt. Dazu zunächst ein Beispiel mit Oracle:

-- 12.1.0.1
drop table t1;
drop table t2;

create table t1
as
select rownum id, lpad('*', 50, '*') col1
  from dual
connect by level <= 10000;

create table t2
as
select rownum id, lpad('*', 50, '*') col1
  from dual
connect by level <= 10000;

create index t1_idx on t1(id);
create index t2_idx on t2(id);
 
select *
  from (select *
          from t1
         union all
        select *
          from t2)
 where id <= 10;
 
------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |    20 |  1100 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                 |        |    20 |  1100 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1     |    10 |   550 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T1_IDX |    10 |       |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2     |    10 |   550 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | T2_IDX |    10 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID"<=10)
   6 - access("T2"."ID"<=10)
 
 
select * from t1 where id <= 10
 union all
select * from t2 where id <= 10;

-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |    20 |  1100 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL                           |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |    10 |   550 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX |    10 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2     |    10 |   550 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_IDX |    10 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"<=10)
   5 - access("ID"<=10)

Gesucht sind alle Datensätze mit einer id <= 10: in der ersten Query erfolgt diese Einschränkung für eine Inline-View, in der zweiten Query wird sie für beide angesprochenen Tabellen explizit angegeben. Der Blick auf den Ausführungsplan zeigt, dass die Einschränkung auch bei der Verwendung der Inline-View an die Basistabellen weitergeleitet wird: es ist also nicht so, dass die Klammerung dazu führen würde, dass erst die Menge aller Sätze aus t1 und t2 ermittelt werden müsste, ehe die Einschränkungen durchgeführt werden. Einen Unterschied gibt es allerdings zwischen den beiden Varianten: die Cost-Angabe für die Version mit der Inline-View ist nur halb so hoch, wie die für die beiden über UNION ALL verbundenen Queries. Außerdem ist für die Inline-View ein zusätzlicher Step VIEW enthalten, der aber keinen Einfluss auf den eigentlichen Zugriff haben sollte. Meiner Einschätzung nach ist die Kostenangabe für den zweiten Fall plausibler, da die Kosten der beiden Teiloperationen addiert werden sollten. Natürlich könnte sich ein solcher Unterschied im Rahmen einer komplexeren Query auf folgende Schritte auswirken.

Wie sieht der Fall nun für Postgres aus? Dazu ein entsprechendes Beispiel:

-- PostgreSQL 9.3.6
drop table t1;
drop table t2;

create table t1
as
with
basedata as (
select * from generate_series(1, 10000) id
)
select id
     , lpad('*', 50, '*')::text col2
  from basedata
;

-- manuelle Statistikerfassung
analyze t1;

create index t1_idx on t1(id);


create table t2
as
with
basedata as (
select * from generate_series(1, 10000) id
)
select id
     , lpad('*', 50, '*')::text col2
  from basedata
;

-- manuelle Statistikerfassung
analyze t2;

create index t2_idx on t2(id);

explain
select *
  from (select *
          from t1
         union all
        select *
          from t2) t
where id <= 10;

+------------------------------------------------------------------------+
|                               QUERY PLAN                               |
+------------------------------------------------------------------------+
| Append  (cost=0.29..16.88 rows=18 width=55)                            |
|   ->  Index Scan using t1_idx on t1  (cost=0.29..8.44 rows=9 width=55) |
|         Index Cond: (id <= 10)                                         |
|   ->  Index Scan using t2_idx on t2  (cost=0.29..8.44 rows=9 width=55) |
|         Index Cond: (id <= 10)                                         |
+------------------------------------------------------------------------+

explain
select * from t1 where id <= 10
union all
select * from t2 where id <= 10;

+------------------------------------------------------------------------+
|                               QUERY PLAN                               |
+------------------------------------------------------------------------+
| Append  (cost=0.29..17.06 rows=18 width=55)                            |
|   ->  Index Scan using t1_idx on t1  (cost=0.29..8.44 rows=9 width=55) |
|         Index Cond: (id <= 10)                                         |
|   ->  Index Scan using t2_idx on t2  (cost=0.29..8.44 rows=9 width=55) |
|         Index Cond: (id <= 10)                                         |
+------------------------------------------------------------------------+

Postgres kommt somit für beide Varianten exakt zum gleichen Plan.

Nun ist das Predicate Pushing für den Fall einer Inline-View mit über UNION ALL verknüpften Sub-Queries natürlich eher trivial, weil es auf der Hand liegt, dass alle Teiloperationen komplett getrennt behandelt werden können, da das Gesamtergebnis als Summe der Teilergebnisse definiert ist. Aber die Optimizer der großen RDBMS beherrschen noch sehr viele andere Tricks, die dafür sorgen können, dass die tatsächlich ausgeführte Operation deutlich anders aussieht, als das, was das abgesetzte SQL erwarten ließe. Für Oracle kann man die Evaluierung der Transformationen und ihr Ergebnis mit Hilfe eines CBO-Trace-Files (Event 10053) untersuchen - und für postgres könnte man sich den Optimizer-Code anschauen bzw. debuggen. In vielen Fällen genügt aber eine Blick auf den Ausführungsplan, um zu erkennen, was intern geschehen sein muss.

Dass es trotz dieser Transformationen immer noch sehr viele Fälle gibt, in denen sich relativ simple Änderungen in der Formulierung von SQL-Queries massiv auf den Ausführungsplan auswirken, steht auf einem anderen Blatt.