MariaDB V5 auf QNAP als sqlHistory langsam

[BananaJoe]

@sneak-l8 laut dem Log ist MariaDB auf Port 3310 erreichbar, laut config ist es 3306.
Was ist in ioBroker eingestellt?

Außerdem ist der Socket auf IP 127.0.0.1 gebunden - damit wäre der Server nur lokal erreichbar (aus sich selbst heraus), ich meine da müsste 0.0.0.0 stehen, dann lauscht es an allen Netzwerkkarten.
So sieht das bei mir aus:

2022-03-17 10:17:19 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-03-17 10:17:19 0 [Note] Reading of all Master_info entries succeeded
2022-03-17 10:17:19 0 [Note] Added new Master_info '' to hash table
2022-03-17 10:17:19 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.34-MariaDB-0ubuntu0.20.04.1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 20.04

Das mit der 127.0.0.1 wäre ein Eintrag wie folgt

bind-address            = 0.0.0.0

[JLeg]

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

/tmp/mysql_mediadb.sock

Mariadb: /tmp/mysql_mediadb.sock
my.cnf: /tmp/mysql.sock

finde den Fehler...

Sieht für mich so aus, als würde deine my.cnf nicht "gezogen" - eigentlich ist die uralte alleinige "my.cnf" ja auch seit einem Jahrzehnt oder so durch eine differenzierte Hierarchie ersetzt worden, möglicherweise auch bei deinem NAS-Paket (/etc/my.cnf.d/ ?).
Ansonsten - die letzte 5er-Version hat auch schon ihren 10. Geburtstag gehabt, ich würde unbedingt eine aktuelle(re) 10er benutzen, es sind seit dem soooo viele Dinge gefixt worden...

[Sneak-L8]

@bananajoe Stimmt, das ist mir gar nicht gleich aufgefallen. Aber es ist komisch:
Auch bei Starts vor ein paar Monaten sehe ich im Log Port 3310 und Bin auf 127.0.0.1. Aber dennoch war die DB auf Port 3306 von anderen Systemen erreichbar.
Läuft da evtl. noch ein zweiter dienst oder werden die Anfragen von einem weiteren Service durchgereicht?

Ich habe jetzt mal wieder die "alte" my.cnf wiederhergestellt, damit strtet die MariaDB wieder korrekt.

Das Log sieht ähnlich aus:

220318 09:37:27 mysqld_safe Starting mysqld daemon with databases from /share/CE_CACHEDEV1_DATA/.system/data
220318  9:37:27 [Note] /usr/local/mariadb/bin/mysqld (mysqld 5.5.57-MariaDB) starting as process 987 ...
220318  9:37:27 InnoDB: The InnoDB memory heap is disabled
220318  9:37:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
220318  9:37:27 InnoDB: Compressed tables use zlib 1.2.3
220318  9:37:27 InnoDB: Initializing buffer pool, size = 128.0M
220318  9:37:27 InnoDB: Completed initialization of buffer pool
220318  9:37:27 InnoDB: highest supported file format is Barracuda.
220318  9:37:28  InnoDB: Waiting for the background threads to start
220318  9:37:29 Percona XtraDB (http://www.percona.com) 5.5.55-MariaDB-38.8 started; log sequence number 1659821
220318  9:37:29 [Note] Plugin 'FEEDBACK' is disabled.
220318  9:37:29 [Note] Server socket created on IP: '127.0.0.1'.
220318  9:37:29 [Note] Server socket created on IP: '127.0.0.1'.
220318  9:37:29 [Note] Event Scheduler: Loaded 0 events
220318  9:37:29 [Note] /usr/local/mariadb/bin/mysqld: ready for connections.
Version: '5.5.57-MariaDB'  socket: '/tmp/mysql_mediadb.sock'  port: 3310  MariaDB Server
220318  9:37:29 [Note] Event Scheduler: scheduler thread started with id 1

Unterschiede zum Original sind lediglich die DefaultStorageEngine und die Einstellungen für die InnoDB.
Damit klappt jetzt der Zugriff mit Port 3306...

[Sneak-L8]

@jleg Habe gerade mal auf MariaDB 10 umgestellt. Auf den ersten BLick ist die DB einen Tick schneller. In den State kann ich mir einen Verlauf ansehen. Die Charts im vis bringen das NAS immer noch an den Anschlag. Werde als nächstes mal die Einstellungen der neuen Maria DB anschauen und Ihr mehr speicher spendieren.

Gibt es das Schema der ioBroker-DB irgnedow zum Nachlesen? Vielleicht sind in den Anfaängen meiner ioBroker-Karriere mal ein paar Dinge mit der DB schiefgelaufen (ich hatte auch mal einen Wechsel der interner ID-Felder so dass die Daten nicht mehr zusammengepasst hatten).
Nicht, dass da z.B. ein oder mehere Index auf Spalten fehlen und daher alles so langsam ist.

[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...