MySQL server gone down.

July 24, 2018 206 views
MySQL Ubuntu 18.04

I am using a 2GB droplet with 2 Core CPU. My application is CS Cart.
Last day my mysql server gone down. I don't see any spike in traffic its all normal we don't have many visitors in site may be a 10 visitors /day.

This is the log file when mysql is down.

2018-07-20T09:26:00.722785Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-20T09:26:00.724979Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu18.04.1) starting as process 30120 ...
2018-07-20T09:26:00.758021Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-07-20T09:26:00.758060Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-20T09:26:00.758067Z 0 [Note] InnoDB: Uses event mutexes
2018-07-20T09:26:00.758072Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-07-20T09:26:00.758078Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-07-20T09:26:00.758084Z 0 [Note] InnoDB: Using Linux native AIO
2018-07-20T09:26:00.758434Z 0 [Note] InnoDB: Number of pools: 1
2018-07-20T09:26:00.758566Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-07-20T09:26:00.760514Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-07-20T09:26:00.760560Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2018-07-20T09:26:00.760571Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-07-20T09:26:00.760578Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-07-20T09:26:00.760585Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-07-20T09:26:00.760592Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-07-20T09:26:00.760598Z 0 [ERROR] Failed to initialize builtin plugins.
2018-07-20T09:26:00.760603Z 0 [ERROR] Aborting

2018-07-20T09:26:00.774515Z 0 [Note] Binlog end
2018-07-20T09:26:00.774604Z 0 [Note] Shutting down plugin 'CSV'
2018-07-20T09:26:00.780733Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
1 Answer

Hello friend!

Your system is running out of memory and the kernel is killing off MySQL to stay alive. I could only theorize about the cause, but it is very important to understand that this can occur despite no changes and no obvious spike in traffic, as MySQL memory usage is not entirely dependent on these items alone. I'll give an example:

I used to manage customer built Wordpress sites, and I would continually run into this scenario where a site began using more and more memory and slowing down over time, despite it remaining statistically consistent on workload/traffic. I noticed that these websites were running a security plugin that would write every visitor to a database table, then query every visitor against that table, and it would never clean the table. This meant that the exact same effort continually grew in overhead over time, as that table took longer and longer to query for each page load.

That may not be relevant to your situation, and I'm afraid I can't say for sure what is relevant to your situation, but I can say that this is evidence that something needs to be reviewed. Upgrading the droplet to a larger size is easy, and may fix it for while, but it also may not. It may also continually grow to each memory point, forcing continual upgrades without justification based on visits to your website. In this case, you really need to have someone familiar with your software review the server's performance and overhead to determine the best course of action.

Kind Regards,
Jarland

Have another answer? Share your knowledge.