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? -
@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? -
@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 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?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.typewenn 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) fortsetzenwenn 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ügenINSERT INTO `ts_number` select * from `ts_number1`;beachte die getauschten tabellennamen und das fehlen der where klausel
-
@oliverio sagte in SQL Disk Full:
INSERT INTO
ts_number1select * fromts_numberwhere 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?
-
@stfan1409 said in SQL Disk Full:
@oliverio sagte in SQL Disk Full:
INSERT INTO
ts_number1select * fromts_numberwhere 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=12=neue id
1=alte idpass 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.
-
@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.599Kann 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. -
@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.
