MariaDB V5 auf QNAP als sqlHistory langsam

[BananaJoe]

@sneak-l8 mach doch einfach eine 2. Instanz des SQL Adapters auf mit einer 2. Datenbank-Datei. Dann kannst dud ir die Tabellen ansehen (war 3 meine ich)
Ich glaube aber nicht das sich was am Schema geändert hat - und wenn müsste der Adapter es anpassen oder Fehler ausspucken.

[Sneak-L8]

@bananajoe Ich hab jetzt bei github den Code zum SQL-Adapter angesehen. Und siehe da: auch die Werte-Tabellen fehlt immer der Primary key. Kein Wunder, dass da alles lang dauert. Muss aber erst die Tabellen bereinigen, weil ich duplicate keys habe.
Werde berichen, wie schnell die DB anschließend ist ...

[BananaJoe]

@sneak-l8 und noch mal ein Nachtrag: Es kann halt total doof sein wenn die Datenbank auf einen anderen Gerät ist und per Netzwerk abgefragt wird.

Beispiele: Meine MariaDB liegt auf dem gleichen Host wie ioBroker. Es ist eine VM mit Ubuntu, sowohl ioBroker als auch Mariadb sind in dieser VM installiert. Die Zugriffszeit auf die Datenbanken liegen hierbei im unteren 2-stelligen ns-Bereich (Nanosekunden).

Jetzt ping mal von deinem ioBroker Host aus dein NAS an. Wenn es gut läuft sollte die Werte so bei 0.5 bis 0.6 ms liegen. wenn ich das hier mache kommt auch ab und zu ein Zugriff über 1ms. Wenn es ein WLAN Gerät ist sind es immer 2 bis 10 ms.

0.6ms sind 600ns. das ist schon mal um den Faktor 10 langsamer - und die Rechenleistung der Abfrage kommt ja noch dazu. Und das es in TCP/IP Pakete verpackt werden muss. Ok, das haben wir lokal auch, findet dann aber in Wirklichkeit nur im RAM statt.

Also allein durch das Auslagern auf eine tatsächlich anderes Netzwerkgerät bis du hier erheblich langsamer als eine lokale Datenbank. Oder eine Datenbank die auf den gleichen Host liegt. Würde ich meine Datenbank in eine andere VM auslagern, diese liegt aber auf dem gleichen (Virtualisierungs-)Host passiert es wiederum nur im Arbeitsspeicher. Beruflich nutzen ich so etwas als gezielte Tuningmaßnahmen.

Wenn du es richtig schnell haben willst schieß des Raspberry in den Wind und setze sowohl ioBroker als auch die Datenbank auf einem System auf. Wobei es schon schneller sein könnte wenn du eine SSD per USB an das Raspberry anschließt und die Datenbank darauf packst. Aber ein RaspPi 3 wäre mir inzwischen einfach zu schwach dafür, insbesondere was den RAM angeht sind 1Gb zu wenig für ioBroker wie ich finde.

Und ich weis ja nicht was das NAS sonst noch so macht, wäre mir aber nur für SQL zu viel Stromverbrauch. Um den ioBroker da mit drauf zu packen ... keine Ahnung wie die CPU-Performance des Dings ist. Sicherlich schneller als ein Raspberry, aber der hat immerhin 4 statt 2 Kerne.

[Sneak-L8]

@bananajoe @JLeg So, jetzt hab ich es geschafft. Die Primary Keys anzulegen war gar nicht einfach, die QNAP TS-251B knausert etwas mit /tmp. Dort waren nur 64MB verfügbar. Dadurch konnten keine größeren SQL-Statements ausgeführt werden. So lies sich erstmal auch kein Key anlegen.
Aber nachdem ich /tmp mal auf 1G erhöht habe, liefen die SQL-Statements sauber durch.
Jetzt klappt es wieder, alle historischen Werte der States anzusehen und auch vis liefert nun dauerhaft Charts ohne Verzögerung. Das NAs bleibt dennoch bei <10% Last.
Damit ist mein Problem gelöst, Hauptursache waren also:

• fehlende Primary Keys für alle Werte-Tabellen
• zu geringer /tmp Space um das zuvor genannte Problem zu lösen (64M -> 1G)

Danke für alle Tipps!

[BananaJoe]

@sneak-l8 jetzt hast du mich neugierig gemacht:

Kannst du das mit den fehlenden Primary Keys für alle Werte-Tabellen mal etwas näher ausführen? Wie hast du das geprüft / festgestellt, wie hast du das behoben?

Eventuell habe ich ja das Problem auch bzw. kann da noch was optimieren.

[Sneak-L8]

@bananajoe Das war einfach (wenn man weiß, wo nach man suchen muss).
Zunächst hab ich mir das Repo des SQL-Adapters angesehen. Dort ist im Ordner Lib die mysql.js in der die SQL-Kommandos fürs Anlagen der ioBroker-datenbank stehen. So weiß ich, wie meine DB aussehen muss.
Dann hab ich mit phpMyAdmin mir die DB angesehen. Im Reiter Structure einer Tabelle sidn die Indizes aufgelistet. Bzw. bei mir war die Liste leer. Index kann man hier auch direkt anlegen (unten "2 Spalten" auswählen und auf ok, dann als Typ PRIMARY angeben und Ok.
Klappt halt nur wen die DB schnell genug bzw. der Temp-Space ausreichend groß definiert ist. Das war mein Problem an der Sache.

[BananaJoe]

@sneak-l8 mhh, also bei mir sind dann id und ts die Primärschlüssel?

MariaDB [iobroker]> SHOW KEYS FROM ts_number WHERE Key_name = 'PRIMARY';
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ts_number |          0 | PRIMARY  |            1 | id          | A         |       10535 |     NULL | NULL   |      | BTREE      |         |               |
| ts_number |          0 | PRIMARY  |            2 | ts          | A         |    13843516 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)

id ist ja der zugehörige Datenpunkt aus der Tabelle datapoints, ts der UNIXTIMESTAMP im "mit Millisekunden" Format

[Sneak-L8]

@bananajoe Korrekt. PK ist also angelegt. Das würdest Du sonst auch schnell in der Performace merken....

[Dr. Bakterius]

@sneak-l8 sagte in MariaDB V5 auf QNAP als sqlHistory langsam:

PK ist also angelegt.

Bei mir sind sie auch von Haus aus angelegt worden. Stellt sich nur die Frage warum das bei dir nicht der Fall ist?

[Sneak-L8]

@dr-bakterius wie ich ganz am Anfang schon geschrieben hab. Ich hab mal den ioBroker umgestellt (von NAS-Container auf Raspi, Wehcsel von postgre auf Maria, ...). Da hatte ich auch gesehen, dass die IDs durcheinander kamen. Vermutlich wurde die DB neu initialieirst, ich hatte aber nur die ts_...-werte zurckgespielt oder so.
Vermutlich läuft es schon seit damals krum...