SQL Disk Full

Dr. Bakterius

Frage 1: warum habe ich soviele Werte?

Das kannst nur du beantworten. Wie viele Datenpunkte lässt du aufzeichnen? Wie lange speicherst du die Aufzeichnungen? Wie oft ändern sich die aufgezeichneten Werte? Werden nur Änderungen gespeichert? Wie lange befüllst du die Datenbank schon. Hast du alte Werte gespeichert die du schon lange nicht mehr brauchst?

Meine läuft jetzt schon einige Jahre und neben Stromverbrauch diverser Steckdosen und des Stromzählers, werden Raum- und Außentemperaturen, Luftfeuchtigkeit, CO2, Regenmengen und Windstärke aufgezeichnet. Dann auch noch einige Systemwerte, die Wassertemperatur vom Aquarium und den Status der Alarmanlage. Alles nur bei Änderung und unterschiedliche Behaltedauer. Und ich habe nicht einmal 10% deiner Datenbankgröße!

Du kannst selektiv unnötige Werte oder auch zu alte Werte per Datenbankabfragen löschen und die Datenbank danach optimieren lassen. Wird aber eine Weile dauern bei der Größe.

OliverIO

@stfan1409 sagte in SQL Disk Full:

@oliverio
jetzt wird es interessant - ich vergleiche mal ts_number:
also bei dir: ###570.702 geteilt durch##34,2MiB ---------> 0,0000599
und bei mir: 93.784.196 geteilt durch 4,5GiB (4500Mib?) -> 0,0000479

wenn ich den Speicher pro Wert nehme komme ich auf das Selbe oder?

Frage 1: warum habe ich soviele Werte?
Frage 2: ist 4,6GB eine Grenze?
Frage 3: kann ich die Werte jetzt einmalig von Hand "glätten" / reduzieren?

und hier hab ihc noch eine Info gefunden:

Aha, da haben wir es ja.
Zu jeder Tabelle gehören mindestens ein Index.
Wenn Werte hinzugefügt werden, muss der Index aktualisiert werden.
Das passiert nicht immer gleich, sondern meist etwas zeitversetzt. Das ist aber das Geheimnis der Datenbankoptimierer der Hersteller.
Allein der Index ist dann größer wie der freie Speicher, da dieser erst als neue Datei angelegt wird und erst zum Schluss umkopiert wird, um ja einen Ausfall zu verhindern.

Ich habe bei mir im sql-Adapter eingestellt, das ich die Werte nur ein Jahr zurück benötige. Das könntest du einmal für alle Datenpunkte tun.
ausserdem, das nur veränderte Werte gespeichert. Aber ich kenne deinen konkreten Anwendungsfall nicht was da aufgezeichnet wird
Ich glaub das muss man für jeden DP separat machen, ansonsten musst du mal im Hilfethread für den sql-adapter nachfragen.

Zur Sicherheit kannst du auch die ganze Datenbank in myphpAdmin wegsichern. Allerdings dauert das bei dieser Datenmenge ein wenig.

stfan1409

@oliverio
kann ich auch sehen, von welcher Variable die größte Datenmenge ist? Nicht das ich bei einer Variable sehr viel abspeichere, weil ich einen Haken vergessen/übersehen habe?
Wie finde ich die Variable in ioBroker wieder(um dann dort die Einstellungen zu überprüfen)?
Ich bin der Meinung, dass die Datenbank noch nicht ein ganzes Jahr läuft...

OliverIO

@stfan1409 sagte in SQL Disk Full:

@oliverio
kann ich auch sehen, von welcher Variable die größte Datenmenge ist? Nicht das ich bei einer Variable sehr viel abspeichere, weil ich einen Haken vergessen/übersehen habe?
Wie finde ich die Variable in ioBroker wieder(um dann dort die Einstellungen zu überprüfen)?
Ich bin der Meinung, dass die Datenbank noch nicht ein ganzes Jahr läuft...

die variablen nennen sich hier datenpunkte.
nicht das jemand in zukuft das mit den variablen aus javascript verwechselt

