Issue Mysql server under pressure (too much ?)

[Teal_cfr]

Hi Everyone,
Just to give you back some information about this

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User='admin';
+-------+-----------+
| User  | Host      |
+-------+-----------+
| admin | localhost |
+-------+-----------+
1 row in set (0.000 sec)

So my solution was :

CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY '1234xxx';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;

And Then :

sudo perl mysqltuner.pl --host 127.0.0.1 --user admin --pass '1234xxx'

 

[Teal_cfr]

Can i have your advice about my : my.cnf file

Serveur have about 124Gb of RAM

[mysql]
#performance_schema = on
#max_allowed_packet=4196M
max_connections = 200

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
query_cache_size=64M
join_buffer_size = 8M

#bind-address = ::
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0
#max_allowed_packet=4196M
net_buffer_length=16K
wait_timeout=300
interactive_timeout=300

####### Control slow query ######

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

####### CACHE - Management - 15/09/23 ######
tmp_table_size = 4096M
max_heap_table_size = 4096M
sort_buffer_size = 2M
thread_cache_size = 16
#temptable_max_mmap = 4G
#temptable_max_ram = 4G
innodb_buffer_pool_size=48G
innodb_log_file_size=6G
innodb_buffer_pool_instances = 48
performance_schema=ON
table_definition_cache=1500
skip-name-resolve=ON
query_cache_size=8G
#query_cache=0

[client]
#wait_timeout=31536000
#max_allowed_packet=4196M

[mysqldump]
max_allowed_packet=4196M

I was wondering if changing these values can help my problem

tmp_table_size=256M
max_heap_table_size=256M
max_connections=500
thread_cache_size=50
innodb_log_file_size=512M
innodb_buffer_pool_size=100G
wait_timeout=300
interactive_timeout=300
net_buffer_length=64K
max_allowed_packet=256M

 

[mow]

You could also just have run mysqltuner without the --host argument ...

 

[mow]

innodb_buffer_pool_size

Also, 100GB seem excessive, especially when it's 200x innodb_log_file_size

 

[Teal_cfr]

Also, 100GB seem excessive, especially when it's 200x innodb_log_file_size

I just read on documentation that buffer_pool_size should be 80% of the RAM

Calculating InnoDB Buffer Pool Size For Your MySQL Server

In this post, you'll learn 2 methods of calculating InnoDB buffer pool size, examine pros and cons, and how to compute a optimum value based on system RAM size.

scalegrid.io

MySQL :: MySQL 8.0 Reference Manual :: 8.12.3.1 How MySQL Uses Memory

dev.mysql.com

So what is your advice ?

 

[mow]

I just read on documentation that buffer_pool_size should be 80% of the RAM

Calculating InnoDB Buffer Pool Size For Your MySQL Server

In this post, you'll learn 2 methods of calculating InnoDB buffer pool size, examine pros and cons, and how to compute a optimum value based on system RAM size.

scalegrid.io

Please also read the last paragraph on that site:
"The considerations in this blog post are for Linux systems that are dedicated for MySQL. For Windows systems or systems that run multiple applications along with MySQL, these observations can be inaccurate."
Look into monitoring, check the amount of memory that is not used by applications (disregard cache) at any time including the daily tasks at 6:25, and base your calculations on that.
And according to mysqltuner, InnoDB log file size should be 1/4 of pool size, and InnoDB buffer pool chunk size * InnoDB buffer pool instances must not be smaller than total pool size.
You have query_cache_size specified twice. 8G for query cache is unnecessary because the query cache is very stupid and gets invalidated at every write. It is only efficient if the application sends the exact same expensive query repeatedly, which is very bad application design *cough*magento.

 

[Teal_cfr]

Finally i have choose this configuration but i still have the same problem :

ubuntu@modest-hoover:/var/log/mysql$ mysql
mysql: unknown variable 'max_connections=500'

my.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# Custom config for xxxx
# 25.10.2023

[mysql]
max_connections = 500

[client-server]

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
# join_buffer_size customised on MySQLTuner advices
join_buffer_size = 16M
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
thread_cache_size = 50

#bind-address = ::
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0

# Ajustements for packet size and waiting time
wait_timeout=300
interactive_timeout=300
net_buffer_length=64K
max_allowed_packet=256M

# keep this to log slow query
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# Cache and temp table management
# tmp_table_size and max_heap_table_size are defined above

# InnoDB Ajustements
innodb_log_file_size=512M
innodb_buffer_pool_size=80G  # Approximativement 64% de la mémoire totale
innodb_buffer_pool_instances = 48

# Custom rules from JC 15.09.2023
performance_schema=ON 
table_definition_cache=1500 
skip-name-resolve=ON 
query_cache_size=8G 
#query_cache=0 
#temptable_max_mmap = 4G 
#temptable_max_ram = 4G 

[client]
max_allowed_packet=256M

[mysqldump]
max_allowed_packet=256M

 

[Teal_cfr]

Ok then it seems to be [mysqld] instread of [mysql]

and now working

Apache config:
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# Custom config for xxxx
# 25.10.2023

[mysql]
max_connections = 500