cagkana9f5cd873f5b8188e469
By:
cagkana9f5cd873f5b8188e469

MariaDB Shuts Down Frequently

July 31, 2017 1.7k views
MariaDB CentOS

Hello,

I'm using wordpress and my mariadb shuts down very frequently. dont understand why.

here's my error logs:

170729 22:21:33 mysqldsafe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170729 22:21:40 mysqldsafe Starting mysqld daemon with databases from /var/lib/mysql
170729 22:21:40 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 3724 ...
170729 22:21:40 InnoDB: The InnoDB memory heap is disabled
170729 22:21:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170729 22:21:40 InnoDB: Compressed tables use zlib 1.2.7
170729 22:21:40 InnoDB: Using Linux native AIO
170729 22:21:40 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(551026688 bytes) failed; errno 12
170729 22:21:40 InnoDB: Completed initialization of buffer pool
170729 22:21:40 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170729 22:21:40 [ERROR] Plugin 'InnoDB' init function returned error.
170729 22:21:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170729 22:21:40 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
170729 22:21:40 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
170729 22:21:40 [Note] Plugin 'FEEDBACK' is disabled.
170729 22:21:40 [ERROR] Unknown/unsupported storage engine: InnoDB
170729 22:21:40 [ERROR] Aborting

170729 22:21:40 [Note] /usr/libexec/mysqld: Shutdown complete

170729 22:21:40 mysqldsafe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170729 22:22:45 mysqldsafe Starting mysqld daemon with databases from /var/lib/mysql
170729 22:22:46 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 4043 ...
170729 22:22:46 InnoDB: The InnoDB memory heap is disabled
170729 22:22:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170729 22:22:46 InnoDB: Compressed tables use zlib 1.2.7
170729 22:22:46 InnoDB: Using Linux native AIO
170729 22:22:46 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(551026688 bytes) failed; errno 12
170729 22:22:46 InnoDB: Completed initialization of buffer pool
170729 22:22:46 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170729 22:22:46 [ERROR] Plugin 'InnoDB' init function returned error.
170729 22:22:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170729 22:22:46 [Note] Plugin 'FEEDBACK' is disabled.
170729 22:22:46 [ERROR] Unknown/unsupported storage engine: InnoDB
170729 22:22:46 [ERROR] Aborting

170729 22:22:46 [Note] /usr/libexec/mysqld: Shutdown complete

170729 22:22:46 mysqldsafe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170729 22:23:28 mysqldsafe Starting mysqld daemon with databases from /var/lib/mysql
170729 22:23:28 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 4378 ...
170729 22:23:29 InnoDB: The InnoDB memory heap is disabled
170729 22:23:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170729 22:23:29 InnoDB: Compressed tables use zlib 1.2.7
170729 22:23:29 InnoDB: Using Linux native AIO
170729 22:23:29 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(551026688 bytes) failed; errno 12
170729 22:23:29 InnoDB: Completed initialization of buffer pool
170729 22:23:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170729 22:23:29 [ERROR] Plugin 'InnoDB' init function returned error.
170729 22:23:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170729 22:23:29 [Note] Plugin 'FEEDBACK' is disabled.
170729 22:23:29 [ERROR] Unknown/unsupported storage engine: InnoDB
170729 22:23:29 [ERROR] Aborting

170729 22:23:29 [Note] /usr/libexec/mysqld: Shutdown complete

Log In to Comment
3 Answers
UKn0Me July 31, 2017

As it says, it cannot allocate the memory for the buffer pool (in layman's terms, you're outta RAM). You can either lower your buffer pool size for something more suitable to the amount of RAM your droplet has, or you can increase the RAM of your droplet.

