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:

Zitat von Blup (Beitrag 1550273)

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:

Zitat von Blup (Beitrag 1550273)

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:

Zitat von Blup (Beitrag 1550273)

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:

Zitat von HolgerX (Beitrag 1550275)

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:

Zitat von MyRealName (Beitrag 1550278)

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.

[Uwe Raabe]

Nur mal so ein wenig out-of-the-box gedacht:

Was wäre wenn du alle Datensätze im Voraus anlegst, diese nach aufsteigender ID per UPDATE befüllst und wenn du beim letzten angekommen bist wieder mit dem ersten weitermachst (ein Ringbuffer halt). In einer separaten Tabelle führst du ID und LogTime des ältesten und neuesten Satzes mit.

Da ja (nach 90 Tagen) für jede Sekunde ein

SQL-Code:

LogTime NOT NULL

-Datensatz vorhanden ist, kannst du unter Berücksichtigung des Überlaufs für jede Sekunde die ID bestimmen. Den Index auf LogTime brauchst du nicht mehr und der für ID ändert sich nicht, da beim UPDATE die ID gleich bleibt.

Abfragen nach Zeiträumen errechnen die WHERE Klausel für die entsprechenden ID Werte (in den ersten 90 Tagen noch mit

SQL-Code:

LogTime NOT NULL

) und die Sortierung läuft über ein

SQL-Code:

ORDER BY LogTime

.

Das müsste man sicher noch im Detail ausarbeiten, aber es wäre zumindest ein interessanter Ansatz.

[BigAl]

Zitat:

Zitat von Uwe Raabe (Beitrag 1550284)

Nur mal so ein wenig out-of-the-box gedacht:

Was wäre wenn du alle Datensätze im Voraus anlegst, diese nach aufsteigender ID per UPDATE befüllst und wenn du beim letzten angekommen bist wieder mit dem ersten weitermachst (ein Ringbuffer halt). In einer separaten Tabelle führst du ID und LogTime des ältesten und neuesten Satzes mit.

Da ja (nach 90 Tagen) für jede Sekunde ein

SQL-Code:

LogTime NOT NULL

-Datensatz vorhanden ist, kannst du unter Berücksichtigung des Überlaufs für jede Sekunde die ID bestimmen. Den Index auf LogTime brauchst du nicht mehr und der für ID ändert sich nicht, da beim UPDATE die ID gleich bleibt.

Abfragen nach Zeiträumen errechnen die WHERE Klausel für die entsprechenden ID Werte (in den ersten 90 Tagen noch mit

SQL-Code:

LogTime NOT NULL

) und die Sortierung läuft über ein

SQL-Code:

ORDER BY LogTime

.

Das müsste man sicher noch im Detail ausarbeiten, aber es wäre zumindest ein interessanter Ansatz.

Hallo Uwe

Ich bin immer wieder begeistert von Deiner Denkweise. Genau so ein Ansatz ist mir im Kopf herumgeschwirrt. Das könnte funktionieren, wenn da auch mit einer kleinen Hürden für die ich noch keine Lösung habe:

Aktuell speichere ich einen NULL-Datensatz ab, der mir eine Unterbrechung der Aufzeichnung markiert. Wird die Anlage also mal abgeschaltet, dann kann ich damit die Lücke im Graph entsprechend visualisieren:

Anhang 57682

Wie ich diesen "Sprung" in der vordefinierten Tabell festhalten soll weiß ich momentan noch nicht. Eine Idee wäre eine Spalte wie "NextID" neben der "ID" einzuführen. Diese speichert dann den jeweils nächsten gültigen Index. Die Sprünge müsste ich dann halt mit meiner Software herausfiltern. Da ich in der Anzeige maximal zwei Stunden pro Seite ausgebe wäre das bei 7200 Datensätzen nicht die Welt...

Ich werde aber auf jeden Fall diesen Ansatz weiter verfolgen.

Danke!

[Uwe Raabe]

Die Logik aus einem sekundengenauen Datum eine ID zu berechnen erfordert allerdings genau einen Datensatz für jede Sekunde. Eine solche Unterbrechung würde dann eben auch durch mehrere Null-Sätze in der Tabelle hinterlegt werden. Ob du das dann als einen Eintrag anzeigst bleibt dir ja dann immer noch offen.

[BigAl]

Zitat:

Zitat von Uwe Raabe (Beitrag 1550286)

Die Logik aus einem sekundengenauen Datum eine ID zu berechnen erfordert allerdings genau einen Datensatz für jede Sekunde. Eine solche Unterbrechung würde dann eben auch durch mehrere Null-Sätze in der Tabelle hinterlegt werden. Ob du das dann als einen Eintrag anzeigst bleibt dir ja dann immer noch offen.

Klar. Es gibt halt zwei Möglichkeiten:

1. Nach einer Unterbrechung die übersprungenen Datensätze mit entsprechenden Null-Werten füllen.

oder

2. Eben mit der "NextID"-Markierung entsprechend markieren wie viele Datensätze zu überspringen sind.

Bei Lösung 2 müsste halt nur ein Datensatz aktualisiert werden und man könnte die Null-Daten nach dem Einlesen schneller bearbeiten (überspringen), da nicht jeder Datensatz betrachtet werden müsste. Allerdings wäre dann das Problem, dass man im schlimmsten Fall innerhalb der Null-Werte anfängt zu lesen und nicht weiß, dass es sich um Null-Werte handelt. Da ist dann wieder Lösung 1 besser...

[TigerLilly]

Die Idee mit dem Vorausfüllen ist gut. Mich würde nur der Aufwand abschrecken und der Overhead, der dadurch entsteht.

Ein REORGANIZE müsstest du nicht nach jedem Löschen ausführen. Ich schätze mal, dass 1x am Tag genügt. REORGANIZE ist Standard-MS-SQL, das kann schon mehrfach aufrufen.

Vielleicht kann man auch Hardwareseitig optimieren: Mehr RAM + den Index auf eine eigene SSD.

[BigAl]

Zitat:

Zitat von TigerLilly (Beitrag 1550296)

Die Idee mit dem Vorausfüllen ist gut. Mich würde nur der Aufwand abschrecken und der Overhead, der dadurch entsteht.

Ein REORGANIZE müsstest du nicht nach jedem Löschen ausführen. Ich schätze mal, dass 1x am Tag genügt. REORGANIZE ist Standard-MS-SQL, das kann schon mehrfach aufrufen.

Vielleicht kann man auch Hardwareseitig optimieren: Mehr RAM + den Index auf eine eigene SSD.

Das mit dem Ausfüllen ist eigentlich nicht so schlimm: "UPDATE ... WHERE ID >= x AND ID <= x+n" oder so ähnlich. Die Null-Kennung kann man ja an einem Feld festmachen. Es muss ja nicht alles genullt werden...

Das mit dem Speicher ist halt ein Problem. Die Express-Version des SQL-Servers ist auf 10 GB Größe begrenzt. Da knallt es sehr schnell (habe es ausprobiert). Selbst meine "großen" Version des Servers (ich nutze hier den Development-Server) ist da eine Weile beschäftigt und erzeugt Unmengen an Daten...