MySql 最大内存过高，仍要求增加

Question

我在具有 4 核/8GB RAM 的虚拟机上有一个流量非常小的 WordPress 网站。我注意到该站点的查询有时运行得非常慢，因此通过运行 mysqltuner.pl 脚本进行调查。结果如下:

[OK] Currently running supported MySQL version 10.1.22-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: ./master_error.log(1M)
[OK] Log file ./master_error.log exists
[OK] Log file ./master_error.log is readable.
[OK] Log file ./master_error.log is not empty
[OK] Log file ./master_error.log is smaller than 32 Mb
[!!] ./master_error.log contains 89 warning(s).
[!!] ./master_error.log contains 24 error(s).
[--] 4 start(s) detected in ./master_error.log
[--] 1) 2017-03-24  3:43:22 140281079671040 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-03-24  3:41:30 140390495832320 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-03-24  3:37:36 139677438822656 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-03-03 11:45:32 139685722654976 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in ./master_error.log
[--] 1) 2017-03-24  3:42:18 140390132259584 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-03-24  3:40:01 139677360564992 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-03-24  3:37:32 139685358557952 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 277M (Tables: 677)
[OK] Total fragmented tables: 0

-------- 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: 13d 11h 25m 55s (3M q [2.764 qps], 43K conn, TX: 20G, RX: 451M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 24.3G
[--] Other process memory: 2.3G
[--] Total buffers: 22.5G global + 12.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 22.7G (296.63% of installed RAM)
[!!] Maximum possible memory usage: 24.3G (318.26% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (11K/3M)
[OK] Highest usage of available connections: 10% (16/151)
[OK] Aborted connections: 0.00%  (2/43596)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 40.2% (1M cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 71K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 51% (44K on disk / 86K total)
[OK] Thread cache hit rate: 99% (23 created / 43K connections)
[OK] Table cache hit rate: 22% (854 open / 3K opened)
[OK] Open file limit used: 0% (60/16K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M 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: 4 thread(s).
[--] Using default value is good enough for your version (10.1.22-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (2B used / 11B cache)
[OK] Key buffer size / total MyISAM indexes: 11.0G/123.0K
[OK] Read Key buffer hit rate: 99.3% (1K cached / 9 reads)
[OK] Write Key buffer hit rate: 100.0% (60 cached / 60 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 11.0G/278.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.77556818181818 %): 100.0M * 2/11.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (111655571 hits/ 111658256 total)
[OK] InnoDB Write log efficiency: 97.88% (1070445 hits/ 1093608 total)
[OK] InnoDB log waits: 0.00% (0 waits / 23163 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.3% (1M cached / 35K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into ./master_error.log file
Control error line(s) into ./master_error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    performance_schema = ON enable PFS
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=5G) if possible.
    innodb_buffer_pool_instances(=11)

...这是我的 server.conf:

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
# Connection and Thread variables
socket = /var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/maria.pid
tmpdir = /run/mariadb
optimizer_search_depth=0
bind-address    = 127.0.0.1

skip-external-locking
key_buffer_size = 11G
max_allowed_packet = 1M
table_open_cache = 4000
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 256M
query_cache_limit = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 11G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64


# Security variables
local_infile                   = 0 
secure_auth                    = 1 
sql_mode                       =    TRADITIONAL,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip_name_resolve              = 1
symbolic-links=0
skip-networking

### LOGS ###
# MySQL error log
log_error                      = master_error.log
log_warnings                   = 2
# innodb_print_all_deadlocks     = 1

# Slow Query Log
slow_query_log_file            = master_slow.log
slow_query_log                 = 1
log_queries_not_using_indexes  = 1
long_query_time                = 0.5
min_examined_row_limit         = 100

# General Query Log
general_log_file               = master_general.log
general_log                    = 0

[mysqldump]
quick
max_allowed_packet = 16M

有些事情看起来显然是错误的，例如 table_open_cache 为 4000，但我想在开始摆弄设置之前看看一些专家是否有一些建议。也很困惑为什么会有如此高的内存使用率，但仍然建议进一步增加值。

Answer 1

mysqltuner 并不完美。但您确实有一些设置对于 8GB RAM 来说确实太高了...

8GB RAM 并且基本上没有 MyISAM 表？ key_buffer_size 应该更像 20M，而不是 11G!如果 mysqltuner 人们正在倾听，他们应该修复该测试。

此外，innodb_buffer_pool_size 应该仅为 5G，以避免耗尽 RAM! (并将 innodb_buffer_pool_instances 更改为 5。)

query_cache_size 超过大约 50M 时效率就会降低。

log_queries_not_using_indexes 不必要地使慢日志变得困惑；将其关闭。

Aria - 1B 是一个字节还是十亿字节？ (mysqltuner 的另一个错误)。

由于您为 MySQL 过度分配了 RAM，因此它可能正在交换。对于 MySQL 来说，交换非常效率低下，因为它假设所有缓冲区都驻留在 RAM 中。