93_DbLog - Überlegungen zur Optimierung (RPI und andere Systeme)

[SusisStrolch]

Ich hatte es mit

Code Auswählen Erweitern

SELECT *  INTO OUTFILE 'history.dat'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM `fhem`.`history`;

LOAD DATA INFILE './history.dat'
    INTO TABLE `fhem`.`historyNew`
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

durchgeführt, weil laut der MySQL-Doku die wenigsten Reibungsverluste beim Inport auftreten sollten.

Bei der InnoDB sieht man, dass er erst im /tmp (tmpfs) die Zwischendateien anlegt.
Das knallt nämlich, wenn df(/tmp) < sizeof(db)...
Mit Partitionen kommt man dann darum herum...

[DS_Starter]

So, hier ist das Ergebnis.
Export (into file)  aus InnoDB:

Query OK, 55651919 rows affected (8 min 2.57 sec)

Der Import in die Aria-Tabelle:

Query OK, 55651919 rows affected  (13 min 38.07 sec)

Größe der DB rund 5,5 GB. Wenn ich mich nicht verrechnet habe entspricht das einem Durchsatz von ca. 22 GB/h.
Die CPU war dabei ca. 20% vom mysqld belegt, aber das Volume zeigte eine Auslastung zw. 77-99% (hatte ich schon bei einem meiner anderen Tests).

[SusisStrolch]

Ich fass es nicht.
Was für eine DS hast du?

[DS_Starter]

Ich habe eine 415+. Die dürfte eigentlich die gleiche Maschine wie deine sein, außer das meine nur 4 Slots hat.

EDIT: Platten sind 3 x WD Red 3GB im Synology SHR (Raid 5) Verbund

Ach so .... ich habe meine DS auf 8 GB RAM hochgerüstet. Normal hat dieser Typ nur 2GB.

[DS_Starter]

Gestern hatte ich den Import-Test auf MariaDB 5 gemacht und ihn nun auf meiner MariaDB 10-Installation in eine Aria-Tabelle wiederholt.
Das Ergebnis ist das gleiche:

Code Auswählen Erweitern


Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1306
Server version: 10.0.30-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> LOAD DATA CONCURRENT INFILE 'history.dat' IGNORE INTO TABLE fhemtest.test  FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';
Query OK, 55687497 rows affected, 65535 warnings (13 min 28.11 sec)
Records: 55687497  Deleted: 0  Skipped: 0  Warnings: 1180618


Es werden wiederum ca. 13 Min für die ~5 GB benötigt. Das besondere ist, dass ich Maria 10 in keinster Weise angepasst habe. Es ist noch "jungfräulich" so wie Syno das Package installiert. Die Standardkonfiguration befindet sich in /usr/local/mariadb10/etc/mysql/my.cnf

Ein

Code Auswählen Erweitern


SELECT count(TYPE) from test

dauert (55687497 insgesamt, Die Abfrage dauerte 46.4177 Sekunden.)

Hier ist die Standardkonfiguration für MariaDB 10 wie sie ausgeliefert wurde:

Code Auswählen Erweitern

[client]
port = 3307
socket = /run/mysqld/mysqld10.sock

[mysqld]
bind-address = 0.0.0.0
port = 3307
socket = /run/mysqld/mysqld10.sock
pid-file = /run/mysqld/mysqld10.pid
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/packages/MariaDB10/target/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/packages/MariaDB10/target/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_file_per_table = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

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


Vielleicht hilft dir das noch etwas.

LG
Heiko

[SusisStrolch]

Mir bleibt nur noch ein Support-Ticket aufzumachen.
Hatte MariaDB10 bereits komplett reinstalliert, alle CNF's auf die Seite geschoben so dass MariaDB10 mit der Factory-Config läuft.
Versuch 1:
DB-Ordner auf eine (nahezu leeren) 3TB WD-Red gelegt:

Code Auswählen Erweitern

Import der 2016er Daten:
Query OK, 33891066 rows affected (3 hours 18 min 55.40 sec)
Records: 33891066  Deleted: 0  Skipped: 0  Warnings: 0


