1

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

14
  • Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Optional very helpful information, if available includes - htop or top or mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x 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. – Wilson Hauck Aug 19 '18 at 19:07
  • Your ulimit -a report will indicate open files of 1024, use of ulimit -n 16384 will increase the capacity and the first two lines in the error log will no longer be reported. You will need to make the ulimit -n change persistent across OS restart by reviewing the content of this url and taking similar action glassonionblog.wordpress.com/2013/01/27/… – Wilson Hauck Aug 19 '18 at 19:18
  • 2
    This question has nothing to do with programming, this is pure admin issue, therefore it is off topic here on SO. Dba or serverfault sister sites can offer you better assistance in such questions. – Shadow Aug 20 '18 at 2:55
  • Thanks to the moderator for moving this from SO Questions 51920801 to dba.stackexchange.com. – Wilson Hauck Aug 20 '18 at 8:15
  • 1
    @AnupRaj The top display indicates 30 copies of mysqld are in memory at 168M each of RAM. So, with your ~1G RAM you are trying to support 5G and you have NO swap space. Review your doc on how to kill a process in your OS and kill 29 of the PID's listed on top report. The my.cnf posted has 2 include lines requesting files to be used at startup. Please post the named files so we can see your 'configuration' details. Not sure that multiple apache2 PID's listed in top are reasonable. Probably also need to be killed by you, especially if they are more than a minute old according to time. – Wilson Hauck Aug 21 '18 at 8:14
1

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.

1
  • Tip: see errno 12 in the output, you can translate this using the mysql/mariadb perror command: perror 12 -> OS error code 12: Cannot allocate memory – danblack Aug 28 '18 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.