MariaDB V5 auf QNAP als sqlHistory langsam

[BananaJoe]

@sneak-l8 naja, wenn ein Prozess auf einen Datenträger warten (bzw. auf Daten/Blöcke von Festplatte) dann hat der Prozess eben volle Auslastung.
Ich habe oft bei Kunden analysieren müssen warum es hohe CPU Lasten gibt bzw. warum es so langsam ist.
die 100% können zum einen daran liegen das er es berechnen/auswerten muss. Zum anderen aber auch das er einfach auf die Daten warten muss.

Wie groß ist denn die Datenbank nicht gezippt? Das NAS hat 2 oder 4 GByte RAM, die CPU mit 2 Kernen könnte eher der Schwachpunkt sein. Cool wäre wenn die Datenbank so ziemlich ins RAM passt.

Aber dein Raspberry ist nun ja auch kein Geschwindigkeitswunder. Könnte also auch daran liegen das der die Daten nicht schnell genug abnimmt.
Wüsste aber gerade nicht wie man das so adhoc analysieren könnte (Ich mache sonst VMware, da ist das relativ einfach)

[Sneak-L8]

@jleg Danke für den Hinweis. Vor alem waren dort alle InnoDB-einstellungen noch auskommentiert, da der Default ja MyISAM war. Ich aktiviere diese jetzt mal mit den empfohlenen Einstellungen sowei maxcon = 300 und dann sehe ich weiter.

@BananaJoe Mein NAS hat 8 GB RAM, da spendiere ich der Maria mal 2GB. CPU hat in der Tat zwei Kerne mit 2 Ghz. Ungepackt ist der DB-Dump 250 MB groß.

[Sneak-L8]

@jleg @BananaJoe

So, habe mal die mycnf geändert und das NAS neu gestartet. Leider ist die MariaDB jetzt nicht mehr erreichbar.

Ich habe das Log gefunden und es sieht m.E. gut aus:

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

Trotzdem ist die DB nicht erreichbar, auch ein Stop meldet

error: 'Can't connect to local MySQL server through socket '/tmp/mysql_mediadb.sock' (2)'

Und das obwohl der Socket laut log erstelt wurde.

Meine my.cnf sieht jetzt so aus:

[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MariaDB server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
max_connections = 300
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine = InnoDB
#skip-innodb

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Habt Ihr einen Tipp für mich, ich weiß geraden icht mehr, wo ich nach einer Mldung suchen könnte, die mir sagt, warum der Prozess (wohl im Hintergrund) wieder beendet wird?

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