mysql - MariaDB 每天因内存问题崩溃一次——分配更多内存并不能解决问题

Question

过去一周的每一天，我们的 MariaDB 都在崩溃(通常是深夜)。它在日志中崩溃了:

2018-10-20  6:44:35 140525276776704 [Note] InnoDB: Initializing buffer 
pool, size = 184.0M
InnoDB: mmap(202080256 bytes) failed; errno 12
2018-10-20  6:44:35 140525276776704 [ERROR] InnoDB: Cannot allocate 
memory for the buffer pool
2018-10-20  6:44:35 140525276776704 [ERROR] Plugin 'InnoDB' init function returned error.
2018-10-20  6:44:35 140525276776704 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-10-20  6:44:35 140525276776704 [Note] Plugin 'FEEDBACK' is disabled.
2018-10-20  6:44:35 140525276776704 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-10-20  6:44:35 140525276776704 [ERROR] Aborting

或者有时这样:

2018-10-17  9:05:54 140453757659392 [ERROR] mysqld: Out of memory (Needed 128663552 bytes)

在重新启动后的 30 分钟左右(它工作了大约 24 小时)，我总是在日志中看到这个:

2018-10-20  8:03:55 139957660669696 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 0 failed attempts to flush a page!
2018-10-20  8:03:55 139957660669696 [Note] InnoDB: Consider increasing the buffer pool size.

我们在 my.cnf.d/server.cnf 中多次增加了这个 innodb_buffer_pool_size 几次，但它没有解决问题。这是其中的 [mysqld] 部分:

[mysqld]
basedir = /usr
bind-address = 127.0.0.1
datadir = /var/lib/mysql
expire_logs_days = 10
key_buffer_size = 16M
log-error = /var/log/mariadb/mariadb.log
max_allowed_packet = 16M
max_binlog_size = 100M
max_connections = 151
pid-file = /var/run/mariadb/mariadb.pid
port = 3306
query_cache_limit = 1M
query_cache_size = 16M
skip-external-locking
socket = /var/lib/mysql/mysql.sock
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql
innodb_buffer_pool_size=184M
innodb_log_buffer_size=128M
innodb_buffer_pool_instances=1

我不是 DBA，我们是一家小型企业，非常感谢您的帮助!

mysqltuner.pl 输出:

[OK] Currently running supported MySQL version 10.1.31-MariaDB
[OK] Operating on 64-bit architecture

Performance Metrics
[--] Up for: 4h 35m 15s (369K q [22.368 qps], 87 conn, TX: 2G, RX: 226M)
[--] Reads / Writes: 67% / 33%
[--] Binary logging is disabled
[--] Physical Memory     : 9.6G
[--] Max MySQL memory    : 930.1M
[--] Other process memory: 4.5G
[--] Total buffers: 496.0M global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 703.0M (7.16% of installed RAM)
[OK] Maximum possible memory usage: 930.1M (9.47% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/369K)
[OK] Highest usage of available connections: 47% (72/151)
[OK] Aborted connections: 0.00%  (0/87)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 22.4% (45K cached / 201K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
[!!] Joins performed without indexes: 2742
[OK] Temporary tables created on disk: 0% (0 on disk / 2K total)
[!!] Thread cache hit rate: 17% (72 created / 87 connections)
[OK] Table cache hit rate: 91% (62 open / 68 opened)
[OK] Open file limit used: 0% (25/16K)
[OK] Table locks acquired immediately: 100% (216K immediate / 216K locks)

InnoDB Metrics
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 184.0M/95.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (52.1739130434783 %): 48.0M * 2/184.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.96% (196286081 hits/ 196367903 total)
[OK] InnoDB Write log efficiency: 98.79% (5736869 hits/ 5806871 total)
[OK] InnoDB log waits: 0.00% (0 waits / 70002 writes)

General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Control error line(s) into /var/log/mariadb/mariadb.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    thread_cache_size (> 8)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=23M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Answer 1

感谢所有看过的人。我希望找到与 MDB 配置或其他相关的答案，但 Occam 的 Razor :错误是与另一个团队成员的未知升级相关的流氓进程。它正在吞噬所有内存。

如果您遇到此问题或这些错误消息:请考虑您的 MDB 没有任何问题，可能只是本地化的服务器/虚拟机问题。