gehe in phpmyadmin
klicke links in baumstruktur auf die datenbank iobroker
wähle oben den reiter SQL aus
trage den folgenden text ein

SELECT dp.name,count(val.val) FROM ts_number as val INNER JOIN datapoints as dp ON val.id = dp.id  GROUP BY val.id

als ergebnis kommt die anzahl datensätze je datenpunkt

stfan1409

@oliverio sagte in SQL Disk Full:

SELECT dp.name,count(val.val) FROM ts_number as val INNER JOIN datapoints as dp ON val.id = dp.id GROUP BY val.id

wie lange kann das denn dauern? Ich hab den Text reinkopiert und "ok" angeklickt. Dann steht da "Laden..."
oder muss ich den Text noch anpassen?

OliverIO

@stfan1409 sagte in SQL Disk Full:

SELECT dp.name,count(val.val) FROM ts_number as val INNER JOIN datapoints as dp ON val.id = dp.id GROUP BY val.id

ich habe 570 Tausend Datensätze, da hat es 0,3 Sekunden gedauert.

du hast knapp 100 Millionen Datensätze!!!

Es kann sein, das du mit deinen System da schon an den Grenzen bist.
Um rauszufinden, ob das abgebrochen ist oder noch läuft, müsste man in die logs reinschauen.

oder du kannst auf den Namen einen Index setzen
Wenn das nicht funktioniert, dann sollte man mal daran denken, alle oder viele Datensätze zu löschen.
Evtl kannst du auch die Tabelle mit den 100 Millionen einfach mal als Sicherung in eine 2. Tabelle kopieren.
Datenbank auswählen->Tabelle auswählen->Reiter Operationen->Kopiere Tabelle nach (Datenbank.Tabellenname)

Aber bei der Datenmenge dauert das kopieren, anlegen eines Index einfach eine Weile. (kopieren der Tabelle mit 570K dauerte bei mir ca 3-4 Sekunden)

stfan1409

@oliverio sagte in SQL Disk Full:

ich habe 570 Tausend Datensätze, da hat es 0,3 Sekunden gedauert.

irgend etwas ist da defekt. Ich habe 426 Tausend Datensätze in eine neue Datenbank kopiert und es hat 21 Sekunden gedauert, d.h. für die 95Mio benötige ich 80 Min. Aber es klappt einfach nicht.

Jetzt hab ich nur noch eine Hoffnung, dass ich die Daten vielleicht halbieren kann, indem ich jeden zweiten Datensatz lösche.
In einem anderen Forum hab ich das gelesen, ich weiß aber nicht, wie ich es hier eingeben muss.
Hier die Infos:
https://www.selfphp.de/forum/showthread.php?t=23644

 AW: DB verkleinern: jeden 2. Datensatz löschen
Hi,

das geht auch mit mysql.

1. Primary key löschen (created)
Code:

ALTER TABLE `tabelle1` DROP PRIMARY KEY

2. Spalte id Primary auto increment erstellen
Code:

ALTER TABLE `tabelle1` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

3. Jede 2. Zeile löschen
Code:

DELETE FROM `tabelle1` WHERE id%2=0

4. Spalte id löschen
Code:

ALTER TABLE `tabelle1` DROP `id`

5. Primary key anlegen (created)
Code:

ALTER TABLE `tabelle1` ADD PRIMARY KEY ( `created` )

fertig.

Wie geht das in "phpMyAdmin"? Sonst sehe ich nur die Möglichkeit, die Datenbank zu löschen...

OliverIO

Also das einfachste für einen Neuanfang ist:

du benennst die Datenbank iobroker um in iobroker_backup oder so
und startest den sql-history-adapter neu.
Danach müsstest du eine neue leere datenbank mit namen iobroker haben.

Wenn du die Tabelle entschlacken willst, kannst du die hälfte der Datensätze löschen, aber das sind immer noch 50 Millionen.

besser wäre jeden 100. datensatz zu löschen
dazu musst du die tabelle anpassen. dazu muss die datenbank jeden datensatz anfassen und ändern. wenn die normalen abfragen schon nicht richtig funktioniert haben, dann wird das auch nicht funktionieren.

