Wordpress

farias

Nouvelle optimisation de Mariadb

# 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
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 = 24M
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
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
[embedded]
[mariadb]
[mariadb-10.5]

farias

Lancement d’un test :

# mysqltuner --host localhost 
 >>  MySQLTuner 2.7.0
	 * Jean-Marie Renouard <jmrenouard@gmail.com>
	 * Major Hayden <major@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
ℹ  Performing tests on localhost:3306
✔  Logged in using credentials from Debian maintenance account.
✔  Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
ℹ  Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
ℹ  Data in InnoDB tables: 140.3M (Tables: 171)
ℹ  Data in MyISAM tables: 71.2K (Tables: 19)
ℹ  Data in Aria tables: 32.0K (Tables: 1)
✔  Total fragmented tables: 0
 
✔  Currently running supported MySQL/MariaDB version 10.11.14-MariaDB-0+deb12u2(LTS)
 
-------- Log file Recommendations ------------------------------------------------------------------
✘  Log file  doesn't exist
 
-------- Analysis Performance Metrics --------------------------------------------------------------
ℹ  innodb_stats_on_metadata: OFF
✔  No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
ℹ  Debian 12 - 10.11.14-MariaDB-0+deb12u2
✔  There are no anonymous accounts for any database users
✔  All database users have passwords assigned
✘  There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
ℹ  Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
ℹ  Up for: 1m 5s (222 q [3.415 qps], 64 conn, TX: 110K, RX: 36K)
ℹ  Reads / Writes: 93% / 7%
ℹ  Binary logging is disabled
ℹ  Physical Memory     : 30.6G
ℹ  Max MySQL memory    : 9.9G
ℹ  Other process memory: 0B
ℹ  Total buffers: 9.3G global + 2.9M per thread (200 max threads)
ℹ  Performance_schema Max memory usage: 103M
ℹ  Galera GCache Max memory usage: 0B
✔  Maximum reached memory usage: 9.4G (30.60% of installed RAM)
✔  Maximum possible memory usage: 9.9G (32.46% of installed RAM)
✔  Overall possible memory usage with other process is compatible with memory available
✔  Slow queries: 0% (0/222)
✔  Highest usage of available connections: 1% (2/200)
✔  Aborted connections: 0.00% (0/64)
✔  Query cache is disabled by default due to mutex contention on multiprocessor machines.
✔  No Sort requiring temporary tables
✘  Joins performed without indexes: 3
✘  Temporary tables created on disk: 30% (9 on disk / 30 total)
✔  Thread cache hit rate: 96% (2 created / 64 connections)
✔  Table cache hit rate: 74% (173 hits / 233 requests)
✔  table_definition_cache (4096) is greater than number of tables (482)
✔  Open file limit used: 0% (94/32K)
✔  Table locks acquired immediately: 100% (176 immediate / 176 locks)
 
