Firebird schwächelt

[Dieter Naber]

Hallo,

ich setze Firebird 1.52 unter Windows XP Pro SP2 P4 3GHz 512 MB RAM ein. Es handelt sich um eine Gebäude-Leittechnik Software.

Die DB wird in 7/24 Studen-Betrieb gefahren. Belastung: jeden Tag ca. 1 Million neue Datensätze, wobei jeder Eintrag innerhalb der DB zusätzliche interne Einträge in mehreren anderen Tabellen über SP und Trigger nach sich zieht. Alle Datensätze, die älter als 7 Tage sind, werden Nachts gelöscht, anschließend ein Backup und dann ein Sweep.

Im Prinzip wird die DB mit dem Job noch fertig. Problematisch ist aber, wenn eine Abfrage (z.B. für Chart) über die 7 Millionen Datenätze erstellt wird.
Wurde die DB neu erstellt, dauert es nur wenige Sekunden. Sobald die DB aber so gefüllt ist, dass nach 7 Tagen die erste Million Datensätze gelöscht werden. wird die Abfrage jeden Tag langsamer. Dauert teilweise bis über 15 Minuten.

Der Aufbau der Tabelle ist recht simpel:

Integer
Timestamp
Integer
Double
Integer
Integer

PK ist 1. Integer-Feld mit Timestamp
Zusätzlich noch über die selben Felder einen Index in andere Sortierrichtung.

Die Abfrage : Select * from historie where Integer1 = x and Timestamp > ... and Timestamp < .....

Hat jemand Optimierungsvorschläge ?

[Thomas Steinmaurer]

Hallo Dieter,
wenn Du wirklich ein Backup/Restore nach dem Löschen der Datensätze machst, dann ist unser Tipp entweder, dass der PLAN des SELECTs nicht in Ordnung ist, d.h. vielleicht ein Index nicht ausbalanciert ist (nach vielen Inserts). Wenn das Backup/Restore <b>nicht</b> ausgeführt wird, dann kann es mit der Garbage Collection zusammenhängen, die von einem SELECT ausgelöst wird, wenn viele Datensätze gelöscht wurden. Des weiteren würden wir Dir auch empfehlen, die OIT/OAT zu checken, wenn die Datenbank langsam ist, d.h. <b>vor</b> einem Backup/Restore.
<br>
Wenn das nicht weiterhilft, dann schau einfach heute oder morgen in Prag auf der Konferenz vorbei. ;-)
<br>
Thomas und Lucas (von der Firebird Konferenz in Prag ;-)

[Dieter Naber]

Thomas und Lucas (von der Firebird Konferenz in Prag ;-))

na dannn viel Spass.

Also ein Restore führe ich in der Nacht nicht durch. Der Ablauf ist:

1. veraltetet Datensätze löschen
2. Backup (kein Restore) der DB
3. Manuelles Auslösen eines Sweep

OIT/OAT habe ich im Griff:

Database header page information:
Flags 0
Checksum 12345
Generation 12135416
Page size 16384
ODS version 10.1
Oldest transaction 12135407
Oldest active 12135408
Oldest snapshot 12135408
Next transaction 12135409
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 30000
Next header page 0
Database dialect 3
Creation date Nov 2, 2005 10:10:38
Attributes

Variable header data:
Sweep interval: 0
*END

[Heiko Luettge]

Hallo Dieter,

1. dann wäre hier die Freage, wieviel Ram zum Zeitpunkt des Problems von BF benutzt wird.
Wenn der immer größer wird,
muss windows ja irgendwann swappen.

2. Was sagt denn der IBPlanalyzer zu der Query?
Vielleicht sind ja wirklich die Indexwerte so verschieden, dass der Optimzer Mist macht.<br>
Das wäre aber sehr unüblich.<br>

3. Zu dem 3Ghz fällt mir noch das alte Interbase-Problem mit Hyperthreading ein (Stichwort Affinity, IBAffinity). <br>
@Thomas: Ist das in Firebird mittlerweise gelöst ?<br>
Das Problem müßte aber beim Starten schon auffalen.

Heik

[Thomas Steinmaurer]

Hallo Dieter,
verstehe ich das also richtig, dass Du ein Sweep jede Nacht nach dem Löschen durchführst? Wenn ja, dann sollte die Garbage Collection durch ein SELECT nicht der Grund sein. Vorausgesetzt natürlich, dass das Sweep "effektiv" war, d.h. auch wirklich den Aufräumjob machen konnte. Dies passiert am besten, wenn sonst keine anderen Verbindung, d.h. auch offene Transaktionen existieren.
<br>
Was mir noch aufgefallen ist, dass Du eine Page Size von 16K in Kombination mit Page Buffers 30000. Mit dieser Kombination wird bestimmt, wieviel Hauptspeicher für das Caching maximal verwendet werden kann. Das sind ca. 468 MB nur für die eine Datenbank und das für die SuperServer Architektur. Solltest Du Classic Server einsetzen, dann multipliziert sich dieser Wert noch mit der Anzahl der DB-Verbindungen zu dieser Datenbank. Wie gesagt, nur für diese eine Datenbank. Sollte dann noch eine zweite oder dritte DB mit im Spiel sein, ... Ich denke nun eher, dass einfach zu viel RAM für das Caching verwendet wird, und letztendlich das Betriebssystem swappen muss. Setzt Du Super oder Classic Server ein?
<br>
@Heiko: Das "SMP-Problem" wurde mit Firebird 1.5.2 und wird auch mit 2.0 nicht beseitigt sein, sofern man von der SuperServer Architektur spricht. Classic hatte dieses Problem nie und kann wirklich jede CPU ausnützen.
<br>
Thoma

