ApsaraDB RDS:Performance testing reports of the fully encrypted database feature

Test environment

Test tool (sysbench)

sysbench is a modular, cross-platform, and multi-threaded benchmark tool that can be used to evaluate the performance of a heavily loaded database system based on core metrics. For more information about sysbench and how to use sysbench, see sysbench documentation.

Performance metrics

• Transactions per second (TPS): the number of transactions that are successfully committed on an RDS instance per second.

• Average latency: the average period of time that is required for an RDS instance to execute a transaction. Unit: milliseconds.

Test schema

CREATE TABLE test1(
  id INTEGER NOT NULL,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
);
CREATE INDEX k_1 on test1(k);

Test procedure

In this test, table columns are encrypted to analyze the impacts of the fully encrypted database feature on the performance of an RDS instance in different scenarios.

1. Install the PostgreSQL client on the ECS instance. For more information, see Official documentation.

2. Open the PostgreSQL command-line tool (CLI) and run the following command to connect to the RDS instance:

   psql -h <Endpoint of the RDS instance> -U <Username> -p <Port number> -d postgres

   Note

   • For more information about how to obtain the endpoint and port number of an RDS instance, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

   • For more information about how to obtain the username, see Create an account.

   • You can also use Data Management (DMS) to connect to an RDS instance in the ApsaraDB RDS console. For more information, see Use DMS to log on to an ApsaraDB RDS for PostgreSQL instance.

3. Create a test database.

   CREATE DATABASE testdb;
   \c testdb

4. Create an encrypted table and encrypt columns based on different scenarios.

   Scenario	Description	Example SQL statement
   The primary key column is not encrypted. Other columns are encrypted.	In most scenarios, id is an auto-increment field and irrelevant to business. In this test scenario, the id column is not encrypted. Other columns are encrypted as sensitive information.	CREATE TABLE test1( id INTEGER NOT NULL, k enc_int4 DEFAULT '\xa509008855508aade16ec573d21e6aca47ab5e490d7044e748161a6635a5d939c5bbbee4' NOT NULL, c enc_text DEFAULT '\x9d39006e340b9cffb37a989ca544e69e6e9c0bdb0b6500a91dcc433dc5df0496' NOT NULL, pad enc_text DEFAULT '\x9d39006e340b9cffb37a989ca544e69e6e9c0bdb0b6500a91dcc433dc5df0496' NOT NULL, PRIMARY KEY (id) ); CREATE INDEX k_1 on test1(k);
   All columns are encrypted.	In this test scenario, the primary key column is also encrypted.	CREATE TABLE test1( id enc_int4 NOT NULL, k enc_int4 DEFAULT '\xa509008855508aade16ec573d21e6aca47ab5e490d7044e748161a6635a5d939c5bbbee4' NOT NULL, c enc_text DEFAULT '\x9d39006e340b9cffb37a989ca544e69e6e9c0bdb0b6500a91dcc433dc5df0496' NOT NULL, pad enc_text DEFAULT '\x9d39006e340b9cffb37a989ca544e69e6e9c0bdb0b6500a91dcc433dc5df0496' NOT NULL, PRIMARY KEY (id) ); CREATE INDEX k_1 on test1(k);

5. Exit the RDS instance and use sysbench to load the test data.

   Note

   This test uses sysbench to load 32 tables. Each table contains one million rows of records.

   sysbench --db-driver=pgsql \
     --pgsql-host=[database server host] \
     --pgsql-port=[database server port] \
     --pgsql-user=[database user name] \
     --pgsql-password=[database user password] \
     --pgsql-db=testdb \
     --auto_inc=false \
     --table_size=1000000 \
     --tables=32 \
     --threads=32 \
     --time=600 \
     --pg_use_encrypt=[true or false] \
     oltp_common prepare

6. Run the custom test script.

   sysbench --db-driver=pgsql \
     --pgsql-host=[database server host] \
     --pgsql-port=[database server port] \
     --pgsql-user=[database user name] \
     --pgsql-password=[database user password] \
     --pgsql-db=testdb \
     --table_size=1000000 \
     --tables=32 \
     --threads=XXX \
     --time=600 \
     --report-interval=1 \
     --pg_use_encrypt=[true or false] \
     [lua script name] run

7. Delete data.

   sysbench --db-driver=pgsql \
     --pgsql-host=[database server host] \
     --pgsql-port=[database server port] \
     --pgsql-user=[database user name] \
     --pgsql-password=[database user password] \
     --pgsql-db=testdb \
     --tables=32 \
     oltp_common cleanup