Versuch 2:
/tmp auf 2GB reduziert, tmpfs mit 8GB angelegt und das Datenbank-Verzeichnis in die Ram-Disk gelegt.
Dann einen DB-Import der 2016er Daten gestartet.
Zwischenstatus: 8% nach 800sec...
Das wären - grob kalkuliert - >2Std bei einer Datenbank, die komplett im RAM liegt....

Ach ja - vorher natürlich ein

Code Auswählen Erweitern

sysctl -w vm.swappiness=0


[DS_Starter]

Mir bleibt nur noch ein Support-Ticket aufzumachen.

Würde ich auch machen. Das kommt mir schon arg komisch vor.
Hast du denn auch sonst Performanceprobleme ?
Wenn ich irgendeinen Vergleich beisteuern kann gib einfach Bescheid....

[SusisStrolch]

Hast du denn auch sonst Performanceprobleme ?

Nö - das ist ja das Seltsame. Schreiben/Lesen via nfs oder die Syslog-Auswertungen sind eigentlich ohne Befund.

Code Auswählen Erweitern

strolch@Strolchi /var/media/misc $ dd if=/dev/zero of=zero bs=16M count=1024
1024+0 records in
1024+0 records out
17179869184 bytes (17 GB, 16 GiB) copied, 254,19 s, 67,6 MB/s
strolch@Strolchi /var/media/misc $ dd if=/var/media/misc of=/dev/null bs=16M
1024+0 records in
1024+0 records out
17179869184 bytes (17 GB, 16 GiB) copied, 252,829 s, 68,0 MB/s

/var/media/misc ist ein NFS-Mount auf /volume1/misc...

Hatte auch bereits die üblichen Verdächtigen (Intrusion Detection, Firewall, Virenchecker) schlafen gelegt.

[DS_Starter]

Hmm... schau mal zum Vergleich:

Code Auswählen Erweitern


Heiko@SDS1:~$ dd if=/dev/zero of=zero bs=16M count=1024
1024+0 records in
1024+0 records out
17179869184 bytes (17 GB) copied, 104.823 s, 164 MB/s


[SusisStrolch]

Jo - Faktor 2,5 - suboptimal, aber noch immer nicht im gelben Bereich.
Im Gegensatz zur MariaDB auf RAMDisk - die importiert noch immer...

[DS_Starter]

Mir ist nur immer wieder aufgefallen dass bei mir das Plattensubsytem die Leistung begrenzte, so auch bei diesem Test. Die CPU lief jetzt mit gesamt ~30% wobei das Volume zu 100% ausgelastet war. (Screenshot)

Bei dir war es eher die CPU wenn ich mich nicht irre.
Da wir die gleichen CPUs haben, kann ich mir da keinen Reim drauf machen.

Na mal schauen was Syno sagt ....

[SusisStrolch]

Die CPU-Load kann man vernachlässigen - es waren nie mehr als 2 Cores aktiv.

Sobald ich was habe gebe ich Bescheid.

[DS_Starter]

Hallo @all,

wie hier https://forum.fhem.de/index.php/topic,74234.msg664064.html#msg664064 gemeldet und diskutiert, gibt es in manchen Fällen (Modul dewpoint) die Notwendigkeit die Event-API im kompatiblen Modus (Events ohne state-String) aufzurufen.
In der angehängten Version 2.22.0 kann man dieses Verhalten mit dem Attribut "addStateEvent = 0" einschalten.
Per default verwendet DbLog "addStateEvent = 1",  d.h. bei Events mit dem state-Reading wird der state-String mit abgerufen.

Bitte testet diese Version bei euch.

viele Grüße
Heiko

[JoeALLb]

wie hier https://forum.fhem.de/index.php/topic,74234.msg664064.html#msg664064 gemeldet und diskutiert, gibt es in manchen Fällen (Modul dewpoint) die Notwendigkeit die Event-API im kompatiblen Modus (Events ohne state-String) aufzurufen.

