MySQL每隔3-7天就会耗尽内存。

Question

我们使用的是MySQL 5.7，并且有一个更大的设置，每个客户都有自己的数据库和表。所有具有相同表设置的数据库，只是为了自己包含它们。

我们的服务器目前有32 on内存，我们遇到的问题是，根据活动的不同，MySQL在3到7天内占用所有内存。

我们知道我们的系统也需要优化，我们正在进行优化，但这是一个缓慢的过程，因为我们无法确定哪些查询是最糟糕的。

附上了一些不同的信息和今天突然增加的记忆的图片。

网络数据存储图

MySQLTuner

>>  MySQLTuner 1.8.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.36-0ubuntu0.18.04.1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 32.4G (Tables: 45628)
[--] Data in MyISAM tables: 37.8M (Tables: 22)
[!!] Total fragmented tables: 7
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 13h 57m 55s (60M q [125.132 qps], 3M conn, TX: 264G, RX: 24G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 22.6G
[--] Other process memory: 0B
[--] Total buffers: 20.1G global + 17.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 20.8G (66.35% of installed RAM)
[OK] Maximum possible memory usage: 22.6G (72.02% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (400/60M)
[OK] Highest usage of available connections: 29% (44/151)
[OK] Aborted connections: 0.00%  (1/3159508)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[!!] Sorts requiring temporary tables: 38% (2M temp sorts / 7M sorts)
[!!] Joins performed without indexes: 27618
[!!] Temporary tables created on disk: 52% (1M on disk / 3M total)
[OK] Thread cache hit rate: 99% (12K created / 3M connections)
[OK] Table cache hit rate: 28% (38M hits / 137M requests)
[!!] table_definition_cache(36000) is lower than number of tables(45930) 
[OK] Open file limit used: 0% (10/5K)
[OK] Table locks acquired immediately: 100% (618K immediate / 618K locks)
[OK] Binlog cache memory access: 99.45% (1719946 Memory / 1729414 Total)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.0M
[!!] Read Key buffer hit rate: 85.1% (877K cached / 130K reads)
[OK] Write Key buffer hit rate: 100.0% (927 cached / 927 writes)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 20.0G/32.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/20.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 20
[--] Number of InnoDB Buffer Pool Chunk : 160 for 20 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: 100.00% (25437207254 hits/ 25437676712 total)
[!!] InnoDB Write Log efficiency: 56.94% (2658589 hits/ 4669027 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2010438 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 1 server(s).
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] No replication setup for this server or replication not started.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control error line(s) into /var/log/mysql/error.log file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `data_al6`.`files`; -- can free 141 MB
      OPTIMIZE TABLE `data_bo3`.`files`; -- can free 102 MB
      OPTIMIZE TABLE `data_ha3`.`files`; -- can free 19 MB
      OPTIMIZE TABLE `data_ju1`.`files`; -- can free 88 MB
      OPTIMIZE TABLE `data_kc1`.`files`; -- can free 19 MB
      OPTIMIZE TABLE `data_re41`.`files`; -- can free 33 MB
      OPTIMIZE TABLE `data_so6`.`files`; -- can free 39 MB
    Total freed space after theses OPTIMIZE TABLE : 441 Mb
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_definition_cache(36000) > 45930 or -1 (autosizing if supported)
    innodb_buffer_pool_size (>= 32.4G) if possible.

my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

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

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[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
innodb_buffer_pool_size=20G
table_definition_cache=36000
innodb_buffer_pool_instances=20
skip-external-locking
#
# * Default charset
#
character-set-server    = utf8

#
# * Fine Tuning
#
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam_recover_options  = BACKUP
#max_connections        = 100
table_open_cache        = 128

#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 64M
#
# * Logging and Replication
#
log_error = /var/log/mysql/error.log
#
#################################################################
# Slow query log:
slow_query_log_file      = /var/log/mysql/mysql-slow.log
long_query_time          = 1
slow_query_log           = 1
#################################################################
#
# 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
binlog-format                   = mixed
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
innodb_file_per_table
log_bin                         = /var/log/mysql/mysql-bin.log
expire_logs_days                = 2
max_binlog_size                 = 512M
log_bin_trust_function_creators = 1

# innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size used
# min. innodb_log_file_size = (10 * MEDIUM_BLOB) / innodb_log_files_in_group = (10 * 16 MB) / 2 = 80 MB;
innodb_log_file_size            = 2G

#
# * InnoDB
#

# Make sure strict mode is disabled:
sql_mode                        =IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# Make sure timestamp defaults are explicitly declared:
explicit_defaults_for_timestamp

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size         = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Answer 1

只是OOM的主要原因之一。

com_savepoint counted 3,882 during your 1.1 days of SHOW GLOBAL STATUS

com_release_savepoint counted 0 releases.

当保存点不再需要时，每次发布保存点的失败都等于最终OOM未发布的资源。请查找教程并仔细查看。

还有更多的配置需求。在你周一早上的另一个回答中，我会给你一些建议来解决你的桌子被打开的重击，这正在扼杀你的表现。请查看联系人信息的配置文件。

Answer 2

每秒速率= RPS

建议考虑减少table_open打击的情况。

操作系统根据您的-a报告有开放文件限制为1024从您的操作系统命令提示符，ulimit -n 500000和按回车将允许动态限制开放文件支持MySQL和其他应用程序时需要。仅在MySQL中，您的系统就在不到一周的时间内将大约1,500个表添加到了今天新的MySQL表/文件计数47,148个表中。

若要在OS停止/启动过程中使其持久，请遵循此URL以获得指南。

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

您的具体情况可能略有不同。请按本指南所做的那样申请50万英镑。试着在接下来的几天内在受控的环境中保持这种持久性。如果没有，只需使用ulimit -n 500000动态启用较高的限制，然后在不受控制的OOM崩溃后启动MySQL。当有疑问时使用ulimit -a来确定当前的打开文件限制。

关于考虑my.cnf的建议

table_open_cache=120000  # from 128 to reduce opened_tables RPS of 218
innodb_open_files=120000  # from 300 - this should always be = table_open_cache
table_definition_cache=75000  # from 36000 for more than table_count 
        to reduce opened_table_definitions RPS of 148
open_files_limit=256000  # from 5000 - to reduce opened_files RPS of 226 

这些更改包括静态全局变量，需要MySQL启动/停止。

查看联系人信息的配置文件，如果需要的话。您的应用程序还有更多的性能调优机会。我们愿意提供协助。