mariadb crash solution on low configuration VPS

introduction

Recently, the blog has gone into a funk again, and when I open the homepage, the message Error Establishing a Database Connection is displayed. If you think about it, mariadb, the database server, is down; We've had similar problems before. After analyzing the logs and combining the data on the Internet, the problem was finally solved.

Logs

The following is the more critical log information when the mariadb server was down. From the following log information, it is easy to see that the database server crashed at startup due to insufficient memory.

InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
160919  2:47:12  InnoDB: Waiting for the background threads to start
160919  2:47:13 Percona XtraDB (http://www.percona.com) 5.5.46-MariaDB-37.6 started; log sequence number 352718445
160919  2:47:13 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
160919  2:47:13 [Note] Plugin 'FEEDBACK' is disabled.
160919  2:47:13 [Note] Server socket created on IP: '0.0.0.0'.
160919  2:47:13 [Note] Event Scheduler: Loaded 0 events
160919  2:47:13 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.47-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
160919 02:47:35 mysqld_safe Number of processes running now: 0
160919 02:47:35 mysqld_safe mysqld restarted
160919  2:47:35 [Note] /usr/libexec/mysqld (mysqld 5.5.47-MariaDB) starting as process 28614 ...
160919  2:47:35 InnoDB: The InnoDB memory heap is disabled
160919  2:47:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160919  2:47:35 InnoDB: Compressed tables use zlib 1.2.7
160919  2:47:35 InnoDB: Using Linux native AIO
160919  2:47:35 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
160919  2:47:35 InnoDB: Completed initialization of buffer pool
160919  2:47:35 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160919  2:47:35 [ERROR] Plugin 'InnoDB' init function returned error.
160919  2:47:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160919  2:47:35 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
160919  2:47:35 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
160919  2:47:35 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
160919  2:47:35 [Note] Plugin 'FEEDBACK' is disabled.
160919  2:47:35 [ERROR] Unknown/unsupported storage engine: InnoDB
160919  2:47:35 [ERROR] Aborting

Work out

Symptom When the free -m command is used to view memory information, the swap partition size is 0. No wonder the database server can't start, and when it runs out of memory and can't use the swap partition, it naturally crashes. Since VPS uses SSDS, performance is naturally good. Here we add 1024M swap partition to the server system CentOS 7 by creating a swap file:

Create swapfile with the following command:

# 1048576 = 1024 * 1024
dd if=/dev/zero of=/swapfile bs=1024 count=1048576

Use the following command to configure the swap file:

mkswap /swapfile

Next, use the following command to enable swapfile immediately, so you don't have to wait until the next reboot to automatically enable it:

swapon /swapfile


Finally, we add the following line in /etc/fstab so that the swapfile created automatically takes effect the next time the system restarts:

/swapfile       swap    swap defaults   0 0


Use cat /proc/swaps or free -m to see if the swapfile is in effect, as shown below:

After completing the above steps, you can also add some configuration information to the /etc/my.cnf configuration file to reduce mariadb resource requirements. For details, see the link at the end of the article.

activate

To start the apache server: systemctl start httpd.service; start the mariadb server: systemctl start mariadb.service. Once the startup is complete, open the home page again, bingo, problem solved!

Sum up

Low VPS is best to increase the size of swap partition, especially for the use of SSD VPS, swap partition performance is also very good; After a database server crashes, it is important to remember to analyze logs. The simplest way to do this is to use the tail command to look at the most recent crash log and look for a solution based on the crash information.

The WordPress program itself is relatively resource-intensive, so when running in low-configuration VPS, you still need to do some optimization work. Please refer to the link at the end of the article.

Latest comments