FB5 hat den statement cache eingeführt, damit kann man u.a. Pläne von SPs extrahieren, in der neuen Syntax: mon$compiled_statements ist die virt. Tabelle, um den cache auszulesen:
select * from test_sp('A') -- prepare reicht bereits, damit die SP und alle eventuellen sub-SPs im statement cache landen, inkl Plan
-- alle gecachten statements
select * from mon$compiled_statements
-- pläne für die gesamte SP test_sp
select cast(cs.mon$explained_plan as varchar(20000)) pln
from mon$compiled_statements cs
where cs.mon$object_name = 'TEST_SP'
and cs.mon$object_type = 5
order by cs.mon$compiled_statement_id desc
fetch first row only
Disconnect und der statement cache für diese connection ist wieder leer (mon$compiled_statements ist connectionspezifisch)
MaxStatementCacheSize = 0 in firebird.conf schaltet entgegen der Doku [...] den statement cache nicht ab. Könnt ihr das reproduzieren?
...
Grüße, Volker
Hallo Volker.
Für den Check habe ich die Beispiel-Datenbank "employee.fdb" - View: "PHONE_LIST" verwendet.
Habe zusätzlich in der Schrift:
"Detailed New Features Of Firebird 5" (D.Simonov; A.Kovyazin, Seiten: 15/16).
(dt: Detaillierte Neuerungen von Firebird 5 ((D.Simonov; A.Kovyazin)
nachgeschaut.
Demnach ist standardmäßig das Cachin durch den Eintrag "#MaxStatementCacheSize = 2M" in der "firebird.conf" in Firebird v5.01 (und bei mir) aktiviert.
Ändere ich in der "firebird.conf" den Wert auf "MaxStatementCacheSize = 0" dann ist der Anweisungs-Cache deaktiviert. Dieser Eintrag tut somit das, was ich erwarte.
#MaxStatementCacheSize = 2M (Standard-Einstellung hier bei mir mit ISQL Version: LI-V5.0.1.1469)
Bei Änderungen der .conf ist i.d.R. auch ein Neustart des Servers erforderlich.
Leider ist der Cache derzeit nur je Connection.
Wenn man also ohne Pool arbeitet und auf Grund des Satzversioning mit
- open
- start transaction
- execute Statements (also auch mehrere
- commit/rollback
- close
arbeitet bringt mir der Cache nichts.
In anderen DB's wird der Cache persistiert und liegt nicht nur im Speicher.
Andererseits habe ich in FB 3.0 auch bei komplexen SQL's für den Prepare-Vorgang selten mehr als 10 Millisekunden im SQL-Monitor gesehen, somit ist der Cache nur bei Abfragen relevant, wenn sie mehr als ein paar 1000 Sätze im Result liefern.
Interessant klingt eher der Optimizer. Daraus kann ich meine automatisierte Indexstrategie anpassen um die Trefferquote zu erhöhen (Vergleich Plan-Meldung zu meiner Erstellung).
Schön wäre es , wenn der Optimizer auch Index-Vorschläge auswerfen könnte um diese dann permanent zu erstellen.
Derzeit prüft der FB-Server an hand der vorhandenen Indexe welcher davon am besten zu verwenden ist.
Dabei wird, laut Doku 5.0, die where klausel, ebenso auch die on-Klausel des Joins, analysiert um das jeweils beste Verfahren anzuwenden.
Das Ergebnis ist dann der Plan, den wir auslesen können.
Nun ist es für einen normalen Anwendungentwickler ein leichtes, während der Entwicklung verschiedene Indexe anzulegen und auszuprobieren. Indexe, die nicht genannt werden, können dann wieder entfernt werden. Dazu gibs, relativ allgemein bekannt, ein paar Grundregeln, wie ein Index aussehen könnte.
Da nun mal die FB nach ihren Regeln und der Indexselektivity einen Index sucht, sollte es doch auch möglich sein, die Suchanfrage, die ja den gewünschten Index darstellen sollte, ausgeben zu können.
Wenn die Where-Klausel also F1 = x and F4 = y and F7 = z enthält, wäre das gesuchte Ergebnis ein Index mit F1,F4,F7.
Da Or-Klauseln nach boolscher Algebra zerlegt werden um Indizes zu ermitteln erhält man mehrere Möglichkeiten:
f1 = x and (f4 = y or f7 = z ) ergibt f1 = x and f4=y or f1 = x and f7 = z.
Somit 2 Indexe als Vorschlag: F1,F4 sowie F1,F7.
Der Index F1,F4 ist bereits in F1,F4,F7 inkludiert, daher kann dieser mit verwendet werden.
F1,F7 wäre dann halt zu erstellen.
Wobei letzteres von der DB2 for i (ja, ich weiß das ist AS/400) auchdazu führt, dass der Index F1,F4,F7 trotzdem verwendet werden kann, aber das ist ja was anderes.
Hintergrund dazu ist, dass durch BI-Anwendungen außerhalb der normalen Anwendung mit den Daten umgegangen wird.
Hierzu werden dem Enduser diverse Werkzeuge bereitgestellt um beliebige Abfragen zu gestalten über die sich der ursprüngliche Entwickler nie Gedanken gemacht hat.
Wenn man nun die Enduser an die orginal DB's lässt ist da schon mal Ärger vorprogrammiert, da nun mal ständige Abfragen der BI-Tools auch die DB belasten und ggf. andere Aktivitäten ausbremsen. Beim SQL-Server sogar durch Lock-Escalation bis zum Stillstand während einer Abfrage.
Und je nach Qualität eines BI-Tools bleibt die Verbindung dann auch noch geöffnet, was im lebendenden System bei der FB zu erheblichen Steigerungen der Satzversionen führt.
Meine internen automatisierten Verfahren analysieren also ebenso die Where-Klausel, allerdings so wie ich sie mir denke, erstelle die Indexe und trage diese mit einem Datum in eine Tabelle ein.
Über den gemeldet Plan trage ich dann die Zugriffszeit ein. Bei einer entsprechenden Auswertung zeigte sich in der Vergangenheit eine Trefferquote von 80-90%.
Nun werden die Abfragen jedoch immer komplexer. Es werden viele Tabellen (manchmal bis zu 30) miteinander verknüpft und die Where-Klausel zieht sich ebenso über mehrere Tabellen. Hier ist es ebenso erforderlich, die Klauseln zu analysieren und Indexe zu erstellen.
Aber genau das ist nun das Problem. Die Join-Tabellen werden i.d.R. mittels Left join Primary Key verknüpft. Inner join geht halt nicht, da eben nicht für jeden Schlüssel ein Satz da sein muss.
Trotzdem müsste ich nun herausfinden, wie ggf. die On-Beziehung zu ergänzen wäre um einen passenden Index zu erstellen.
Dazu gibts halt auch Regeln:
select a.f1, a.f2, b.f3 from table1 a left join table2 b on a.f1 = b.f3 and a.f2 = b.f7
where a.f1 = x and b.f5 = y
Der Index a.F1 kann erstellt werden, was ggf. jedoch keine Sinn macht wenn die Selektivity nicht passt.
Für table2 gibts den Primary Key f3,f7. Ich müsste nun einen Index erstellen über
f3,f7,f5 und die Joinbeziehug anpassen. Zumal eine Abfrage b.f5 = y aus dem left join einen inner join impliziert.
Um das zu verhindern, wird automatisiert aus b.f5 = y ein coalesce(b.f5, default) = y.
Allerdings nur, wenn der default = y ist.
Fazit aktuell: Die Tefferquote liegt eher bei 30 - 50%.
Usw. usf.
Da nun die FB-Entwickler wissen, wie ihre Strategie ist, einen Index zu finden, könnte es auch eine Vorschlagsliste, wie der Plan, geben, welche Indexe von Vorteil wären.
Gerd hat geschrieben: ↑So 6. Okt 2024, 19:58
Ändere ich in der "firebird.conf" den Wert auf "MaxStatementCacheSize = 0" dann ist der Anweisungs-Cache deaktiviert. Dieser Eintrag tut somit das, was ich erwarte.
Danke für die Forschung. Könntest Du den Test bitte nochmal mit einer SP wiederholen? Ich habe folgende benutzt, (ohne Plan, der interessiert hierbei nicht), und die SP erscheint immer in mon$compiled_statements, die StatementCache-Abschaltung auf Serverebene und/oder DB-Ebene wird ignoriert (den Server nach Änderung natürlich neu gestartet):
Kurz vorab:
:: Ja, ich versuche es hier mit deiner SP nachzustellen. Nur ist es unklar, ob ich heute noch dazu komme.
:: habe ja mit der Beispiel-Datenbank "employee.fdb" diesbezüglich hantiert.
Die hat einige SP (bspw. Anlegen eines neuen Projekts, ...). Habe ich ausgeführt. Das hat auch, wie von mir erwartet, funktioniert.
Ich melde mich.
(Was mich betrifft, so belasse ich die Standardeinstellung (=Anlegen des Statement-Cache). Aber auch ich möchte sicher sein, dass es funktioniert.)
Viele Grüße
Gerd
ISQL Version: LI-V5.0.1.1469
Linux Mint 22 Cinnamon 6.2.9
Gerd hat geschrieben: ↑Mi 9. Okt 2024, 13:21
Hallo Volker.
Kurz vorab:
:: Ja, ich versuche es hier mit deiner SP nachzustellen. Nur ist es unklar, ob ich heute noch dazu komme.
Viele Grüße
Gerd
Hallo Volker.
So erledigt:
Vielleicht hat es was mit dem Objekttyp "5 - stored procedure") zu tun?
Fakt ist, ich erwarte diesen Eintrag in der Monitortabelle "mon$compiled_statements" bei "MaxStatementCacheSize = 0" - wie du - auch nicht!
Das ist ein Fehler - da hast du den Finger in der Wunde. Und ich schließe mich dem nun auch an.
firebird.conf --> "#MaxStatementCacheSize = 2M" ( = Standard -> Statement Cache ist aktiviert)
danke für den Test! Hätte schon früher Rückmeldung gegeben, aber ich hab noch keine abschließende Antwort zu dem Thema von einem der Entwickler. Nur so viel, dass das schon immer so ist, seit es den statement cache gibt und das ich ein Ticket aufmachen soll, wenn ich es für einen Bug halte.
Der Auslöser: Ich hatte einen Fall, wo eine Firebird 5-DB in einem seltsamen Zustand war (laut checks mit gfix aber alles ok) und innerhalb einer (von hunderten) SPs einen suboptimalen Plan benutzte, ansonsten alles unauffällig und ok. Konnte das leider nicht außerhalb der Anwendung (nächtlicher ETL-Lauf zur Data Warehouse-Erzeugung) reproduzieren. Nach Restore der Firebird 5-DB war die Planregression weg und trat seitdem (ca 6 Wochen) auch nicht mehr auf. Ich hatte zwischendurch den statement cache in Verdacht, weil diese spezielle Planregression bis Firebird 4.04 nicht auftrat. Sie tritt nun aber auch bei Firebird 5.0.1 und 5.0.2 nicht mehr auf, egal ob der statement cache an oder aus ist.
Der einzige Anhaltspunkt, den ich bisher habe, warum die Firebird 5-DB in diesen Zustand geraten sein könnte ist, dass bei dieser einen Installation die [Max]ParallelWorkers überdimensioniert waren, die CPU hatte nur 2 log. CPUs, konfiguriert waren aber 6 [Max]ParallelWorkers. Ich weiß nicht, ob das die Ursache war. Jedenfalls, nach Restore der Firebird 5-DB und Anpassung der Max]ParallelWorkers trat der Effekt nicht mehr auf.
