MySQL 中的某些東西導致負載越來越高，幾乎消耗了所有記憶體

我有這台伺服器，它只有一個站點，上面有一個數據庫。然而，我無法調整它以使負載恢復正常。

如果我重新啟動mysql服務，記憶體和負載會恢復正常，但幾天后，記憶體和負載會逐漸增加。

伺服器規格

• 700
• 總記憶體7.8G
• 交換1G

htop 輸出

MySQL版本

mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

我的.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#max_allowed_packet=1024M


##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

plugin-load-add=validate_password.so


max_allowed_packet=100M

#------------------| query cach config
# https://easyengine.io/tutorials/mysql/query-cache
#------------------|
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
#query_cache_strip_comments =1



port            = 3306
#socket         = /var/lib/mysql/mysql.sock
#skip-external-locking
key_buffer_size = 512M
max_allowed_packet=268435456
table_open_cache = 256
sort_buffer_size = 256
read_buffer_size = 256
read_rnd_buffer_size = 40
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's x 2 for thread_concurrency
#thread_concurrency = 2

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id      = 1



# ============================================================ #
# =============== Custom server tweaks [start] =============== #
# ============================================================ #

default_storage_engine = InnoDB
innodb_buffer_pool_size =  2G #4G # 8G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
innodb_file_per_table = 1
#--------------------------| 
# 2 means log buffer is flushed to OS file cache on every transaction commit. 
# The implication of 2 is optimal and improve performance 
# if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes
# -------------------------|
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M

#---------------------------| 
# Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, 
# even the first time you set it up, as the default value is 8M. 
# Don't go above 256M.
# ref: https://gist.github.com/wh4u/1fafe2bf98f630d7753717b5a8835bca
#---------------------------|
innodb_log_file_size = 256M


innodb_file_per_table=ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8  #(or 1 if innodb_buffer_pool_size < 1GB)
#query_cache_type = 0
#query_cache_size = 0 #(disabling mutex)

#---------------|
# ref: http://techinfobest.com/optimize-mysql-table_open_cache/
# --------------|
#open_files_limit=10000
open_files_limit=19107
table_open_cache=39390

slow-query-log=1
long_query_time = 1
log-queries-not-using-indexes = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

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

[mysqlhotcopy]
interactive-timeout

幾天后htop輸出

如果我不重新啟動 MySQL，3-4 天后會發生這種情況

有人可以指出我正確的方向嗎？

額外細節

MySQL 稍後崩潰，日誌中有以下消息：

2020-01-03T12:59:51.514413Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4887ms. The settings might not be optimal. (flushed=142 and evicted=0, during the time.)
2020-01-03T13:00:35.061257Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11874ms. The settings might not be optimal. (flushed=89 and evicted=0, during the time.)
2020-01-03T13:01:13.857771Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 78941)
2020-01-03T13:01:13.858498Z 0 [Warning] Changed limits: table_open_cache: 2419 (requested 39390)
2020-01-03T13:01:14.177515Z 0 [Warning] option 'sort_buffer_size': unsigned value 256 adjusted to 32768
2020-01-03T13:01:14.177549Z 0 [Warning] option 'read_buffer_size': unsigned value 256 adjusted to 8192
2020-01-03T13:01:14.177630Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-03T13:01:14.420855Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28-log) starting as process 15029 ...
2020-01-03T13:01:15.607997Z 0 [Warning] You need to use --log-bin to make --binlog-format work.
2020-01-03T13:01:15.758435Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-01-03T13:01:15.758521Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-03T13:01:15.758535Z 0 [Note] InnoDB: Uses event mutexes
2020-01-03T13:01:15.758552Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-01-03T13:01:15.758563Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-03T13:01:15.758574Z 0 [Note] InnoDB: Using Linux native AIO
2020-01-03T13:01:15.759295Z 0 [Note] InnoDB: Number of pools: 1
2020-01-03T13:01:15.759589Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-01-03T13:01:15.773538Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
2020-01-03T13:01:16.072379Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-03T13:01:16.142472Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-03T13:01:16.237526Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-01-03T13:01:16.349890Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 82179195773
2020-01-03T13:01:16.349986Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 82179207508
2020-01-03T13:01:16.471207Z 0 [Note] InnoDB: Database was not shutdown normally!

我嘗試了重複問題建議中提供的解決方案，但這使事情變得更糟。伺服器在幾分鐘內耗盡記憶體。

更新

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 1654382
Buffer pool size   131072
Free buffers       1
Database pages     128972
Old database pages 47448
Modified db pages  1578
Pending reads      1
Pending writes: LRU 0, flush list 163, single page 0
Pages made young 17903348, not young 602752081
0.00 youngs/s, 0.00 non-youngs/s
Pages read 75403666, created 44758503, written 47149356
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 0 / 1000 not 67 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 128972, unzip_LRU len: 0
I/O sum[106576]:cur[1912], unzip sum[0]:cur[0]

是Old database pages不是太多了？你看到什麼問題？

更新 #2 這是 my.cnf 目前的樣子

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#max_allowed_packet=1024M


##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

plugin-load-add=validate_password.so


max_allowed_packet=100M

#------------------| query cach config
# https://easyengine.io/tutorials/mysql/query-cache
#------------------|
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
#query_cache_strip_comments =1



port            = 3306
#socket         = /var/lib/mysql/mysql.sock
#skip-external-locking
key_buffer_size = 512M
max_allowed_packet=268435456
#table_open_cache = 256
sort_buffer_size = 256
read_buffer_size = 256
read_rnd_buffer_size = 40
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's x 2 for thread_concurrency
#thread_concurrency = 2

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id      = 1



# ============================================================ #
# =============== Custom server tweaks [start] =============== #
# ============================================================ #

default_storage_engine = InnoDB
innodb_buffer_pool_size =  2G #4G # 8G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
innodb_file_per_table = 1
#--------------------------| 
# 2 means log buffer is flushed to OS file cache on every transaction commit. 
# The implication of 2 is optimal and improve performance 
# if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes
# -------------------------|
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M

#---------------------------| 
# Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, 
# even the first time you set it up, as the default value is 8M. 
# Don't go above 256M.
# ref: https://gist.github.com/wh4u/1fafe2bf98f630d7753717b5a8835bca
#---------------------------|
innodb_log_file_size = 256M


innodb_file_per_table=ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8  #(or 1 if innodb_buffer_pool_size < 1GB)
#query_cache_type = 0
#query_cache_size = 0 #(disabling mutex)

#---------------|
# ref: http://techinfobest.com/optimize-mysql-table_open_cache/
# --------------|
table_open_cache=1501
#table_open_cache=30000
open_files_limit=3003
#open_files_limit=19107
#table_open_cache=39390

slow-query-log=0
long_query_time = 1
log-queries-not-using-indexes = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

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

[mysqlhotcopy]
interactive-timeout

我注意到文件中的一些參數是雙重的，所以我註釋掉了重複項。egtable_open_cache在mysqld下的兩個地方，一個值為256，另一個值為39390。

對於性能問題，導致問題的從來都不是一件事。我會寫下我所做的所有調整，希望這對其他人也有幫助。每個人的評論都非常有幫助，你們都展示了找到解決方案的方法。

我按照本指南計算正確的數字。

mysql> show global status  like 'open%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_files               | 43    |
| Open_streams             | 0     |
| Open_table_definitions   | 375   |
| Open_tables              | 2288  |
| Opened_files             | 6842  |
| Opened_table_definitions | 376   |
| Opened_tables            | 3286  |
+--------------------------+-------+

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
+----------+
| COUNT(*) |
+----------+
|      273 |
+----------+

mysql> show global status like '%Threads_connected%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 11    |
+-------------------+-------+

Table_open_cache = total_tables*Threads_connected
                   = 273 * 11
                   = 3003


table_open_cache=1501
open_files_limit=3003

Rick James 關於之前的值太大是對的。

然後以下參數不正確：

