Mysqldump missing-q Parameter causes MySQL to be killed by oom

One day early morning, I received a db Fault Warning and went to the machine for check. mysql was restarted by the myqld daemon. Check varlogmessages and the latest dmesg and find that the mysql process is

One day early morning, I received a db Fault Warning and went to the machine for check. mysql was restarted by the myqld daemon. Check/var/log/messages and the latest dmesg and find that the mysql process is

One day early morning, I received a db Fault Warning and went to the machine for check. mysql was restarted by the myqld daemon. Check/var/log/messages and the recent dmesg and find that the mysql process is killed by oom.

The following is an excerpt from the information:
[1, 13045702.638964] kthread invoked oom-killer: gfp_mask = 0xd0, order = 1, oomkilladj = 0
[13045702.638969]
[2, 13045702.638969] Call Trace: {Oom_kill_process + 87}
[13045702.638977] {Out_of_memory + 271} {Autoremove_wake_function + 0}
ERROR: Fatal error found, match ERROR-KEYWORD 'out _ of_memory'
...

[13045702.716335] Out of Memory: Kill process 25795 (mysqld) score 1591671 and children.
ERROR: Fatal error found, match ERROR-KEYWORD 'out of Memory'

[13045702.716359] Out of memory: Killed process 25795 (mysqld ).
.....
[13045702.802080] Out of Memory: Kill process 1907 (mysqld) score 955002 and children.
ERROR: Fatal error found, match ERROR-KEYWORD 'out of Memory'

[13045702.802102] Out of memory: Killed process 1907 (mysqld ).
....
[13045762.203463] Out of Memory: Kill process 24544 (mysqld) score 341071 and children.
ERROR: Fatal error found, match ERROR-KEYWORD 'out of Memory'
[13045762.203485] Out of memory: Killed process 24544 (mysqld ).

[13045762.322333] sap1002 invoked oom-killer: gfp_mask = 0x201d2, order = 0, oomkilladj = 0
.....
[13045762.479886] Out of Memory: Kill process 19530 (mysqldump) score 93607 and children.
ERROR: Fatal error found, match ERROR-KEYWORD 'out of Memory'

[13045762.479907] Out of memory: Killed process 19530 (mysqldump ).

Due to insufficient memory, the system selects several processes that consume a large amount of memory and kill them. Kill mysqld first and then kill mysqldump. This may be because mysqldump consumes a large amount of memory.

On this db, mysqldump is used every morning to back up a single table and compress it through pipelines. This backup task has been deployed for more than one year.
After checking the OS performance data collected in the background, you can also find that the memory consumption will increase a lot after mysqldump is started until the backup is complete. This is certainly because mysqldump consumes a large amount of memory, leading to this failure.

Check the backup script. The backup logic is roughly as follows:
1: show tables like 'xxx % 'get the name of the table to be backed up
2: mysqldump -- uxx-pyy -- skip-opt >

Now the problem is clear. mysqldump causes OOM because the-q parameter is not used to consume too much memory.

Description of the mysqldump-q Parameter (from man mysqldump)
? -- Quick,-q

This option is useful for dumping large tables. it forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

This option is used to dump large tables. It forces mysqldump to retrieve data from one row on the server rather than caching the data of all rows into the memory before the output.

The following is a test. We can see the impact of the-q Parameter on memory consumption:
1: without the-q Parameter
The top output is as follows:
Pid user pr ni virt res shr s % CPU % mem time + COMMAND
20010 mysql 15 0 18.0g 17g 4496 S 10 56.6 2765: 51 mysqld
27518 mysql 25 0 4227 m 4.1g 1048 R 100 0: 33. 05 mysqldump
Memory consumption exceeds 4 GB
2: With the-q Parameter
The top output is as follows:
Pid user pr ni virt res shr s % CPU % mem time + COMMAND
20010 mysql 16 0 18.0g 17g 4496 S 84 56.6 2766: 12 mysqld
27686 mysql 25 0 11628 1380 R 98 1052. 20 mysqldump
Memory consumption is very small, only a few K

Solution:
Modify the backup script and add the-q parameter.

This backup task has been deployed for more than one year. Why is this fault only recently?
Make a statistics on the data in the DB, and the data volume of the backup table needs to suddenly increase from 100 M/day to 4G/day. Zhihui R & D followed up the problem, and finally found that the front-end configuration was changed, resulting in continuous frontend retry.

Experience:
In many cases, failure occurs only when many factors are intertwined. When a problem occurs, you need to take a deeper look at the root cause of the problem.

Related reading:

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases