Note: the content of the article originated from the network and was obtained through our own experiment; however, the reference article address was not recorded at that time. If you find any infringement problem, please leave a message.
Stress test means that before MySQL goes online, a large number of stress tests need to be carried out to reach the delivery standard. Stress testing can not only test the stability of MySQL services, but also test the bottlenecks of MySQL and the system.
TPCC test: Transaction Processing Performance Council. TPC is a series of transaction processing and database benchmark specifications. TPC-C is a benchmark model for OLTP. On the one hand, it can measure the performance of database, on the other hand, it can measure the cost performance of hardware. It is also a widely used and concerned test model.
TPC-C test model provides a unified test standard for benchmark test, which is not the real test result in the actual application system, but through the test results, we can roughly observe a series of problems such as MySQL database service stability, performance and system performance. TPC-C is only a test model, but in the actual test, we need to refer to and use some test tools to test the system and MySQL database for stress and stability.
TPC-C model is a model designed with an online retail industry as an example. Refer to the specific architecture diagram
tpcc test database data scale
tpcc test database data table diagram
2, Build install
1. Download address
2. Modify program to support MGR
If you are testing the MGR mode, you need to manually modify the contents related to the history table, because the table does not have a primary key, while the MGR requires that the table must have a primary key: otherwise, you can skip.
# unzip tpcc-mysql-master.zip
1) In the table creation statement, add a new primary key column:
# vim tpcc-mysql-master/create_table.sql
Create a table statement for the history table at about line 62; add a statement to establish the primary key id:
create table history ( id bigint not null auto_increment, -- newly added id Autoincrement h_c_id int, h_c_d_id tinyint, h_c_w_id smallint, h_d_id tinyint, h_w_id smallint, h_date datetime, h_amount decimal(6,2), h_data varchar(24), PRIMARY KEY (id) -- New primary key(Note comma separation from previous line) ) Engine=InnoDB;
2) Modify INSERT statement
# vim tpcc-mysql-master/src/load.c
About 234 lines are the INSERT statement of the history table, which is modified as follows:
"INSERT INTO history values(null,?,?,?,?,?,?,?,?)", 48) ) goto Error_SqlCall_close;
Add a null value to the primary key column id when inserting, so that the id can increase automatically;
Change the number recording the character number verification of the original INSERT statement from 43 to 48;
After the above 2 is not modified, you can continue to perform compilation.
3. Decompress and compile
# cd tpcc-mysql-master/src/ # Make (no make install)
After successful execution, TPCC load and TPCC start commands are generated in the TPCC MySQL master decompression directory
4. View libmysqlclient.so.xx library file
The current version of tpcc must use the mysql library file of libmysqlclient.so.xx.
mysql5.6 is libmysqlclient.so.18
mysql5.7 is libmysqlclient.so.20
There are two settings:
Method 1: libmysqlclient.so.20 of mysql5.7
# cat /etc/ld.so.conf include ld.so.conf.d/*.conf # echo "/usr/local/mysql-5.7.24/lib/" >> /etc/ld.so.conf # cat /etc/ld.so.conf include ld.so.conf.d/*.conf /usr/local/mysql-5.7.24/lib/ # /sbin/ldconfig -v
Mode 2: libmysqlclient.so.18 of mysql5.6
# ls -l /usr/lib64/libmysqlclient.so*
If it does not exist, an error will be reported when the TPCC load and TPCC start commands are executed later.
Put the copy of libmysqlclient.so.18 in the lib directory under the Mysql program directory into the system library and grant permissions (you can also specify the file address of the library in the PATH of the environment variable):
# mv libmysqlclient.so.18 /usr/lib64/ # chmod 777 /usr/lib64/libmysqlclient.so.18
After the test, delete the library file
# rm /usr/lib64/libmysqlclient.so.18
3, Load test data
Enter the working directory (the extracted directory)
# cd tpcc-mysql-master
1. Create a test database on the target MySQL
mysqladmin -uroot -p -S /data/s1/mysql.sock create tpcc1000
2. Create table
mysql -uroot -p -S /data/s1/mysql.sock tpcc1000 < create_table.sql
tpcc creates nine test tables:
Customer: customer table
Region: district Table
Historical order: history table
New orders: new orders table
Order status: order line table
orders: orders table
Stock status: stock table
Warehouse: warehouse table
The business logic of TPCC MySQL and its related table functions are as follows:
New order = = > a new order is a complete order transaction involving almost all tables
Payment = = > payment, mainly corresponding to the orders and history tables
Order status = = > order status, mainly corresponding to the orders and order line tables
Delivery = = > delivery, mainly corresponding to the order line table
Stock level = = > stock, mainly corresponding to stock table
3. Add foreign key to table
mysql -uroot -p -S /data/s1/mysql.sock tpcc1000 < add_fkey_idx.sql
4. Load test data
# ./tpcc_load --help Usage: tpcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
For example: create 5 warehouses and load the test data of all tables
# ./tpcc_load -h 127.0.0.1 -P 3306 -d tpcc1000 -u root -p '123456' -w 5
1) This process is a little slow. One warehouse corresponds to 10 regions, one region corresponds to 3000 users, and the size of 10 warehouses just imported is about 1G;
2) The number of warehouses can be set according to the needs. According to the summary of online data, setting 40-100 is a test of CPU, and 400-1000 is a test of IO. No matter how many transactions are below 40, lock competition is not easy to happen;
3) In the real test scenario, the number of warehouses is generally not recommended to be less than 100, depending on the hardware configuration of the server. If SSD or PCIE SSD is equipped with high IOPS devices, it is recommended to be at least 1000.
If you want to load test data in parallel, you can use the load.sh script.
4, Perform tests
1, Non MGR multi main mode pressure test
./tpcc_start -h127.0.0.1 -P 3306 -dtpcc1000 -uroot -p '123456' -w 5 -c 32 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx
-h: Test host
-d: Database tested
-u: Users tested
-p: Test user's password
-w: Number of warehouse s tested
-c: Number of connection threads tested
-r: Warm up time, warm up time, in seconds. The default is 10 seconds. The purpose is to load data into memory
-l: Test time, default is 20 seconds
-i: Report \ interval specifies the interval between reports
-f: Report? File output the records of various operations in the test to the specified file for saving
-t: TRX file output more detailed operation information to the specified file
In the real test scenario, it is recommended that the preheating time be no less than 5 minutes, and the duration of continuous pressure test be no less than 30 minutes, otherwise the test data may not have reference significance.
2. MGR multi main mode pressure measurement
Due to the limitation and deficiency of MGR in the support of large transactions and the detection of transaction conflicts, it is impossible to test MGR directly in parallel. Oracle's official multi primary mode test is on each node,
In this way, it can avoid the problem that tools can not be pressed in parallel, and reduce the possibility of conflict. Remember, multi primary mode must avoid the scene of hot data conflict.
For example, there are three nodes in the MGR cluster, A, B and C respectively. Then, at least three libraries need to be built during the pressure test. In this way, each tpcc pressure test uses one thread to test one library and starts multiple tpcc to test at the same time.
You can test this:
./tpcc_start -h126.96.36.199 -P3306 -uroot -p '123456' -d tpcc1 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
./tpcc_start -h188.8.131.52 -P3306 -uroot -p '123456' -d tpcc2 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
./tpcc_start -h184.108.40.206 -P3306 -uroot -p '123456' -d tpcc3 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
However, the multi primary mode compression test will not have a good result, because the hot spots are too centralized, which will lead to many submission failures, and may lead to performance degradation.
5, Result analysis
[root@localhost tpcc-mysql-master]# ./tpcc_start -h127.0.0.1 -P24801 -dtpcc1000 -uroot -p '' -w 5 -c 32 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx *************************************** *** ###easy### TPC-C Load Generator *** *************************************** option h with value '127.0.0.1' option P with value '24801' option d with value 'tpcc1000' option u with value 'root' option p with value '' option w with value '5' option c with value '32' option r with value '10' option l with value '30' option i with value '10' option f with value 'tpcc_mysql.log' option t with value 'tpcc_mysql.rtx' <Parameters> [server]: 127.0.0.1 [port]: 24801 [DBname]: tpcc1000 [user]: root [pass]: [warehouse]: 5 [connection]: 32 [rampup]: 10 (sec.) [measure]: 30 (sec.) RAMP-UP TIME.(10 sec.) MEASURING START. -- After preheating, start pressure test -- Set to output pressure test data every 10 seconds 10, trx: 1500, 95%: 204.109, 99%: 430.483, max_rt: 666.515, 1502|936.479, 150|225.372, 151|995.094, 151|650.192 20, trx: 1349, 95%: 191.559, 99%: 458.275, max_rt: 659.284, 1342|842.605, 134|191.606, 136|981.992, 135|522.363 30, trx: 1211, 95%: 209.746, 99%: 519.825, max_rt: 766.855, 1213|987.708, 122|142.544, 121|1085.795, 121|386.247 //Description: it is divided into 6 items, including operation time (seconds), order creation, order payment, order status query, logistics delivery and inventory storage query 10, ==>Time of test(second) trx: 1500, ==>The number of new order transactions executed during a given interval; it is basically throughput per interval, the higher the value, the better. 95%: 204.109,==>95% of each given interval％New order transaction response time of, here is 204.109 Seconds. 99%: 430.483,==>99% of each given interval％New order transaction response time of, here is 430.483 Seconds. max_rt: 666.515,==>Maximum response time for each new order transaction at a given interval, here is 666.515 Seconds. 1502|936.479, 150|225.372, 151|995.094, 151|650.192==>Is the throughput and maximum response time of other types of transactions, which can be ignored.(Order payment, order status query, logistics delivery and inventory query) STOPPING THREADS................................ -- End of pressure test <Raw Results>  sc:0 lt:4060 rt:0 fl:0 avg_rt: 86.8 (5)  sc:1 lt:4056 rt:0 fl:0 avg_rt: 167.6 (5)  sc:345 lt:61 rt:0 fl:0 avg_rt: 8.6 (5)  sc:0 lt:408 rt:0 fl:0 avg_rt: 449.4 (80)  sc:15 lt:392 rt:0 fl:0 avg_rt: 132.6 (20) in 30 sec. -- Statistical description of the first result  ==>New-Order，New order business succeeded(success,Shorthand sc)Times, delay(late,Shorthand lt)Times, retrying(retry,Shorthand rt)Times, failed(failure,Shorthand fl)Number of times; a complete order transaction involving almost all tables.  ==>Payment，Payment business statistics, others are the same as above, mainly corresponding to orders,history Table;  ==>Order-Status，Order status statistics, others are the same as above, mainly corresponding to orders,order_line Table;  ==>Delivery，Shipment business statistics, others are the same as above, mainly corresponding to order_line Table;  ==>Stock-Level，Inventory business statistics, others are the same as above, mainly corresponding to stock Table; <Raw Results2(sum ver.)>  sc:0 lt:4060 rt:0 fl:0  sc:1 lt:4060 rt:0 fl:0  sc:345 lt:61 rt:0 fl:0  sc:0 lt:408 rt:0 fl:0  sc:15 lt:392 rt:0 fl:0 <Constraint Check> (all must be [OK]) -- All business logic results below must be OK Only then [transaction percentage] Payment: 43.45% (>=43.0%) [OK] -- Payment success times(In the above statistical results sc + lt)Must be greater than 43.0%，Otherwise, the result is NG，instead of OK Order-Status: 4.35% (>= 4.0%) [OK] -- Order status, other as above Delivery: 4.37% (>= 4.0%) [OK] -- Delivery, other as above Stock-Level: 4.36% (>= 4.0%) [OK] -- Inventory, other as above [response time (at least 90% passed)] -- Response time indicator must exceed 90%Only if it passes New-Order: 0.00% [NG] * -- All of the following response time-consuming indicators failed Payment: 0.02% [NG] * Order-Status: 84.98% [NG] * Delivery: 0.00% [NG] * Stock-Level: 3.69% [NG] * <TpmC> 8120.000 TpmC -- TpmC Result value (number of transactions per minute, which is the number of new order transactions in the first statistics result divided by the total time-consuming minutes)