LogDB -> MariaDB10 -> Synology DS918+

[aHome77]

Hallo Zusammen,

bei mir läuft fhem auf einem Raspberry Pi mit LogDB. Die Datenbank MariaDB10 auf einer Synology DS918+
Über den Sommer läuft das ganze meist unbeaufsichtig. Nun haben aber div. Devices zwecks Fehlern die
Datenbank mit Millionen!!! von Datensätzen zugemüllt. Ein paar Devices hatten das attribut DbLogExclude .*
nicht, dies wurde behoben.

Code Auswählen Erweitern

attr .* DbLogExclude .*

Die Devices mit den Millionen Datensätzen habe ich identifiziert.

Code Auswählen Erweitern

SELECT DEVICE, COUNT(*) FROM history GROUP BY DEVICE ORDER BY 2 DESC;

Nun wollte ich "kurz" die entsprechenden Datensätze löschen, da diese so oder so nicht geloggt werden sollen

Code Auswählen Erweitern

DELETE FROM history WHERE DEVICE = 'SSCam.Kamera1' or DEVICE = 'SDS1' or DEVICE = 'SSCam.Kamera2'

Allerdings läuft das ganze Stunden lang! Hat jemand eine Idee an was das liegen könnte? Performance MariaDB10 auf DS918+ ?
Die Datenbank hatte 74 Millionen Datensätze, nach 4h +x immer noch 62 Millionen.

Die my.cnf würde erstellt. Die DS918+ hat 8GB RAM. CPU und RAM sind nicht am Limit.

Code Auswählen Erweitern

[mysqld]

innodb_buffer_pool_size = 4G

Was sind eure Erfahrungen mit Diskstation und MariaDB10 und Anzahl Datensätze?

[Wernieman]

Unabhängig davon habe ich mir angewöhnt, immer erst teile zu löschen, also z.B. erstmal 1.000 Datensätze, bevor man "mehr" Auswählt.

10M Datensätze innerhalb von 4h .... was machen die Platten bei Dir?

[aHome77]

So habe ich das vor ein paar Tagen auch probiert:

Code Auswählen Erweitern

DELETE FROM fhem.history WHERE TYPE = 'UWZ' LIMIT 1000;

Aber da kommt man nicht weit.

Eine genaue Zeitangabe habe ich auch nicht wirklich, da ich schon seit 3 Tagen damit rumprobiere... Für phpMyAdmin war das zuviel (Zeit?) der wollte nicht mehr.... Mache die Bereinigung seid gestern jetzt mit einem Windows SQL Client und schmeiße erst mal alles raus was nicht in die Datenbank gehört. Devices komplett und danach Einträge von Device welches ich nicht benötige.

Code Auswählen Erweitern

MariaDB [(none)]> show processlist;
+-----+-------------+-------------------------+------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id  | User        | Host                    | db   | Command | Time  | State                    | Info                                                                                                 | Progress |
+-----+-------------+-------------------------+------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|   2 | system user |                         | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
|   1 | system user |                         | NULL | Daemon  |  NULL | InnoDB purge coordinator | NULL                                                                                                 |    0.000 |
|   3 | system user |                         | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
|   4 | system user |                         | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
|   5 | system user |                         | NULL | Daemon  |  NULL | InnoDB shutdown handler  | NULL                                                                                                 |    0.000 |
|  43 | fhemuser    | Win10PC.fritz.box:42408 | fhem | Query   | 16685 | Updating                 | DELETE FROM history WHERE DEVICE = 'SSCam.Kamera1' or DEVICE = 'SDS1' or DEVICE = 'SSCam.Kamera2'    |    0.000 |


Auf der DS läuft noch die Surveillance Station, ein paar Docker, VMM, ...CPU bei knapp 30%. Festplatten WD RED 8TB

[ergerd]

Wäre es eine Idee die Daten der Devices, die du behalten möchtest in
eine neue Tabelle zu übertragen und dann die Tabellen umzubenennen?
Danach kann die große Tabelle einfach gelöscht werden.
Ist vielleicht weniger Arbeit.

LG
ergerd

[DS_Starter]

Wenn Lese-/Löschvorgänge mit der DB so lange dauern, lässt das einen fehlenden Index vermuten. Evtl. wurde er von dir  nicht angelegt.
Führe bitte ein

Code Auswählen Erweitern

set ... configCheck

auf deinem DbLog-Device aus. Das sieht man damit.

Ansonsten kann man solche Datenpflege mit einem (mehreren) DbRep-Devices ausführen. Unter anderem können fehlende Indexe angelegt werden bzw. Daten innerhalb von gewünschten Zeitgrenzen gelöscht werden wobei die entsprechenden Devices/Readings inkludiert oder auch exkludiert werden können.

[Wernieman]

10M Datensätze innerhalb von 4h .... was machen die Platten bei Dir?

Abgesehen von den letzten Info von DS_Starter:
Mit "was machen die Platten bei Dir?" wollte ich die Auslastung der Platten wissen ... um den Flaschenhalz zu wissen.

