Archives par mot-clé : mariadb

Mariadb : Ma configuration pour WordPress

Publié le par
Répondre

Ma VM a 32 Go de RAM.
Voici ma configuration :

# cat /etc/mysql/mariadb.conf.d/50-server.cnf | grep -v "#" | grep -v "^$"
[server]
[mysqld]
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking
skip-name-resolve=ON
performance_schema=ON
query_cache_limit = 1024K
query_cache_min_res_unit = 2k
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
bind-address            = 127.0.0.1
key_buffer_size        = 24M
max_connections        = 200
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
thread_cache_size = 16
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_open_cache = 4096
table_definition_cache = 4096
join_buffer_size = 16M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
[embedded]
[mariadb]
[mariadb-10.5]

Mariadb : Optimisation pour humhub

Publié le par
Répondre

J’utilise Mariadb sous Debian pour Humhub.

Modification dans /etc/sysctl.conf

vm.swappiness=1

Et changement en direct :

/usr/sbin/sysctl -w vm.swappiness=1

Modification dans /etc/mysql/mariadb.conf.d/50-server.cnf avec :

innodb_buffer_pool_size = 4G

Avant le restart :

mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52245
Server version: 10.5.26-MariaDB-0+deb11u2 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0,001 sec)

MariaDB [(none)]> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           0.125000000000 |
+------------------------------------------+
1 row in set (0,001 sec)

MariaDB [(none)]> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0,003 sec)

Après :

mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.26-MariaDB-0+deb11u2 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 4294967296     |
+-------------------------------------+----------------+
10 rows in set (0,003 sec)

J’ai pas vu beaucoup de changement, alors dans /etc/mysql/mariadb.conf.d/50-server.cnf j’ai ajouté :

innodb_buffer_pool_size = 8G
innodb_log_file_size = 512M
thread_cache_size = 16
query_cache_size = 128M
query_cache_type = 1
table_open_cache = 4096

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

A suivre.