Home > Storage > PowerFlex > White Papers > Deploying PostgreSQL on Dell PowerFlex > PostgreSQL performance tests
This section describes the tests that were performed to validate the PostgreSQL database performance on-premises with the two-layer PowerFlex system deployment.
The benchmarking tool that is used for these workloads is pgbench, which is designed for PostgreSQL database performance testing. By running predefined patterns of database transactions and queries, pgbench creates a workload that mimics the behavior of intensive real-world applications. This workload allows database administrators and developers to gauge the performance of their PostgreSQL instance under different scenarios, identify bottlenecks, and make informed decisions about system optimizations. For more information about pgbench tool and its options see, pgbench and pgbench-options.
The following use cases have been tested:
The first use case, which is a 100% read workload, is used to create a high system stress while monitoring the PowerFlex storage IOPS and latency metrics. This test demonstrates the storage system ability to service read-only transactions while maintaining high IOPS and low latencies.
The second use case, which is a mixed workload of read and write transactions, is a more common database activity pattern for most applications. This test demonstrates the storage system ability to service common mixture of read and write transactions while maintaining high IOPS and low latencies.
The goal of both test cases is to stress the storage subsystem by keeping both host and database cache small, and therefore generating high level of storage I/O requests.
Two PostgreSQL databases are created in each of the three compute only nodes and loaded with 300 GB of data, for a total of six databases and 1.8 TB of data. The test parameters that are used for each database workload are identical, where the IOPS metric is a sum of the database volumes IOPS as reported by PowerFlex, and the latency metrics are the average latencies for the run, as reported by PowerFlex.
In use case 1, where read-only workload is used, only one PostgreSQL database in each of the three nodes is used. Due to the higher IOPS and resulting CPU demand, it saturated the resources in the compute nodes and there was no need to add the second database workload in each node.
In use case 2, where a mixed workload is used, both PostgreSQL databases in each of the three nodes are used. As a mixed workload has higher demand on database resources, both databases were used before the compute nodes were saturated.
It should be noted that the PowerFlex storage resources were capable of sustaining the high workload from each use case while maintaining sub millisecond latency, demonstrating its ability to support the additional load.
Each database is loaded with data before the tests are performed as shown in the following example:
# pgbench -U postgres -i -s 20000 mydb1
# pgbench -U postgres -i -s 20000 mydb2
For the database load, a scale factor of 20,000 is used to generate 300 GB of data as shown in the following figure:
Figure 8. PostgreSQL database capacity after loading data
Pgbench uses the -S option for running a SELECT query, or a read-only workload. The number of clients and threads are variables that can be changed based on available system resources. The following example shows the pgbench parameters that were used to run each of the PostgreSQL databases:
# pgbench -U postgres -c 320 -j 90 -T 6000 -S mydb1 -p 5436
Figure 6 shows the test results. The test demonstrated the ability of the system to support 1,142,401 IOPS (1.14 million IOPS) while maintaining a sub millisecond read latency in this test environment.
Figure 9. Performance graph for read-only workload
The following figure shows the results of the PostgreSQL read-only workload from the PowerFlex Manager dashboard:
Figure 10. PowerFlex 4 dashboard for read-only workload
Pgbench uses the -S -N option for running 50 percent read and 50 percent write workload. The test runs 50 percent of the UPDATE and 50 percent of the SELECT queries on the database. The number of clients and threads are variables, that can be changed based on the available system resources. The following example shows the pgbench parameters that were used to run each of the PostgreSQL databases:
# pgbench -U postgres -c 250 -j 80 -T 1200 -N -S mydb1 -p 5436
# pgbench -U postgres -c 250 -j 80 -T 1200 -N -S mydb2 -p 5438
For this test, two postgres instances are running at port 5436 and port 5438, each instance is running with 250 client connections and 80 threads and performance is measured under sub millisecond latency.
The following figure shows the results of a mixed workload. The test demonstrated the ability of the system to support 521,000 IOPS (~521 K IOPS) while maintaining a sub millisecond read/write latency in this test environment.
Figure 11. Performance graph of mixed workload
The following figure shows results of PostgreSQL read/write workload from the PowerFlex Manager dashboard:
Figure 12. PowerFlex dashboard for mixed workload