MySQL: Como eu resolvo o problema de travamento do mysql?

Question

Tenho uma instância do Amazon EC2 com 1 GB de memória. Meu servidor fica travando quando eu recebo mais de 25 usuários ativos.

Estou tentando ajustar o MySQL usando mysqltuner.pl e obter os seguintes resultados:

 >>  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.

Aqui está a configuração do arquivo mycnf após o link Variáveis de sessão

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

    

Answer 1

How do I address mysql crashing issue?

Descubra por que ele está falhando. Você não pode resolver este problema sem saber disso.

Procure em seus registros, encontre entradas relevantes ao redor do tempo em que o acidente acontece. Entenda o que as entradas significam e tome ações para corrigir os problemas.

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

Eu estaria procurando erros relacionados à exaustão de memória, talvez até mesmo o assassino da OOM saindo para jogar como o motivo da queda, mas YMMV.