weißt du mittlerweile warum du soviele datensätze hast?
das solltest du zuerst prüfen, sonst läuft die neue datenbank auch schnell voll.
in welchem zeitraum sind die daten den entstanden?
für wieviel datenpunkte hast du den das aktiviert?

je nach anwendungsfall sollten die folgenden einstellungen in der sql-history-einstellungen je datenpunkt eingestellt sein:
nur änderungen: ja
änderungen ignorieren bis: 1000
Vorhaltezeit der Werte: 1 Jahr

@stfan1409 sagte in SQL Disk Full:

@oliverio sagte in SQL Disk Full:

ich habe 570 Tausend Datensätze, da hat es 0,3 Sekunden gedauert.

irgend etwas ist da defekt. Ich habe 426 Tausend Datensätze in eine neue Datenbank kopiert und es hat 21 Sekunden gedauert, d.h. für die 95Mio benötige ich 80 Min. Aber es klappt einfach nicht.

Jetzt hab ich nur noch eine Hoffnung, dass ich die Daten vielleicht halbieren kann, indem ich jeden zweiten Datensatz lösche.
In einem anderen Forum hab ich das gelesen, ich weiß aber nicht, wie ich es hier eingeben muss.
Hier die Infos:
https://www.selfphp.de/forum/showthread.php?t=23644
 AW: DB verkleinern: jeden 2. Datensatz löschen
Hi,

das geht auch mit mysql.

1. Primary key löschen (created)
Code:

ALTER TABLE `tabelle1` DROP PRIMARY KEY

2. Spalte id Primary auto increment erstellen
Code:

ALTER TABLE `tabelle1` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

3. Jede 2. Zeile löschen
Code:

DELETE FROM `tabelle1` WHERE id%2=0

4. Spalte id löschen
Code:

ALTER TABLE `tabelle1` DROP `id`

5. Primary key anlegen (created)
Code:

ALTER TABLE `tabelle1` ADD PRIMARY KEY ( `created` )

fertig. 
Wie geht das in "phpMyAdmin"? Sonst sehe ich nur die Möglichkeit, die Datenbank zu löschen...

sissiwup

Hi,

bei großen DBs nutze ich pt-archiver (ggf. installieren)

logfile=/var/skripte/removelogs.log

echo "$(date) Starte removelogs" >> $logfile
echo "Lösche iobroker" >> $logfile

pt-archiver --no-check-charset --source h=192.168.1.13,D=iobroker,t=ts_number,i=zeit_number --columns id,ts --purge --limit 100 --progress 10000 --bulk-delete --statistics --txn-s
ize 100 --where "zeit < DATE_ADD(NOW(),INTERVAL -720 DAY)" >> $logfile
pt-archiver --no-check-charset --source h=192.168.1.13,D=iobroker,t=ts_string,i=zeit_string --columns id,ts --purge --limit 100 --progress 10000 --bulk-delete --statistics --txn-s
ize 100 --where "zeit < DATE_ADD(NOW(),INTERVAL -720 DAY)" >> $logfile
pt-archiver --no-check-charset --source h=192.168.1.13,D=iobroker,t=ts_bool,i=zeit_bool --columns id,ts --purge --limit 100 --progress 10000 --bulk-delete --statistics --txn-size
100 --where "zeit < DATE_ADD(NOW(),INTERVAL -720 DAY)" >> $logfile

echo "$(date) Ende removelogs" >> $logfile

192.168.1.13 = DB-Server, ggf. localhost
ggf. brauchst du noch ein user/passwort

720 ersetzt du durch wieviele Tage du die Daten aufheben möchtest

Dann kommt so was raus:

Source: D=iobroker,h=192.168.1.13,t=ts_bool
SELECT 83479
INSERT 0
DELETE 83479
Action        Count       Time        Pct
deleting      83479   248.7364      81.89
select          836    49.2140      16.20
commit          835     0.0982       0.03
other             0     5.7138       1.88

Bei mir sind es ca. 300.000 Datensätze pro Tag die ich lösche.
DB ist ca. 17 GB mit ca. 180 Mio Einträgen.

