MySQL：我如何解决mysql崩溃问题？

我有一个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. 

以下是http://www.omh.cc/mycnf/会话variables后的mycnf文件configuration

 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 

• 如何避免更新Mysql表中的所有行？
• MySql：在强大的服务器上，每三天崩溃一次
• 有没有人有一个快速的方法来“预热”从快照创build的RDS实例
• 你如何改变MySQL中日志文件的位置？
• 移动到外部驱动器后无法启动mysql