2

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

Phong Thai
  • 21
  • 1
  • 5
  • Please add to your question, A) my.cnf as of mm/dd/ccyy B) last 400 lines of current error log. Are you still getting 'has gone away' at this time? – Wilson Hauck Nov 10 '18 at 20:15
  • Additional information request. Post on pastebin.com or here. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis to allow preparing Suggestions for your instance stability. – Wilson Hauck Dec 23 '18 at 19:51

1 Answers1

1

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.

danblack
  • 1,249
  • 13
  • 15
  • yes, thanks, this fatal error seems not always? just disabled query cache to test for a while – Phong Thai Sep 19 '18 at 00:41
  • and currently I have this error "ERROR 2006 (HY000): MySQL server has gone away", may you know reason of this issue? thanks – Phong Thai Sep 19 '18 at 00:42
  • You've told mariadb there is 2.4G available for its buffer pool. At some point it will try to allocate that. It won't always be initially however it will fail, fatally. When this happens our client will get a 'gone away' error because the server process would of crashed. You only have 1G ram so set `innodb_buffer_pool_instances=1` and `innodb_buffer_pool_size=500M`. Reduce further or buy a bigger VPS if you still have problems. – danblack Sep 19 '18 at 00:58
  • @danblack innodb_buffer_pool_instances value is NOT a multiplier of innodb_buffer_pool_size. It is a divisor to break up the pool to avoid mutex contention. Please review any version of the refman. – Wilson Hauck Oct 09 '18 at 19:03
  • @PhongThai Suggestions to consider query_cache_type=OFF, query_cache_size=0, innodb_buffer_pool_size=256M, innodb_buffer_pool_instances=1, thread_cache_size=16 and let us KNOW how your are doing in 3 working days, please. – Wilson Hauck Oct 16 '18 at 00:00
  • @PhongThai Are you running now? If any suggestion(s) helped, please upvote so we know. – Wilson Hauck Nov 07 '18 at 15:50
  • @WilsonHauck hi, it's seems network / droplet issues or something like that, not seems by OS or services issues because there's no error from my last comment – Phong Thai Nov 10 '18 at 08:12