Um mit den Daten was anfangen zu können habe ich noch ein paar zusätzliche Indizes und auch z.B. berechnete Spalten wie echter Timestamp.
Um die Namen der Datenpunkte in der DB zu haben habe ich ein paar extra Tabellen (findest du wenn du nach meinen Einträgen und mysql suchst).

PS: Zeit ist als:
timestamp, current_timestamp definiert.

stfan1409

Hurra - endlich eine Erfolgsmeldung

alte Datenbank umbenannt
neue angelegt
alle Variablen die geloggt werden durchgesehen und 6 Stück gefunden, bei denen der Haken fehlte "nur bei Änderungen loggen"

Ich habe nun den Haken gesetzt "nur Änderungen aufzeichnen"
trotzdem gleiche Werte aufzeichnen (Sekunden): 86400 - also 1x pro Tag

Jetzt bekomme ich manchmal im Log den Fehler:

sql.0	2021-06-22 19:18:08.079	error	(23282) Counter cannot have type not "number"!

Soll ich für die Zähler "Zeichenfolge" auswählen?

Durch die neue Datenbank sind nun leider auch die Werte der Solarzelle weg. Kann ich die aus der alten Datenbank exportieren?
Auf die anderen Werte kann ich verzichten. obwohl, ich könnte ja alles in die neue Datenbank kopieren - außer "ts_number" mit den 5 GB. oder spricht etwas dagegen?

OliverIO

@stfan1409 said in SQL Disk Full:

Hurra - endlich eine Erfolgsmeldung

alte Datenbank umbenannt

neue angelegt

alle Variablen die geloggt werden durchgesehen und 6 Stück gefunden, bei denen der Haken fehlte "nur bei Änderungen loggen"

Ich habe nun den Haken gesetzt "nur Änderungen aufzeichnen"
trotzdem gleiche Werte aufzeichnen (Sekunden): 86400 - also 1x pro Tag

Jetzt bekomme ich manchmal im Log den Fehler:
sql.0	2021-06-22 19:18:08.079	error	(23282) Counter cannot have type not "number"!
Soll ich für die Zähler "Zeichenfolge" auswählen?

Durch die neue Datenbank sind nun leider auch die Werte der Solarzelle weg. Kann ich die aus der alten Datenbank exportieren?
Auf die anderen Werte kann ich verzichten. obwohl, ich könnte ja alles in die neue Datenbank kopieren - außer "ts_number" mit den 5 GB. oder spricht etwas dagegen?

zu der Fehlermeldung mit dem counter kann ich nichts sagen. da muss jemand was vom sql adapter sagen.

das problem mit den werten ist, das die in ts_number drin stehen.
die logik ist wie folgt:
die namen des datenpunkts steht in datapoints drin.
je nach typ stehen die werte dazu dann in den verschiedenen tabellen, die mit ts_ anfangen, welche davon genommen wird steht in datapoints.type

wenn du nun nur ganz bestimmte datenpunkte haben möchtest, dann musst du schauen welchen id der datenpunkt in tabelle datapoint hat.

dann in eine neue tabelle anlegen per insert/select befehl
für jeden datenpunkt würde ich das separat machen.

schritt für schritt anleitung

hier erst mal nur in der backup datenbank arbeiten

neue leere tabelle als kopie von ts_number anlegen
-> phpmyadmin->db anklicken->tabelle ts_number anklicken->reiter operationen auswählen->im abschnitt kopieren neuen namen (ts_number1) eintragen und "nur struktur" selektieren
id deines datenpunkts in tabelle datapoints nachschlagen
folgendes insert/select ausführen

INSERT INTO `ts_number1` select * from `ts_number` where id=1;

ts_number1 = neue tabelle
ts_number = bisherige tabelle
die 1 gegen die id austauschen die du unter 2 herausgesucht hast
4) prüfen wieviel datensätze das waren.
wenn es wieder sehr viele sind, dann muss man erst mal die daten optimieren. das hängt aber davon ab, warum so viele daten in der datenbank sind. dazu musst du dir die daten anschauen.
5) ggfs. mit 2) fortsetzen

