Test Methodology for Percona TDE Performance Test

As mentioned in the main blog posting „Performance Test for Percona Transparent Data Encryption (TDE)“, here’s a detailed explanation for the TDE performance test.

Hardware Details

This test was run on a dedicated physical server, to avoid external influences and fluctuations from virtualization.

The server has a Intel(R) Xeon(R) Gold 5412U CPU with 48 cores, 256 GB RAM, and a 2 TB SAMSUNG MZQL21T9HCJR NVram disk dedicated for the tests (OS was running on a different disk).

The dedicated disk is XFS formatted, and uses the following mount parameters: rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,sunit=256,swidth=256,noquota.

Server is running Ubuntu 24.4, with a Linux kernel version 6.8.0.

PostgreSQL Settings

The following relevant settings have been applied for each test:

1
2
3
4
5
6
7

shared_buffers = 16GB
work_mem = 1GB
max_wal_size = 16384
log_min_messages = 'debug3'
og_min_error_statement = 'debug3'
log_filename = 'logfile-%Y-%m-%d_%H%M%S.log'
logging_collector = 'on'

Additionally, the tests with Percona PostgreSQL did use:

1

shared_preload_libraries = 'pg_tde'

The memory settings are in line with recommended performance tuning. These settings have been applied equally to all tests. I ran initial tests to figure out which shared_buffers, work_mem, and max_wal_size showed good results, and settled on the above settings.

Database logfiles are rotated after each test, and copied along with the server settings and result files. This allowed investigation of problems later on.

Tests

All tests used pgbench and were run in a loop, the following values were changed between tests:

• Scale Factor (for pgbench): 5, 10, 50, 100, 400, 600, 1000, 2000, 4000, 5000
• Number clients (for pgbench): 1, 2, 5, 10, 20, 30, 40, 50, 60, 70
• Read-only / Read-write

The number of transitions was increased to 1000 for all tests, from the default of 10. Initial results have shown fewer fluctuations in numbers when this number is increased.

Each test ran five (5) rounds with the pgbench r/w test and five rounds with the read-only test. The best number from each set was selected.

One set of tests was run with an unmodified PostgreSQL 17, one set of tests with Percona PostgreSQL 17 with TDE version 1.0-rc with table encryption and without WAL encryption, and one additionally set with WAL encryption.

The database (not the cluster) was dropped and re-created before each single test, the time to initialize and encrypt the tables is not taken into account. For the test with encrypted tables, the encryption of all tables was tested before each test, which discovered a bug early on. The WAL encryption was switched on for the third round of tests, however there is not yet a function to verify the encryption. Given that this is a setting which requires a restart, it was not deemed critical for the test to verify WAL encryption every time.

Additionally, after initializing and analyzing the tables for each test, and doing all checks, the test did wait for the system load to drop down to 0.2, plus an additional 30 seconds. This guaranteed equal system load at the beginning of each test.

Performance Graphs

pgbench scale factor

During the tests, different pgbench scale factors have been used to measure different database sizes.

A scale factor of 1 equals roughly a data size of 13 MB:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

pgbench -i -s 1 test
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 0.15 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.03 s).

\dt+
                                        List of relations
 Schema |       Name       | Type  | Owner | Persistence | Access method |  Size   | Description
--------+------------------+-------+-------+-------------+---------------+---------+-------------
 public | pgbench_accounts | table | test  | permanent   | heap          | 13 MB   |
 public | pgbench_branches | table | test  | permanent   | heap          | 40 kB   |
 public | pgbench_history  | table | test  | permanent   | heap          | 0 bytes |
 public | pgbench_tellers  | table | test  | permanent   | heap          | 40 kB   |
(4 rows)

The scale factor is nearly linear:

The scale factor 5000 creates a roughly 62.5 GB large dataset.

Graphs per scale factor

We see that the graph is slowing down starting at around 30 clients, and more or less flattening out at around 50 clients. From there on increasing the number of clients does not increase the number of transactions.

This correlates with the number of cores (‘48’) in the server. A second server was not available to move the pgbench tests to a separate host.

This affects both the TDE tests as well as the tests with the regular PostgreSQL.

Recommendations and Identified Bugs

While performing the tests, I made a number of recommendations to Percona for improvements (mostly from a user/DBA perspective), and found some issues.

PG-1213: User function to check if WAL is encrypted

Make it possible for the DBA to check that WAL is encrypted.

Bugtracker: PG-1213

PG-1215: Allow schema name in pg_tde_is_encrypted()

Previously pg_tde_is_encrypted() only worked on the first found table in search_path. It was not possible to specify a table together with a schema name.

Bugtracker: PG-1215

PG-1217: Bug when changing table access method

While enabling encryption using ALTER TABLE … SET access method tde_heap an error was raised:

1

ERROR:  invalid page in block 0 of relation base/16384/16437

This only happens when using ALTER TABLE, not when the table is created with tde_heap right away.

This bug was fixed shortly after.

Bugtracker: PG-1217

PG-1243: Performance issues at specific threshold

The cleared hint bits caused a severe performance issue. The root cause was identified and fixed.

Bugtracker: PG-1243

PKG-314: Better versions (timestamps) for builds

Previously it was not clear which exact version is currently installed.

Bugtracker: PKG-314

PKG-535: db_stop script is missing

db_stop script was missing in the postinstall (postinst) script. This caused the database to remain running while the package was deinstalled. The issue was fixed.

Bugtracker: PKG-535

Comments

With an account on the Fediverse or Mastodon, you can respond to this post. Known replies are displayed below:

Load Comments from Fediverse

Note: This will load data from mastodon.social.

Learn how this is implemented here.