开启general_log对性能的影响

最近想开发一个工具(轻量级),实时定位mysql的性能问题(DB层),当然有些公司,通过中间层来捕获mysql的执行情况(重量级),因为show processlist及slow.log定位问题局限性,最终考虑暂时开启mysql的general_log,分析general_log,找出问题sql。用这个工具当然也有风险,会在测试完成后,把源码共享出来,并具体介绍。这节主要关注打开general_log到底有多大的性能影响。

机器配置:
cpu:16 core
memory:24G

一、使用general log测试:
1、开启general log(MySQL5.1.12版本及以上可以动态开启)

root@(none) 11:55:04>set global log_output=file;
Query OK, 0 rows affected (0.00 sec)
 
root@(none) 12:08:10>set global general_log_file='/tmp/general.log';
Query OK, 0 rows affected (0.00 sec)
 
root@(none) 12:08:23>set global general_log=on;
Query OK, 0 rows affected (0.01 sec)

2、mysqlslap压测

$mysqlslap -a --concurrency=500 --number-of-queries=300000 
--iterations=3 --auto-generate-sql-add-autoincrement 
--auto-generate-sql-load-type=mixed --debug-info
 
Benchmark
        Average number of seconds to run all queries: 16.549 seconds
        Minimum number of seconds to run all queries: 16.486 seconds
        Maximum number of seconds to run all queries: 16.676 seconds
        Number of clients running queries: 500
        Average number of queries per client: 600
 
 
User time 8.70, System time 11.22
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 10955, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 1800288, Involuntary context switches 3193

3、查看CPU及磁盘情况(每隔一段时间有2万左右个block写出)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.35    0.00    8.43    0.25    0.00   68.98
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5             55.00         0.00     22904.00          0      22904
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.99    0.00    6.50    0.06    0.00   74.45
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5              0.00         0.00         0.00          0          0
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.48    0.00    6.37    0.19    0.00   74.97
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5              0.00         0.00         0.00          0          0

二、未使用general log测试:
1、关闭general_log

root@(none) 12:08:45>set global general_log=off;
Query OK, 0 rows affected (0.00 sec)

2、mysqlslap压测

$mysqlslap -a --concurrency=500 --number-of-queries=300000 
--iterations=3 --auto-generate-sql-add-autoincrement 
--auto-generate-sql-load-type=mixed --debug-info
 
Benchmark
        Average number of seconds to run all queries: 14.590 seconds
        Minimum number of seconds to run all queries: 14.529 seconds
        Maximum number of seconds to run all queries: 14.635 seconds
        Number of clients running queries: 500
        Average number of queries per client: 600
 
 
User time 8.63, System time 11.07
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 10979, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 1793394, Involuntary context switches 4104

3、查看CPU及磁盘情况

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5              4.95         0.00       102.97          0        104
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.81    0.00    0.69    0.00    0.00   98.50
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5              0.00         0.00         0.00          0          0
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          19.11    0.00    6.12    0.19    0.00   74.58
 
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda5              0.00         0.00         0.00          0          0

小结:

1、计算开启general_log后,每次按pk查询会慢多少:0.0000022s

root@(none) 01:11:47>select (16.549-14.590)/900000;
+------------------------+
| (16.549-14.590)/900000 |
+------------------------+
|              0.0000022 |
+------------------------+

2、因为general_log要写磁盘,所以每隔一段时间会刷磁盘,如果io比较繁忙的情况下,可能就不太适合

另:可以做一些基准测试,开启和未开启的情况下,最多能支撑多少tps、qps等


One Response to “开启general_log对性能的影响”

  1. mr.ct说道:

    影响不大啊!

Post a Comment