Use Swingbench to test the performance of Oracle databases

1. Introduction to swingben 

Currently, open-source Oracle stress testing tools on the network are mainly orabm and swingbench. Because orabm does not support oracle 11g, this test uses swingben for stress testing. In addition, swingbench can also test rac. Swingbench is UK based oracle Database Solutions group developed an oracle stress testing tool, it seems to be an official waste of a project, the official page on the http://dominicgiles.com/swingbench.html can download the latest software version.

Swingbench can run on windows and linux platforms. This test uses the linux platform. The specific process is as follows:
1. Use oewizard to import test data. You can import data as prompted.
2. Set the environment variable java version to 1.6 or above, and the connection string to 10. 4. ×. ×: 1521: ×
Export PATH =/monitor/agent_12c/core/12.1.0.1.0/jdk/bin/: $ PATH
Export DISPLAY = 10. 6. *. *: 0.0
Cd/monitor/swingbench/bin/
3. Use the swingbench homepage for stress testing

2. Stress Test Results

The server type is an IBM X3755M3 server with 4 CPU x 8 core and GB memory.The test results show that the peak value reaches 0.48 million TPMC, And the tps is close to 10 thousand.At this time, the CPU usage of the Database Host is close to 95%.

Top-15:26:34 up 6 days, 2 users, load average: 189.06, 121.48, 58.92

Tasks: 909 total, 36 running, 873 sleeping, 0 stopped, 0 zombie

Cpu (s): 79.1% us, 12.9% sy, 0.0% ni, 6.5% id, 0.2% wa, 0.1% hi, 1.2% si, 0.0% st

 

Appendix:

Test parameters of A and swingbench

1. users on swingbench GUI: the number of users (threads) that attach to a database and the amount and tye of work they perform. users can dynamically monitor the response times and load which is displayed in a series of graphs.
Here, users controls the number of users connected to oracle at the same time. We know that every user connected to oracle will be assigned a PGA, so here it should be understood as a degree of parallelism.
2. min/max think time: Minimum/large time for thinking between each transaction. If min think time is set, the specified time interval is used between each transaction.
3. max trans: If set, the maximum number of transactions is limited.
4. the top transaction panel: load: Indicates the "weight" of the transaction in comparison to othe transactions. A higher weight indicates that it more likely to be run.
This Panel can be used to cancel some fixed transaction types. The load column is mainly used to adjust the weights of some transactions in the test. For example, browse product is mainly a select statement, which can increase its weight to indicate more people to query.
Several parameters in oewizard:
Number of Customers: Number of users pre-loaded to the database table.
Number of Orders: Number of Orders pre-loaded to the database table.
The whole OE test is based on nine tables. Is the test result different when oewizard is used to pre-load different data volumes?
For oracle itself, there is no difference in the performance of indexed tables when the size is certain, but when the number of rows in a table reaches a certain level, such as hundreds of millions of rows, the index performance is not as good as the full table scan performance. Therefore, for the scope allowed by OE, I think the table data size has no significant impact on performance.
Swingbench is a stress testing tool. The result tpmc also indicates the number of transactions per minute. If more data is pre-loaded and all types of weights in TX are fixed, adjust the number of parallel users to obtain an optimal tpms value. According to my previous test results, the parallel user is fixed, and the more data is pre-loaded, the smaller the result of the tpmc is, I am also confused. After careful analysis, I found that, the number of parallel users should be modified accordingly.
By modifying the weight of each transaction type in the TX panel, you can also obtain some oracle performance parameters, such as increasing the query, if the value of tpmc is similar, this shows that the query capability of this database is good.

B. Introduction to TPMC

According to the definition of TPC, the traffic indicator describes how many New-Order transactions can be processed per minute while the system executes the four types of transactions: Payment, Order-status, Delivery, and Stock-Level. The response time for all transactions must meet the requirements of the TPC-C test specification.
The larger the traffic indicator value, the better!
TPMC calculation basis
To facilitate the calculation of the shape of the database server, we agree:
"The number of simultaneous online users of the system is 1500 (U1 );
"On average, each user sends two business requests per minute (N1 );
"The system generates 1/3 of the Business requests for updates, queries, and statistics;
"Three transactions (T1) are generated each time the service is updated on average );
"An average of 8 transactions (T2) are generated for each query );
"An average of 13 transactions (T3) are generated for each statistical service );
"The amount of processing during busy hours in a day is 5 times of the average;
"The empirical coefficient is 1.6 (actual engineering experience)
"Considering that the server retains 30% redundancy;
The server requires the following processing capabilities:
TPC-C = U1 * N1 * (T1 + T2 + T3)/3*3 * empirical coefficient/redundancy Coefficient
The processing performance of the application server is estimated to be:
TPC-C = 1500*2*(3 + 8 + 13)/3*5*1.6/0.7 = 274,285 tpmC
The database server is related to the stable operation of the entire system. Considering the high reliability and availability, and pay attention to the scalability and cost-effectiveness of the equipment, the system will configure two high-performance database servers with a TPC-C value not less than 0.28 million.