Datenlogger mit MSSQL und Index-Fragmentierung

[BigAl]

Hallo,

Ich muss für ein aktuelles Projekt Daten loggen. Dabei handelt es sich um einen relativ einfach aufgebauten Datensatz in der Form:

ID INT IDENTITY
LogTime DATETIME NOT NULL
DST BIT NOT NULL,
(... ca. 150 x real ...)

Primärschlüssel ist ID (Clustered), ein zusätzlicher Schlüssel ist für die "LogTime" definiert.

In die Tabelle werden im Sekundentakt Daten geschrieben. Also 86400 Datensätze pro Tag. Die Daten werden 90 Tage gehalten, ehe die ältesten Daten gelöscht werden. Das Schreiben wird von einer eigene Thread übernommen. Diese prüft auch einmal pro Minute, ob alte Datensätze vorhanden sind (DELETE TOP (100) FROM Table WHERE LogTime < ...).

Das Problem ist nun, dass das der von den Indizes (Indexe (?)) benötigte Speicherplatz immer weiter ansteigt und sich nie verkleinert. Ein REORGANIZE oder REBUILD schafft hier zwar Abhilfe, blockiert aber recht lange. Beim REORGANIZE handelt es sich zwar um eine Online-Funktion die theoretisch in einer unabhängigen Task laufen könnte aber die Frage ist, ob da dauerhaft hilft. Die Software muss 24/7/365 laufen und visualisiert einen recht kritischen Prozess. Die Sicherheitsfunktionen sind zwar nicht von der Software abhängig, aber wenn die Oberfläche nicht aktualisiert werde die Benutzer nervös...

Hat von Euch schon einmal jemand sowas gemacht? Wie bekommt ihr das in den Griff? Letztendlich soll die Datenbank nur bis zu einem bestimmten Punkt wachsen (86400 * 90 = 7.7 Mio. Datensätze) und dann möglichst in dieser Größe verharren. Die Daten selbst geben ja den Speicher nach dem DELETE wieder für neue Daten frei, aber der vom Index benötigte Speicher wächst kontinuierlich.

Mein Wunsch wäre, das ganze auf einem MS SQL-Express Server laufen zu lassen. Rechnerisch benötige ich ca. 5 bis 6 GB für die gesamte Datenbank (es gibt noch ein paar andere Tabellen die aber selten beschrieben werden). Grundsätzlich wäre der Express-Server mit 10 GB Datenbankgröße und von der Geschwindigkeit ausreichend.

Man könnte natürlich auch ein eigenes Datenfile für die Logger-Daten erstellen, da diese eh immer sequentiell sind, aber das würde viele andere Dinge verkomplizieren.

Also: Hat jemand eine Idee wie man einen Ringpuffer mit MS SQL sinnvoll implementieren kann? Wie macht ihr sowas?

Alex

[Blup]

Hallo,
wird diese Datenbank auch regelmäßig ausgewertet oder nur im Fehlerfall?
Denn dann sollten die für die Auswertung nötigen Indizes auch erst dann angelegt werden.
Das Schreiben der Datensätze geht dadurch viel schneller.
Statt Datensätze einzeln zu löschen, lege für jeden Tag oder Monat eine neue Datenbank an.

[BigAl]

Zitat von Blup:

Hallo,
wird diese Datenbank auch regelmäßig ausgewertet oder nur im Fehlerfall?
Denn dann sollten die für die Auswertung nötigen Indizes auch erst dann angelegt werden.

Ja, die Daten werden permanent angezeigt. Zwar nur ein Teil der Kanäle (ca. 20) aber die anderen müssen immer verfügbar sein. Der Nutzer kann jederzeit in den Detailansichten auf diese zugreifen.

Zitat von Blup:

Das Schreiben der Datensätze geht dadurch viel schneller.

Die Geschwindigkeit beim Schreiben ist kein Problem. Ich habe da mal einen Test gemacht und könnte mit dem Client ca. 3500 bis 4000 Datensätze pro Sekunde schreiben. Und dabei scheint der Flaschenhals noch außerhalb des SQL-Servers zu liegen. Dessen Last ist dann gerade mal bei 10% bis 15%...

Zitat von Blup:

Statt Datensätze einzeln zu löschen, lege für jeden Tag oder Monat eine neue Datenbank an.

Auf die Idee bin ich noch nicht gekommen. Da muss ich mir mal Gedanken machen. Es geht ja - wie gesagt - nur um diese eine Tabelle. Evtl. noch um eine Alarm-Tabelle, welche aufgrund der Konsistenz dazu gepackt werden könnte. Alle anderen Daten sind fest, bzw. ändern sich nur sehr sehr selten...

[HolgerX]

Hmm..

Oder nach dem Delete ein ALTER INDEX ALL auf die Tabelle.

https://learn.microsoft.com/de-de/sq...l-server-ver17

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

[BigAl]

Ach ja,

komischerweise belegt der Primärindex Unmengen an Seiten:

Code:

TABLE   INDEX                     FRAGMENTATION [%] PAGE COUNT
------- ----------------------- ----------------- ----------
Trends  PK_Trends_ID                         59,80     590734
Trends  IDX_Trends_LogTime                    0,04      21326

[BigAl]

Zitat von HolgerX:

Hmm..

Oder nach dem Delete ein ALTER INDEX ALL auf die Tabelle.

https://learn.microsoft.com/de-de/sq...l-server-ver17

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Ein REBUILD blockiert die Tabelle relative lange (aktuell ca. 1,5 bis 2 Minuten). Evtl. ein REORGANIZE, welches auch relativ lange benötigt, aber das Schreiben und Lesen weiterhin zulässt. Ob es aber zielführend ist das in kurzen Intervallen auszuführen ist fraglich...

[MyRealName]

Alte Datensätze müssen doch nicht jede Minute geprüft und gecheckt werden (es sollten 60 sein). Mach es einmal am Tag, am Besten zu einer Zeit, wo Du ohne Probleme auch den Index wieder herrichten kannst. Nachts um 2 zum Bsp.

[BigAl]

Zitat von MyRealName:

Alte Datensätze müssen doch nicht jede Minute geprüft und gecheckt werden (es sollten 60 sein). Mach es einmal am Tag, am Besten zu einer Zeit, wo Du ohne Probleme auch den Index wieder herrichten kannst. Nachts um 2 zum Bsp.

Naja. Einmal pro Minute max. 100 Datensätze löschen hat nicht wirklich Einfluss auf die Performance. Einmal pro Stunde müsste ich dann schon > 3600 alte Datensätze löschen. Das geht dann auch wieder zu Lasten des transaction-log-Files...

Nachts ist keine gute Idee. Dann lieber zu einer Zeit zu der die Ingenieure greifbar sind. Sonst gibt es nachts Anrufe

[MyRealName]

Bei 7.7 mio DS zu jeder Zeit in der DB machen 3600 DS nichts aus, zumal es indiziert ist. Und selbst 24*3600 DS macht da nicht viel aus. Es geht ja auch nicht ums Löschen, sondern um den Index neu bauen. Mach das 1x pro Tag zu einer ruhigen Stunde. Weil Dir geht es ja eh nur um den Speicherplatz, der Index funktioniert ja korrekt auch nach dem Löschen.

[Bernhard Geyer]

evtl. anderes DBMS nehmen?
z.B. ein PostgreSQL welche einen Mechanismus hat automatisch immer wieder indizes zu defragmentieren hat, so das hier das evtl. gar nicht auffällt wenn das passiert.