Wordpress
-
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) -
Je dois avoir un problème de cache :
W3 Total Cache
-
-
Ajout de cache REDIS:
-
Avec la migration je suis passé de PHP 8.4.12 => 8.4.15
-
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) -
Je viens de mettre Redis Cache :
-
C’est pas encore bon …
-
Je vais virer la SWAP :
# /sbin/sysctl vm.swappiness=0 vm.swappiness = 0 # /sbin/swapoff -a # /sbin/swapon -a -
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) -
Je viens aussi de mettre : /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] skip-name-resolve -
Je lance :
# mysqlcheck --auto-repair --optimize --all-databases ... -
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 …
-
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 -
Update :
Bonjour ! Vous semblez intéressé par cette conversation, mais vous n’avez pas encore de compte.
Marre de refaire défiler les mêmes messages ? Créez un compte pour retrouver votre position, recevoir des notifications des nouvelles réponses, sauvegarder vos favoris et voter pour les messages que vous appréciez.
Grâce à votre participation, ce message peut devenir encore meilleur 💗
S'inscrire Se connecter