Question

Issue with MySQL Server stopping on Ubuntu

Posted January 16, 2019 3.4k views
MySQLWordPressDigitalOceanUbuntu 18.04Databases

I have two wordpress projects on a droplet having 1GB ram, and today the mysql server stopped two times, which led both sites being unnresponsive. I’ve decreased the value of innodbbufferpool_size from 134217728 to 104857600, but that didn’t help. I’ve attached the mysql logs below:

2019-01-16T06:37:29.751611Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicitdefaultsfortimestamp server option (see documentation for more details).
2019-01-16T06:37:29.754413Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.24-0ubuntu0.18.04.1) starting as process 24176 …
2019-01-16T06:37:29.763480Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-01-16T06:37:29.763510Z 0 [Note] InnoDB: Mutexes and rwlocks use GCC atomic builtins
2019-01-16T06:37:29.763515Z 0 [Note] InnoDB: Uses event mutexes
2019-01-16T06:37:29.763520Z 0 [Note] InnoDB: GCC builtin _atomicthread_fence() is used for memory barrier
2019-01-16T06:37:29.763524Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-01-16T06:37:29.763528Z 0 [Note] InnoDB: Using Linux native AIO
2019-01-16T06:37:29.764826Z 0 [Note] InnoDB: Number of pools: 1
2019-01-16T06:37:29.768004Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-01-16T06:37:29.770837Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-01-16T06:37:29.771629Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-01-16T06:37:29.771653Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-01-16T06:37:29.771659Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-01-16T06:37:29.771666Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2019-01-16T06:37:29.771671Z 0 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
2019-01-16T06:37:29.771676Z 0 [ERROR] Failed to initialize builtin plugins.
2019-01-16T06:37:29.771679Z 0 [ERROR] Aborting

2019-01-16T06:37:29.771701Z 0 [Note] Binlog end
2019-01-16T06:37:29.771767Z 0 [Note] Shutting down plugin 'CSV’
2019-01-16T06:37:29.772530Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

and

2019-01-16T12:50:44.967079Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicitdefaultsfortimestamp server option (see documentation for more details).
2019-01-16T12:50:44.969612Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.24-0ubuntu0.18.04.1) starting as process 30672 …
2019-01-16T12:50:44.978445Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-01-16T12:50:44.978482Z 0 [Note] InnoDB: Mutexes and rwlocks use GCC atomic builtins
2019-01-16T12:50:44.978488Z 0 [Note] InnoDB: Uses event mutexes
2019-01-16T12:50:44.978492Z 0 [Note] InnoDB: GCC builtin _atomicthread_fence() is used for memory barrier
2019-01-16T12:50:44.978497Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-01-16T12:50:44.978502Z 0 [Note] InnoDB: Using Linux native AIO
2019-01-16T12:50:44.979849Z 0 [Note] InnoDB: Number of pools: 1
2019-01-16T12:50:44.983044Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-01-16T12:50:44.985170Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2019-01-16T12:50:44.986343Z 0 [ERROR] InnoDB: mmap(107380736 bytes) failed; errno 12
2019-01-16T12:50:44.986364Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-01-16T12:50:44.986370Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-01-16T12:50:44.986380Z 0 [ERROR] Plugin 'InnoDB’ init function returned error.
2019-01-16T12:50:44.986387Z 0 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
2019-01-16T12:50:44.986394Z 0 [ERROR] Failed to initialize builtin plugins.
2019-01-16T12:50:44.986399Z 0 [ERROR] Aborting

2019-01-16T12:50:44.986427Z 0 [Note] Binlog end
2019-01-16T12:50:44.986486Z 0 [Note] Shutting down plugin 'CSV’
2019-01-16T12:50:44.986919Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Also I’ve noticed that with barely any traffic on the sites, the system uses a lot of memory, which is probably why the server shuts itself:

          total        used        free      shared  buff/cache   available

Mem: 985 566 111 124 307 152
Swap: 0 0 0

Do you have any ideea why this happens and how can I fix the issue with the mysql server?

Add a comment
larisablaga
By:
larisablaga
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
4 answers
Mohsen47 January 16, 2019

@larisablaga

if you calculate the innodbbufferpool_size in Gigabytes it is 100GB of memory and your droplet only has 1GB of memory, this size is very huge decrease it.

Your last output shows that you have 152MB of available memory.

It is better for you to increase the RAM size to 2GB and use two CPUs they are the same price.

Hope this helps.

Reply Report
unixynet January 16, 2019

InnoDB (mmap) isn’t able to allocate the memory you set in the pool size. So work your way up from a smaller value for innodb_buffer_pool_size like 32M or even less. But this isn’t the only variable at play. PHP also needs RAM. You need to be very conservative about memory or upgrade it.

Cheers

Reply Report
SebasianG January 16, 2019

Would be nice to post your my.cnf config file content here.

Reply Report
  • larisablaga January 17, 2019

    I have the default configuration. The only variable I played with was innodbbufferpool_size:

    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/

    /etc/mysql/conf.d/mysql.cnf:

    [mysql]

    ** /etc/mysql/mysql.conf.d/mysqld.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

    This will be passed to all mysql clients

    It has been reported that passwords should be enclosed with ticks/quotes

    escpecially if they contain “#” chars…

    Remember to edit /etc/mysql/debian.cnf when changing the socket location.

    Here is entries for some specific programs

    The following values assume you have at least 32M ram

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    innodbbufferpool_size = 100M

    * Basic Settings

    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /var/lib/mysql
    tmpdir = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking

    Instead of skip-networking the default is now to listen only on

    localhost which is more compatible and is not less secure.

    bind-address = 0.0.0.0

    * Fine Tuning

    keybuffersize = 16M
    maxallowedpacket = 16M
    threadstack = 192K
    thread    cache_size = 8

    This replaces the startup script and checks MyISAM tables if needed

    the first time they are touched

    myisam-recover-options = BACKUP

    max_connections = 100

    tableopencache = 64

    thread_concurrency = 10

    * Query Cache Configuration

    querycachelimit = 1M
    querycachesize = 16M

    * Logging and Replication

    Both location gets rotated by the cronjob.

    Be aware that this log type is a performance killer.

    As of 5.1 you can enable the log at runtime!

    generallogfile = /var/log/mysql/mysql.log

    general_log = 1

    Error log - should be very few entries.

    log_error = /var/log/mysql/error.log

    Here you can see queries with especially long duration

    slowquerylog = 1

    slowquerylog_file = /var/log/mysql/mysql-slow.log

    longquerytime = 2

    log-queries-not-using-indexes

    The following can be used as easy to replay backup logs or for replication.

    note: if you are setting up a replication slave, see README.Debian about

    other settings you may need to change.

    server-id = 1

    log_bin = /var/log/mysql/mysql-bin.log

    expirelogsdays = 10
    maxbinlogsize = 100M

    binlogdodb = includedatabasename

    binlogignoredb = includedatabasename

    * InnoDB

    InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

    Read the manual for more InnoDB related options. There are many!

    * Security Features

    Read the manual, too, if you want chroot!

    chroot = /var/lib/mysql/

    For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.

    ssl-ca=/etc/mysql/cacert.pem

    ssl-cert=/etc/mysql/server-cert.pem

    ssl-key=/etc/mysql/server-key.pem

    Reply Report
tingtongpingpong2016 January 17, 2019
Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help