MySQL 内存使用率过高

MySQL 内存使用率过高

我在具有 64GB RAM + 64GB 交换分区的系统上运行 Ubuntu 16.04。它不是专用服务器,因此同时还有其他进程在运行。无论如何,当 MySQL 守护进程停止时,最大内存使用量约为 38GB,因此 MySQL 最多可以使用 25GB 而无需交换(在我看来太多了)。

当 MySQL 守护进程启动时,htop 报告大约 1Gb RES(2.6Gb VIRT)。

一切运行良好,但是它会缓慢增加内存使用量,直至大约 2 天后填满整个系统内存(物理内存和交换内存)。

当它即将崩溃时,htop 报告大约有 25Gb RES(96Gb VIRT)。然后不知何故 MySQL 自动重启,我们又回到原点。

我在内存使用量达到峰值时运行了 MySQLTuner 脚本:

# ./mysqltuner.pl
 >>  MySQLTuner 1.7.10 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.23-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(843B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 5 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MEMORY tables: 0B (Tables: 1)
[--] Data in MyISAM tables: 35.3G (Tables: 2792)
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[OK] Total fragmented tables: 0

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 12h 20m 20s (8M q [61.889 qps], 171K conn, TX: 2G, RX: 2G)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 1.5G
[--] Other process memory: 82.6G
[--] Total buffers: 1.2G global + 1.8M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.2G (1.97% of installed RAM)
[OK] Maximum possible memory usage: 1.5G (2.33% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 13% (20/151)
[OK] Aborted connections: 0.60%  (1037/171776)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 25.6% (557K cached / 2M selects)
[!!] Query cache prunes per day: 386310
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 57882
[OK] Temporary tables created on disk: 3% (52K on disk / 1M total)
[OK] Thread cache hit rate: 99% (20 created / 171K connections)
[!!] Table cache hit rate: 0% (416 open / 1M opened)
[OK] Open file limit used: 46% (481/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 38.5% (413M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/5.3G
[OK] Read Key buffer hit rate: 100.0% (42B cached / 7M reads)
[!!] Write Key buffer hit rate: 5.0% (4M cached / 222K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (5574849 hits/ 5575178 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control error line(s) into /var/log/mysql/error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    Dedicate this server to your database for highest performance.
    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: http://...
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (431)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://...
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 32M)
    join_buffer_size (> 512.0K, or always use indexes with JOINs)
    table_open_cache (> 431)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

笔记:

  • 该系统仅使用 MyISAM 引擎。InnoDB 引擎仅由 MySQL 内部使用。
  • 据此,最大可能的内存使用量:1.5G(这显然不是真的!)
  • /var/log/mysql/error.log 中的错误只是一些中止的连接

我的 my.cnf 文件中的当前配置是:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[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
#performance-schema=0

skip-name-resolve
lower_case_table_names = 1
default-storage-engine = MYISAM
character_set_server=latin1
collation-server=latin1_spanish_ci
secure_file_priv =
#sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#skip-innodb

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 10.88.1.5

#
# * Fine Tuning
#
key_buffer_size         = 1G
max_allowed_packet      = 32M
thread_stack            = 256K
thread_cache_size       = 24
tmp_table_size          = 32M
max_heap_table_size     = 32M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections         = 100
#table_open_cache        = 64
#thread_concurrency      = 10

read_buffer_size        = 256K
sort_buffer_size        = 512K
join_buffer_size        = 512K

#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 32M
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
#log_slow_queries       = /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
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
default_tmp_storage_engine = MYISAM

我已经尝试了不同的值key_buffer_size, thread_stack, read_buffer_size, sort_buffer_size, join_buffer_size并启用/禁用query_cache。它们都没有产生任何显着差异。

有人可以帮忙吗?

提前致谢。

编辑:

根据要求提供更多信息:

# ps eaxo rss,vsz,pid,command --sort rss | tail -n 55
16732 2534624 107493 /usr/sbin/named -f -u bind LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin RESOLVCONF=no OPTIONS=-u bind
17732 385232 139905 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
17744 466856 172740 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
18240 466856 172737 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19252 466868 172722 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19412 466868 132925 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19456 466872 172729 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
19948 466888 172731 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
20004 466868 132953 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
20996 466812 130247 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
21096 466868 102906 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
21484 388864 163258 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
21792 466868  72842 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
22000 466868 102905 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
23016 388972 164428 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
23392 466880  26122 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
23472 466868  94437 /usr/sbin/apache2 -k start APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=C APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/
28588 397504 163212 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
30320 388988 164587 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
31516 390568 164754 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
42904 405572 176109 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
43316 320100  93396 /usr/bin/php /var/www/src/coodebox/scripts/coode_reviews.php 60 APACHE_RUN_DIR=/var/run/apache2 APACHE_PID_FILE=/var/run/apache2/apache2.pid PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin APACHE_LOCK_DIR=/var/lock/apache2 LANG=en_US.UTF-8 APACHE_RUN_USER=www-data APACHE_RUN_GROUP=www-data APACHE_LOG_DIR=/var/log/apache2 PWD=/var/www/html/coodebox/ws/REST
56732 414504 164502 /usr/sbin/smbd -D PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin LANG=en_US.UTF-8 _SYSTEMCTL_SKIP_REDIRECT=true PWD=/
1098744 2794316 45939 /usr/lib/virtualbox/VBoxHeadless --comment XP-5012 --startvm 2abd8bb1-4f00-4362-819c-b10b8dd4b871 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1103480 2798412 45727 /usr/lib/virtualbox/VBoxHeadless --comment XP-5003 --startvm 4bbc1a71-461f-41d1-b69d-b284f99500cd --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1106228 2800460 45721 /usr/lib/virtualbox/VBoxHeadless --comment XP-5002 --startvm 51ecd0c8-cbc5-42fb-a72e-d08da1a736f4 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1110200 2806604 45791 /usr/lib/virtualbox/VBoxHeadless --comment XP-5006 --startvm 8eb5368c-56a4-4e4e-8ba2-8dec3a961050 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1114872 2804556 45885 /usr/lib/virtualbox/VBoxHeadless --comment XP-5010 --startvm ac303817-9449-417e-920c-a2f0fe893a0a --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1115996 2810700 45909 /usr/lib/virtualbox/VBoxHeadless --comment XP-5011 --startvm 17c097df-421f-4d67-85c7-3be00228eb72 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1117156 2792268 45763 /usr/lib/virtualbox/VBoxHeadless --comment XP-5005 --startvm c5299acb-3b9f-422c-90d8-2fe31725d9c0 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1117404 2792268 45863 /usr/lib/virtualbox/VBoxHeadless --comment XP-5009 --startvm a0fe46f9-7a1c-47ab-b3b4-aa20441db26c --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1128424 2814796 45715 /usr/lib/virtualbox/VBoxHeadless --comment XP-5001 --startvm b58e7653-0d72-42b5-b9a7-3d692b76e6fc --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1139780 2814796 45836 /usr/lib/virtualbox/VBoxHeadless --comment XP-5008 --startvm fb572cbd-b78d-4ef6-b6f8-a37c0d9510c8 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1140052 2837324 45743 /usr/lib/virtualbox/VBoxHeadless --comment XP-5004 --startvm af874537-e9bd-4e2e-897b-25001e5b6f2f --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1144164 2922436 45954 /usr/lib/virtualbox/VBoxHeadless --comment XP-5013 --startvm 3ed3b1a1-bd37-42c8-97c6-d6e6c4c25c4e --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1172692 2798412 46009 /usr/lib/virtualbox/VBoxHeadless --comment XP-5015 --startvm fffe8b4c-e0fc-4392-9d23-0685e8e4d091 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1218940 2894668 46627 /usr/lib/virtualbox/VBoxHeadless --comment XP-5007 --startvm d551f639-b6ac-4208-b122-6a9a47825d1e --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1246860 3037460 2271 /usr/lib/virtualbox/VBoxHeadless --comment A7-7004 --startvm ac7998be-0195-4a70-b90a-36b3c9bee5f7 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1349212 3046220 45982 /usr/lib/virtualbox/VBoxHeadless --comment XP-5014 --startvm 9f6a2cf1-933c-449b-8ad5-250453454df7 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1400096 3189792 76852 /usr/lib/virtualbox/VBoxHeadless --comment W7-6002 --startvm 2b62c82d-81e9-4fac-9a91-558cd37be161 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1769280 3549124 47192 /usr/lib/virtualbox/VBoxHeadless --comment ASHOINDEX2 --startvm 2b975e4c-81ce-4637-a990-c139478ca3ad --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
1862860 3562476 18827 /usr/lib/virtualbox/VBoxHeadless --comment W7-6004 --startvm 07fe24bf-f61e-4a31-8698-8bb81ee0aa58 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2182876 4030316 46788 /usr/lib/virtualbox/VBoxHeadless --comment W7-CGS --startvm 176bbaa0-14d0-4710-a39a-4efb27c67cce --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2203432 4094584 44848 /usr/lib/virtualbox/VBoxHeadless --comment W2008R2_Terra AD --startvm 8d23ece3-c06e-4dd4-a4ec-ba979841e27c --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
2471096 4306412 106689 /usr/lib/virtualbox/VBoxHeadless --comment W7-6050 --startvm 7eb6e7bd-c191-42e0-9457-533eb5cae4f8 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
4236200 6012176 84909 /usr/lib/virtualbox/VBoxHeadless --comment W7_Conta --startvm a7943c66-ebd6-4979-8413-fb34dda3d080 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
5368420 7103980 46905 /usr/lib/virtualbox/VBoxHeadless --comment W7-6003 --startvm 2c34c221-8ebb-49c1-9efe-3a5a48af2eab --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
5628600 7465120 189125 /usr/lib/virtualbox/VBoxHeadless --comment W7-6001 --startvm 9e4f415e-8754-477d-acc5-a7de4248fc52 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6344388 8120096 47107 /usr/lib/virtualbox/VBoxHeadless --comment W7-6009 --startvm bdef968a-ef10-4a7f-91c0-7938f59d6088 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6385324 8192928 86248 /usr/lib/virtualbox/VBoxHeadless --comment W7-6012 --startvm 8322842f-2a8f-45b0-b739-671691ada611 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
6690988 8454508 47040 /usr/lib/virtualbox/VBoxHeadless --comment W7-6007 --startvm 8c1d5eca-3b93-448a-abba-875dc321f3a2 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7269696 9028588 46972 /usr/lib/virtualbox/VBoxHeadless --comment W7-6005 --startvm 0db152e4-681d-4617-a07d-2d76571fbbb0 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7510940 9319584 47005 /usr/lib/virtualbox/VBoxHeadless --comment W7-6006 --startvm e4f2507c-172c-4314-a44a-43e3ef66fcfb --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
7877836 9697440 47073 /usr/lib/virtualbox/VBoxHeadless --comment W7-6008 --startvm f2033ed9-aa7b-4b38-ac3f-f21e577a5985 --vrde config PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/bin:/sbin:/usr/sbin LANG=en_US.UTF-8 PWD=/ HOME=/home/jordi VBOX_XPCOM_HOME=/usr/lib/virtualbox
17632060 65790608 137687 /usr/sbin/mysqld LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin HOME=/nonexistent LOGNAME=mysql USER=mysql SHELL=/bin/false

# cat /proc/meminfo
MemTotal:       65852816 kB
MemFree:          282728 kB
MemAvailable:    7572992 kB
Buffers:          339380 kB
Cached:          6791312 kB
SwapCached:        51144 kB
Active:         18524112 kB
Inactive:        7280780 kB
Active(anon):   15971052 kB
Inactive(anon):  2739776 kB
Active(file):    2553060 kB
Inactive(file):  4541004 kB
Unevictable:       13632 kB
Mlocked:           13632 kB
SwapTotal:      67009532 kB
SwapFree:       24527708 kB
Dirty:              1076 kB
Writeback:             0 kB
AnonPages:      18640248 kB
Mapped:         37892856 kB
Shmem:             32504 kB
Slab:            1245464 kB
SReclaimable:     693236 kB
SUnreclaim:       552228 kB
KernelStack:       25712 kB
PageTables:       356720 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    99935940 kB
Committed_AS:   93746836 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:     18432 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:     1171636 kB
DirectMap2M:    54304768 kB
DirectMap1G:    13631488 kB

编辑2:

更多信息请点击此处https://pastebin.com/qWcmmNtU

答案1

当它即将崩溃时,htop 报告大约有 25Gb RES(96Gb VIRT)。然后不知何故 MySQL 自动重启,我们又回到原点。

如果 OOM-killer 正在终止它,则这将在系统日志中可见。自动重启意味着您的 init 脚本或其他 HA 系统进行了干预。您应该找出具体发生了什么。

数据库通常具有较大的共享内存和较小的进程内存,而不是相反。

启用慢查询日志并找出哪些查询未使用索引。查看使用此数据库的应用程序并确定它们当时正在做什么。数据库进程分配数十 GB 的 RAM 是有原因的。

答案2

在我们的案例中,该performance_schema设置导致了同样的问题。
在文件中将其关闭my.cnf解决了我们的问题

[mysqld]
performance_schema='0'

答案3

针对 Ubuntu 操作系统的建议

ulimit -a    report will likely reveal low number of Open Files available.

ulimit -n 48000    will enable additional file handles to the OS and MySQL

要使此更改在操作系统重启后仍然有效,请参阅此 URL

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

您的 ubuntu 详细信息可能略有不同。

针对 my.cnf [mysqld] 部分需要考虑的建议每秒速率 = RPS

open_files_limit=30000  # from ~ 1K to support your activity
query_cache_min_res_unit=512  # from 4096 to conserve QC space used
query_prealloc-size=32K  # from 8K to minimize malloc activity
query_alloc_block_size=32k  # from 8K to reduce VmallocTotal count of meminfo
table_open_cache=10000  # from 416 to reduce tables_opened RPS
table_definition_cache=3000  # ? to reduce opened_table_definitions count
key_buffer_size=4G  # from 1G to support 5.3G of MyISAM indexes per mysqltuner.pl
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS
thread_cache_size=100  # from 24 to support 171K connections.  CAP is 100 per V5.7 refman

答案4

根据鸭子类型这是一种内存泄漏的方法。由于您使用的是 5.7,因此您可以使用其内置工具来找出责任方:“… 好消息是:从 MySQL 5.7 开始,我们在 performance_schema 中进行了内存分配。我们可以这样使用它...”

相关内容