SQL Disk Full
-
@stfan1409
Ja, man kann die node-Version downgraden.dfist ein Konsolenbefehl und zeigt wie voll die Partition ist.
-
@thomas-braun
so hab mich mal mit der Konsole vom NAS verbunden und "df" eingegeben. scheint einiges frei zu sein:
-
Du musst in die VM oder in den Container rein, in dem die Datenbank läuft.
-
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. -
@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. -
@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? -
@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ößedu 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?
-
@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.
-
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
-
@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,0000479wenn 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:
-
@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.
-
@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,0000479wenn 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.
-
@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... -
@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 verwechseltgehe in phpmyadmin
klicke links in baumstruktur auf die datenbank iobroker
wähle oben den reiter SQL aus
trage den folgenden text einSELECT dp.name,count(val.val) FROM ts_number as val INNER JOIN datapoints as dp ON val.id = dp.id GROUP BY val.idals ergebnis kommt die anzahl datensätze je datenpunkt
-
@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? -
@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)
-
@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=23644AW: 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...
-
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=23644AW: 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...
-
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" >> $logfile192.168.1.13 = DB-Server, ggf. localhost
ggf. brauchst du noch ein user/passwort720 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.88Bei 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. -
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 TagJetzt 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?
