mysql shut itself down on a clean server install?!?

January 17, 2017 2.6k views
MySQL Ubuntu
deanoboy
By:
deanoboy

I created a new droplet from a one-click app of phpmyadmin on ubuntu 14.4 then uploaded a wordpress site (did some basic stuff like enable mod rewrite and firewall etc...). Left the server/site alone for a couple of hours and next time I look I see the database is down and I had to restart

This is the mysql error log:

170117 17:39:35 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170117 17:39:35 [Note] Plugin 'FEDERATED' is disabled.
170117 17:39:35 InnoDB: The InnoDB memory heap is disabled
170117 17:39:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170117 17:39:35 InnoDB: Compressed tables use zlib 1.2.8
170117 17:39:35 InnoDB: Using Linux native AIO
170117 17:39:35 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
170117 17:39:35 InnoDB: Completed initialization of buffer pool
170117 17:39:35 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170117 17:39:35 [ERROR] Plugin 'InnoDB' init function returned error.
170117 17:39:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170117 17:39:35 [ERROR] Unknown/unsupported storage engine: InnoDB
170117 17:39:35 [ERROR] Aborting

170117 17:39:35 [Note] /usr/sbin/mysqld: Shutdown complete

170117 17:39:36 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170117 17:39:36 [Note] Plugin 'FEDERATED' is disabled.
170117 17:39:36 InnoDB: The InnoDB memory heap is disabled
170117 17:39:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170117 17:39:36 InnoDB: Compressed tables use zlib 1.2.8
170117 17:39:36 InnoDB: Using Linux native AIO
170117 17:39:36 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
170117 17:39:36 InnoDB: Completed initialization of buffer pool
170117 17:39:36 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170117 17:39:36 [ERROR] Plugin 'InnoDB' init function returned error.
170117 17:39:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170117 17:39:36 [ERROR] Unknown/unsupported storage engine: InnoDB
170117 17:39:36 [ERROR] Aborting

170117 17:39:36 [Note] /usr/sbin/mysqld: Shutdown complete

Any ideas what went wrong and how to stop it happening again?

Log In to Comment
3 Answers
gparent January 17, 2017

Go with the lines that say ERROR.

170117 17:39:35 InnoDB: Fatal error: cannot allocate memory for the buffer pool

Your droplet needs more memory or you need to make MySQL use less of it.

Reply
  • deanoboy January 17, 2017

    Is this something a swap file would fix? In the DO tutorial on creating a swap file they recommend against it for SSDs though

    • gparent January 17, 2017

      Swap is meant to be temporarily used when you need more memory than usual. It has very bad performance.

    • jtittle1 January 18, 2017

      @deanzod

      It's actually recommended that you do not use swap as it can degrade overall performance.

      Since the SSD's are shared amongst other users on the same server you're residing on, and depending on how much you dig in to swap as well as how often, it could affect not only the Droplet you're on, but others too.

deanoboy January 17, 2017
[deleted]
Reply
jtittle1 January 18, 2017

@deanzod

It does appear that you're running out of memory from the details provided. In order to prevent these issues, you should upgrade your droplet. MySQL and forks, such as MariaDB, can be ran on 512MB droplets, but it's a balancing act and you'd need to be comfortable working from the CLI in order to work on optimizing your configurations.

You can reduce the amount of RAM required by MySQL (or MariaDB) in order to work with less RAM, but what works for a little while may not work long-term. It's also a trade off. Working with less RAM often means that MySQL won't perform as well as it should and would with more RAM free.

Looking beyond MySQL, WordPress can sometimes be resource intensive, so it's important to enable some sort of caching, especially on resource-constrained servers. W3TC and WP Super Cache are two that instantly come to mind when it comes to caching. They will allow you to use PHP's built-in object caching (if OPCode Caching is enabled), though that too takes RAM (as would Memcached or Redis).

The benefit to enabling caching, however, is that you an reduce the resources required by your web server as a result since instead of constantly performing dynamic operations normally required by WordPress, the caching serves statically generated pages until the cache is updated.

