Key elements of mysql high concurrency performance and mysql concurrency Elements

Key elements of mysql high concurrency performance and mysql concurrency Elements

Author: fallen flower fragrance

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

If other websites are reprinted, please be sure to indicate the source!

Preface: I have been optimizing project performance recently. After more than a week of detours, I finally found a breakthrough. After that, I am very grateful to my colleagues, later, he will write the process in his blog, and I will post the connection address to this blog). Our futures trading platform's performance has been greatly improved on the server.

First let's take a look at my computer (i3-3220, 4G memory, without configuring the innodb_buffer_pool_size parameter, put aside the program performance, after a thread-8 thread test results) in this environment, we can see the figure below. In the case of thread concurrency, the time is constantly rising. This is a great headache. What about high performance and high concurrency?

Result:

However, after more than a week of hard work, I accidentally found on my colleagues' computers that after the same code test, the time was not of the same magnitude as mine, it is much faster than my computer, his computer (other conditions are equivalent, the CPU is the i3-4150), 8 Goods 8000 hanging orders can reach 135 ms, first of all, it indicates that the CPU upgrade is very effective for program processing, but it is still very poor in the case of high concurrency.

To this day, compare the mysql configuration file my. ini. Inadvertently, we found that the innodb_buffer_pool_size is in the 8 m and 48 m configurations, and the performance is simply not in the same level. OK, so proceed to the investigation and come to the following conclusion:

innodb_buffer_pool_size=2Ginnodb_buffer_pool_instances=1

For more information, see the following.

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and# row data. The bigger you set this the less disk I/O is needed to# access data in tables. On a dedicated database server you may 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 be limited to 2-3.5G of user level memory per process, so do not# set it too high.

In short, pool-size can cache indexes and row data. The larger the value, the less I/O reads and writes, this parameter can be set to 80% of the computer's physical memory. Wow, this parameter is amazing. Then, after a lot of tests, we found that Innodb_buffer_pool_size = 2 GB and innodb_buffer_pool_instances = 1In the case of parameter settings, the effect is optimal. OK
Please let me know what happened. The average time is 55 ms for each of the 20 products under this parameter and 1000 orders per product. Don't tease me, mysql. Why don't you tell me earlier.

Therefore, we recommend that you set the following parameters when mysql reads and writes data in high concurrency and is an innodb data table.

innodb_buffer_pool_size=2Ginnodb_buffer_pool_instances=1

Summary: The performance improvement is long and takes a lot of time. At present, we have only done the first step, and we need to do more.