Menu
gianmarcotoscano
By:
gianmarcotoscano

MySQL stopped and it can't be restart

April 25, 2017 522 views
MySQL Ubuntu 16.04

Hi there,

I have a big issue on my droplet.

Today, after pull from my git repo, I install a php extension (imagick).

After that, mysql began to start/stop frequenty, and now I am not able to restart it.

If i try:

sudo service mysql restart

It says me 

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Log of journalctl -xe says:

-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Unit entered failed state.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished shutting down.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has begun starting up.
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.796:1567): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.812:1568): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.812:1569): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:39 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

And finally, /var/log/mysql/error.log says:

2017-04-25T10:45:43.868541Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-04-25T10:45:43.868576Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-04-25T10:45:43.868596Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-04-25T10:45:43.868615Z 0 [ERROR] Failed to initialize plugins.
2017-04-25T10:45:43.868629Z 0 [ERROR] Aborting

2017-04-25T10:45:43.868673Z 0 [Note] Binlog end
2017-04-25T10:45:43.868837Z 0 [Note] Shutting down plugin 'CSV'
2017-04-25T10:45:43.869563Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Any help would be appreciated, thank you in advance.

Log In to Comment
4 Answers
hansen April 25, 2017

Hi @gianmarcotoscano

Can you run and post tail -30 /var/log/mysql/error.log?
Just need to get a few more lines of the crash. You need to run it after MySQL stopped.

I suspect you're running out of RAM, but the log will tell us.

Reply
gianmarcotoscano April 25, 2017

Hi,

here you have what you asked me:

2017-04-25T11:05:06.131450Z 0 [Note] Binlog end
2017-04-25T11:05:06.131597Z 0 [Note] Shutting down plugin 'CSV'
2017-04-25T11:05:06.132363Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2017-04-25T11:05:36.361208Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-25T11:05:36.361401Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-04-25T11:05:36.688896Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-25T11:05:36.694729Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.17-0ubuntu0.16.04.2) starting as process 7624 ...
2017-04-25T11:05:36.708734Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-04-25T11:05:36.708823Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-25T11:05:36.708835Z 0 [Note] InnoDB: Uses event mutexes
2017-04-25T11:05:36.708845Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-04-25T11:05:36.708855Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-04-25T11:05:36.708865Z 0 [Note] InnoDB: Using Linux native AIO
2017-04-25T11:05:36.709647Z 0 [Note] InnoDB: Number of pools: 1
2017-04-25T11:05:36.710008Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-04-25T11:05:36.714526Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-04-25T11:05:36.714649Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2017-04-25T11:05:36.714679Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2017-04-25T11:05:36.714707Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-04-25T11:05:36.714729Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-04-25T11:05:36.714741Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-04-25T11:05:36.714767Z 0 [ERROR] Failed to initialize plugins.
2017-04-25T11:05:36.714775Z 0 [ERROR] Aborting

2017-04-25T11:05:36.714803Z 0 [Note] Binlog end
2017-04-25T11:05:36.714950Z 0 [Note] Shutting down plugin 'CSV'
2017-04-25T11:05:36.715472Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

I also checked RAM memory usage with htop, I have 103 Mb free now.

Thank you in advance

Reply
  • hansen April 25, 2017

    @gianmarcotoscano
    As expected, it was forced-closed because it was running out of RAM. The giveaway is this line [ERROR] InnoDB: Cannot allocate memory for the buffer pool

    But if you are on a 512MB droplet running both git and a full WordPress, then it is expected to run out of RAM. It's actually quite difficult to run WordPress on 512MB.

    I would generally say that WordPress needs 1GB RAM minimum in production. You might be able to run on 512MB, but if just a couple of people visit your site at the same time and WordPress is checking for updates, then you can easily run out of RAM.

jtittle1 April 25, 2017

@gianmarcotoscano

In addition to this error:

[ERROR] InnoDB: Cannot allocate memory for the buffer pool

... this error also indicates that you're running out of RAM:

InnoDB: mmap(137428992 bytes) failed; errno 12

You may have 103MB of RAM free with the MySQL service stopped, though as soon as you run:

service mysql restart

... MySQL is attempting to utilize more than what's available and since it's unable to do so, it fails.

For database-driven websites, I recommend at least 1GB of RAM minimum. You can run with 512MB, though it requires a lot of tweaking and tuning, and to do it, I'd recommend NGINX over Apache.

That said, upgrading is a the best route to take since MySQL, and even MariaDB and Percona, will need more than 103MB of RAM.

Reply
gianmarcotoscano April 26, 2017

@hansen @jtittle
Ok guys, I resized my droplet to 1Gb and now everything seems to be ok (and website also seems to go faster).

I also activated an alert if RAM consume is greater than 70% for above 10 minutes.

Do you suggest me some other tool to install or run some optimisation to avoid RAM usage problems?

Is 1Gb droplet suitable for a wordpress site with average traffic of maybe is required the 2Gb solution?

Thanks again for your help guys, I really appreciated it :)

Reply
  • hansen April 26, 2017

    @gianmarcotoscano
    It is difficult to say exactly, since it depends on what you mean by average traffic and how many plugins you have, how big your site is and which theme you are using.
    If it's running okay on 1GB, then stay there - if you hit RAM limit again, then consider upgrading again.

  • jtittle1 April 26, 2017

    @gianmarcotoscano

    There's really not an automatic optimization tool in terms of one that'll magically fix an issue with RAM usage. MySQL Tuner will help you to identify potential areas of optimization, though it'd be up to you to make the changes in MySQL's configuration file and see what works for you.

    https://github.com/major/MySQLTuner-perl

    Ideally, MySQL should be running for 24-48 hours straight, without a restart, before you run the tool. It'll output a lot of information and make a few suggestions towards the bottom. I wouldn't, however, blindly make configuration changes unless you know what those changes do as you could very well end up making things worse (it's not hard, even for someone who does know what they mean :-) ).

    The biggest issue is, simply put, the stock configuration for MySQL really isn't meant for usage on production servers. It's meant as a starting point. It may work well for a smaller site with low traffic, though it may crumble (and crash) under higher levels of traffic.

    There's a lot of variables at play when it comes to tweaking and tuning MySQL. MySQL Tuner can help, but it's not a replacement for a sysadmin / db admin.

Have another answer? Share your knowledge.

Related Questions