wenn die neue tabelle ein erträgliches maß hat, dann kannst du über die auswahl der neuen tabelle -> operationen
diese tabelle in die neue datenbank kopieren und dort die daten einfach mit folgendem befehl der tabelle wieder anfügen

INSERT INTO `ts_number` select * from `ts_number1`;

beachte die getauschten tabellennamen und das fehlen der where klausel

stfan1409

@oliverio sagte in SQL Disk Full:

INSERT INTO ts_number1 select * from ts_number where id=1;

Vielen vielen Dank. Ich hatte schon befürchtet, das alle Werte weg sind. Aber nun scheint es ja doch zu klappen.

Ich habe die interessanten und geringen Daten in "ts_number1" kopiert. Nun hat sich aber in der neuen Datenbank die Nummer der ID geändert. Wie kann ich nun in den alten Daten die Nummer der ID ändern - also umbenennen?

OliverIO

@stfan1409 said in SQL Disk Full:

@oliverio sagte in SQL Disk Full:

INSERT INTO ts_number1 select * from ts_number where id=1;

Vielen vielen Dank. Ich hatte schon befürchtet, das alle Werte weg sind. Aber nun scheint es ja doch zu klappen.

Ich habe die interessanten und geringen Daten in "ts_number1" kopiert. Nun hat sich aber in der neuen Datenbank die Nummer der ID geändert. Wie kann ich nun in den alten Daten die Nummer der ID ändern - also umbenennen?

UPDATE ts_number1 SET id=2 WHERE id=1

2=neue id
1=alte id

pass auf, das es die neue nummer in der tabelle noch nicht gibt, sonst mischt du die daten 2er datenpunkte. lieber noch ein weiterer zwischenschritt über eine ganz andere nummer machen.

stfan1409

@oliverio
oh das ist toll dass du mir so viel hilfst - vielen, vielen Dank dafür!!!

ich habe nun kopiert, die ID´s angepasst und zurückkopiert.
Mittlerweile sind in der Tabelle: ts_number auch schon neue Werte(von gestern und heute).
Ich habe mit:

INSERT INTO `ts_number` select * from `ts_number_old`;

die Werte aus der neu zusammengestellten Tabelle(ts_number_old) kopiert/eingefügt.

Nun hätte ich erwartet, dass ts_number größer ist als ts_number_old - aber genau andersrum:
ts_number:~~~~974.507
ts_number_old: ~995.599

Kann ich das auch noch "einfach" korregieren? ansonsten kann ich damit auch so leben
Aber es wundert mich schon etwas.

Bevor ich die Tabelle(ts_number_old) in die andere(ts_number - neu erstellte fast leere Tabelle) eingefügt habe hatte sie: ~986.927
Die Anzahl der Werte der fast leeren Tabelle weiß ich nicht mehr.

OliverIO

@stfan1409
Das ist schon seltsam.
Im insert into Befehl haben wir ja keine Bedingungen (where ...) angehängt. Von der müssen eigentlich mindestens die gleiche Anzahl an Datensätze drin sein.

Ich gehe mal davon aus, das du die Daten visualisieren möchtest. Evtl siehst du ja dann ob da Lücken entstanden sind.

Korrigieren kannst du immer, die Frage ist, ob du die Tabelle vorher gesichert hast.
Bei massendatenverarbeitung immer fleißig zwischensichern da rückgängig machen nicht immer so einfach oder gar unmöglich ist.

Hilfe ist kein Problem, ist ja nur ein wenig sql Hilfe.

stfan1409

@oliverio
Also die Kurven die ich haben wollte sind da. Mir ist so auch kein Zeitraum aufgefallen der fehlt.
Ich lasse die Datenbank nun so und freue mich, dass sie wieder funktioniert und ich wichtige Kurven retten konnte.
Nochmal vielen Dank für Deine großartige Hilfe!!! Wirklich!!!

NEWS

SQL Disk Full

Support us

988
Online

31.7k
Users

79.8k
Topics

1.3m
Posts

NEWS

SQL Disk Full

Support us

988Online

31.7kUsers

79.8kTopics

1.3mPosts

988
Online

31.7k
Users

79.8k
Topics

1.3m
Posts