MariaDB stopped working until restart

Question

Today our website displayed a “Database connection error”

Via the command line I tried to connect to MariaDB and it failed:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111 "Connection refused")

I did:

service mysql restart

The service restarted and I was able to connect again.

The mysql/mariadb error logs are empty. Where can I look to find out what happened?

EDIT

OK I think I answered my own question, after looking in syslog:

Jun 17 12:12:42 svr kernel: [751960.001590] Out of memory: Kill process 1644 (mysqld) score 160 or sacrifice child
Jun 17 12:12:42 svr kernel: [751960.002941] Killed process 1644 (mysqld) total-vm:602692kB, anon-rss:79864kB, file-rss:0kB
Jun 17 12:12:42 svr mysqld_safe: Number of processes running now: 0
Jun 17 12:12:42 svr mysqld_safe: mysqld restarted
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] /usr/sbin/mysqld (mysqld 10.0.29-MariaDB-0ubuntu0.16.04.1) starting as process 3572 ...
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] mysqld: Out of memory (Needed 128909312 bytes)
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Using mutexes to ref count buffer pool pages
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: The InnoDB memory heap is disabled
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Compressed tables use zlib 1.2.8
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Using Linux native AIO
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Using CPU crc32 instructions
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] InnoDB: Initializing buffer pool, size = 128.0M
Jun 17 12:12:42 svr mysqld: InnoDB: mmap(139722752 bytes) failed; errno 12
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] Plugin 'InnoDB' init function returned error.
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] Plugin 'FEEDBACK' is disabled.
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] Unknown/unsupported storage engine: InnoDB
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [ERROR] Aborting
Jun 17 12:12:42 svr mysqld:
Jun 17 12:12:42 svr mysqld: 170617 13:12:42 [Note] /usr/sbin/mysqld: Shutdown complete
Jun 17 12:12:42 svr mysqld:
Jun 17 12:12:42 svr mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

Looks like we need a bigger droplet?

Flamber Hansen · Accepted Answer

Hi @DigitalCarrot Yes, total system ran out of RAM, so resize the droplet up. And it also looks like your database storage is actually 140MB, but the buffer pool size is the default 128MB, which means it’s swapping constantly to disk, making it much slower. Your pool should be greater than the amount of data.

alexdo · Answer

Hello, @DigitalCarrot

What you could do as well after you perform the Droplet upgrade in order to increase the memory is to use the MySQLTuner script.

The MySQLTuner is a script written in Perl and allows you to quickly test your MySQL configuration and it gives you suggestions for adjustments to increase performance and stability.

According to the official GitHub page, it supports 300 indicators for MySQL/MariaDB/Percona Server in this last version.

To run the script you could do the following:

SSH to your Droplet
Download the script:

wget http://mysqltuner.pl/ -O mysqltuner.pl

Then execute it:

perl mysqltuner.pl

The script would run multiple checks against your MySQL instance, all checks done by MySQLTuner are documented here.

Also as stated in the official documentation, it is still extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust.

As a good practice make sure to always test your changes on staging environments before implementing them on your production database.

On the same note, if you want to have a worry-free MySQL hosting and focus on your application, I would recommend trying out the DigitalOcean Managed Databases:

https://www.digitalocean.com/products/managed-databases-mysql/

Hope that this helps! Regards, Bobby