MariaDB V5 auf QNAP als sqlHistory langsam

[Sneak-L8]

Hallo zusammen,

eine ganze Zeit hatte ich Ruhe und die SQL-Datenaufzeichnung lief ohne Probleme.
Frühere Meldung wie "ER_CON_COUNT_ERROR: Too many connections" kontne ich lösen, indem ich in der my.cnf die Zeile "max_connections = 1500" eingefügt hatte.

In den letzten Tagen treten diese Meldungen wieder auf. Evtl. liegt es an einem Firmware-Update meiens NAS. Die Konfiguration:

• ioBroker auf Raspi 3, alle Adapter aktuell (Stable)
• NAS QNAP TS-251B mit vorinstalliertem MariaDB5
• Sicherung der DB ist gezippt 57 MB groß

my.cnf:

[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
max_connections = 1500
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 = MyISAM
log-bin=mysql-bin
binlog_format=mixed
server-id	= 1

[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

Was mir auch aufgefallen ist:

• Die Anzeige der States im ioBroker ist jetzt übersichtlicher, aber die historischen Werte werden nicht mehr angezeigt (Verlaufsbalken bewegt sich ewig ohne Ergebnis
• Das NAS wird dann auch sehr langsam und ich sehe, dass die MariaDB ziemlich am Anschlag läuft.

Ich hab gesehen, dass es für QNAP auch MariaDB v10 gibt. Angeblich würde die DB autoamtisch von v5 nach v10 geschwenkt, wenn ich es installiere. Dam traue ich aber nicht so ganz und weiß auch nicht, ob das mein Problem lösen wird.

Mir scheint, dass die DB-Abfragen sehr langsam sind und daher keine History zu sehen ist. Das scheint dann wiederum die DB in die Knie zu zwingen.

Ich bin aber gerade etwas ratlos, wo ich mit der Ursachenforschung weitermachen soll. Hat jemand einen Tipp für mich?

[opossum]

Hallo, @sneak-l8,

Du könntest vor dem Update ja eine Datensicherung der Datenbank machen. Die könntest Du dann zurückspielen, wenn es mit der Migration auf v10 nicht klappt?

[Sneak-L8]

@opossum Sicherung läuft ja auch täglich, aber wird das Update mein Problem lösen? Sonst sehe ich noch keinen Grund auf MariaDB 10 zu wechseln. QNAP supportet derzeit beide Versionen.

[opossum]

Hallo, @sneak-l8,

meine Sicherung ist zur Zeit 268 MB groß. Meine MariaDB läuft auf einer Proxmox VM. Bisher hatte ich, auch mit Updates, keine Probleme. Könnte Dir anbieten, dass Du mal auf meine DB schaust via AnyDesk und dann via HeidiSQL?

[Sneak-L8]

@opossum Danke für das Angebot. Aber was soll ich da schauen?

[BananaJoe]

Du könntest - auf eigene Gefahr - auch versuchen die Zugriffe zu optimieren, für meine Monitoring-Server nehme ich gerne diese Zeilen (in der Konfigurationsdatei des MariaDB/MySQL unter [mysqld]

# Transaktionswerte nach Möglichkeit nicht in den Doublewritebuffer schreiben
innodb_doublewrite = 0
# Transaktionen schon bestätigen sobald diese im Cache stehen:
innodb_flush_log_at_trx_commit = 2
# Wieviel Hauptspeicher soll zum Puffern verwendet werden? Nehmt die Hälfte des vorhandenen Speichers (bei 4GB RAM also 2G, bei 1G also 512M)
innodb_buffer_pool_size = 1G

Nutze ich seit Jahren ... was aber auch bedeutet das dies eventuell mal auf den Prüfstand müsste.
Liegt bei mir jetzt eh immer auf NVMe, da habe ich keine Geschwindigkeitsprobleme mehr

[Sneak-L8]

@bananajoe Danke für den Tipp. Aber sind die Werte wirklich relevant, wenn als default-storage-engine = MyISAM angegeben ist?
Bei den innodb-Werten steht als Kommentar "Uncomment the following if you are using InnoDB tables" drüber. Klingt, als wenn es dan bei mir keine Auswirkung hätte.
Beim Typ der Tabellen steht auch "MyISAM".
Gibt es evtl. eine Beschreibung, wie die Strutur der Tabellen aussehen sollte? Sehe z.B. dass bei ts_number und tS_string keine Indizes angelegt sind. Dann dürfte jede Abfrage ja zu einem, Table Full Scan führen. Gibt es da eine Option, das zu fixen?

[BananaJoe]

@bananajoe sagte in MariaDB V5 auf QNAP als sqlHistory langsam:

Nutze ich seit Jahren ... was aber auch bedeutet das dies eventuell mal auf den Prüfstand müsste.

@Sneak-L8 also Prüfstand
Hab mal gecheckt: Ich setzte zu 99% MySQL oder MariaDB unter Ubuntu ein - und es ist immer InnoDB der Default. Passt also für mich. Wir haben 2022, wer nutzte denn noch MyISAM, seit 5.5 ist das doch InnoDB und die Version kam im Dezember 2010 raus. Seit mehr als 10 Jahren ist das Default.

[Sneak-L8]

@bananajoe Ok, danke fürs Checken. Dann ist das wohl der Default bei QNAP gewesen.
Da bin ich mir jetzt aber unsicher, ob ich einfach die Default-Storage-Engine ändern kann, da die DB ja (soweit ich weiß) auch von den NAS-Apps der QNAP genutzt wird und ob das für Bestandsfelder was bringt. Vermutlich nicht.
Aber gut, dann müsste ich die bestehenden Tabelen ändern. Geht das so einfach? Oder muss ich die Datenbank dan neu aufsetzen und die gesicherten Daten zurückspielen? Wenn ioBroker die DB anlegt, wird er die Engine vermutlich nicht vorgeben, also muss ich dann doch die Default-Engine anpassen.
Das könnte ich dann gleich mit Installation der MariaDB 10 machen und für die Migration evtl. beide kurzzeitig parallel nutzen.

Danke, ich glaub, ich weiß jetzt, in welche Richtung ich laufen muss ...

[Sneak-L8]

@sneak-l8 So, ich hab jetzt die DB umgestellt udn und alle ioBroker auf die Engine InnoDB gesetzt. Das ging problemlos im laufenden Betrieb ohne Unterbrechung.
Aber ich sehe weiterhin, dass mein NAS auf 100% CPU springt, sobald ich mit VIS ein Chart anzeigen oder zu einem State die historiscen werte anzeigen lassen will.
Irgendwie scheint so eine Abfrage die DB zu überfordern ...?

[JLeg]

@sneak-l8 und wie sieht my.cnf jetzt aus? Hoffentlich nicht wie oben…
(mehr als 300 bei max_connections sind im Normalfall nicht gesund)

Die wichtigsten Basics sind hier zusammengefasst:
https://mariadb.com/de/resources/blog/10-database-tuning-tips-for-peak-workloads/

Der bei innodb wichtigste Parameter: innodb_buffer_pool_size

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