perl mysqltuner.pl --host 127.0.0.1
[--] Skipped version check for MySQLTuner script
[--] Performing tests on 127.0.0.1:3306
[!!] Successfully authenticated with no password - SECURITY RISK!
[!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS\G
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 10.4.17-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to none of known auth columns exists
-------- CVE Security Recommendations --------------------------------------------------------------
[!!] CVE-2021-27928(<= 10.4.17) : "A remote code execution issue was discovered in MariaDB 10.2 before 10.2.37
[!!] 1 CVE(s) found for your MySQL release.
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 20h 44m 2s (48K q [0.196 qps], 384 conn, TX: 885M, RX: 18M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 1.8G
[--] Max MySQL memory : 46.5G
[--] Other process memory: 0B
[--] Total buffers: 8.3G global + 258.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 11.3G (617.92% of installed RAM)
[!!] Maximum possible memory usage: 46.5G (2537.09% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/48K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 1.30% (5/384)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 19.8% (11K cached / 56K selects)
[!!] Query cache prunes per day: 7584
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 9K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 14% (1K on disk / 10K total)
[OK] Thread cache hit rate: 80% (73 created / 384 connections)
[OK] Table cache hit rate: 99% (74K hits / 74K requests)
[OK] table_definition_cache(400) is upper than number of tables(77)
[OK] Open file limit used: 0% (48/32K)
[OK] Table locks acquired immediately: 99% (5K immediate / 5K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.4.17-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/0B
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] No tables are Innodb
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/0B
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.171875 %): 48.0M * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (609465732 hits/ 609485088 total)
[!!] InnoDB Write Log efficiency: 88.45% (3109 hits/ 3515 total)
[OK] InnoDB log waits: 0.00% (0 waits / 406 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 94.9% (28K cached / 1K 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
Uno de nuestros servidores de producción tiene algún problema de rendimiento, descubrí que estaba principalmente relacionado con MySQL y, después de ejecutar MySQLTuner, encontré algunas cosas de interés. Sorprendentemente, no hay unión sin índices, así que no creo que pueda hacer mucho y parece deberse al almacenamiento en caché, y sé que estamos usando W3T Total Cache, pero no veo ninguna configuración excepto las generales en el panel de administración de WP, así que me pregunto si hay formas de almacenar en caché más consultas, porque solo el 20% de ellas parecen estar almacenadas en caché por algún motivo. Y las eliminaciones de caché de consultas son demasiado altas, por lo que me pregunto si entiendo esas métricas correctamente y podría podar menos cada día para poder almacenar en caché al menos el 40%. Además, creo que aumentar la RAM podría ayudar a nuestro servidor, pero eso es casi un tema diferente.
Estos parecen ser los más relevantes:
[!!] Maximum reached memory usage: 11.3G (617.92% of installed RAM)
[!!] Maximum possible memory usage: 46.5G (2537.09% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 19.8% (11K cached / 56K selects)
[!!] Query cache prunes per day: 7584
Respuesta1
Todas sus tablas parecen ser tablas MyISAM. Se recomienda que todas las tablas se configuren como tablas InnoDB hoy en día.
La caché de consultas de MySQL es una característica obsoleta debido a variasasuntos, por lo tanto lo mejor es desactivarlo.
Después de cambiar todas las tablas a InnoDB, ejecute MySQLTuner nuevamente y ajuste el grupo de búfer de InnoDB de acuerdo con sus recomendaciones.
También debes actualizar tu servidor MySQL, ya que tu versión tiene una vulnerabilidad abierta.
Respuesta2
Sospecho queintercambiandoes el verdadero problema. Tienes RAM comprometida excesivamente. Estas son las cosas que me llaman la atención:
Physical Memory : 1.8G
InnoDB buffer pool / data size: 8.0G/0B
Highest usage of available connections: 7% (12/151)
Después de cambiar a InnoDB y apagar el control de calidad (como recomienda Tero), tenga en cuenta que su RAM es demasiado pequeña para el archivo innodb_buffer_pool_size.
Más específicamente, realice estos cambios de configuración:
innodb_buffer_pool_size = 500M
max_connections = 30
query_cache_type = 0
query_cache_size = 0
key_buffer_size = 24M
Si aún tiene problemas después de realizar esos cambios, comience una nueva pregunta en dba.stackexchange.com; Es un mejor foro para la configuración de MySQL/MariaDB.