Wordpress

farias

Migration du NUXIT à PROXMOX.
Mais une perte de performance : 6.6 => 5.9

A cause de la base.

farias

J’ai fait une première modification :

farias

je viens de modifier le fichier /etc/php/8.4/fpm/php-fpm.conf

# cat /etc/php/8.4/fpm/php-fpm.conf | grep -v "^$" 
;;;;;;;;;;;;;;;;;;;;;
; FPM Configuration ;
;;;;;;;;;;;;;;;;;;;;;
; All relative paths in this configuration file are relative to PHP's install
; prefix (/usr). This prefix can be dynamically changed by using the
; '-p' argument from the command line.
;;;;;;;;;;;;;;;;;;
; Global Options ;
;;;;;;;;;;;;;;;;;;
[global]
; Pid file
; Note: the default prefix is /var
; Default Value: none
; Warning: if you change the value here, you need to modify systemd
; service PIDFile= setting to match the value here.
pid = /run/php/php8.4-fpm.pid
; Error log file
; If it's set to "syslog", log is sent to syslogd instead of being written
; into a local file.
; Note: the default prefix is /var
; Default Value: log/php-fpm.log
error_log = /var/log/php8.4-fpm.log
; syslog_facility is used to specify what type of program is logging the
; message. This lets syslogd specify that messages from different facilities
; will be handled differently.
; See syslog(3) for possible values (ex daemon equiv LOG_DAEMON)
; Default Value: daemon
;syslog.facility = daemon
; syslog_ident is prepended to every message. If you have multiple FPM
; instances running on the same server, you can change the default value
; which must suit common needs.
; Default Value: php-fpm
;syslog.ident = php-fpm
; Log level
; Possible Values: alert, error, warning, notice, debug
; Default Value: notice
;log_level = notice
; Log limit on number of characters in the single line (log entry). If the
; line is over the limit, it is wrapped on multiple lines. The limit is for
; all logged characters including message prefix and suffix if present. However
; the new line character does not count into it as it is present only when
; logging to a file descriptor. It means the new line character is not present
; when logging to syslog.
; Default Value: 1024
;log_limit = 4096
; Log buffering specifies if the log line is buffered which means that the
; line is written in a single write operation. If the value is false, then the
; data is written directly into the file descriptor. It is an experimental
; option that can potentially improve logging performance and memory usage
; for some heavy logging scenarios. This option is ignored if logging to syslog
; as it has to be always buffered.
; Default value: yes
;log_buffering = no
; If this number of child processes exit with SIGSEGV or SIGBUS within the time
; interval set by emergency_restart_interval then FPM will restart. A value
; of '0' means 'Off'.
; Default Value: 0
;emergency_restart_threshold = 0
; Interval of time used by emergency_restart_interval to determine when
; a graceful restart will be initiated.  This can be useful to work around
; accidental corruptions in an accelerator's shared memory.
; Available Units: s(econds), m(inutes), h(ours), or d(ays)
; Default Unit: seconds
; Default Value: 0
;emergency_restart_interval = 0
; Time limit for child processes to wait for a reaction on signals from master.
; Available units: s(econds), m(inutes), h(ours), or d(ays)
; Default Unit: seconds
; Default Value: 0
;process_control_timeout = 0
; The maximum number of processes FPM will fork. This has been designed to control
; the global number of processes when using dynamic PM within a lot of pools.
; Use it with caution.
; Note: A value of 0 indicates no limit
; Default Value: 0
process.max = 128
; Specify the nice(2) priority to apply to the master process (only if set)
; The value can vary from -19 (highest priority) to 20 (lowest priority)
; Note: - It will only work if the FPM master process is launched as root
;       - The pool process will inherit the master process priority
;         unless specified otherwise
; Default Value: no set
; process.priority = -19
; Send FPM to background. Set to 'no' to keep FPM in foreground for debugging.
; Default Value: yes
;daemonize = yes
; Set open file descriptor rlimit for the master process.
; Default Value: system defined value
rlimit_files = 2048
; Set max core size rlimit for the master process.
; Possible Values: 'unlimited' or an integer greater or equal to 0
; Default Value: system defined value
;rlimit_core = 0
; Specify the event mechanism FPM will use. The following is available:
; - select     (any POSIX os)
; - poll       (any POSIX os)
; - epoll      (linux >= 2.5.44)
; - kqueue     (FreeBSD >= 4.1, OpenBSD >= 2.9, NetBSD >= 2.0)
; - port       (Solaris >= 10)
; Default Value: not set (auto detection)
;events.mechanism = epoll
; When FPM is built with systemd integration, specify the interval,
; in seconds, between health report notification to systemd.
; Set to 0 to disable.
; Available Units: s(econds), m(inutes), h(ours)
; Default Unit: seconds
; Default value: 10
;systemd_interval = 10
;;;;;;;;;;;;;;;;;;;;
; Pool Definitions ;
;;;;;;;;;;;;;;;;;;;;
; Multiple pools of child processes may be started with different listening
; ports and different management options.  The name of the pool will be
; used in logs and stats. There is no limitation on the number of pools which
; FPM can handle. Your system will tell you anyway :)
; Include one or more files. If glob(3) exists, it is used to include a bunch of
; files from a glob(3) pattern. This directive can be used everywhere in the
; file.
; Relative path can also be used. They will be prefixed by:
;  - the global prefix if it's been set (-p argument)
;  - /usr otherwise
include=/etc/php/8.4/fpm/pool.d/*.conf

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