[Dieter Naber]

Hallo Thomas,

Mit den Page Buffers ist mir gestern auch aufgefallen, habe ich erst mal auf 15000 reduziert. Der Rechner bedient nur diese DB, Hyperthreading der CPU ist abgeschaltet und die Systemwiederherstellungvon Windows ist abgeschaltet.

Ich verwende den Superserver und führe immer nach dem Backup manuell ein Sweep durch. Aber ob der "effektiv" ist, ist eine interessante Frage. Tatsache ist, das bedingt durch den Einsatz im technischen Umfeld Meldungen und Messwerte ständig, auch Nachts, abgespeichert werden müssen. Das können bis zu 30 Datensätze /Sekunde sein. Jeder einzelne Datensatz wird gecommittet. Aber natürlich werden auch während des Sweeps (ca. 20 Minuten) tausende von Datensätzen abgespeichert. Die DB kennt keine Ruhephase, in der sie sich nur mit sich selbst beschäftigen könnte

[Thomas Steinmaurer]

Hallo Dieter,
in der Roadmap auf der Konferenz wurde gesagt, dass erst mit Firebird 2.0 eine max. Anzahl der Page Buffers von 128K sinnvoll einsetzbar ist. Daher könnte es durchaus sein, dass ein Page Buffers Wert > 10.000 (eine magische Grenze, die immer wieder als oberste Schranke angegeben wurde) kontraproduktiv ist. Ich würde an Deiner Stelle einfach einmal ein Page Buffers von 0 probieren. Es wird dann der serverweite Default-Wert von 2048 für SuperServer verwendet.
<br>
Thoma

[Dieter Naber]

Also es scheint auch am Index zu liegen, sprich der Sweep kommt nicht so richtig zum Zuge. Laut Statistik sieht er so aus:

DATENPUNKT_DYN (147)
Primary pointer page: 170, Index root page: 171
Data pages: 19362, data page slots: 24499, average fill: 67%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 19361
80 - 99% = 0

Index DATENPUNKT_DYN_IDX1 (1)
Depth: 3, leaf buckets: 4600, nodes: 4559323
Average data length: 3.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 39
20 - 39% = 376
40 - 59% = 2988
60 - 79% = 754
80 - 99% = 443

Index PK_DATENPUNKT_DYN (0)
Depth: 3, leaf buckets: 4184, nodes: 4559586
Average data length: 3.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 112
20 - 39% = 389
40 - 59% = 2067
60 - 79% = 601
80 - 99% = 1015

Nach einem Restore sieht das ganze so aus:

DATENPUNKT_DYN (147)
Primary pointer page: 170, Index root page: 171
Data pages: 18763, data page slots: 18763, average fill: 67%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 18762
80 - 99% = 0

Index DATENPUNKT_DYN_IDX1 (1)
Depth: 3, leaf buckets: 2473, nodes: 4417160
Average data length: 3.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 2472

Index PK_DATENPUNKT_DYN (0)
Depth: 3, leaf buckets: 2473, nodes: 4417160
Average data length: 3.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 2472

Kann man den Index wieder ohne Restore hinbiegen

[Heiko Luettge]

Hallo Dieter,

alter index idx_bla inacitve
alter index idx_bla active

baut ihn komplett wieder auf

set statistics index idx_bla setzt die selectivity neu

Was nu besser ist ?
Keine Ahnung

Ich nutze das 2.

Heik

[Thomas Steinmaurer]

Hallo,
um nur die Selektivität eines Index neu zu berechnen reicht ein SET STATISTICS ... Soll der Index komplett neu aufgebaut werden, dann ist ein deaktivieren/aktivieren notwendig.
<br>
Dieter, Irgendwie drehen wir uns hier im Kreis. Zumindest habe ich so das Gefühl. Der Index DATENPUNKT_DYN_IDX1 weist die selben Charakteristika wie der Index für den Primärschlüssel auf. Ist DATENPUNKT_DYN_IDX1 ein Index ebenfalls auf dem Primärschlüsselattribut? Wenn ja, DESCENDING? Hast Du noch andere Indizes auf dieser Tabelle, womöglich auf Feldern, die nicht UNIQUE sind? Ein Problem im Zusammenhang mit der Garbage Collection sind Indizes mit sehr vielen Duplikaten als Feldwerte. Das Entfernen dieser Indexeinträge braucht sehr viel CPU und kann auch über längere Zeit laufen. Dieses Problem wurde mit der neuen internen Indexstruktur in Firebird 2.0 beseitigt.
<br>
Du bist uns auch noch den verwendeten PLAN für Deine Abfrage schuldig. ;-)
<br>
Thoma