Test scenario 1: The primary key column is not encrypted. Other columns are encrypted.

Test data

• Performance of point queries

  Percentage	SQL template
  100% Note The percentage indicates the proportion of SQL statements that are defined in the SQL template and executed in a transaction to the total number of SQL statements that are executed in the transaction.	SELECT c FROM test1 WHERE id=?;

  

  Table 1 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	46.99	39.94	15.0%
  16	91.07	79.86	12.3%
  24	132.78	117.64	11.4%
  32	173.58	154.28	11.1%

• Performance of range queries

  Percentage	SQL template
  100%	SELECT SUM(k) FROM test1 WHERE id BETWEEN ? AND ?; Note By default, the length of a range query is 100.

  

  Table 2 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	14.81	12.09	18.3%
  16	28.88	23.22	19.6%
  24	41.62	33.12	20.4%
  32	54.27	42.48	21.7%

• Write performance

  Percentage	SQL template
  25%	UPDATE test1 SET k=k+1 WHERE id=?;
  25%	UPDATE test1 SET c=? WHERE id=?;
  25%	DELETE FROM test1 WHERE id=?;
  25%	INSERT INTO test1 (id, k, c, pad) VALUES (?, ?, ?, ?);

  

  Table 3 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	6.62	6.37	3.8%
  16	11.36	10.91	4.0%
  24	15.05	14.68	2.6%
  32	19.13	17.74	7.3%

Test conclusion

In this test scenario, if you enable the fully encrypted database feature, the performance loss is low.

Test scenario 2: All columns are encrypted.

Test data

• Performance of point queries

  Percentage	SQL template
  100%	SELECT c FROM test1 WHERE id=?;

  

  Table 4 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	46.99	18.31	61.0%
  16	91.07	35.66	60.8%
  24	132.78	49.85	62.5%
  32	173.58	63.34	63.5%

• Performance of range queries

  Percentage	SQL template
  100%	SELECT SUM(k) FROM test1 WHERE id BETWEEN ? AND ?; Note By default, the length of a range query is 100.

  

  Table 5 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	14.81	2.83	80.9%
  16	28.88	5.52	80.9%
  24	41.62	7.12	82.9%
  32	54.27	8.71	84.0%

• Write performance

  Percentage	SQL template
  25%	UPDATE test1 SET k=k+1 WHERE id=?;
  25%	UPDATE test1 SET c=? WHERE id=?;
  25%	DELETE FROM test1 WHERE id=?;
  25%	INSERT INTO test1 (id, k, c, pad) VALUES (?, ?, ?, ?);

  

  Table 6 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Performance loss
  8	6.62	2.67	59.7%
  16	11.36	4.62	59.3%
  24	15.05	5.92	60.7%
  32	19.13	7.04	63.2%

Test conclusion

In this test scenario, you must decrypt columns on the client. In addition, Intel SGX-related commands are more frequently run to query the indexes of encrypted columns. As a result, the total cost and performance loss are higher than those in Test scenario 1.

Test scenario 3: Non-primary key columns are queried.

In this test scenario, the encdb_btree extension provided by Alibaba Cloud is used to improve the efficiency of ciphertext index-related operations in fully encrypted databases. For more information, see Use the encdb_btree extension.

Test data

• Point query

  Percentage	SQL template
  100%	SELECT c FROM test1 WHERE k=?;

  

  Table 7 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Fully encrypted database feature enabled and the encdb_btree extension used
  8	47.27	18.49	27.86
  16	90.41	35.85	54.4
  24	132.66	49.97	78.21
  32	172.96	63.32	99.38

• Range query

  Percentage	SQL template
  100%	SELECT SUM(k) FROM test1 WHERE k BETWEEN ? AND ? Note By default, the length of a range query is 100.

  

  Table 8 1,000 TPS comparison

  Number of concurrent threads	Fully encrypted database feature disabled	Fully encrypted database feature enabled	Fully encrypted database feature enabled and the encdb_btree extension used
  8	14.97	2.83	7.8
  16	28.89	5.53	15.2
  24	42.38	7.15	20.09
  32	54.8	8.75	24.86

Test conclusion

In this test scenario, the query performance on non-primary key columns is similar to the query performance on primary key columns. After the fully encrypted database feature is enabled, you can use the encdb_btree extension to improve the query performance of ciphertext data.

Usage suggestions

We recommend that you enable the fully encrypted database feature to encrypt only sensitive data columns.