Database selection MySQL (iii)

Liu Yong Email: [email protected]

This blog record author in work and research experience, on the one hand to their work and life leave a mark, on the other hand if you can help, then fortunately even!

Introduction

Because the high-frequency Central Library task part occupies more machines, to save costs, investigate the database or cache. MySQL (ii) in the database selection, local access to MySQL on the SSD can meet the requirements of its 10,000/s operation, because there are multiple varieties (multi-process, multi-threaded access to the database) business requirements in the real environment, therefore, In this paper, we use multi-thread to access MySQL locally in the SSD to provide practical reference for the post-structure adjustment of high-frequency Central library. It should be noted that the author of this paper is not in favor of this cost-saving solution.

test Environment

Hardware environment

10.1.120.34:intel Core i5-4590, Frequency: 3.30G, Memory: 16G, Solid State Drive

Software Environment:

10.1.120.34:cent OS 6.5, MySQL 5.6.26 (Community Edition)

Performance Testing

In view of the application demand of high frequency production, this paper constructs the data structure of the high Frequency Central library system, uses the multithreaded simulation business requirement to write the local node MySQL, and stores the total data as 60K, 100K, 600K data, and tests its rate. It should be noted that because the bottleneck of common I/O access is mainly limited to write testing, this article only tests for write operations, regardless of read operation or mixed read and write, if the write operation does not meet the requirements, other operations do not need to be tested.

Because the 10.1.120.34 on the SSD as storage media, it is installed with MySQL, according to the application scenario, from 10, 20, 30 threads concurrently access MySQL deployment test.

Test the entire result as shown in Figure 1:

Figure-1 Complete test results

10 Threads

Transaction processing from 10 threads, it should be noted that the amount of transaction data fixed to 1000, the following multithreading is similar, no longer repeat, the average rate of 10 threads see table-1.

Table-1 10 threads per thread average access to MySQL test results

node	average write rate (bar/s)
local Node		60
local Node		100
Local Node	10.1.120.34	600	3021

20 Threads

Transactions are performed from 20 threads, and the average rate of 20 threads is shown in table-2.

Table-2 20 threads per thread average access to MySQL test results

node	database IP	average write rate (bar/s)
local Node		60
local Node		100
Local Node	10.1.120.34	600	1525

30 Threads

Transactions are performed from 30 threads, and the average rate of 30 threads is shown in table-3.

Table-3 30 threads per thread average access to MySQL test results

Node	Database IP	Data Volume (K)	Average write rate (bar/s)
Local Node	10.1.120.34	60	1266
local Node		100
Local Node	10.1.120.34	600	879

Summary

From table 1-3:1) as the number of threads increases, the average thread write rate decreases, and it is obvious; 2) as the amount of data accessed increases, the average access rate for each thread is significantly lower, as is the case for 100,000 to 600,000.

The test results in this paper provide some practical reference for the subsequent architecture adjustment of the high-frequency center library, if the average rate is multiplied by the number of threads, the high-frequency production is satisfied with the demand, but in case of imbalance, the data can be produced in the local storage of the solid-state drive to save cost. Then with Redis as the cache to alleviate access to the system access pressure of the solution, the author is not in favor of this scheme, I hope to have similar business needs of friends to help.

Appendix:

Test part of the program source code:

1  Public Static voidMain (string[] args) {2         3Transaction [] ts =NewTransaction[threadtest.max];4Test []test =NewTest[threadtest.max];5         intSymbol = 100000;6          for(inti = 0; i < ts.length; i++) {7Ts[i] =NewTransaction (NULL,8"",9"",Ten"010000", One                     NewBigDecimal (15.857). Setscale (3, roundingmode.half_up), A                     NewBigDecimal (18.550). Setscale (3, roundingmode.half_up), -                     NewBigDecimal (13.147). Setscale (3, roundingmode.half_up), -                     NewBigDecimal (16.383). Setscale (3, roundingmode.half_up), the                     NewBigDecimal (0.151). Setscale (3, roundingmode.half_up), -                     NewBigDecimal (1.550). Setscale (3, roundingmode.half_up), -                     NewBigDecimal (5000000). Setscale (3, roundingmode.half_up), -                     NewBigDecimal (500000000). Setscale (3, roundingmode.half_up), + System.currenttimemillis (), -"SSE"); +Test[i] =NewTest (); A              at         } -  -          -runthread[] Thread =NewRunthread[threadtest.max]; -          for(inti = 0; i < thread.length; i++) { - Test[i].initmysql (); inThread[i] =NewRunthread (ts[i], symbol, test[i]); -Symbol + = threadtest.num*1000; to         } +          -          for(inti = 0; i < thread.length; i++) the Thread[i].start (); *          $          while(true) {Panax Notoginseng             Try { -Thread.Sleep (1000); the}Catch(interruptedexception e) { + e.printstacktrace (); ATest[0].down (); the             } +         } -          $}

View Code

Database selection MySQL (iii)