MariaDB Innodb fatal semaphore wait threshold

Peut être trop de conso RAM ?
Que donne un mysqltuner ? Comparé à la RAM du serveur notamment...

la ram, ce n'est pas très chargé.

Mysqltuner... c'est vrai que je devrais utiliser ça.
Je l'installe et je check.
Merci

Que donne un mysqltuner ? Comparé à la RAM du serveur notamment...

Je viens d'installer mysqltuner.
(je l'utilisais il y a quelques années... comment n'y ai-je pas pensé !)

Brut, pas encore analysé en détail :

>> MySQLTuner 1.7.17 - Major Hayden mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[!!] Your MySQL version 10.6.7-MariaDB-2ubuntu1.1 is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in InnoDB tables: 253.0M (Tables: 1110)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'mariadb.sys@localhost' has no password set.
[--] There are 620 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
Use of uninitialized value in multiplication (*) at /bin/mysqltuner line 2705 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
and the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.


-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 1h 7m 11s (312K q [3.455 qps], 6K conn, TX: 4G, RX: 571M)
[--] Reads / Writes: 84% / 16%
[--] Binary logging is disabled
[--] Physical Memory : 4.0G
[--] Max MySQL memory : 2.5G
[--] Other process memory: 0B
[--] Total buffers: 704.0M global + 12.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 779.2M (19.25% of installed RAM)
[OK] Maximum possible memory usage: 2.5G (64.15% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/312K)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.06% (4/6534)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 27.3% (94K cached / 344K selects)
[!!] Query cache prunes per day: 20176
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (483 on disk / 5K total)
[OK] Thread cache hit rate: 99% (15 created / 6K connections)
[!!] Table cache hit rate: 17% (512 open / 2K opened)
[OK] Open file limit used: 0% (59/8K)
[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 3 thread(s).
[--] Using default value is good enough for your version (10.6.7-MariaDB-2ubuntu1.1)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/253.0M
Use of uninitialized value in concatenation (.) or string at /bin/mysqltuner
line 5614 (#1)
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0 %): 96.0M * /128.0M should be equal to 25%
Use of uninitialized value in division (/) at /bin/mysqltuner line 5621 (#1)

Illegal division by zero at /bin/mysqltuner line 5621 (#2)
(F) You tried to divide a number by 0. Either something was wrong in
your logic, or you need to put a conditional in to guard against
meaningless input.

Uncaught exception from user code:
Illegal division by zero at /bin/mysqltuner line 5621.
main::mysql_innodb() called at /bin/mysqltuner line 6353


Il n'a pas l'air d'apprécier MariaDB ?
Non ma version de MariaDB n'est pas, je pense, obsolète.

Et il se termine par une **illegal division by zero ?**

Pas de log files...
en effet, **rien dans /var/log/mysql**

La RAM, c'est très largement ok.

[!!] Cannot calculate MyISAM index size - **re-run script as root user**

Mais j'ai explicitement lancé mysqltuner avec --user et --pass ...

**Illegal division by zero :**

"_This issue have been solved in a later version 1.7.24_"

Ubuntu 22.04 me donne mysqltuner 1.7.17-1 ...


-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/253.0M
Use of uninitialized value in concatenation (.) or string at /bin/mysqltuner
line 5614 (#1)
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0 %): 96.0M * /128.0M should be equal to 25%
Use of uninitialized value in division (/) at /bin/mysqltuner line 5621 (#1)

Illegal division by zero at /bin/mysqltuner line 5621 (#2)
(F) You tried to divide a number by 0. Either something was wrong in
your logic, or you need to put a conditional in to guard against
meaningless input.

Uncaught exception from user code:
Illegal division by zero at /bin/mysqltuner line 5621.
main::mysql_innodb() called at /bin/mysqltuner line 6353

Peut être qu'il est monté un peu trop haut en RAM, ou d'autres process à côté.
Il y a quoi dans /var/log/message en rapport à mariadb ?
Sinon le buffer pool mériterait un peu d'amour, il faut penser à adapter le innodb-log-file-size en même temps.
Et je vois que mysqltuner n'a pas été lancé en root ?
> Cannot calculate MyISAM index size - re-run script as root user

mysqltuner a été lancé en root !
(root mysql qui n'a pas le mm mot de passe que root linux)

mysqltuner --user root --pass 7Zq+123+123+123

(sans ça, il bloquait car password invalide)

Il y a quoi dans /var/log/message en rapport à mariadb ?

Je l'ai pas ça dans /var/log ...
J'ai un problème de logs je pense, mais l'install MariaDB doit quand même par défaut me mettre des logs ?

dernière version de mysqltuner :

/root/download/mysqltuner/mysqltuner.pl --user root --pass 7Z+123+123+123
>> MySQLTuner 2.0.5
* Jean-Marie Renouard gmail.com>
* Major Hayden mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.6.7-MariaDB-2ubuntu1.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in InnoDB tables: 253.0M (Tables: 1110)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 1h 55m 16s (315K q [3.385 qps], 7K conn, TX: 4G, RX: 574M)
[--] Reads / Writes: 84% / 16%
[--] Binary logging is disabled
[--] Physical Memory : 4.0G
[--] Max MySQL memory : 12.0G
[--] Other process memory: 0B
[--] Total buffers: 704.0M global + 76.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.1G (28.74% of installed RAM)
[!!] Maximum possible memory usage: 12.0G (302.89% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/315K)
[OK] Highest usage of available connections: 3% (6/151)
[!!] Aborted connections: 6.29% (486/7728)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 27.3% (94K cached / 347K selects)
[!!] Query cache prunes per day: 19555
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (519 on disk / 6K total)
[OK] Thread cache hit rate: 99% (15 created / 7K connections)
[OK] Table cache hit rate: 95% (223K hits / 233K requests)
[!!] table_definition_cache (400) is less than number of tables (1402)
[OK] Open file limit used: 0% (11/8K)
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)

-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M / 253.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.59% (6235163 hits/ 6260926 total)
[!!] InnoDB Write Log efficiency: 12.77% (6013 hits/ 47069 total)
[OK] InnoDB log waits: 0.00% (0 waits / 41056 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K
[OK] Aria pagecache hit rate: 99.0% (40K cached / 401 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 32M)
table_definition_cache (400) > 1402 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 253.0M) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals 25% of buffer pool size.

C'est très intéressant !
Donc ma config fait que MariaDB peut bouffer bcp trop de ram, et mettre en danger la stabilité du système ?

ça expliquerait bien des choses...

Je vais voir les modifs proposées et tester ça ce soir.
Merci

vi, faut éviter de dépasser les 50% de RAM pour mysql...
En gros ce qui risque de se passer c'est que MySQL (ou MariaDB osef) va consommer trop de RAM.
OOM-Killer va passer dans le coin et kill le SGBD...

Le truc c'est que c'est bon pour corrompre des tables ça..

Réduire le nbr max de connexion déjà. A voir pour le query cache, ce n'est pas tjrs utile de l'avoir.
Surtout que là bcp de choses sont retirées du cache car il est trop petit.

c'est certainement ça qui s'est passé quand le serveur a bloqué !

Et systemd kill la tâche...

Bon, j'essaie 2 variables en vitesse :

skip-name-resolve=1
performance_schema=ON

Je les mets dans un membre dans mariadb.conf.d , ou dans mariadb.cnf
C'est refusé !

dans /etc/mysql/mariadb.conf.d/50-server.cnf (sur debian en tt cas).
A éditer en tant que root.
Faut réduire le nbr max de connexion aussi. 32 devrait suffire pour commencer.

ça devrait être identique en Ubuntu server...

ok, il a pris les 2 variables (pas encore modifié les connexions)

MariaDB me donne plein de msg "Access denied for user root" !

Y compris quand je vais simplement le restart

Aug 28 13:40:59 ct822-drupal systemd[1]: Started MariaDB 10.6.7 database server.
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892988]: Upgrading MySQL tables if necessary.
Aug 28 13:40:59 ct822-drupal mariadbd[892969]: 2022-08-28 13:40:59 0 [Note] InnoDB: Buffer pool(s) load completed at 220828 13:40:59
Aug 28 13:40:59 ct822-drupal mariadbd[892969]: 2022-08-28 13:40:59 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892991]: Looking for 'mysql' as: /usr/bin/mysql
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892991]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892991]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892991]: FATAL ERROR: Upgrade failed
Aug 28 13:40:59 ct822-drupal /etc/mysql/debian-start[892997]: Checking for insecure root accounts.
Aug 28 13:40:59 ct822-drupal mariadbd[892969]: 2022-08-28 13:40:59 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Aug 28 13:40:59 ct822-drupal debian-start[893000]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


