我正在管理具有以下配置的伺服器，並使用 mysqltuner.pl 調整 MySQL。然而，最終伺服器使用了 100% 的已安裝記憶體。我尋求你的建議來糾正這個問題。
Ubuntu 伺服器 18.04 RAM：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 小時正常運作時間後 C) 顯示全域變數； D) 顯示完整的流程清單；
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 繁忙程度。
也存在許多改進您的配置的機會。查看我的個人資料、網路個人資料以取得聯絡資訊以及可免費下載的實用程式腳本以提高效能。