私は以下の構成でサーバーを管理しており、mysqltuner.pl を使用して MySQL をチューニングしました。しかし、サーバーがインストールされているメモリを 100% 使用してしまいました。これを修正するためのアドバイスをお願いします。
Ubuntu Server 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 時間ほど前に、wait_timeout と interactive_timeout を 90 に変更して、mysql サーバーを再起動しました。ただし、サーバーの稼働時間が低いため、現在は状況は正常のようです。
私はDrupal 7ベースのニュースウェブサイトを運営しており、平均して毎日90,000ページ訪問のトラフィックがあります。
@Wilson Hauck への返信: はい、サーバーはハードディスクとして SSD を使用します。B) SHOW GLOBAL STATUS; の詳細については、次の pastbin リンクを参照してください。最低 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
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 の使用率が大幅に減少することがわかります。
構成を改善する機会は他にもたくさんあります。私のプロフィール、連絡先情報のネットワーク プロフィール、およびパフォーマンスを改善するための無料でダウンロード可能なユーティリティ スクリプトをご覧ください。