Testing MySQL

1. Access the run directory of BenchmarkSQL, and then modify the configuration file according to the actual situation.

   1 2 3

   cd /home/benchmarksql5.0-for-mysql/run cp props.conf my_mysql.properties vi my_mysql.properties

2. When editing the configuration file, modify the following content:
   

   • Parameters for importing data: warehouses (data volume) and loadWorkers (concurrency).
   • Parameters for the pressure test: runMins (execution time) and terminals (concurrency).
   • The runMins and runTxnsPerTerminal parameters specify two running modes. runMins specifies the running time, and runTxnsPerTerminal specifies the number of transactions on each terminal. The two parameters cannot be enabled at the same time, and one of them must be set to 0.

   Table 1 Database information

   Parameter	Value	Description
   conn	192.168.222.120	Address of the database server. Set this parameter based on the actual situation.
   	3306	Port number of the MySQL database. Set this parameter based on the actual situation.
   	tpcc	Database name. Set this parameter based on the actual situation. In this example, the database is named as tpcc.
   user	root	Set this parameter to the user name that creates the tpcc database.
   password	123456	Set this parameter to the password used for creating the tpcc database.
   warehouses	1000	Number of data warehouses to be created during data loading of initialization. For example, 200 indicates that 200 data warehouses are created. The data volume of each data warehouse is about 76,823.04 KB. The data volume may fluctuate slightly because existing records will be inserted or deleted during the test.
   loadworker	100	Number of processes submitted each time when data is loaded.

   Table 2 Program running parameters

   Parameter	Description
   terminals	Number of concurrent terminals, indicating the concurrency level. Values 140, 170, and 200 are traversed.
   runTxnsPerTerminal	Number of transactions executed by each terminal per minute.
   runMins	Execution duration, in minutes. For example, 15 minutes. This parameter is used together with terminals.
   limitTnxsPermin	Total number of transactions executed per minute.
   terminalWarehouseFixed	Binding mode of terminals and warehouses. If this parameter is set to true, the 4.x compatibility mode is used (each terminal has a fixed warehouse). If this parameter is set to false, the terminals can randomly access the warehouses.

3. Press Esc, type :wq!, and press Enter to save the file and exit.

1. Run the following command in the run directory of BenchmarkSQL to grant the execute permission on the file:

   1	chmod 777 *.sh

   

   If the required permission has been granted, skip this step.

2. Load data.

   1	./runDatabaseBuild.sh my_mysql.properties

   Create and initialize nine tables (bmsql_warehouse, bmsql_stock, bmsql_item, bmsql_order_line, bmsql_new_order, bmsql_history, bmsql_district, bmsql_customer, and bmsql_oorder) and one configuration table (bmsql_config).

   

   • If "Cannot locate SQL file for extraHistID" is reported after data is imported, ignore it because it does not affect the performance test.
   • If the following error message is displayed, run vi my_mysql.properties to open the file, add serverTimezone=UTC to the file, and run the ./runDatabaseBuild.sh my_mysql.properties command again:

     ERROR: The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to       use a more specifc time zone value if you want to utilize time zone support.

1

./runBenchmark.sh my_mysql.properties

After the test is complete, the value of tpmC (NewOrders) is used as the test indicator.

In the database performance test, tpmC (NewOrders) is a common performance indicator that measures the number of transactions created per minute. A larger value indicates a stronger transaction processing capability of the database server and higher overall performance.