Mysqldump backup plus-q parameter

write in front: when we use mysqldump to back up data, make sure to remember to add - Qparameters, the consequences may be very serious, do not dig a hole for yourself oh. Why is it, and listen to my slow way!

Let's take a look at the explanation of the-Q parameter in Mysqldump–help:

-Q,--quick Don ' t buffer query, dump directly to stdout.

In short, that is, after the addition of-Q, will not put the results of the select out in buffer, but directly dump to the standard output, at most, the current row of buffer is the result, under normal circumstances will not exceed the max_allowed_packet limit, it is open by default.

If this parameter is turned off, the result of the select is placed in a local buffer and then output to the client, which consumes more memory.

In the MYSQLDUMP.C can also see the comparison of the two (now popular in-depth source, although I am not focused on development, find a few lines of source power still exist, used to install B, we know that good, haha):

if (quick)  Res=mysql_use_result (sock); else  Res=mysql_store_result (sock);

There is a theory, but also to have practice is not, we have to see in the actual scenario, add the difference between-Q how big.

	Partial backup (Enable-Q)	Partial backup (disable-Q)	Full backup (Enable-Q)	Full Backup (disable-Q)
Total time to Backup	27.882 seconds	22.665 seconds	277.387 seconds	217.074 seconds
Memory consumption (swap included)	3056KB	2.5GB	3048KB	Memory: 12GBSWAP:305MB

You can see that if you just back up a small amount of data, enough to put in the free memory buffer, disable-Q will be faster, but if it is a large data set, there is no way to completely hold in memory buffer, will produce swap, efficiency is worse, really lend your money.

Therefore, if you use mysqldump to back up your data, it is recommended that you always add the-Q parameter to avoid swap and affect backup efficiency.

Detailed process (patience can continue to look down)

1. Full-scale backup: Do not use the-Q parameter when backing up

Mysqldump--quick=false-smysql.sock-b Yejr--tables t_yejr# First Look at the beginning of the state:mem:  32863040k total, 29338704k used,   3524336k free,   227632k buffersswap:16777208k total,    23548k used, 16753660k free,& nbsp 8200416k cachedpid user      pr  ni  virt  res  SHR S%CPU%MEM     time+  COMMAND21986 root      20   0 6119m 5.9g 2192 S 20.6 18.9 &nbs P 0:21.69 mysqldump# again look at the state after the end of the backup, memory is not enough, resulting in swapmem:  32863040k total, 32521328k used,   341712k free,       440k buffersswap:16777208k total,   336876k used, 16440332k free,    315192k cachedpid user      pr  ni  virt  res  SHR S%CPU%MEM     time+  COMMAND21986 root      20   0 12.3g  12g  656 R 10 0.0 39.1   2:23.93 mysqldump# Last look at the total time spent on backupsreal    4m37.387suser    2m2.731ssys     0m24.608s 

2. Full-scale backup: Enable-Q parameter when backing up

Mysqldump-smysql.sock-b Yejr--tables t_yejr# First look at the status at the beginning: mem:32863040k total, 20157476k used, 12705564k free, 4608k buffersswap:16777208k Total, 0k used, 16777208k free, 488296k cached# again look at the end of the backup, you can see that no use to swapmem:32863040k tot Al, 32644496k used, 218544k free, 920k buffersswap:16777208k total, 0k used, 16777208k free, 12618740k Cach    Edpid   USER PR NI VIRT RES SHR S%cpu%MEM time+ COMMAND25234 root 0 50880 3048 2192 S 57.6 0.0 2:22.79 mysqldump# Last look at the total elapsed time statistics: Real 3m37.074suser 2m6.018ssys 0m17.315s

3. Partial backup: Do not use the-Q parameter when backing up

Mysqldump-w "id<100000"-smysql.sock--quick=false-smysql.sock-b yejr--tables t_yejr# See the total time-consuming real 0m22.665suser 0M20 .458ssys 0m2.156s# then look at the memory consumed by the mysqldump process, with a maximum of 2.5G memory 20619 root 0 2571m 2.5g 2208 R 99.9 7.8 0:11.63 mysqldump

4. Partial backup: Enable the-Q parameter when backing up

Mysqldump-w "id<100000"-smysql.sock-smysql.sock-b yejr--tables t_yejr# Look at the total time, and not slow how much real 0m27.882suser 0m22.610ssy S 0m0.670s# then look at the memory consumed by the mysqldump process, consuming only a very small amount of memory 19690 root 0 50880 3056 2200 S 73.4 0.0 0:06.01 mysqldump

Mysqldump backup plus-q parameter