使用 mysqltuner 时我有点担心 sql 似乎想要占用的最大内存...我只是想调整 SQL 以更好地运行并了解如何自己做。
这是我的 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.
My.cnf 文件很基础，因为我不了解所有不同的变量；
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
有什么想法或建议吗？
答案1
为什么最大内存使用量可能这么高？
下面我们来看一下细节：
您的 MySQL 使用默认数字max_connections150（这实际上允许数据库管理员用户连接该数字加一；这就是显示的数字为 151 的原因）。
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
这告诉我们如果同时使用所有这些连接，将使用多少缓冲区空间。如果您手边没有计算器，则总计为 38,903.1M (38.0G)。
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
从历史上看，您最多只能同时连接 10 个，因此max_connections可能可以大大减少。我会将其设置为 20 作为起点，并且仅在必要时才从那里增加。
[OK] Highest usage of available connections: 6% (10/151)
但更令人担忧的是实际使用了多少内存。
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
除非您需要将大型二进制 blob 移入和移出数据库（如果需要，请重新考虑您的应用程序设计），否则您可以显著减少max_allowed_packet。默认值为 64M，您可以将其设置为该值以减少近 3/4 的内存使用量（实际和潜在）。如果您收到“数据包太大”错误，那么您可以提高它，但这需要每个打开的连接都有 RAM。
答案2
如果您遇到性能问题，可能的解决方案是索引和/或重新制定查询。建议您找到最慢的查询并让我们提供建议。
同时，在您的“小” 2.8GB RAM 中需要调整两个设置：
innodb_buffer_pool_size应该是500M左右。max_allowed_packet = 30M