MySQL server (mariadb) auto shutdown without overload

innodbmariadbMySQLshutdown

With old configs, my VPS (1 CPU, 1 GB RAM, 1 GB swap) works normally for almost 2 years. I didn't change anything from beginning.

But I have this issue several days ago, MySQL server automatically shut down, while watched via DO Graph monitoring, I realized seems it shut down every time CPU overloaded (>100%).

Then I tried making:

  • some MySQL server optimizations in etc/my.cnf; convert to innodb, accept localhost only, cache configs, etc

  • turn off all sites in server, leave 1 WordPress site only, this WordPress site used innodb by default

After restarting Mariadb server, seem everything is okay, I tested for a while, it works normally. But then when I'm offline about 1 hour, now it shuts down again but all loads are normal, seems this issue does not involve to overload?

Please help me for this, anyone face this problem?

And may tell me what is process crypto with top command?

Here's full log from

180918 10:02:50 mysqld_safe Number of processes running now: 0
180918 10:02:50 mysqld_safe mysqld restarted
180918 10:02:50 InnoDB: The InnoDB memory heap is disabled
180918 10:02:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180918 10:02:50 InnoDB: Compressed tables use zlib 1.2.7
180918 10:02:50 InnoDB: Using Linux native AIO
180918 10:02:50 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 32632 ...
180918 10:02:50 InnoDB: Initializing buffer pool, size = 800.0M
InnoDB: mmap(860979200 bytes) failed; errno 12
180918 10:02:50 InnoDB: Completed initialization of buffer pool
180918 10:02:50 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180918 10:02:50 [ERROR] Plugin 'InnoDB' init function returned error.
180918 10:02:50 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180918 10:02:50 [Note] Plugin 'FEEDBACK' is disabled.
180918 10:02:50 [ERROR] Unknown/unsupported storage engine: InnoDB
180918 10:02:50 [ERROR] Aborting

180918 10:02:50 [Note] /usr/libexec/mysqld: Shutdown complete

180918 10:02:50 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180918 10:10:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180918 10:10:07 InnoDB: The InnoDB memory heap is disabled
180918 10:10:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180918 10:10:07 InnoDB: Compressed tables use zlib 1.2.7
180918 10:10:07 InnoDB: Using Linux native AIO
180918 10:10:07 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 1625 ...
180918 10:10:07 InnoDB: Initializing buffer pool, size = 800.0M
180918 10:10:08 InnoDB: Completed initialization of buffer pool
180918 10:10:08 InnoDB: highest supported file format is Barracuda.
180918 10:10:08  InnoDB: Starting crash recovery from checkpoint LSN=698432796
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
180918 10:10:08  InnoDB: Starting final batch to recover 2 pages from redo log
180918 10:10:09  InnoDB: Waiting for the background threads to start
180918 10:10:10 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence nu$
180918 10:10:10 [Note] Plugin 'FEEDBACK' is disabled.
180918 10:10:10 [Note] Server socket created on IP: '127.0.0.1'.
180918 10:10:10 [Warning] 'proxies_priv' entry '@ root@sentora.mydomain.com' ignored in --skip-name$
180918 10:10:10 [Note] Event Scheduler: Loaded 0 events
180918 10:10:10 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
180918 11:19:10 mysqld_safe Number of processes running now: 0
180918 11:19:10 mysqld_safe mysqld restarted
180918 11:19:10 InnoDB: The InnoDB memory heap is disabled
180918 11:19:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180918 11:19:10 InnoDB: Compressed tables use zlib 1.2.7
180918 11:19:10 InnoDB: Using Linux native AIO
180918 11:19:10 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 3017 ...
180918 11:19:10 InnoDB: Initializing buffer pool, size = 800.0M
InnoDB: mmap(860979200 bytes) failed; errno 12
180918 11:19:10 InnoDB: Completed initialization of buffer pool

And here are optimizations I made in /etc/my.cnf

[mysqld]
# custom
bind-address = 127.0.0.1

skip-name-resolve
skip-external-locking

key_buffer_size = 32K
max_allowed_packet = 4M
table_open_cache = 8
sort_buffer_size = 128K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
thread_stack = 480K
thread_cache_size = 8

innodb_file_per_table=1
max_connections = 100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5

query_cache_type = 1
query_cache_limit=1M
query_cache_min_res_unit = 2k
query_cache_size=32M ## 32MB for every 1GB of RAM

tmp_table_size= 64M
max_heap_table_size= 64M

log_error = /var/log/mariadb/error.log
expire_logs_days    = 5
max_binlog_size   = 100M
innodb_buffer_pool_size=800M
innodb_buffer_pool_instances = 3

And here's top command

Best Answer

If you read the log, there is a small bit of text that says 'Fatal error'. This indicates a fatal error. The few lines before this indicate why.

Tune innodb_buffer_pool_size to match your available memory.

You can also save RAM and improve performance by disabling the query cache.

crypto, and all the processes in top that consume 0 memory, are linux kernel threads.

Related Topic