我有一个 1GB 内存的 Amazon EC2 实例。当我收到超过 25 个活跃用户时，我的服务器就会崩溃。
我正在尝试使用 mysqltuner.pl 调整 MySQL，并得到以下结果：
>> MySQLTuner 1.7.0 - 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 10.1.20-MariaDB-1~trusty
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: (0B)
[!!] Log file doesn't exist
[!!] Log file isn't readable.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 30M (Tables: 114)
[--] Data in InnoDB tables: 28M (Tables: 109)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 14h 23m 9s (2M q [10.697 qps], 18K conn, TX: 9G, RX: 701M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 992.5M
[--] Max MySQL memory : 1.0G
[--] Other process memory: 116.1M
[--] Total buffers: 656.0M global + 7.5M per thread (50 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 761.5M (76.72% of installed RAM)
[!!] Maximum possible memory usage: 1.0G (104.04% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 28% (14/50)
[OK] Aborted connections: 0.00% (0/18161)
[!!] 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: 44.3% (1M cached / 3M selects)
[!!] Query cache prunes per day: 1794
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 29K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 21% (21K on disk / 103K total)
[OK] Thread cache hit rate: 99% (14 created / 18K connections)
[OK] Table cache hit rate: 20% (371 open / 1K opened)
[OK] Open file limit used: 33% (338/1K)
[OK] Table locks acquired immediately: 99% (485K immediate / 485K locks)
[OK] Binlog cache memory access: 99.83% (9766 Memory / 9783 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.1.20-MariaDB-1~trusty)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.0% (25M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/4.5M
[OK] Read Key buffer hit rate: 100.0% (5M cached / 938 reads)
[!!] Write Key buffer hit rate: 28.0% (20K cached / 14K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 64.0M/28.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 16.0M * 2/64.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.94% (3324294 hits/ 3326210 total)
[!!] InnoDB Write Log efficiency: 77.67% (42515 hits/ 54737 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12222 writes)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_type (=0)
query_cache_size (> 64M)
innodb_log_file_size should be equals to 1/4 of buffer pool size (=16M) if possible.
以下是 mycnf 文件配置http://www.omh.cc/mycnf/ 会话变量
max_allowed_packet 1.0 MB
sort_buffer_size 2.0 MB
net_buffer_length 16.0 KB
thread_stack 192.0 KB
read_rnd_buffer_size 256.0 KB
read_buffer_size 128.0 KB
join_buffer_size 128.0 KB
Total (per session)3.7 MB
Global variables
innodb_log_buffer_size 1.0 MB
innodb_buffer_pool_size 1.0 MB
innodb_additional_mem_pool_size 1.0 MB
key_buffer_size 128.0 KB
Total 3.1 MB
Total memory needed (for 100 connections): 373.4 MB
答案1
如何解决 mysql 崩溃问题？
找出崩溃的原因。你不知道这一点就无法解决这个问题。
查看日志，找到崩溃发生时的相关条目。了解这些条目的含义，然后采取措施纠正问题。
*** MySQL 的最大内存使用量过高，非常危险 ***
*** 增加 MySQL 缓冲区变量之前，请先添加 RAM ***
我会寻找与内存耗尽相关的错误，甚至可能是 OOM 杀手作为崩溃的原因，但 YMMV。