MySQL server (mariadb) auto shutdown without overload
09-18-2018, 08:27 PM
(This post was last modified: 09-18-2018, 09:23 PM by 7rdoq.)
Hi there
I have this issue several days, MySQL server automatically shut down, while watched via DO Graph monitoring, I realized seem 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
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, currently I'm on mobile, show can't show log, anyone face this problem?
--- updated ---
and may tell me what is process crypto with top command? thanks
here's full log from /var/log/mariadb/mariadb.log since last crash
and here are optimizations I made in /etc/my.cnf
here's top command
I have this issue several days, MySQL server automatically shut down, while watched via DO Graph monitoring, I realized seem 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
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, currently I'm on mobile, show can't show log, anyone face this problem?
--- updated ---
and may tell me what is process crypto with top command? thanks
here's full log from /var/log/mariadb/mariadb.log since last crash
PHP Code:
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
PHP Code:
[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
here's top command