-------- Performance schema ------------------------------------------------------------------------
ℹ  Performance_schema is activated.
ℹ  Memory used by Performance_schema: 103.4M
ℹ  Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
ℹ  ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
✘  Consider migrating 19 following tables to InnoDB:
ℹ  * InnoDB migration request for `farias17`.`af_es_pluginconfig` Table: ALTER TABLE `farias17`.`af_es_pluginconfig` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_users` Table: ALTER TABLE `farias17`.`af_users` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_commentmeta` Table: ALTER TABLE `farias17`.`af_commentmeta` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_flag_album` Table: ALTER TABLE `farias17`.`af_flag_album` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_flag_comments` Table: ALTER TABLE `farias17`.`af_flag_comments` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_ai1ec_event_category_colors` Table: ALTER TABLE `farias17`.`af_ai1ec_event_category_colors` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_xmlgooglemaps_cache` Table: ALTER TABLE `farias17`.`af_xmlgooglemaps_cache` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_es_emaillist` Table: ALTER TABLE `farias17`.`af_es_emaillist` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_xmlgooglemaps` Table: ALTER TABLE `farias17`.`af_xmlgooglemaps` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_links` Table: ALTER TABLE `farias17`.`af_links` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_es_templatetable` Table: ALTER TABLE `farias17`.`af_es_templatetable` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_es_deliverreport` Table: ALTER TABLE `farias17`.`af_es_deliverreport` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_xmlgooglemaps_gpxfile` Table: ALTER TABLE `farias17`.`af_xmlgooglemaps_gpxfile` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_flag_gallery` Table: ALTER TABLE `farias17`.`af_flag_gallery` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_es_notification` Table: ALTER TABLE `farias17`.`af_es_notification` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_xmlgooglemaps_gpxfile_item` Table: ALTER TABLE `farias17`.`af_xmlgooglemaps_gpxfile_item` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_translations_log` Table: ALTER TABLE `farias17`.`af_translations_log` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_es_sentdetails` Table: ALTER TABLE `farias17`.`af_es_sentdetails` ENGINE=InnoDB;
ℹ  * InnoDB migration request for `farias17`.`af_translations` Table: ALTER TABLE `farias17`.`af_translations` ENGINE=InnoDB;
ℹ  General MyIsam metrics:
ℹ   +-- Total MyISAM Tables  : 19
ℹ   +-- Total MyISAM indexes : 51.0K
ℹ   +-- KB Size :128.0M
ℹ   +-- KB Used Size :23.3M
ℹ   +-- KB used :18.2%
ℹ   +-- Read KB hit rate: 0% (0 cached / 0 reads)
ℹ   +-- Write KB hit rate: 0% (0 cached / 0 writes)
✘  Key buffer used: 18.2% (23.3M used / 128.0M cache)
✔  Key buffer size / total MyISAM indexes: 128.0M/51.0K
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
ℹ  InnoDB is enabled.
✔  InnoDB File per table is activated
✔  InnoDB Buffer Pool size ( 8.0G ) under limit for 64 bits architecture: (17179869184.0G )
✔  InnoDB buffer pool / data size: 8.0G / 140.3M
✔  Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 1/8.0G should be equal to 25%
ℹ  innodb_buffer_pool_chunk_size is set to 'autosize' (0) in MariaDB >= 10.8. Skipping chunk size checks.
ℹ  InnoDB Read buffer efficiency: metrics are not reliable (reads > read requests)
✘  InnoDB Write Log efficiency: 64% (16 hits / 25 total)
✔  InnoDB log waits: 0.00% (0 waits / 9 writes)
 
-------- Query Cache Information -------------------------------------------------------------------
ℹ  QUERY_CACHE_INFO plugin is not active or not installed.
 
-------- Aria Metrics ------------------------------------------------------------------------------
ℹ  Aria Storage Engine is enabled.
✔  Aria pagecache size / total Aria indexes: 128.0M/344.0K
✘  Aria pagecache hit rate: 75.0% (52 cached / 13 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:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
    Temporary table size is already large: reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    MyISAM engine is deprecated, consider migrating to InnoDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    key_buffer_size (~ 24M)
    innodb_log_buffer_size (> 24M)

farias

Je dois avoir un problème de cache :
W3 Total Cache

farias

farias

Ajout de cache REDIS:

farias

Avec la migration je suis passé de PHP 8.4.12 => 8.4.15

farias

Migration InnoDB :

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2618
Server version: 10.11.14-MariaDB-0+deb12u2-log Debian 12

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 CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE='MyISAM'
    -> AND table_schema = 'farias17';
+-----------------------------------------------------------+
| CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')       |
+-----------------------------------------------------------+
| ALTER TABLE af_es_pluginconfig ENGINE=InnoDB;             |
| ALTER TABLE af_users ENGINE=InnoDB;                       |
| ALTER TABLE af_commentmeta ENGINE=InnoDB;                 |
| ALTER TABLE af_flag_album ENGINE=InnoDB;                  |
| ALTER TABLE af_flag_comments ENGINE=InnoDB;               |
| ALTER TABLE af_ai1ec_event_category_colors ENGINE=InnoDB; |
| ALTER TABLE af_xmlgooglemaps_cache ENGINE=InnoDB;         |
| ALTER TABLE af_es_emaillist ENGINE=InnoDB;                |
| ALTER TABLE af_xmlgooglemaps ENGINE=InnoDB;               |
| ALTER TABLE af_links ENGINE=InnoDB;                       |
| ALTER TABLE af_es_templatetable ENGINE=InnoDB;            |
| ALTER TABLE af_es_deliverreport ENGINE=InnoDB;            |
| ALTER TABLE af_xmlgooglemaps_gpxfile ENGINE=InnoDB;       |
| ALTER TABLE af_flag_gallery ENGINE=InnoDB;                |
| ALTER TABLE af_es_notification ENGINE=InnoDB;             |
| ALTER TABLE af_xmlgooglemaps_gpxfile_item ENGINE=InnoDB;  |
| ALTER TABLE af_translations_log ENGINE=InnoDB;            |
| ALTER TABLE af_es_sentdetails ENGINE=InnoDB;              |
| ALTER TABLE af_translations ENGINE=InnoDB;                |
+-----------------------------------------------------------+
19 rows in set (0,004 sec)

farias

Je viens de mettre Redis Cache :

farias

C’est pas encore bon …

farias

Je vais virer la SWAP :

# /sbin/sysctl vm.swappiness=0
vm.swappiness = 0
# /sbin/swapoff -a
# /sbin/swapon -a

farias

Optimisation MariaDB :

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 471
Server version: 10.11.14-MariaDB-0+deb12u2-log Debian 12

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 status like 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 4     |
+-----------------+-------+
1 row in set (0,002 sec)

MariaDB [(none)]> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 476   |
+---------------+-------+
1 row in set (0,001 sec)

MariaDB [(none)]> set global thread_cache_size = 90;
Query OK, 0 rows affected (0,001 sec)

farias

Je viens aussi de mettre : /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
skip-name-resolve

farias

Je lance :

# mysqlcheck --auto-repair --optimize --all-databases
...

farias

Je comprends pas le problème avec la base.

Pourtant j’ai aucune requete qui dort :

# mysqladmin processlist 
+-----+------+-----------+----+---------+------+----------+------------------+----------+
| Id  | User | Host      | db | Command | Time | State    | Info             | Progress |
+-----+------+-----------+----+---------+------+----------+------------------+----------+
| 288 | root | localhost |    | Query   | 0    | starting | show processlist | 0.000    |
+-----+------+-----------+----+---------+------+----------+------------------+----------+

C’est toujours instantanée …

farias

Optimisation :

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9343
Server version: 10.11.14-MariaDB-0+deb12u2-log Debian 12

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 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0,001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'net_buffer_length';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0,004 sec)

MariaDB [(none)]> set global net_buffer_length=1000000;
Query OK, 0 rows affected, 1 warning (0,000 sec)

MariaDB [(none)]> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected, 1 warning (0,000 sec)

MariaDB [(none)]> \quit
Bye

farias

Update :

NodeBB

Wordpress