8.3. Calculating Server Capacity #
You can calculate the maximum number of analytical queries per second that a server can process by dividing:
the available CPU cores by the average CPU consumption per query
the storage read throughput by the average amount of data read per query
The smallest result is the limit.
The examples below illustrate this calculation using the results of benchmark tests performed by Postgres Pro. Both examples assume that the server has the following characteristics:
32 CPU cores
128 GB of RAM
10 Gbit network
The total amount of data is 10 TB in a local storage with an array of NVMe RAID-0 disks and 4 GB/s read throughput or in an S3 storage with 500 MB/s read throughput.
Example 8.1.
ClickBench benchmark test (scale factor 100) with analytical queries evenly distributed over time:
Average CPU consumption per query: 7 seconds.
Average RAM consumption per query: 500 MB.
Average amount of data read per query: 900 MB.
The limits are as follows:
The CPU limit is reached at 32 / 7 = 4.5 queries per second.
The local storage read throughput limit is reached at 4 / 0.9 = 4.4 queries per second.
The S3 storage read throughput limit is reached at 0.5 / 0.9 = 0.5 queries per second.
Therefore, the server can process up to 4.4 analytical queries per second with the local storage or up to 0.5 analytical queries per second with the S3 storage.
Example 8.2.
TPC-H benchmark test (scale factor 100) with analytical queries evenly distributed over time:
Average CPU consumption per query: 34 seconds.
Average RAM consumption per query: 1.4 GB.
Average amount of data read per query: 6.8 GB.
The limits are as follows:
The CPU limit is reached at 32 / 34 = 1 query per second.
The local storage read throughput limit is reached at 4 / 6.8 = 0.6 queries per second.
The S3 storage read throughput limit is reached at 0.5 / 6.8 = 0.07 queries per second.
Therefore, the server can process up to 0.6 analytical queries per second with the local storage or up to 0.07 analytical queries per second with the S3 storage.
In both examples, the RAM consumption by analytical queries is low compared to the available RAM, so the remaining RAM can be used for caching Parquet files using the operating system file cache or the pgpro_axe cache.