sort_buffer_size = 256   (Default value: 262144, as per official documentation)
read_buffer_size = 256    (Better 128K)
read_rnd_buffer_size = 40     (Default Value: 262144, as per official documentation) 

reasonable alternatives are: 
sort_buffer_size=512K 
read_buffer_size=256K 
read_rnd_buffer_size=128K 

正如威爾遜豪克建議的那樣。

max_connections是150，所以我減少到50。

此外，需要編輯php-fpm配置。

pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35


pm.start_servers= min_spare_servers + (max_spare_servers - min_spare_servers) / 2

changing to 
pm.max_children = 25
pm.start_servers = 11
pm.min_spare_servers = 5
pm.max_spare_servers = 17

最好始終遵循官方文件中提供的公式。

所有這些都使記憶體恢復正常使用。但是CPU負載仍然太高。

最後但同樣重要的是，我使用了工具JetProfiler。甚至免費版本也有幫助。我確定了工作時間過長的查詢。很棒的是，jetprofiler 甚至會為您提供建議，您可以優化查詢。

現在htop看起來好多了。

table_open_cache=1501,open_files_limit=3003（正如 Vasiliki 所建議的）應該沒問題。

注：open_files_limit大於ulimit -n時，啟動時前者降低。所以，SHOW VARIABLES LIKE 'open_files_limit';看看設置到底是什麼。（同上table_open_cache。）

table_open_cache是一個“記憶體”。因此，太大的值會浪費記憶體；太小的值會減慢某些事情的速度。該值是表的數量，而不是字節數。2000 是我調查的一百多台伺服器使用的中值。（第 10 個百分位：400；第 90 個百分位：10000。您的 39390 大約是第 95 個百分位 - 一個危險信號。）將其設置為 1501 並忘記它，除非您正在做一些非常不正常的事情。

ulimit -n是 Linux（及其表親）施加的每個程序的文件限制。1024 是某些作業系統版本中的預設值，對於臨時程序來說是一個合理的限制。然而，它對於 MySQL 來說太低了，正如許多從事重要數據庫工作的使用者所發現的那樣。然而，這主要是防止您在打開文件時瘋狂的限制。將其設置為 10000 可能是合理的；這對 MySQL 有幫助，而不是“有害”。

open_files_limit被初始化為ulimit -n。（我不明白你怎麼有 1024 和 19107。但請注意“

$$ Warning $$更改的限制：table_open_cache：2419（請求的 39390）“）但是，這只是防止程序失控的限制。 table_open_cache是所有這一切的關鍵。但是，由於其他兩個設置，您可能會停止設置它。

但…

你問過“高負載”——幾乎總是來自低效的查詢。這通常可以通過改進索引（認為“複合”）和/或重新編寫查詢來解決。讓我們看一個慢查詢並討論它。

你展示了大量的程序。請提供SHOW VARIABLES LIKE 'max_connections'; 它可能是預設的 151，這可以。（注意：此預設值已根據版本更改了幾次。）考慮將其降低到 50。另外，讓我們看看SHOW GLOBAL STATUS LIKE 'Max_used_connections'; 這是否超過 10，那麼這可能是其他問題的徵兆。

你提到記憶不斷增長。嗯，這是“正常的”，但只是在一定程度上。RAM的最大使用者應該是buffer_pool；innodb_buffer_pool_size = 2G表示該部分將增長到佔用 2GB，然後停止增長。過大的表記憶體（由 控制table_open_cache）是另一個記憶體使用者。（不，我不知道每個條目中有多少字節。）每個“連接”都會消耗一些記憶體。並且連接是“記憶體的”，所以我的評論max_connections表明另一個記憶體消費者和控制它的方式。（同樣，我不知道每個連接的記憶體使用情況。）

htop輸出不支持您的“高負載” 。你有6核？他們平均約為3-4％？另一方面，平均負載大於 3？（我比洛杉磯更信任 CPU 百分比。）再次；我們來看一個慢查詢；您“無法擺脫性能問題”。

引用自：https://dba.stackexchange.com/questions/256651