mais il me donne des erreurs "Access denied for user ... avec un mot de passe" !
mais !!!

Aug 28 13:45:18 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:18 686 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:21 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:21 687 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 688 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 689 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 690 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 691 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 692 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 693 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:22 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:22 694 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 695 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 696 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 697 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 698 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 699 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:23 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:23 700 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
Aug 28 13:45:24 ct822-drupal mariadbd[892969]: 2022-08-28 13:45:24 701 [Warning] Access denied for user 'root'@'localhost' (using password: YES)


Pas encore trouvé de posts m'aidant sur ça

les solutions proposées pour ces msgs d'erreur Mysql ou Mariadb :

mariadbd[301]: 2022-08-28 13:55:14 115 [Warning] Access denied for user 'root'@'localhost' (using password: YES)


c'est en général de donner tous les privilèges à root !

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '';

Je trouve ça un peu violent...
Comment se fait-il que root (mariadb) essaie des accès dont il n'a pas les droits ?

mariadbd[301]: 2022-08-28 13:55:14 115 [Warning] Access denied for user 'root'@'localhost' (using password: YES)

ces messages **toutes les 5 minutes !**

C'est **MUNIN** qui n'a pas le mot de passe MariaDB root pour son plugin !

less /var/log/munin/munin-node.log

...
2022/08/28-14:25:22 [7769] Error output from mysql_replication:
2022/08/28-14:25:22 [7769] DBI connect('mysql;mysql_read_default_file=/etc/mysql/debian.cnf;mysql_connect_timeout=5','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /etc/munin/plugins/mysql_replication line 1071.
2022/08/28-14:25:22 [7769] Service 'mysql_replication' exited with status 255/0.
2022/08/28-14:25:23 [7769] Error output from mysql_binlog_groupcommit:
2022/08/28-14:25:23 [7769] DBI connect('mysql;mysql_read_default_file=/etc/mysql/debian.cnf;mysql_connect_timeout=5','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /etc/munin/plugins/mysql_binlog_groupcommit line 1071.
2022/08/28-14:25:23 [7769] Service 'mysql_binlog_groupcommit' exited with status 255/0.
...


Ce problème user/password pour **MUNIN** n'a rien à voir avec MariaDB ou le serveur lui-même.

J'en ferai si nécessaire un sujet séparé.
Merci

Sur debian il y a des fichiers de config du genre : /etc/mysql/debian.cnf avec les accès pour MySQL.
Sur les versions récentes il y a un compte root avec tous les droits sans mot de passe mais en passant par le socket, et faut être root pour que ça marche.
Visiblement l'install est buggée non ? Des fichiers de config ont du être changé, ou des users sur le sgbd directement...

Ce serait probablement utile de fixer ces erreurs avant d'aller + loin...