MySQL的最大内存使用量高得危险，CPU运行100%

Question

嗨，我们在服务器上遇到了一个问题，这个问题持续了1个月，而且每天都很糟糕。我真的不是服务器管理员，

这是mysqltuner.pl结果，

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 16.9G (Tables: 4633)
[--] Data in MyISAM tables: 13.3M (Tables: 150)
[--] Data in Aria tables: 4.1M (Tables: 1)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

[--] Up for: 14m 59s (9M q [10K qps], 7K conn, TX: 5G, RX: 2G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory     : 125.8G
[--] Max MySQL memory    : 367.5G
[--] Other process memory: 0B
[--] Total buffers: 52.4G global + 322.6M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 75.8G (60.21% of installed RAM)
[!!] Maximum possible memory usage: 367.5G (292.12% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (22/9M)
[OK] Highest usage of available connections: 7% (74/1000)
[OK] Aborted connections: 0.00% (0/7652)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 48.7% (8M cached / 16M selects)
[!!] Query cache prunes per day: 27571979
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 135K sorts)
[!!] Joins performed without indexes: 5959
[OK] Temporary tables created on disk: 16% (27K on disk / 164K total)
[OK] Thread cache hit rate: 99% (74 created / 7K connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (7000) is greater than number of tables (4945)
[OK] Open file limit used: 1% (364/32K)
[OK] Table locks acquired immediately: 100% (884 immediate / 884 locks)
[OK] Binlog cache memory access: 99.45% (5746 Memory / 5778 Total)-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/5.9M
[!!] Read Key buffer hit rate: 91.5% (317 cached / 27 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 50.0G / 16.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 7.0G * 2/50.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 50
[--] Number of InnoDB Buffer Pool Chunk: 400 for 50 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.87% (693596513 hits / 694467712 total)
[OK] InnoDB Write Log efficiency: 99.80% (6253500 hits / 6265916 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12416 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/40.0K
[OK] Aria pagecache hit rate: 95.4% (151K cached / 6K reads)
 
-------- 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 4 server(s).
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] No replication setup for this server or replication not started.
Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    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).
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 64M)
    join_buffer_size (> 256.0M, or always use indexes with JOINs)
    performance_schema=ON
    key_buffer_size (~ 24M)

我看到最大可能的内存使用量: 367.5G (已安装内存的292.12%)非常高，但是找不到如何减少它，而且如果您能够帮助我更好地配置my.cnf以获得更好的性能，我将非常出色:)

编辑添加.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
skip-name-resolve
sql_mode        = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

#
# * Fine Tuning
#
open_files_limit               = 20240
sort_buffer_size               = 2097144
table_cache            = 7000
table_open_cache               = 7000
table_definition_cache         = 7000
tmp_table_size                 = 128M
max_heap_table_size            = 128M
max_connections                = 1000
connect_timeout                = 10
wait_timeout                   = 120
interactive_timeout            = 1800
max_allowed_packet             = 64M
bulk_insert_buffer_size        = 16M
thread_stack                   = 256K
thread_cache_size              = 128
thread_pool_size               = 24
log_warnings                   = 1

# MyISAM
key_buffer_size                = 128M
join_buffer_size               = 256M
myisam_recover_options         = BACKUP
myisam_repair_threads          = 1
myisam_sort_buffer_size        = 512M
concurrent_insert              = 2
read_buffer_size               = 131072
read_rnd_buffer_size           = 262144

#
# * Query Cache Configuration
#
query_cache_type    = 1
query_cache_limit      = 32M
query_cache_size        = 64M

#
# * 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            = 0
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
slow_query_log         = on
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 4
#log_slow_rate_limit    = 1000
#log_slow_verbosity     = query_plan
#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
auto_increment_increment       = 1
auto_increment_offset          = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 2
max_binlog_size        = 100M
binlog_format                  = ROW
sync_binlog                    = 0
#binlog_do_db           = include_database_name
#binlog_ignore_db       = exclude_database_name