Hallo, das ist ein schönes Feature. Ausführlich testen konnte ich es leider noch nicht.
Da "state" ja eigentlich nicht zum loggen gedacht ist, musste ich bisher immer kleinere Handstände machen, um diese aus meiner DB zu verbannen,
dieses Feature reduziert somit meine Systemlast, da ich mir das Loggen und spätere aussortieren löschen spare! Herzlichen Dank!
Ich werde das auf Seite 1 bei unfer #24 ergänzen!

Joe

[SusisStrolch]

Ich bin's nochmal...
Um es vorwegzunehmen - mein Problem liegt darin, dass das MariaDB tmpdir auf /tmp liegt - und /tmp ein tmpfs device ist.
Ab einer bestimmten Datenbankgröße gehen die Zugriffszeiten explosionsartig nach oben.
Ich nehme an, dass dies im Wesentlichen dem 32bit-Kernel geschuldet ist.

Legt man tmpdir auf die Harddisk, so sieht man bei 'kleinen' Datenbanken nur unwesentliche Unterschiede. Hingegen kommt der eklatante Leistungseinbruch bei der großen Datenbank nicht zum Tragen. Die nachfolgenden Werte wurden mit der Synology Default-Konfiguration durchgeführt, die Custom mysql.cnf enthält lediglich ein "tmpdir=/volumeSATA1/satashare1-1/@tmp" Statement.

Testablauf:

Code Auswählen Erweitern

TRUNCATE TABLE `fhem`.`historyAria`;
ALTER TABLE `fhem`.`historyAria` DROP PRIMARY KEY, DROP INDEX Report_Idx,DROP INDEX Reading_Time_Idx,DROP INDEX Search_Idx;
LOAD DATA INFILE './history.dat'  INTO TABLE `fhem`.`historyAria` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
ALTER TABLE `fhem`.`historyAria` ADD PRIMARY KEY(`DEVICE`,`READING`,`TIMESTAMP`);
CREATE INDEX Report_Idx ON `fhem`.`historyAria` (TIMESTAMP, READING);
CREATE INDEX Reading_Time_Idx ON `fhem`.`historyAria` (READING,TIMESTAMP);
CREATE INDEX Search_Idx  ON `fhem`.`historyAria` (DEVICE,READING,TIMESTAMP);
ALTER TABLE `fhem`.`historyAria` DROP PRIMARY KEY, DROP INDEX Report_Idx,DROP INDEX Reading_Time_Idx,DROP INDEX Search_Idx;
ALTER TABLE `fhem`.`historyAria` ADD PRIMARY KEY(`DEVICE`,`READING`,`TIMESTAMP`), ADD INDEX Report_Idx(TIMESTAMP, READING), ADD INDEX Reading_Time_Idx(READING,TIMESTAMP), ADD INDEX Search_Idx(DEVICE,READING,TIMESTAMP);


Vergleich 'tmpfs' vs. Harddisk bei kleinen sowie größeren DBs:

	tmpfs	/dev/sd	tmpfs	/dev/sd
DB rows	33.891.066	33.891.066	70.674.191	104.555.703
IMPORT	6:15.58	6:17.33	13:02.20	19:14.06
PRIMARY KEY	9:20.36	9:32.38	20:10.58	29:44.66
Report_Idx	11:15.87	11:15.74	25:57.34	38:56.62
Reading_Time_Idx	14:13.99	14:13.53	34:13.53	50:48.60
Search_Idx	19:14.34	19:07.35	45:11.09	67:21.90
DROP all PKey,Idx	4:22.41	4:26.33	---	---
ADD all PKey, Idx	19:08.72	19:08.70	---	---

Fazit:
Werden die Idx sequentiell generiert, so ist bereits beim Hinzufügen des 1. Index tmdir auf der Platte nicht langsamer als im tmpfs.
DROP all Idx,PKey; ADD all Idx,PKey ist deutlich schneller als KEY und INDEX successive hinzuzufügen.

[Edit: Zeiten für DROP all, ADD all mit tmpfs hinzugefügt]