What are the common causes and solutions for MySQL database crashes

Check the startup time of the MySQL database

systemd and mysqld_safe in the Linux system will automatically restart the MySQL service after the mysqld process crashes. Please note that If you use kill -9 to kill the mysqld process, the system will automatically restart, but if you just use the kill command, it will not restart. Because when you execute the kill command, the system will send a SIGTERM signal to mysqld, and the mysql database will shut down normally, and it will appear in the log. Records similar to the following:

2020-10-26T09:06:48.435181Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19 ) MySQL Community Server - GPL.

MySQL database will be restarted after a crash, so sometimes we may not know that the MySQL database has crashed, but we can find clues from the mysql database startup time, as follows Introduces four methods to check the MySQL database startup time.

Checking MySQL service status

scutech@scutech:~$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-10-21 05:54:18 NDT; 4 days ago
  Process: 774 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 708 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 791 (mysqld)
    Tasks: 27 (limit: 2328)
   CGroup: /system.slice/mysql.service
           └─791 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

shows that the MySQL database has been running for more than 4 days.

Check the uptime status in MySQL

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 428334 |
+---------------+--------+
1 row in set (0.32 sec)

This value is in seconds. The following conversion to days is more than 4 days.

mysql> select 428334/60/60/24;
+-----------------+
| 428334/60/60/24 |
+-----------------+
|  4.957569444444 |
+-----------------+
1 row in set (0.01 sec)

Another way to query the uptime status is to use mysqladmin version or use "\s" to query in the mysql client.

Use ps to check the process startup time

Use the ps command to query and find that mysqld has been started for 4 days, 23 hours, 3 minutes and 54 seconds

scutech@scutech:~$ ps -eo pid,user,args,etime|grep mysqld
  791 mysql    /usr/sbin/mysqld --daemoniz  4-23:03:54

Check the MySQL log

Look for the keyword "ready for connections" to find startup information.

2020-10-21T08:24:18.986765Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.28-log' socket: '/ var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)

Common reasons for MySQL database crash

There are two most common reasons for MySQL database crash , one is a bug in mysql, and the other is a failure of mysql to apply for system resources or a memory leak.

MySQL bug

One of the most common reasons for MySQL database crash is of course MySQL bug. 95% of bugs are related to specific sql. Usually there is a problem with the last sql executed before MySQL crashes. Therefore, when locating bugs, you should open the general query log and look for clues based on the last sql.
After you determine the cause of the crash, you should check the MySQL bug library (https://bugs.mysql.com), usually using Advanced search, to see if there are any similar problems. If you find a bug that may be relevant to you, confirm that it has been fixed. If it has been fixed, then upgrade MySQL to a version where the bug has been fixed.

There is a Bugs Fixed section in the Release Notes of each version, where you can check the fixed bugs.

MySQL fails to apply for system resources or memory leaks

Insufficient memory or MySQL fails to apply for system resources will cause MySQL to crash, such as the disk space is full, the files on the disk are corrupt, etc. At this time, there are several methods to locate the root cause of the crash:

• Read the MySQL error log carefully. Some of the program debugging information in this log may seem confusing, but it is quiet. If you look carefully, you will often find clues;

• Open the general query log, find the last table or index accessed by SQL, check the table or index, and rebuild it if there is any problem. This usually solves the problem.

• Use strace, pstack, pmap, and gdb to analyze the mysqld code. You may need to open core dump;

• Use the CMake option -DWITH_DEBUG= 1 Recompile mysqld, then run the recompiled mysqld, check the trace file and error log for troubleshooting.

MySQL memory usage calculation

Global memory
innodb_buffer_pool_size innodb_log_buffer_size thread_cache_size table_open_cache table_definition_cache key_buffer_size
Thread memory
binlog_cache_size thread_stack
Single operation Memory
join_buffer_size read_buffer_size read_rnd_buffer_size tmp_table_size sort_buffer_size

Calculation formula
The maximum memory usage reference value calculation formula in MySQL 8:

SELECT ( @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size 
+ @@max_connections * (@@binlog_cache_size + @@thread_stack + @@read_buffer_size 
+ @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size ) 
) / 1024 /1024 AS MAX_MEM_MB;

innodb_buffer_pool_size

• key_buffer_size

• max_connections*(sort_buffer_size read_buffer_size binlog_cache_size)

• max_connections*2MB

Temporary solution You can use the following command to release the cache:

echo 1 > /proc/sys/vm/drop_caches

0: 0 is the system default value, which means that the memory is not released by default and is automatically managed by the operating system
1: Release the page cache
2: Release dentries and inodes
3: Release all caches
In the long run, it is still necessary to modify the corresponding parameters to solve the problem.

MySQL client memory leak

MySQL client memory leak usually has the following prompt

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

This is usually caused by the returned result set received by the client being too large. There are two solutions:

Check the running SQL to see if you really Do you need such a large return result set?
Add the --quick option when allowing mysql, which will reduce the return set received by the client at a time, but will increase the load of mysqld.

The above is the detailed content of What are the common causes and solutions for MySQL database crashes. For more information, please follow other related articles on the PHP Chinese website!