저는 다음 구성으로 서버를 관리하고 있으며 mysqltuner.pl을 사용하여 MySQL을 조정했습니다. 그러나 결국 서버는 설치된 메모리를 100% 사용하게 되었습니다. 이 문제를 바로잡을 수 있도록 조언을 구합니다.
Ubuntu Server 18.04 RAM: 16GB 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)
다음은 현재 서버 상황입니다. wait_timeout 및 Interactive_timeout을 90으로 변경하여 약 6시간 전에 mysql 서버를 다시 설정했습니다. 그러나 현재 서버 가동 시간이 낮아 상황이 정상적인 것으로 보입니다.
나는 평균 일일 트래픽이 90,000페이지 방문하는 Drupal 7 기반 뉴스 웹사이트를 운영하고 있습니다.
@Wilson Hauck에 대한 답변으로 예, 서버는 SSD를 하드 디스크로 사용합니다. B) SHOW GLOBAL STATUS;의 세부 사항을 찾으려면 다음 Pastbin 링크를 참조하십시오. 최소 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 사용량이 크게 줄어드는 것을 확인할 수 있습니다.
구성을 개선할 수 있는 더 많은 기회가 있습니다. 내 프로필, 연락처 정보용 네트워크 프로필, 무료로 다운로드 가능한 유틸리티 스크립트를 확인하여 성능을 향상하세요.