Reply
  • deanoboy January 18, 2017

    Thanks. I've upgraded to 1gb to see how that goes. I'm just confused how a crappy shared hosting account can run hundreds of wp sites without issue?

    • jtittle1 January 18, 2017

      @deanzod

      I've actually ran a few web hosting companies that offered shared hosting solutions, so I can provide some insight there.

      Most shared hosting providers use cPanel and CentOS. By default, unless the provider also has CloudLinux installed, there's really no way to put a hard cap on CPU or RAM usage. With CloudLinux, providers can put both a soft and hard cap on CPU and RAM, but placing caps on database usage (last time I used CloudLinux) was not as easily done and required more configuration than most providers took the time to do.

      That being said, when you're in a shared hosting environment, the RAM that is used by the services on the physical web server are not counted towards your set limits. Only what your account uses from the available resource pool is what is actually counted. So the RAM that gets used by the services themselves doesn't matter. If their MySQL configuration allocates 16GB of RAM to MySQL, that doesn't matter. What matters is what your website uses.

      With a VPS, or Droplet, you've given a set amount of RAM and you have to work within those constraints. It is possible to run Apache/NGINX, PHP, and MySQL on a 512MB VPS, but it's not as simple as running:

      sudo apt-get -y install nginx \
&& sudo apt-get -y install mariadb-server \
&& sudo apt-get -y install php7.1-fpm

      ... and then creating a server block for your website, creating a database, and then running your WordPress installation. You have limited resources and need to optimize all three on a pretty consistent basis.

      But what's the real difference? Both are limiting you on resources, right? The difference is that the shared web hosting provider probably has 32-64GB up to 128-256GB of RAM on the server you're on and can freely allocate that 16GB of RAM to MySQL + cover the RAM used by your installations.

      On the Droplet (for the purpose of this explanation), you have 512MB RAM and there's no buffer room unless you upgrade.

      So why would anyone want a VPS? Control is one reason, the ability to do what you want instead of what the provider allows you to do is another. You have more room to do as you please on a VPS since you have root control over it than you would on a shared hosting account.

      There's a lot more than goes in to running a web server than many imagine, especially those coming from a shared-only environment where they aren't used to having to handle aspects such as security (beyond their application), updating & upgrading, ensuring security, etc.

      • deanoboy January 18, 2017

        Thanks jtittle! Makes sense. So that all said, although I am hoping 1gb ram will be ok in this instance, is there anything I can put in place to stop mysql from shutting down when it runs out of memory...or at least have it restart automatically and send me a notification if it does? Its a bit worrying that I could have an error page for weeks without knowing about it

        • jtittle1 January 18, 2017

          @deanzod

          1GB should be sufficient so long as you're not expecting or receiving high amounts of traffic, though even medium levels of traffic could cause MySQL to crash. It's not really cut and dry (though sometimes I wish it were!).

          That being said, if 1GB isn't sufficient and you're still seeing frequent crashes, you'd really need to look in to optimizing MySQL as there is a point where throwing RAM at it will no longer work. That's when we need to check how your current release is configured and re-configure it to better fit with your current needs.

          As far as configuring it to auto-restart, you can, but I wouldn't recommend it. Why? If it crashes, restarts, crashes, restarts, and crashes again, you're going to lose just as much traffic as you would if it were simply down.

          What I would do is simply setup basic monitoring and when that monitor triggers, you can then respond to it.

          UptimeRobot offers free monitoring and it's pretty reliable (I've used it in the past). You can configure a monitor to check for keywords on a page (i.e. your home page) and if it's not able to find those keywords, an alert will be triggered.

          If you setup keyword triggers, you want to target something that doesn't change, otherwise you're going to get a ton of alerts. Perhaps something in the header (site name, navigation, etc) that isn't prone to changing, or even something in the footer (copyright, site name, etc).

          You can also setup multiple other types of checks, so you could setup HTTP, Ping, port-specific, etc.

          http://uptimerobot.com

          Once you receive an alert, then you respond. If this happens frequently, it can be a bit of a pain, but I'd never recommend blindly auto-restarting services unless you're sure they won't be running up, down, up, down etc.

Have another answer? Share your knowledge.

Related Questions