[Heiko Luettge]

Hallo Thomas,
<p>
naja beseitigt nicht, aber extrem verbessert.
Wie ar es eigentlich i Prag.
Gibt es von den Foren Download.
<p>
Heik

[Thomas Steinmaurer]

Hallo Heiko,
<br>
Die Konferenz war spitze. Sehr informativ und wir hatten natürlich auch sehr viel Spaß. Für Nicht-Konferenz-Teilnehmer wird es eine CD käuflich zu erwerben geben. Ein Blog der Konferenz gibt es hier: http://misc.upscene.com/blog/. Meine Bilder habe ich hier zur Verfügung gestellt: http://www.iblogmanager.com/download/misc/fbcon2005/fbcon2005.html
<br>
Thoma

[Dieter Naber]

Hallo,

ja, der Index DATENPUNKT_DYN_IDX1 bezieht sich auf die selben Felder, wie der PK nur eben DESCENDING.

Es gibt nur die beiden Indizes. Den Tip mit Alter Index .. habe ich versucht, aber wie von mir befürchtet, schlägt das bei dem PK fehl mit dem anderen Index geht es. Die Buffers für die DB habe ich am Donnerstag auf 2048 herabgesetzt.

Bin jetzt aber nicht mehr in der Firma, werde den Plan am Montag noch posten, hatte ich vergessen. Dann kann ich auch schon sagen, ob die Buffers was gebracht haben.

Allen ein erholsames Wochenende

Diete

[Heiko Luettge]

Hallo Dieter,
<p>
dann fahr mal schleunigst wieder hin!<br>
16:00 Feierabend , kann ja wohl nicht sein !!
<p>
Braucht ihr noch Leute ?
<p>
Heik

[Dieter Naber]

Hallo,
jetzt wie versprochen den Plan der Abfragen, habe auch noch die DDL reingestellt:

CREATE TABLE DATENPUNKT_DYN (
PUNKT A_INTEGER NOT NULL /* A_INTEGER = INTEGER */,
ZEIT A_NOW NOT NULL /* A_NOW = TIMESTAMP */,
GERAET A_INTEGER NOT NULL /* A_INTEGER = INTEGER */,
HW A_FLOAT NOT NULL /* A_FLOAT = DOUBLE PRECISION */,
HW_OK A_BOOL NOT NULL /* A_BOOL = CHAR(1) CHECK (VALUE in('*',' ')) */,
ERSTA A_INTEGER NOT NULL /* A_INTEGER = INTEGER */,
ERSTA_OK A_BOOL NOT NULL /* A_BOOL = CHAR(1) CHECK (VALUE in('*',' ')) */,
OPSTA A_INTEGER NOT NULL /* A_INTEGER = INTEGER */,
OPSTA_OK A_BOOL NOT NULL /* A_BOOL = CHAR(1) CHECK (VALUE in('*',' ')) */
);

/************************************************** ****************************/
/**** Primary Keys ****/
/************************************************** ****************************/

ALTER TABLE DATENPUNKT_DYN ADD CONSTRAINT PK_DATENPUNKT_DYN PRIMARY KEY (PUNKT, ZEIT);

/************************************************** ****************************/
/**** Indices ****/
/************************************************** ****************************/

CREATE DESCENDING INDEX DATENPUNKT_DYN_IDX1 ON DATENPUNKT_DYN (PUNKT, ZEIT);

/************************************************** ****************************/
/**** Descriptions ****/
/************************************************** ****************************/

DESCRIBE TABLE DATENPUNKT_DYN
'Enthaelt Historie dynamische Werte zu allen Datenpunkten';

/************************************************** ****************************/
/**** Privileges ****/
/************************************************** ****************************/

/* Privileges of procedures */
GRANT INSERT ON DATENPUNKT_DYN TO PROCEDURE AKT_DATENPUNKTE;
GRANT SELECT, DELETE ON DATENPUNKT_DYN TO PROCEDURE DEL_DP;

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++

Abfrage 1:

select
D.zeit,
D.hw,
D.ersta,
D.opsta
from datenpunkt_dyn D
where D.zeit > :START and D.zeit < :ENDE
and D.punkt = :NR
order by D.zeit

Statement Plan
--------------
PLAN SORT ((D INDEX (PK_DATENPUNKT_DYN)))

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++

Abfrage 2:

select
first(1) D.zeit,
D.hw
from datenpunkt_dyn D
where D.zeit < :Zeit and D.punkt = :NR
order by D.zeit desc

Statement Plan
--------------
PLAN SORT ((D INDEX (PK_DATENPUNKT_DYN)))

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++

So wie der Plan aussieht, kann ich ja wohl den absteigenden Index löschen.

Die Ausführungszeit der Abfragen hat sich jetzt auf einige Sekunden reduziert. Das kann man wohl in erster Linie auf die Reduzierung der Page buffers auf 2048 zurückführen. Es scheint tatsächlich so zu sein, das Firebird zu viel Speicher übel nimmt. Viel hilft wohl eben nicht immer viel.

Gruß

Diete