Reply
  • cagkana9f5cd873f5b8188e469 July 31, 2017

    I've 2gb memory in my vps. how can I change buffer poolsize?

    • UKn0Me July 31, 2017

      In my.cnf (the MySQL/MariaDB configuration file), add or set innodb_buffer_pool_size to something like 256M so that you have something like the following.

      innodb_buffer_pool_size=256M

      my.cnf can be in several locations, from my experience /etc/mysql/my.cnf is the usual spot but other locations may include;

      • /etc/my.cnf
      • /etc/mysql/my.cnf
      • /usr/etc/my.cnf
      • ~/.my.cnf
      • cagkana9f5cd873f5b8188e469 July 31, 2017

        what should I change it to? I've 2gb total ram.
        256mb is good? or less?

        • UKn0Me July 31, 2017

          I'd try 265M but go down to 128M if that doesn't work. Below 128M there may be some other issue at play.

          • cagkana9f5cd873f5b8188e469 July 31, 2017

            170731 8:27:12 [Note] /usr/libexec/mysqld: Shutdown complete

            170731 08:27:13 mysqldsafe mysqld from pid file /var/run/mariadb/mariadb.pid ended
            170731 08:29:10 mysqld            safe Starting mysqld daemon with databases from /var/lib/mysql
            170731 8:29:10 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 13648 ...
            170731 8:29:10 InnoDB: The InnoDB memory heap is disabled
            170731 8:29:10 InnoDB: Mutexes and rwlocks use GCC atomic builtins
            170731 8:29:10 InnoDB: Compressed tables use zlib 1.2.7
            170731 8:29:10 InnoDB: Using Linux native AIO
            170731 8:29:10 InnoDB: Initializing buffer pool, size = 256.0M
            170731 8:29:10 InnoDB: Completed initialization of buffer pool
            170731 8:29:10 InnoDB: highest supported file format is Barracuda.
            InnoDB: The log sequence number in ibdata files does not match
            InnoDB: the log sequence number in the ib            logfiles!
            170731 8:29:10 InnoDB: Database was not shut down normally!
            InnoDB: Starting crash recovery.
            InnoDB: Reading tablespace information from the .ibd files...
            InnoDB: Restoring possible half-written data pages from the doublewrite
            InnoDB: buffer...
            170731 8:29:10 InnoDB: Waiting for the background threads to start
            170731 8:29:11 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 310261486
            170731 8:29:11 [Note] Plugin 'FEEDBACK' is disabled.
            170731 8:29:11 [Note] Server socket created on IP: '0.0.0.0'.
            170731 8:29:11 [Note] Event Scheduler: Loaded 0 events
            170731 8:29:11 [Note] /usr/libexec/mysqld: ready for connections.
            Version: '5.5.52-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
            170731 8:29:12 [ERROR] mysqld: Table './mgsdb/wpoptions' is marked as crashed and should be repaired
            170731 8:29:12 [Warning] Checking table: './mgsdb/wpoptions'
            170731 8:29:16 [ERROR] mysqld: Table './mgsdb/wpwoocommercesessions' is marked as crashed and should be repaired
            170731 8:29:16 [Warning] Checking table: './mgs            db/wpwoocommercesessions'
            170731 8:29:17 [ERROR] mysqld: Table './mutluko/wpoptions' is marked as crashed and should be repaired
            170731 8:29:17 [Warning] Checking table: './mutluko/wp            options'
            170731 8:29:20 [ERROR] mysqld: Table './mutluko/wprateschedule' is marked as crashed and should be repaired
            170731 8:29:20 [Warning] Checking table: './mutluko/wprateschedule'

            no way, 256 and after 128 i've tried. and that didnt work again. :(

          • UKn0Me July 31, 2017

            So that worked but because of the crashes before, your tables aren't in good shape.

            You'll want to back them up (just in case) which is described in this link, but basically running the following

            mysqldump -u [username] -p [database_name] > [database.sql]

            After backing up, you should repair the tables with the following which will repair the affected tables.

            mysqlcheck -u [username] -p --repair --all-databases
          • cagkana9f5cd873f5b8188e469 July 31, 2017

            i repaired. but again there's an error :S

            170731 10:52:01 mysqldsafe Number of processes running now: 0
            170731 10:52:01 mysqld            safe mysqld restarted
            170731 10:52:02 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 19289 ...
            170731 10:52:02 InnoDB: The InnoDB memory heap is disabled
            170731 10:52:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
            170731 10:52:02 InnoDB: Compressed tables use zlib 1.2.7
            170731 10:52:02 InnoDB: Using Linux native AIO
            170731 10:52:02 InnoDB: Initializing buffer pool, size = 256.0M
            InnoDB: mmap(275513344 bytes) failed; errno 12
            170731 10:52:02 InnoDB: Completed initialization of buffer pool
            170731 10:52:02 InnoDB: Fatal error: cannot allocate memory for the buffer pool
            170731 10:52:02 [ERROR] Plugin 'InnoDB' init function returned error.
            170731 10:52:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
            170731 10:52:02 [Note] Plugin 'FEEDBACK' is disabled.
            170731 10:52:02 [ERROR] Unknown/unsupported storage engine: InnoDB
            170731 10:52:02 [ERROR] Aborting

hansen July 31, 2017

Hi @cagkana9f5cd873f5b8188e469

The root problem is not MariaDB, but one of your other services taking up large amounts of RAM, leaving very little for MariaDB, which means it's being crashed, since it's the largest single process.

Have a look at htop to get an overview of what is taking all the memory.

sudo yum install htop

Also, I can see it's an older version of MariaDB. Please remember to update your system.

sudo yum update
Reply
xMudrii July 31, 2017

What is your exact setup/configuration? What's your Web server and do you have something in front of it (e.g. Varnish)?

By latest logs, it's again Out of Memory.

2GB Droplet should run WordPress without a single problem. I've had 512MB Droplet running WordPress for long time, without any problem.

So what you can do. If you use Varnish or any other caching, in-memory caching, disable it until you get the site working.
If that doesn't help, try just running bare bones MySQL to make sure it works - in other words, stop Web server until you get MySQL up.
If you got it up, start Web server once again and monitor situation.
If not, make sure it's exactly memory problem and not something bigger by checking MySQL logs one more time and Syslog as well. It's file /var/log/syslog that you can open with any editor (requires root/ sudo). Observe for Out of memory and mysql entries. If it confirms OOM you can:

  • Try with lowering buffer size more (e.g. 128M)
  • Upgrading Droplet or free and less recommend install Swap. Try going with Swap as its not hard to setup and doesn't cost you anything.

Test once again and repost the logs.

How To Add Swap Space on Ubuntu 16.04
One of the easiest way of increasing the responsiveness of your server and guarding against out of memory errors in your applications is to add some swap space. In this guide, we will cover how to add a swap file to an Ubuntu 16.04 server. <$>[warning] [label...
Reply
Have another answer? Share your knowledge.

Related Questions