Key elements of MySQL high concurrency performance

The flowers are fine.

Blog: http://blog.csdn.net/qing_gee

Other sites, if reproduced, please be sure to indicate the source!

Foreword : Recently has been doing project performance optimization, experienced a one-week detour, and finally found a breakthrough, after and colleagues (thank you very much, later he will write the process in his blog, I will connect the address keep abreast to this blog) efforts, The performance of our futures trading platform has been well improved on the service side.

First we take a look at my computer (i3-3220,4g memory, no configuration innodb_buffer_pool_size parameters, the performance of the program, after 1 threads-8 Threads test results) environment, you can see the following diagram, thread concurrency case, Time continues to climb, which makes people good distress, said the high performance, high concurrency?

Results:

But after one weeks of effort, inadvertently, I found on the colleague's computer, the same code test, the time and my is not an equal level, much faster than my computer, his computer (other conditions equivalent, CPU is i3-4150), 8 items 8000 pending orders can reach 135ms, First of all, the CPU upgrade is very effective for program processing, but still in the case of high concurrency, performance is very poor.

Today, by comparison to the MySQL configuration file My.ini, inadvertently, we found that innodb_buffer_pool_size in 8M and 48M configuration, performance is simply not a level, OK, then continue to investigate it, the final conclusion

Innodb_buffer_pool_size=2ginnodb_buffer_pool_instances=1

A detailed explanation can be seen in the following introduction

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and# row data. The bigger you set this and the less disk I/O is needed to# access data in tables. On a dedicated database server, set this# parameter up to 80% of the machine physical memory size. Do not set it# too large, though, because competition of the physical memory may# cause paging in the operating System.
   note that on 32bit systems you# might is limited to 2-3.5g of the user level memory per process, so does not# set it too high.

simply put, it is pool-size can cache index and row data, the higher the value, IO read and write less, if simply do database service, this parameter can be set to the computer physical memory of 80%, wow, this parameter is simply fantastic. After a lot of testing, we found that innodb_buffer_pool_size=2g and Innodb_buffer_pool_instances=1when the parameters are set, the effect is optimal. OK
What happened please, under this parameter, 20 items, each under 1000 single, average time in 55ms, don't tease me, MySQL, why didn't you tell me earlier.

Therefore, it is recommended that MySQL, in high concurrent read and write situations, and for INNODB data tables, set

Innodb_buffer_pool_size=2ginnodb_buffer_pool_instances=1

Summary: The performance improvement is long and takes a lot of time, we are only doing the first step, and then we need to do more.

Key elements of MySQL high concurrency performance