Testing PostgreSQL

create database tpcc;

1. Access the run directory of BenchmarkSQL and open the configuration file.

   1 2 3

   cd /home/benchmarksql5.0-for-postgresql/run/ cp props.pg my_postgres.properties vi my_postgres.properties

2. Modify the parameters in Table 1 and Table 2 in the configuration file. Use the default values for other parameters.

   Table 1 Database parameters

   Parameter	Value	Description
   conn	localhost	Database server address. Set the value according to the actual situation.
   	5432	PostgreSQL database port. Set the value according to the actual situation.
   	postgres	Database name (tpcc in this example). Set the value according to the actual situation.
   user	-	Set this parameter to the user that creates the database as required.
   password	-	Set this parameter to the password of the user that creates the database as required.
   warehouses	-	Number of data warehouses to be created during data loading of initialization. For example, the value 100 indicates that 100 data warehouses are created. The data volume of each data warehouse is about 76,823.04 KB. The data volume may be slightly different because data entries may be added or deleted during the test.
   loadworker	-	Number of processes submitted each time when data is loaded.

   Table 2 Program running parameters

   Parameter	Description
   terminals	Number of concurrent terminals, which the concurrency level.
   runTxnsPerTerminal	Number of transactions executed by each terminal per minute.
   runMins	Execution duration, in minutes.
   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.

   

   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.

   

1. Run the following command in the run directory to grant the execution 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_postgres.properties

   Create and initialize nine tables (warehouse, Stock, Item, Order-Line, New-Order, History, District, Customer, and Order) and one configuration table.

   

   • If the message "there is no unique constraint matching given keys for referenced table" is displayed during data loading, rectify the fault by referring to Automatic Index Creation and Foreign Key Constraint Creation Are Prohibited During the PostgreSQL Test..
   • If a large amount of data is loaded, for example, when 500 warehouses data is loaded, the error message "FATAL: sorry, too many clients already" is displayed. You can rectify the fault by modifying parameters in the postgresql.conf file. For details, see "FATAL: sorry, too many clients already" Displayed When Loading Data for Initialization During PostgreSQL Test.

1

./runBenchmark.sh my_postgres.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.