Usando mysqltuner estoy un poco preocupado por el máximo de memoria que sql parece querer poder tomar... Sólo quiero ajustar SQL para que se ejecute mejor y entender cómo hacerlo yo mismo.
Aquí están mis resultados de mysqltuner;
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.32
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(141K)
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 246 warning(s).
[!!] /var/log/mysqld.log contains 46 error(s).
[--] 16 start(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:30:59.930120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2021-01-14T11:08:52.530228Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2021-01-14T11:06:44.798420Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2021-01-14T10:58:56.636812Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2021-01-14T10:58:49.998564Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2021-01-14T10:23:15.649280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2021-01-14T09:35:59.598495Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2021-01-14T09:35:24.591837Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2021-01-14T09:26:15.821136Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2021-01-14T08:23:56.658016Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:27:51.895559Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2021-01-14T11:08:49.145150Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2021-01-14T11:06:24.969477Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2021-01-14T10:58:53.419431Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2021-01-14T09:35:54.472640Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2021-01-08T08:38:07.365178Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2021-01-07T20:25:04.834240Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 525.3M (Tables: 446)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] 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: 13m 25s (63K q [79.404 qps], 358 conn, TX: 381M, RX: 20M)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is disabled
[--] Physical Memory : 2.8G
[--] Max MySQL memory : 38.0G
[--] Other process memory: 0B
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/63K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.00% (0/358)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 11
[!!] Temporary tables created on disk: 57% (3K on disk / 5K total)
[OK] Thread cache hit rate: 97% (10 created / 358 connections)
[!!] Table cache hit rate: 16% (863 open / 5K opened)
[OK] table_definition_cache(1400) is upper than number of tables(725)
[OK] Open file limit used: 0% (11/40K)
[OK] Table locks acquired immediately: 100% (160 immediate / 160 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 90.7% (118 cached / 11 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 40.0M/525.3M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (240 %): 48.0M * 2/40.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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: 99.74% (7453089 hits/ 7472246 total)
[!!] InnoDB Write Log efficiency: 72.13% (18568 hits/ 25743 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7175 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64
Read this before increasing for MariaDB
This is MyISAM only table_cache scalability problem, InnoDB not affected.
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (40000) variable
should be greater than table_open_cache (2000)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
innodb_buffer_pool_size (>= 525.3M) if possible.
innodb_log_file_size should be (=5M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
Mi archivo.cnf es básico ya que no entiendo todas las diferentes variables;
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=41943040
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
bind-address=127.0.0.1
skip-name-resolve=1
performance_schema=ON
¿Alguna idea o consejo?
Respuesta1
¿Por qué tiene un uso máximo de memoria posible tan alto?
Aquí veremos los detalles:
Su MySQL está utilizando el número predeterminado de max_connections150 (lo que realmente permite que ese número más uno para que un usuario administrador de la base de datos se conecte; es por eso que el número que se muestra es 151).
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
Esto nos dice cuánto espacio de búfer se usaría si todas esas conexiones se usaran a la vez. Si no tiene una calculadora a mano, eso suma 38.903,1 millones (38,0G).
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
Históricamente, has alcanzado un máximo de 10 conexiones simultáneas, por lo que max_connectionsprobablemente puedas reducirlas significativamente. Lo establecería en 20 como punto de partida y solo lo aumentaría desde allí si fuera necesario.
[OK] Highest usage of available connections: 6% (10/151)
Sin embargo, lo más preocupante es cuánta memoria se utilizó realmente.
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
A menos que necesite mover grandes blobs binarios dentro y fuera de la base de datos (y si lo hace, reconsidere el diseño de su aplicación), entonces puede reducir significativamente max_allowed_packet. El valor predeterminado es 64 M y puede configurarlo allí para reducir casi 3/4 del uso de memoria (tanto real como potencial). Si obtiene errores de "paquete demasiado grande", puede generarlo, pero eso necesita RAM para cada conexión abierta.
Respuesta2
Si tiene problemas de rendimiento, la posible solución pasa por indexar y/o reformular consultas. Le sugerimos que encuentre la consulta más lenta y déjenos asesorarle.
Mientras tanto, dos ajustes a ajustar en sus 'pequeños' 2,8GB de RAM:
innodb_buffer_pool_sizeDebería ser de unos 500 m.max_allowed_packet = 30M