Mysql – Database often becomes unavailable and Mysql has error log

error logMySQL

The service is a droplet in DO with Ubuntu 16.04 and apache2
MySql database often becomes unavailable in this server.
here is the log from /etc/log/mysql/error.log

2018-08-19T18:39:41.534354Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-08-19T18:39:41.534424Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2018-08-19T18:39:41.711277Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-19T18:39:41.713157Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1) starting as process 22330 ...
2018-08-19T18:39:41.718519Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-08-19T18:39:41.718551Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-08-19T18:39:41.718556Z 0 [Note] InnoDB: Uses event mutexes
2018-08-19T18:39:41.718560Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-08-19T18:39:41.718566Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-08-19T18:39:41.718581Z 0 [Note] InnoDB: Using Linux native AIO
2018-08-19T18:39:41.718907Z 0 [Note] InnoDB: Number of pools: 1
2018-08-19T18:39:41.719038Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-08-19T18:39:41.721028Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-08-19T18:39:41.721078Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2018-08-19T18:39:41.721087Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-08-19T18:39:41.721092Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-08-19T18:39:41.721098Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-08-19T18:39:41.721102Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-08-19T18:39:41.721107Z 0 [ERROR] Failed to initialize builtin plugins.
2018-08-19T18:39:41.721110Z 0 [ERROR] Aborting

2018-08-19T18:39:41.721120Z 0 [Note] Binlog end
2018-08-19T18:39:41.721170Z 0 [Note] Shutting down plugin 'MyISAM'
2018-08-19T18:39:41.721193Z 0 [Note] Shutting down plugin 'CSV'
2018-08-19T18:39:41.721567Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Need a fix, tried with lots of solutions on several forums but couldn't get a proper shot.

root@np:/# free -m
              total        used        free      shared  buff/cache   available
Mem:            992         353         260          74         378         394
Swap:             0           0           0


root@np:/# cat /etc/mysql/my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
#innodb_buffer_pool_size = 20M

root@np:~# cat /etc/mysql/conf.d/mysql.cnf
[mysql]

root@np:~# cat /etc/mysql/conf.d/mysqldump.cnf
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

root@np:~# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 3895
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 3895
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

root@np:~# df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            487M     0  487M   0% /dev
tmpfs           100M   11M   89M  11% /run
/dev/vda1        25G  4.6G   20G  19% /
tmpfs           497M     0  497M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           497M     0  497M   0% /sys/fs/cgroup
/dev/vda15      105M  3.4M  102M   4% /boot/efi
tmpfs           100M     0  100M   0% /run/user/0

root@np:~#htop

htop

mysql>SHOW GLOBAL STATUS

https://pastebin.com/q6s3bfq1

mysql> SHOW GLOBAL VARIABLES;

https://pastebin.com/1ifB9x24

root@np:~# iostat -x
Linux 4.4.0-133-generic (npk)   08/21/2018      _x86_64_        (1 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.19    0.00    0.88    0.12    0.00   96.81

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.01    0.00     0.01     0.00     3.20     0.00    0.00    0.00    0.00   0.00   0.00
vda               0.00     3.06   17.73    2.73   305.75    62.00    35.95     0.01    0.27    0.10    1.35   0.10   0.21

Best Answer

Ran out of memory:

2018-08-19T18:39:41.721028Z 0
  [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-08-19T18:39:41.721078Z 0
  [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12

Since you have only 1GB of memory, there is an issue. (Side note: Long ago, I installed mysql on 256MB of RAM, no problem. But times have changed.)

You could try changing this in an attempt to get things stated:

innodb_buffer_pool_size = 32M
query_cache_size = 0

but the better bet is to get more RAM.

Related Question