dbi Blog

Today I had the chance to move forwar with performance testing and some checks on two Flexible Server – Azure Database for PostgreSQL, one with 8vCPU, 64GB RAM and 512GB Storage (2300 IOPs) and another one with the same vCPU and Memory, but 2TB Storage (7500 IOPs).

Microsoft told me that they have replaced the used Linux, from Ubuntu 18.04 to Microsoft’s Mariner Linux, Mariner Linux based on Fedora like Amazon Linux 2022.

select version(); was the first surprise.

postgres=> select version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

postgres=>

Mariner Linux 1.0 isu using GCC 9.1, Mariner Linux 2.0 is using GCC 11.2, so for me it looks like Microsoft has copied binaries build on Unbuntu 18.04 with GCC 7.5.0, ist this really true?

In my mind this is a NoGo copying binaries build on a Ubuntu which is EOL end of this month with an old gcc version to a Linux which has a complete different background, Debian based on Ubuntu, RPM based on Fedora. There will be nice discussions with CISOs.

Even if Microsoft is using Ubuntu 18.04 for a new version in customer preview, it should be never GA Ready in fact of EOL of Ubunt 18.04 end of march 2023.

Coming back to the performance topic, in the past the performance of the Flexible Server for PostgreSQL was really bad, and in my mind nothing important changed with V5.

Testing V3, 8 vCores, 64GB, 512GB Storage (2300 IOPs):

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 15.409 ms
initial connection time = 188.984 ms
tps = 1946.934585 (without initial connection time)

Testing V5, 8 vCores, 64GB, 512GB Storage (2300 IOPs):

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 13.840 ms
initial connection time = 192.167 ms
tps = 2167.571937 (without initial connection time)

Testing V3, 8 vCores, 64GB, 2TB Storage (7500 IOPs):

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 13.577 ms
initial connection time = 174.816 ms
tps = 2209.566073 (without initial connection time)

Testing V5, 8 vCores, 64GB, 2TB Storage (7500 IOPs):

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 11.009 ms
initial connection time = 187.248 ms
tps = 2725.049978 (without initial connection time)

So the performance improvement is carefully spoken “visible”, but not worth to take the extra cost for it, it is more or less that what you see as difference is CPU benchmarks.

Espacially when it will be compared with an own virtual machine. In this testcases it was RHEL 8.7 using PotstgreSQL Community Repository also PostgreSQL 14, but not 14.6, it was 14.7.

RHEL 8.7 VM, 8vCores, 64GB RAM, one 512GB P40 SSD (7500 IOPs):

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 5.678 ms
initial connection time = 20.319 ms
tps = 5283.651417 (without initial connection time)

RHEL 8.7 VM, 8vCPU, 64GB RAM, two 512GB P40 SSD (7500 IOPs each) as a 128K Stripeset:

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 2.783 ms
initial connection time = 19.620 ms
tps = 10772.428231 (without initial connection time)

RHEL 8.7 VM, 8vCPU, 64GB RAM, four 512GB P40 SSD (7500 IOPs each) as a 128K Stripeset:

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 30
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 300000/300000
latency average = 2.369 ms
initial connection time = 21.567 ms
tps = 12660.990295 (without initial connection time)

The conclusion is clear in my mind, if you want to use PostgreSQL on Microsoft Azure operate you own VMs, than you have the things under control Microsoft did not have.

Or give the dbi YAK solution a chance to do it for you:

https://www.dbi-services.com/products/yak/