Wie auch schon angedeutet: Bei großen Änderungen ist eine Neuanlage der Tabelle mit "Solldaten" und löschen der alten Tabelle häufig schneller als ein löschen von Datensätzen....

[aHome77]

Im configcheck waren div. Warnungen siehe Auszug. Bis auf das Encoding habe ich alles angepasst. Nun sind die settings o.k.


Result of encoding check

Encoding used by Client (connection): LATIN1
Encoding used by DB fhem: UTF8
Recommendation: Both encodings should be identical. You can adjust the usage of UTF8 connection by setting the UTF8 parameter in file '/opt/fhem/FHEM/my_own_cfg/db.conf' to the right value. Your DBD version fulfills UTF8 support, no need to update DBD.

Result of insert mode check

Insert mode of DbLog-device LogDB is: Array
Recommendation: Setting attribute "bulkInsert" to "1" may result a higher write performance in most cases. Feel free to try this mode.

Result of plot generation method check

WARNING - at least one of your FHEMWEB devices has attribute "plotfork = 1" and/or attribute "plotEmbed = 2" not set.

WEB: plotfork=0 / plotEmbed=0

Recommendation: You should set attribute "plotfork = 1" and "plotEmbed = 2" in relevant devices. If these attributes are not set, blocking situations may occure when creating plots. Note: Your system must have sufficient memory to handle parallel running Perl processes. See also global attribute "blockingCallMax".

Die Festplatten haben während dem SQL DELETE ... eine Durchschnittlichte Schreibgeschwindigkeit von 7 MB/s bzw.Lesegeschwindigkeit von 4 MB/s. Beim kopieren einer Datei auf den NAS write 60 MB/s je nachdem. Also an den Festplatten liegt es nicht.

[DS_Starter]

Dann vermute ich müsste man die DB Performance auf der Synology optimieren.
Auf dem Server gibt es auch Konfigurationsdateien dafür um der DB RAM zur Verfügung zu stellen und weitere Parameter. Die DB nimmt sich nur soviel RAM wie konfiguriert.

Da kann man ggf. viel Zeit investieren. Es gibt ein Script MySQL Tuner bzw. im GitHub. Das kann man auf der Syno ausführen und es gibt Hinweise zur Optimierung.
Im jetzigen Zustand würde ich ein (mehrere) DbReps definieren und die Löschungen laufen lassen.

Alternativ geht mit DbRep auch ein Export bestimmter Devices die du behalten möchtest mit anschließendem TRUNCATE der history Tabelle wie die "Vorredner" schon erwähnt haben. Dann den Export wieder importieren und fertig.

Auch wenn es lange dauert, mit einer MariaDB kann man solche parallele Tasks ausführen und stört nicht weiter.Wenn du dann wieder Zeit hast, kannst du dir das Script ja mal vornehmen Vllt. hilft es dir.

[Wernieman]

Ko0mm t man wirklich an die Config der DB bei der Synology ran?

Weil genau daran hatte ich auch schon gedacht ....

[DS_Starter]

Ko0mm t man wirklich an die Config der DB bei der Synology ran?

Ja, die Standardkonfiguration befindet sich in /usr/local/mariadb10/etc/mysql/my.cnf.
In der Datei gibt es einen Include

Code Auswählen Erweitern


....
# Please add your custom configuration to here:
!include /var/packages/MariaDB10/etc/my.cnf


Die eigenen Anpassungen macht man dann in /var/packages/MariaDB10/etc/my.cnf. Die bleiben somit auch bei einem Update des DSM erhalten, sonst nicht.

[aHome77]

Ko0mm t man wirklich an die Config der DB bei der Synology ran?

Weil genau daran hatte ich auch schon gedacht ....

Ja, hier:

Code Auswählen Erweitern

MariaDB 10 : /var/packages/MariaDB10/etc

Wenn my.cnf im Ordner MariaDB nicht vorhanden ist, erstellen Sie eine Konfigurationsdatei mit dem Namen my.cnf .

https://kb.synology.com/de-de/DSM/tutorial/Can_MariaDB_settings_be_customized_on_Synology_NAS

Allerdings ist das mit Vorsicht zu genießen. Ich musste die my.cnf anlegen. Hatte es auch geschafft das die MariaDB10 gar nicht mehr startet...

Wie DS_Starter schon geschrieben hatte:

Da kann man ggf. viel Zeit investieren. Es gibt ein Script MySQL Tuner bzw. im GitHub. Das kann man auf der Syno ausführen und es gibt Hinweise zur Optimierung.

Eine Option bei InnoDB ist wohl noch OPTIMIZE TABLE fhem.history; da sonst nach dem löschen der Datensätze die Datenbank (die Datei der Datenbank) die Größe beibehält.

[DS_Starter]

Eine Option bei InnoDB ist wohl noch OPTIMIZE TABLE fhem.history; da sonst nach dem löschen der Datensätze die Datenbank (die Datei der Datenbank) die Größe beibehält.

Das stimmt, hat aber mit der aufgeworfenen Performancefrage IMHO nichts bzw. nicht viel zu tun.