阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736
分类: Mysql/postgreSQL
2012-04-27 12:03:31
4、innodb_max_dirty_pages_pct测试
参数设置
其他参数的设置为:
innodb_additional_mem_pool_size =256 innodb_log_buffer_size = 512M innodb_buffer_pool_size = 32G innodb_log_file_size=4G innodb_thread_concurrency=16 |
测试结果及分析
测试innodb_max_dirty_pages_pct分别为50、75、90时,percona server的性能。
(1)innodb_max_dirty_pages_pct为50情况下得出的TPmC结果:
[transaction percentage] Payment: 43.48% (>=43.0%) [OK] Order-Status: 4.35% (>= 4.0%) [OK] Delivery: 4.35% (>= 4.0%) [OK] Stock-Level: 4.35% (>= 4.0%) [OK] [response time (at least 90% passed)] New-Order: 100.00% [OK] Payment: 100.00% [OK] Order-Status: 100.00% [OK] Delivery: 100.00% [OK] Stock-Level: 100.00% [OK]
18915.684 TpmC |
性能分析如图:
(2)innodb_max_dirty_pages_pct为75情况下得出的TPmC结果:
[transaction percentage] Payment: 43.48% (>=43.0%) [OK] Order-Status: 4.35% (>= 4.0%) [OK] Delivery: 4.35% (>= 4.0%) [OK] Stock-Level: 4.35% (>= 4.0%) [OK] [response time (at least 90% passed)] New-Order: 100.00% [OK] Payment: 100.00% [OK] Order-Status: 100.00% [OK] Delivery: 100.00% [OK] Stock-Level: 100.00% [OK]
19176.000 TpmC |
性能分析如图:
(3)innodb_max_dirty_pages_pct为90情况下得出的TPmC结果:
[transaction percentage] Payment: 43.48% (>=43.0%) [OK] Order-Status: 4.35% (>= 4.0%) [OK] Delivery: 4.35% (>= 4.0%) [OK] Stock-Level: 4.35% (>= 4.0%) [OK] [response time (at least 90% passed)] New-Order: 100.00% [OK] Payment: 100.00% [OK] Order-Status: 100.00% [OK] Delivery: 100.00% [OK] Stock-Level: 100.00% [OK]
18842.551 TpmC |
性能分析如图:
测试结论
innodb_max_dirty_pages_pct为75时,percona server的性能优于50和90时的性能。从分析结果中可以发现,当innodb_max_dirty_pages_pct为50时,会导致集中的写操作。75是Google提供的一个参数,根据具体的业务情况,该值会有所不同。