Test Method

Updated on 2022-09-22 GMT+08:00

PostgreSQL is an open-source object-relational database management system with an emphasis on extensibility and standards compliance. It is known as the most advanced open-source database. It excels in processing complex online transaction processing (OLTP) transactions and supports NoSQL (JSON, XML, or hstore) and geographic information system (GIS) data types. It has earned a reputation for reliability and data integrity, and is widely used for websites, location-based applications, and complex data object processing.

  • RDS for PostgreSQL supports the postgis plugin and provides excellent spatial performance.
  • RDS for PostgreSQL is suitable for various scenarios and is cost-effective. You can flexibly scale resources based on your service requirements and pay for only what you use.

Test Environment

  • ECS: general computing | c3.2xlarge.2 | 8 vCPUs | 16 GB, CentOS7.4 64 bit image. Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.
    NOTE:

    The test environment for RDS for PostgreSQL 12 is as follows:

    • ECS: general computing-plus | c6.4xlarge.2 | 16 vCPUs | 32 GB, CentOS 7.6 (64 bit). Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.

Test Tool

Sysbench is a multi-threaded benchmark tool based on LuaJIT. It is most frequently used for database benchmarks. With sysbench, you can quickly get an impression of database performance. For details, visit https://github.com/akopytov/sysbench.

Sysbench 1.0.12 is used as an example. Run the following commands to install it:

#wget -c https://github.com/akopytov/sysbench/archive/1.0.12.zip

#yum install make automake libtool pkgconfig libaio-devel postgresql-devel

#unzip 1.0.12.zip

#cd sysbench-1.0.12

#./autogen.sh

#./configure --with-pgsql --without-mysql

#make

#make install

NOTE:

The test tool for RDS for PostgreSQL 12 and RDS for PostgreSQL 13 is as follows:

Test Procedure

Replace the database name, connection IP address, and user password based on the site requirements.

  1. Import data.

    1. Run the following commands to log in to a database and create the test database loadtest:

      psql -h<host> -p5432 "dbname=postgres user=root password=<password>" <<TEST

      create database loadtest;

      TEST

    2. Run the following command to import the test background data to the loadtest database:

      sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --num-threads=20 prepare

  2. Run the following command to perform a stress testing:

    sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=64 --report-interval=3 --forced-shutdown=1 run

  3. Run the following command to delete the test data:

    sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=200 cleanup

Testing Model

  1. Table structure:

    CREATE TABLE `sbtest` (

    `id` INTEGER IDENTITY(1,1) NOT NULL,

    `k` INTEGER DEFAULT '0' NOT NULL,

    `c` CHAR(120) DEFAULT '' NOT NULL,

    `pad` CHAR(60) DEFAULT '' NOT NULL,

    PRIMARY KEY (`id`)

    )

  2. Read/write ratio:
    The default transaction submitted by sysbench contains 18 SQL statements. The details are as follows:
    • Ten primary key SELECT statements:

      SELECT c FROM ${rand_table_name} where id=${rand_id};

    • Four range SELECT statements:

      SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};

      SELECT SUM(K) FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};

      SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;

      SELECT DISTINCT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;

    • Two UPDATE statements:

      UPDATE ${rand_table_name} SET k=k+1 WHERE id=${rand_id}

      UPDATE ${rand_table_name} SET c=${rand_str} WHERE id=${rand_id}

    • One DELETE statement:

      DELETE FROM ${rand_table_name} WHERE id=${rand_id}

    • One INSERT statement:

      INSERT INTO ${rand_table_name} (id, k, c, pad) VALUES (${rand_id},${rand_k},${rand_str_c},${rand_str_pad})

Test Metrics

  • TPS refers to the number of transactions executed per second by a database. Each transaction contains 18 SQL statements.
  • QPS refers to the number of SQL statements, including INSERT, SELECT, UPDATE, and DELETE statements, executed per second.
Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback