DocsICP FilingConsole
官方文档
首页

OLTP performance test method

更新时间：
复制 MD 格式
产品详情
我的收藏

This topic describes a method for testing the OLTP load performance of a PolarDB for PostgreSQL 14 cluster with the SysBench tool, enabling you to run your own tests and comparisons to quickly understand the system's performance.

Test tool

Sysbench is a modular, cross-platform, and multi-threaded benchmark tool that evaluates the performance of a database system under high load based on key parameters. Sysbench allows you to quickly assess database performance without a complex benchmark setup, even if a database is not yet installed.

Test environment

  • The ECS instance and the PolarDB for PostgreSQL cluster are in the same region and zone.

  • Both the ECS instance and the cluster use a VPC network.

    Note

    Ensure that the ECS instance and the PolarDB for PostgreSQL cluster are in the same VPC.

  • The test uses a PolarDB for PostgreSQL cluster with the following configuration:

    • Performance tests for read, write, and mixed read/write scenarios use a cluster that consists of one primary node, one read-only node, and one standby node.

    • The test connects using the primary endpoint. To view the endpoints of your PolarDB for PostgreSQL cluster, see View or request endpoints.

  • The test uses an ECS instance with the following configuration:

    • The instance type is ecs.c5.4xlarge.

    • The image is CentOS 7.6 64-bit.

Test scenarios

This topic describes performance tests for a PolarDB for PostgreSQL 14 cluster (one primary node, one read-only node, and one standby node) in read-only, write-only, and mixed read/write scenarios.

Metrics

Transactions Per Second (TPS): The number of transactions that the database commits per second.

Install sysbench

  • To quickly install sysbench, run one of the following commands:

    • Debian/Ubuntu

      sudo apt -y install sysbench

    • RHEL/CentOS

      sudo yum -y install sysbench

  • To install from source:

    The following example uses RHEL/CentOS. Installation requires root privileges.

    ## Install required dependencies
yum -y install make automake libtool pkgconfig libaio-devel git

## Install MySQL-related dependencies
yum -y install mariadb-devel openssl-devel

## Install PostgreSQL dependencies
yum -y install postgresql-devel

## Download sysbench from Git
git clone https://github.com/akopytov/sysbench.git

## Change to the sysbench directory
cd sysbench

## Run autogen.sh
./autogen.sh

## Configure the build and add support for PostgreSQL
./configure --with-pgsql

## Build the source code
make -j

## Install sysbench
make install

    Run the following command to verify the installation. If the output includes pgsql options, the installation was successful.

    sysbench --help

    The returned pgsql options are as follows:

    Note

    In earlier versions of sysbench, the pgsql options may not include --pgsql-sslmode. This does not affect the test.

    pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
  --pgsql-sslmode=STRING  PostgreSQL SSL mode (disable, allow, prefer, require, verify-ca, verify-full) [prefer]
Note

For binary installation commands for other operating systems, see the sysbench documentation.

Procedure

Prepare the test environment

  1. Add the IP address of the ECS instance to the PolarDB cluster whitelist. For more information, see Configure a cluster whitelist.

  2. Create a privileged account. For more information, see Create a database account.

  3. In the console, create a test database named testdb. Set the Database Owner to the account that you created in the previous step. For more information, see Create a database.

  4. Obtain the primary endpoint of the cluster. For more information, see View or request endpoints.

  5. Use SysBench to test the performance of PolarDB for PostgreSQL.

    The following table describes the key parameters.

    Note

    Replace the placeholder values with your actual cluster details.

    Parameter

    Description

    --pgsql-user

    The username for your PolarDB for PostgreSQL account.

    --pgsql-password

    The password for the PolarDB for PostgreSQL account.

    --pgsql-host

    The primary endpoint of the PolarDB for PostgreSQL cluster.

    --pgsql-port

    The port associated with the primary endpoint of the PolarDB for PostgreSQL cluster.

Quick test

Note

  • This quick test imports 8 tables, each with 500,000 rows, for an estimated total data size of 1 GB.

  • Each test runs for 5 minutes with 256 concurrent threads.

  1. Prepare the data.

    ## Clean up existing data
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_insert cleanup
## Prepare the data
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_insert prepare

  2. Run tests for different scenarios.

    • Mixed read/write scenario

      ## Run the oltp_read_write test
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_read_write run

    • The following three write-only tests run for 5 minutes each.

      • Run the oltp_update_index test.

        sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_update_index run

      • Run the oltp_update_non_index test.

        sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_update_non_index run

      • Run the oltp_write_only test.

        sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_write_only run

    • The following two read-only tests run for 5 minutes each.

      • Run the oltp_point_select test.

        sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_point_select run

      • Run the oltp_read_only test.

        sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_read_only run
Note

If you encounter an error such as connection creation failed during your tests, adjust the threads parameter value based on the maximum number of connections supported by your cluster's node instance type. You can check the maximum number of connections in one of the following ways:

  • Using the console

    1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region of the cluster, and then click the target cluster ID to go to the cluster details page.

    2. On the Basic Information page, in the Database Nodes section, click the icon in the upper-right corner to switch views. Check the Default Maximum Connections column.

  • Using the command line

    SHOW max_connections;

I/O-intensive and CPU-intensive tests

  • I/O-intensive test: Evaluates database performance in I/O-intensive scenarios.

  • CPU-intensive test: Evaluates database performance in CPU-intensive scenarios.

The following test configurations use an 8-core, 64 GB PolarDB cluster as an example:

  • An I/O-intensive test typically requires a dataset twice the size of the buffer pool. Update the sysbench parameters as shown in the following table. Other parameters remain the same as in the Quick test.

    Parameter

    Description

    Value

    --tables

    Number of tables

    8

    --table-size

    Number of rows per table

    64000000

  • A CPU-intensive test typically requires a dataset half the size of the buffer pool. Update the sysbench parameters as shown in the following table. Other parameters remain the same as in the Quick test.

    Parameter

    Description

    Value

    --tables

    Number of tables

    8

    --table-size

    Number of rows per table

    12000000

Next steps

Previous：Performance White PaperNext：PolarDB for PostgreSQL 16