#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * 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!

## InnoDB tuning

innodb_file_per_table          = on
innodb_buffer_pool_size        = 50G
innodb_buffer_pool_instances   = 50
innodb_log_file_size           = 7G
innodb_log_buffer_size         = 2G
innodb_thread_concurrency      = 16
innodb_read_io_threads         = 16
innodb_write_io_threads        = 16
innodb_open_files              = 1000
innodb_io_capacity             = 800
innodb_io_capacity_max         = 2000
innodb_lock_wait_timeout       = 60
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = true
innodb_use_native_aio          = 1
innodb_flush_log_at_trx_commit = 2

default_storage_engine         = InnoDB

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]

添加Top命令，但不知道如何修复:)

有了最高统帅我有了这个

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND   
 4162 mysql     20   0   64,4g  17,4g  10608 S 102,6  13,9 941:18.01 mysqld    
 9985 www-fou+  20   0  395960 221516 126856 R 100,0   0,2  14:55.02 php-fpm7.1
 1593 www-fou+  20   0  381424 203876 123500 R  99,7   0,2  11:24.43 php-fpm7.1
19099 www-fou+  20   0  381788 204924 124484 S  93,4   0,2  10:35.16 php-fpm7.1
24495 www-fou+  20   0  383684 212224 129636 R  69,1   0,2  11:37.65 php-fpm7.1
15019 www-fou+  20   0  339196 127520  89412 S  66,4   0,1   2:51.54 php-fpm7.1
11226 www-fou+  20   0  361036 181036 121012 S  65,8   0,1   8:38.26 php-fpm7.1
13478 www-fou+  20   0  333628 157600 125252 S  12,2   0,1  12:15.50 php-fpm7.1
26195 www-fou+  20   0  343160 165036 123072 S   6,2   0,1  11:20.56 php-fpm7.1
22301 www-fou+  20   0  340908 148244 108692 S   3,0   0,1  12:58.10 php-fpm7.1
21292 www-data  20   0 2316000  23908   4088 S   1,6   0,0   0:30.03 apache2   
29112 www-fou+  20   0  330464 156296 126860 S   1,6   0,1  10:24.75 php-fpm7.1
 3229 root      20   0   12680   5084   2944 R   1,3   0,0   0:02.16 top       
18330 www-data  20   0 2249248  28736   3908 S   1,3   0,0   4:00.44 apache2   
31957 www-fou+  20   0  326216 150860 125900 S   1,3   0,1  12:48.35 php-fpm7.1
  892 www-fou+  20   0 1044388 195248  15528 S   1,0   0,1 766:53.93 node      
14357 www-fou+  20   0  331084 121516  91724 S   1,0   0,1   2:17.92 php-fpm7.1

谢谢,

马蒂厄

Answer 1

我本身没有答案，但除了MySQL配置之外，还有什么值得看的地方)：您看过系统进程，比如使用"top“命令来查看wich进程是否占到了cpu使用率的100%？它可能或者不可能是MySQL进程本身。可能会有一个循环应用程序进程连接到MySQL服务器，或者您的服务器有一个坏的HD，并且系统很难存储数据。

Answer 2

“最大可能内存”的计算基本上是不可能的。该工具中的公式是悲观的，应该被忽视。

一些小贴士：

max_connections可能不需要是1000。请参阅SHOW GLOBAL STATUS LIKE 'Max_used_connections';，查看自上次重新启动以来获得的接近1000的数据。

在128 of内存中，innodb_buffer_pool_size是50G。这个设置很低，除非您在同一台服务器上有一些需要内存的应用程序。它通常是安全的设置它占用了70%的可用内存。

不要使用MyISAM；将表切换到InnoDB。然后将key_buffer_size降至20米。

除非您演示了对“查询缓存”的使用，否则请关闭它。“每天查询缓存修剪: 27571979”浪费了大量的CPU。

我没有看到任何其他让我担心的设置。