MySQL Performance Test Experience

I. BACKGROUND

Recently in the reconfiguration of resource scheduling management platform, Resource/property database design, in the absence of more optimized scheme, intends to follow the database structure of the current platform, which needs to analyze the database structure of the current platform, and test and analyze its performance. The resulting data results are used as a reference for subsequent design and optimization.

Second, the test method

1. Test tools: MYSQLSLAP,MYSQLSLAP is the benchmark benchmark tool that comes with the MySQL5.1.4, which simulates multiple clients concurrently issuing query updates to the server, gives performance test data, and provides performance comparisons for a variety of engines. Official Introduction: Http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html,
How to use: http://my.oschina.net/moooofly/blog/152547

2, test process: Establish a test database and the table to be tested tables→ based on the structure of the table, using the script to generate a certain number of valid random data → use mysqlslap to test the corresponding query statement → results data analysis.

3, the key statement: Considering the actual application of the resource platform, through the Resource Attribute query resource operation as the main operation, and this kind of operation time takes the total operation time-consuming proportion is the biggest, therefore corresponds to this kind of operation the query statement is the key statement, to the entire database performance influence is very big, We can evaluate the performance of the entire database by testing the results of this key statement.

Third, the test process 1, the establishment of the table:

Querying resources by property requires two tables: Device table devices and device properties correspond to table deviceattr. The device table corresponds to the resource, records the basic information of the resource, such as Name,type,group,creator, and so on; the Deviceattr table corresponds to the resource and attribute, and records each property and attribute value for each resource, and their structure is as follows:

By querying the Deviceattr table with multiple attribute combinations, the ID is the device ID that satisfies the condition, which can be queried directly in the device table for its basic properties.

The key statement of this test is the combination of the Deviceattr table from the query to meet the criteria of the ID, so this test only need to use deviceattr a table.

2. Generate random data and insert the appropriate table:

Here are several SQL functions that generate random data rand_name (), Rand_value (), Rand_num (), Rand_creator (), and a stored procedure insert_devattr, Used to bulk insert a certain amount of data into a deviceattr table.

It then writes the entire build and stored data into an SQL script and runs the script in MySQL, and the tables and data to be tested are set up. (SQL script in the appendix) as shown in, the generated deviceattr random data has 4,020:

3, the use of MYSQLSLAP test:

The key operation for the entire database is to query the resource through the property, and the key SQL statement for that operation is:

select id from deviceattr where [ n attr ] group by id；

Example: Query the ID of all resources that have Attr10 or ATTR20 properties:

select id from deviceattr where name = ‘attr10’ or name = ‘attr20’ group by id;

After establishing the database to be tested, the MYSQLSLAP can be used for testing, and the MYSQLSLAP command is as follows:

shell < mysqlslap --create-schema=‘test1‘ --query="select id from deviceattr where name=‘attr10‘ or name=‘attr20‘ group by id;" -c 50 -i 100

The above statement means: Use the Test1 database, use the statement specified by query, test 50 concurrent queries, each query 100 times.

The test results for this statement are as follows:

The result is an average time-consuming, time-consuming, minimal time-consuming, number of concurrent threads, and so on for executing statements.

Iv. Analysis of results

In the different data capacity, the different concurrency number, the different query attribute number the condition carries on the multiple tests and takes the average, the data obtained is divided into the following several situations to analyze:

1, the data capacity of the Deviceattr table is 4,000, the concurrency number is 1, 5, 10, 20, 40, 80, the query attribute number is 2, 3, 4, 5, 6, the time of the query statement:

As can be seen from the above table, in the same conditions of query properties, the concurrency increases by one times, time-consuming is also substantially increased by one-time, the query takes less than 100 milliseconds when the number of 40 concurrency, and in the actual application of the 10 concurrency, time is about 40 milliseconds; performance basically meets the requirements of the new resource platform.

2, in 10 concurrency, the deviceattr table capacity of 3000, 4000, 8,000, query the number of attributes from 2 to 6, the time-consuming query statement:

As can be seen from the above table, in the case of a certain number of concurrency, with the increase in the number of query attributes in the statement, time-consuming linear increase, this linear relational data capacity independent.

3, in the case of 10 concurrency, the query attribute number is 5, different data capacity, the query statement time-consuming:

As can be seen from the above table, in other conditions, with the growth of data capacity, time-consuming linear growth, in close to the actual conditions (4000 data capacity, about 10 concurrent number, 5 or so query properties), time is 25 milliseconds, to meet the needs of the new resource management platform.

Appendix:

SQL script to generate test data:

Use Test1drop tableif exists Device,attr,deviceattr;drop functionif exists Rand_name;drop functionif exists Rand_value;drop functionif exists Rand_num;drop functionIf exists Rand_creator;drop procedureIf exists Insert_devattr;drop procedureif exists Insert_dev;CREATE tableDevice (Idint primary Key,name varchar (), typevarchar(aquired),Int,groupnamevarchar(creator),varchar40));CREATE tableattrIdint primary Key,name varchar (40));CREATE tableDeviceattr (IdInt,name varchar (40),Valuevarchar));d Elimiter//Create functionRand_creator () returnsvarchar) Begindeclare Return_strvarchar20)Default ' Aronhe ';d eclare nIntDefault0;Set n = Floor (rand () *10);Case Nwhen0 ThenSet return_str =' Aronhe ';1 ThenSet return_str =' Eeelin ';2 ThenSet return_str =' Shadowyang ';3 ThenSet return_str =' Luzhao ';4 ThenSet return_str =' Tommyzhang ';5 ThenSet return_str =' Pillarzou ';6 ThenSet return_str =' Allenpan ';7 ThenSet return_str =' Beyondli ';8 ThenSet return_str =' Minshi ';9 ThenSet return_str =' Bingchen ';ElseSet return_str =' Joyhu '; endCaseReturn Return_str;end//Create functionRand_num () returnsIntbegindeclare NIntDefault 0;Set n = Floor (rand () *100);Return N;end//Create functionRand_value () returnsvarcharTen) Begindeclare Return_strvarchar10)Default 'False ';d Eclare nIntDefault0;Set n = Floor (rand () *10);Casewhen n<5 ThenSet return_str =' false '; when n>5 ThenSet return_str =' True ';ElseSet return_str =' True '; endCaseReturn Return_str;end//Create functionRand_name () returnsvarchar) Begindeclare Return_strvarchar20)Default ';Set return_str = Concat (' attr ', Floor (rand () *200));Return Return_str;end//CREATE PROCEDUREInsert_devattr (In StartIntIn Maxint) Begindeclare IIntDefault 0;repeatSet i=i+1;InsertInto deviceattrValuesRand_num (),Rand_name (),Rand_value ()); until I =maxend repeat;end//CREATE PROCEDUREInsert_dev (In StartIntIn Maxint) Begindeclare IIntDefault 10;repeatset i=i+1;  INSERT into device values (i,concat (' Runner ', Floor (rand () *100)), ' PC ', floor(rand () * *), ' PCQQ ',rand_creator ()); until I =maxend repeat;end//call insert_devattr (0,4000)// Call Insert_dev (one, each)//           

Experience with MySQL performance testing