阿里巴巴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 11:59:43
参数设置
其他参数的设置为:
innodb_additional_mem_pool_size =256 innodb_log_buffer_size = 512M innodb_buffer_pool_size = 32G innodb_max_dirty_pages_pct=75 innodb_thread_concurrency=16 |
测试结果及分析
测试innodb_log_file_size分别为1G、4G下,percona server的性能。
(1)innodb_log_file_size为1G情况下得出的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]
24216.666 TpmC |
结果分析如图:
(2)innodb_log_file_size为4G情况下得出的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]
27191.732 TpmC |
性能分析如图:
测试结论
innodb_log_file_size为4G时,percona server的性能优于1G时的性能。从分析结果中可以发现,当为1G时,会出现多次checkout的问题,而在4G时,性能更平稳。根据percona的推荐,该值设为innodb_buffer_pool_size的25%。该值仅仅在percona server下修改才有效,在mysql server下,不支持大于4G的情况(是所有log文件的大小不大于4G,而非单个文件)。