SQL Disk Full

Thomas Braun

Du musst in die VM oder in den Container rein, in dem die Datenbank läuft.

stfan1409

hmmmm...also ich hab das Synology NAS, auf dem MariaDB läuft.
Wenn ich auf der NAS-Oberfläche "MariaDB" öffne, steht hier noch etwas von:
Domain-Socket: /run/mysqld/mysqld10.sock
Ich weiß aber nicht, wie ich dahin komme oder was das ist.

OliverIO

@stfan1409 sagte in SQL Disk Full:

Error: ER_DISK_FULL: Disk full (/tmp/#sql_35ae_3.MAI)

was für sql statements laufen da?
hast du die selber erstellt?

Der Fehler
Error: ER_DISK_FULL: Disk full (/tmp/#sql_35ae_3.MAI);
sag aus, das die Disk full ist.
da hier der Pfad tmp angegeben wurde, gehe ich davon aus,
das da eine schlecht formulierte sql-anweisung läuft, die die datenbank dazu zwingt die daten in eine datei (im ordner tmp) auszulagern um dort dann die Operation durchzuführen.
da die datei nach erfolg oder abbruch sofort wieder gelöscht werden, siehst du nicht das die tempoorär voll läuft.
die partition auf der tmp liegt ist auch nicht auf der haupt partition, wo massig platz ist. aber das ist grundsätzlich ok. es könnte auch eine ram-disk sein, die nicht unendlich erweiterbar ist.

stfan1409

@oliverio
Hallo, also ich habe die Benutzeroberfläche "phpMyAdmin". Da habe ich eine neue Datenbank angelegt und diese in ioBroker eingetragen. Mehr nicht. Welche Statements da laufen? Da kenne ich mich nicht mit aus.
Wenn ich nun den Namen der Datenbank ändere - z.b. "daten-alt" und eine neue mit dem vorherigen namen, müsste iobroker die doch einfach so weiter nutzen. Kann ich dann die Daten aus der alten Datenbank in die neue verschieben?

OliverIO

@stfan1409 sagte in SQL Disk Full:

@oliverio
Hallo, also ich habe die Benutzeroberfläche "phpMyAdmin". Da habe ich eine neue Datenbank angelegt und diese in ioBroker eingetragen. Mehr nicht. Welche Statements da laufen? Da kenne ich mich nicht mit aus.
Wenn ich nun den Namen der Datenbank ändere - z.b. "daten-alt" und eine neue mit dem vorherigen namen, müsste iobroker die doch einfach so weiter nutzen. Kann ich dann die Daten aus der alten Datenbank in die neue verschieben?

ist das dann deine einzige datenbank und du nutzt nur den sql-adapter?
dann zeige mal bitte die tabellen übersicht aus myphpadmin.
interessant daraus wäre da der tabellenname, die anzahl der datensätze und die daraus resultierende größe

du hattest oben geschrieben:

Was mich auch verwundert ist, dass Variablen(alle???) aktuell in der Datenbank aufgezeichnet werden.

meinst du das du "alle" datenpunkte aufzeichnen lässt?

stfan1409

@oliverio sagte in SQL Disk Full:

phpMyAdmin

Ich nutze SQL zum ersten Mal. Ich habe nur eine Datenbank für ioBroker angelegt.

du hattest oben geschrieben:

Was mich auch verwundert ist, dass Variablen(alle???) aktuell in der Datenbank aufgezeichnet werden.

meinst du das du "alle" datenpunkte aufzeichnen lässt?

Ich wollte damit sagen, dass z.B. die Außentemperatur weiter aufgezeichnet wird. Im Graphen sehe ich die aktuelle Tageskurve. Neue Variablen dagenen kann ich nicht aufzeichnen bzw. bekomme die Fehlermeldung Speicher voll. Ich zeichne nur bei Änderungen auf.

OliverIO

@stfan1409

das sind nicht die richtigen ansichten.
klicke links in der baumstruktur auf iobroker
dann siehst du so etwas

dort stehe die anzahl datensätze je tabelle und die speichergröße.
wenn da jetzt keine riesigen zahlen stehen, dann weiß ich nicht weiter.

dann hat es wohl mit dem nas und der platzzuweisung dort zu tun.
bei mir läuft die datenbank auf einem nuc in einem docker container.
aber grundsätzlich muss das bei dir schon funktionieren.

wenn sonst niemand mehr was weiß, evtl. auf der nas die datenbank nochmal deinstallieren und neu installieren

stfan1409

@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:

Dr. Bakterius

@stfan1409 sagte in SQL Disk Full:

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?

NEWS

SQL Disk Full

Support us

684
Online

31.7k
Users

79.8k
Topics

1.3m
Posts

NEWS

SQL Disk Full

Support us

684Online

31.7kUsers

79.8kTopics

1.3mPosts

684
Online

31.7k
Users

79.8k
Topics

1.3m
Posts