帮助您了解MySQL的最大内存使用量，这是非常危险的。

Question

有人能帮我检查这个MySQL配置吗？我有一个VPS 32 VPS-8 vcpu和运行1电子商务。

MySQLTuner返回我：

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 140.0M, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 407)

my.cnf设置如下：

key_buffer_size = 256M
join_buffer_size = 140M
tmp_table_size   = 80M
max_heap_table_size = 80M
thread_pool_size = 24
innodb_buffer_pool_instances = 6
innodb_buffer_pool_size = 6G
innodb_log_file_size = 768M
table_open_cache = 4000
skip_name_resolve = 1

max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections         = 200
#table_cache            = 1024
#thread_concurrency     = 40

tmp-table-size          = 32M
max-heap-table-size     = 32M

query_cache_limit       = 4M
query_cache_size        = 0
query_cache_type        = 0

附加信息

-------- Storage Engine Statistics -----------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 76.4M (Tables: 102)
[--] Data in MyISAM tables: 1.3G (Tables: 229)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 19h 15m 53s (19M q [124.090 qps], 69K conn, TX: 21G, RX: 6G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.5G
[--] Max MySQL memory    : 33.8G
[--] Other process memory: 650.8M
[--] Total buffers: 6.3G global + 140.8M per thread (200 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.5G (30.04% of installed RAM)
[!!] Maximum possible memory usage: 33.8G (107.34% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (114/19M)
[OK] Highest usage of available connections: 11% (23/200)
[OK] Aborted connections: 0.01%  (5/69987)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 604
[!!] Temporary tables created on disk: 31% (796K on disk / 2M total)
[OK] Thread cache hit rate: 99% (29 created / 69K connections)
[!!] Table cache hit rate: 0% (400 open / 5M opened)
[OK] Open file limit used: 42% (440/1K)
[OK] Table locks acquired immediately: 99% (33M immediate / 33M locks)



-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.4% (52M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/66.2M
[OK] Read Key buffer hit rate: 98.8% (991M cached / 12M reads)
[!!] Write Key buffer hit rate: 51.6% (511K cached / 263K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 6.0G/76.4M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 768.0M * 2/6.0G should be equal 25%
[OK] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 48 for 6 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% (518324644 hits/ 518327944 total)
[!!] InnoDB Write Log efficiency: 38.31% (36637 hits/ 95624 total)
[OK] InnoDB log waits: 0.00% (0 waits / 58987 writes)

完整的MySQLTuner报告：

https://pastebin.com/53r7cuXv

SHOW GLOBAL STATUS;

https://pastebin.com/ZKptibfK

SHOW GLOBAL VARIABLES;

https://pastebin.com/FBazhWT2

htop

root@xxxxxxxxxxx ~ # 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) 128903
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
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) 128903
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

我是否应该将所有MySQL表更改为InnoDB？

Answer 1

每个Second=RPS建议的费率，供您的my.cnf 米舍尔德部分参考

join_buffer_size=256K  # from 140M for row pointers
thread_cache_size=40  # from 8 to avoid thread starvation
query_cache_limit=0  # from 4M since you have QC turned OFF
innodb_lru_scan_depth=100  # from 1024 to reduce CPU busy every SECOND
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50  # from 100 percent for HOT/WARM caches
key_cache_block_size=16K  # from 1K to evict bigger block when full
open_files_limit=30000  # from 1024 to reduce opened_files RPS
table_open_cache=10000  # from 407 to reduce opened_tables RPS
table_definition_cache=2000  # from 603 to reduce opened_table_definitions RPS

Answer 2

对于其他人(而不是这个OP，因为他使用的是MyISAM表)，在MySQL 5.7中，假设您主要使用的是INNODB表，您可以通过运行这个查询查看当前正在使用多少内存：

SHOW ENGINE INNODB STATUS

仅通过运行此查询，您可以看到实例缓冲所需的最大内存量：

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_allowed_packet
+ @@max_connections * ( 
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@net_buffer_length
    + @@net_buffer_length
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

• net_buffer_length两次用于“连接缓冲区”和“结果缓冲区”

这是您的服务器可用的最大内存量吗？特别是在AWS Aurora中，这将导致您的DB定期重新启动。

Answer 3

别把join_buffer_size，tmp_table_size，max_heap_table_size设得这么高。假设您曾经访问过200个max_connections，最坏情况下的查询可以使用每个连接(join_buffer_size + max(tmp_table_size，max_heap_size) +其他分配)。这非常接近你的记忆极限。

这些缓冲区的大值并不总是有用的。

根据“存储引擎统计”，看起来您的VPS太大了。