我正在管理具有以下配置的服务器，并使用 mysqltuner.pl 调整了 MySQL。但是最终服务器占用了 100% 的已安装内存。我寻求您的建议来纠正此问题。
Ubuntu Server 18.04 内存：16 GB CPU：4 MySQL：5.7.31
以下是我的 MySQL 服务器的配置文件
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 256
performance_schema=0
#performance-schema-instrument='memory/%=COUNTED'
#
# * Fine Tuning
#
key_buffer_size = 1M
max_allowed_packet = 24M
thread_stack = 192K
thread_cache_size = 9
tmp_table_size = 48M
max_heap_table_size = 48M
thread_cache_size = 12
join_buffer_size = 4M
wait_timeout = 120 #set to 10 minutes from 8 hours
interactive_timeout = 120 #set to 15 minutes from 8 hours
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
max_connections = 275
open_files_limit = 8192
table_open_cache = 2750
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 256K
query_cache_size = 96M
query_cache_min_res_unit = 2k
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
刚刚收到的 Mysqltuner.pl 输出
>> MySQLTuner 1.6.4 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[[0;32mOK[0m] Logged in using credentials from debian maintenance account.
[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;32mOK[0m] Currently running supported MySQL version 5.7.31-0ubuntu0.18.04.1-log
[[0;32mOK[0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in InnoDB tables: 7G (Tables: 228)
[[0;31m!![0m] Total fragmented tables: 63
-------- Security Recommendations -------------------------------------------
[[0;32mOK[0m] There are no anonymous accounts for any database users
[[0;32mOK[0m] All database users have passwords assigned
[[0;31m!![0m] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------
[[0;34m--[0m] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 18h 41m 49s (21M q [325.215 qps], 246K conn, TX: 58G, RX: 3G)
[[0;34m--[0m] Reads / Writes: 95% / 5%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 8.2G global + 4.8M per thread (275 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 9.3G (59.59% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 9.4G (60.43% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (10K/21M)
[[0;31m!![0m] Highest connection usage: 89% (247/275)
[[0;32mOK[0m] Aborted connections: 0.00% (1/246569)
[[0;32mOK[0m] Query cache efficiency: 75.8% (15M cached / 20M selects)
[[0;31m!![0m] Query cache prunes per day: 4043524
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (14K temp sorts / 1M sorts)
[[0;31m!![0m] Joins performed without indexes: 1764
[[0;32mOK[0m] Temporary tables created on disk: 0% (1K on disk / 126K total)
[[0;32mOK[0m] Thread cache hit rate: 98% (4K created / 246K connections)
[[0;31m!![0m] Table cache hit rate: 17% (2K open / 13K opened)
[[0;32mOK[0m] Open file limit used: 0% (39/5K)
[[0;32mOK[0m] Table locks acquired immediately: 100% (318 immediate / 318 locks)
-------- MyISAM Metrics ------------------------------------------------------
[[0;31m!![0m] Key buffer used: 18.7% (196K used / 1M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 1.0M/43.0K
[[0;32mOK[0m] Read Key buffer hit rate: 97.9% (430 cached / 9 reads)
-------- InnoDB Metrics ------------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 8.0G/7.6G
[[0;32mOK[0m] InnoDB buffer pool instances: 8
[[0;31m!![0m] InnoDB Used buffer: 71.86% (376697 used/ 524224 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 100.00% (118056700441 hits/ 118056942522 total)
[[0;31m!![0m] InnoDB Write Log efficiency: 73.83% (741389 hits/ 1004247 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 262858 writes)
-------- ThreadPool Metrics --------------------------------------------------
[[0;34m--[0m] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[[0;34m--[0m] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[[0;34m--[0m] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[[0;34m--[0m] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[[0;34m--[0m] No replication slave(s) for this server.
[[0;34m--[0m] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Beware that open_files_limit (5000) variable
should be greater than table_open_cache ( 2357)
Variables to adjust:
max_connections (> 275)
wait_timeout (< 120)
interactive_timeout (< 120)
query_cache_size (> 96M)
join_buffer_size (> 4.0M, or always use indexes with joins)
table_open_cache (> 2357)
以下是服务器的当前状态。我大约 6 小时前重启了 mysql 服务器，将 wait_timeout 和 interactive_timeout 更改为 90。但是，由于服务器正常运行时间短，目前情况似乎正常。
我正在运营基于 Drupal 7 的新闻网站，该网站平均每日页面访问量为 90,000 次
回复@Wilson Hauck，是的，服务器使用 SSD 作为硬盘。请参阅以下 pastbin 链接以查找 B) SHOW GLOBAL STATUS；最低 24 小时 UPTIME C) SHOW GLOBAL VARIABLES；D) SHOW FULL PROCESSLIST；的详细信息
https://pastebin.ubuntu.com/p/YhWP9szmjP/
$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 63934
max locked memory (kbytes, -l) 16384
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 63934
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
$ iostat -xm 5 3
Linux 5.3.0-1032-aws (ip-172-31-21-134) 08/25/20 _x86_64_ (4 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
32.44 0.64 1.79 0.54 0.10 64.49
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.16 0.00 0.00 1.01 0.00 0.13 0.00
loop1 0.12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.24 0.00 0.00 1.01 0.00 0.13 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.82 0.00 0.00 8.64 0.00 1.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.09 0.00 0.00 1.48 0.00 0.33 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 1.00 0.00
xvda 50.74 48.87 1.77 1.00 7.64 17.73 13.09 26.62 1.08 3.41 0.10 35.70 20.89 0.86 8.57
qavg-cpu: %user %nice %system %iowait %steal %idle
82.65 0.00 2.06 0.05 0.00 15.23
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda 5.00 15.80 0.04 0.20 0.00 12.80 0.00 44.76 0.68 1.18 0.00 8.96 12.66 1.73 3.60
avg-cpu: %user %nice %system %iowait %steal %idle
92.67 0.00 2.46 0.10 0.05 4.72
Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
loop0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvda 5.80 9.80 0.16 0.09 6.00 11.00 50.85 52.88 1.28 0.69 0.00 27.72 9.39 2.62 4.08
答案1
每秒速率 = RPS
对 my.cnf [mysqld] 部分的建议
thread_cache_size=100 # from 12 to reduce overhead of threads_created 18,140 in 4 days
innodb_io_capacity=1900 # from 200 to allow higher IOPS on your SSD media
read_buffer_size=512K # from 128K to reduce handler_read_next RPS of 446,320
read_rnd_buffer_size=96K # from 256K to reduce handler_read_rnd_next RPS of 19,363
innodb_lru_scan_depth=100 from 256 to conserve 50% of CPU cycles used for function
您会发现这些变化显著降低了 CPU 繁忙程度。
还有更多机会可以改善您的配置。查看我的个人资料、网络个人资料以获取联系信息和免费下载实用程序脚本以提高性能。