IDO PSQL Performance and tuning

Hi all,

I just found out that PGSQL can perform generally better than MariaDB/MySQL, and this difference becomes more and more noticeable while handling larger amount of data with complex queries (4 joins or more). So I just tried to compare some DBMS in a very large test environment.

The DBMS are: MariaDB 10.3, MariaDB 10.5 and PGSQL 12.
Following logs become from Icinga2 2.11.6 because this is the version we dispatch to our customers, but similar behavior happens with Icinga2 2.12.

First, I noticed that the general throughput of an IdoPgsqlConnection is significantly lower than the one of an IdoMysqlConnection: during the initial reconnecting phase, IdoMysqlConnection is from 30% to 80% faster.

[2021-04-15 12:54:30 +0200] information/IdoMysqlConnection: 'ido-mysql' resumed.
[2021-04-15 12:54:30 +0200] information/IdoMysqlConnection: 'ido-mariadb' resumed.
[2021-04-15 12:54:30 +0200] information/IdoPgsqlConnection: 'ido-pgsql' resumed.
[2021-04-15 12:54:30 +0200] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-15 12:54:30 +0200] information/IdoMysqlConnection: Last update by endpoint '' was 1.61848e+09s ago. Taking over 'ido-mariadb' in HA zone 'master'.
[2021-04-15 12:54:30 +0200] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-15 12:54:30 +0200] information/IdoPgsqlConnection: Last update by endpoint '' was 1.61848e+09s ago. Taking over 'ido-pgsql' in HA zone 'master'.
[2021-04-15 12:54:30 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-15 12:54:40 +0200] information/IdoMysqlConnection: Pending queries: 59933 (Input: 5690/s; Output: 0/s)
[2021-04-15 12:54:40 +0200] information/IdoMysqlConnection: Pending queries: 36638 (Input: 6577/s; Output: 3019/s)
[2021-04-15 12:54:40 +0200] information/IdoPgsqlConnection: Pending queries: 49251 (Input: 6215/s; Output: 1508/s)
[2021-04-15 12:54:50 +0200] information/IdoMysqlConnection: Pending queries: 238194 (Input: 17495/s; Output: 0/s)
[2021-04-15 12:54:50 +0200] information/IdoMysqlConnection: Pending queries: 194374 (Input: 18318/s; Output: 2743/s)
[2021-04-15 12:54:50 +0200] information/IdoPgsqlConnection: Pending queries: 217681 (Input: 18035/s; Output: 1466/s)
[2021-04-15 12:55:00 +0200] information/IdoMysqlConnection: Pending queries: 438220 (Input: 18088/s; Output: 0/s)
[2021-04-15 12:55:00 +0200] information/IdoPgsqlConnection: Pending queries: 407952 (Input: 18616/s; Output: 1433/s)
[2021-04-15 12:55:00 +0200] information/IdoMysqlConnection: Pending queries: 373016 (Input: 18923/s; Output: 2782/s)
[2021-04-15 12:55:10 +0200] information/IdoMysqlConnection: Pending queries: 549573 (Input: 18746/s; Output: 2800/s)
[2021-04-15 12:55:10 +0200] information/IdoPgsqlConnection: Pending queries: 595208 (Input: 18470/s; Output: 1535/s)
[2021-04-15 12:55:10 +0200] information/IdoMysqlConnection: Pending queries: 635789 (Input: 17906/s; Output: 0/s)
[2021-04-15 12:55:20 +0200] information/IdoPgsqlConnection: Pending queries: 647773 (Input: 6161/s; Output: 1485/s)
[2021-04-15 12:55:20 +0200] information/IdoMysqlConnection: Pending queries: 698474 (Input: 5613/s; Output: 0/s)
[2021-04-15 12:55:20 +0200] information/IdoMysqlConnection: Pending queries: 589779 (Input: 6489/s; Output: 2918/s)
[2021-04-15 12:55:30 +0200] information/IdoMysqlConnection: Pending queries: 759118 (Input: 5579/s; Output: 0/s)
[2021-04-15 12:55:30 +0200] information/IdoMysqlConnection: Pending queries: 628364 (Input: 6444/s; Output: 2880/s)
[2021-04-15 12:55:30 +0200] information/IdoPgsqlConnection: Pending queries: 697910 (Input: 6140/s; Output: 1521/s)
[2021-04-15 12:55:40 +0200] information/IdoMysqlConnection: Pending queries: 820989 (Input: 5704/s; Output: 0/s)
[2021-04-15 12:55:40 +0200] information/IdoPgsqlConnection: Pending queries: 749597 (Input: 6270/s; Output: 1536/s)
[2021-04-15 12:55:40 +0200] information/IdoMysqlConnection: Pending queries: 668235 (Input: 6589/s; Output: 2947/s)
[2021-04-15 12:55:50 +0200] information/IdoPgsqlConnection: Pending queries: 802492 (Input: 6408/s; Output: 1477/s)
[2021-04-15 12:55:50 +0200] information/IdoMysqlConnection: Pending queries: 680600 (Input: 11666/s; Output: 26139/s)
[2021-04-15 12:55:50 +0200] information/IdoMysqlConnection: Pending queries: 709910 (Input: 6702/s; Output: 2795/s)

Second, the throughput of the IdoPgsqlConnection is always stuck in the same range (in my environment, from 900 to 1500), while IdoMysqlConnection can scale to tenths of thousands.

[2021-04-15 17:27:29 +0200] information/IdoMysqlConnection: Pending queries: 7359176 (Input: 1455/s; Output: 23220/s)
[2021-04-15 17:27:29 +0200] information/IdoPgsqlConnection: Pending queries: 6971634 (Input: 1456/s; Output: 830/s)
[2021-04-15 17:27:39 +0200] information/IdoMysqlConnection: Pending queries: 7375740 (Input: 1575/s; Output: 23215/s)
[2021-04-15 17:27:39 +0200] information/IdoMysqlConnection: Pending queries: 7375686 (Input: 1575/s; Output: 23220/s)
[2021-04-15 17:27:39 +0200] information/IdoPgsqlConnection: Pending queries: 6978189 (Input: 1576/s; Output: 957/s)
[2021-04-15 17:27:49 +0200] information/IdoMysqlConnection: Pending queries: 7392126 (Input: 1535/s; Output: 23215/s)
[2021-04-15 17:27:49 +0200] information/IdoPgsqlConnection: Pending queries: 6984851 (Input: 1536/s; Output: 909/s)
[2021-04-15 17:27:49 +0200] information/IdoMysqlConnection: Pending queries: 7392072 (Input: 1535/s; Output: 23220/s)
[2021-04-15 17:27:59 +0200] information/IdoMysqlConnection: Pending queries: 7408178 (Input: 1501/s; Output: 23215/s)
[2021-04-15 17:27:59 +0200] information/IdoMysqlConnection: Pending queries: 7408124 (Input: 1501/s; Output: 23220/s)

Since the VM hosting PGSQL has no iowait and its load is really low, the bottleneck most likely is not PGSQL.
In large environment, when load increase the Pending queries queue can increase its size very fast, and as a result Icinga2 can go Out-Of-Memory, which is not a good option.

Can you help me understanding what is going wrong and increase the output of an IdoPgsqlConnection?

Some details about the test environment:

  • Icinga2 server:
    14 vCPU, 48GB RAM; OS is Centos 7; it runs Icinga2 2.11.6 with Icingaweb 2 2.8.2, MariaDB 10.3.13 and InfluxDB 1.8
  • MariaDB 10.5 server:
    4 vCPU, 16GB RAM; OS is Centos 7; it runs only MariaDB 10.5.9
  • PGSQL server:
    4 vCPU, 16GB RAM; OS is Centos 7; it runs only PGSQL 12.6.1

Icinga contains 30k mock hosts (check interval is 60 seconds) with 9 services each (check interval is 300 seconds). Each service is a ping to localhost. To handle everything MaxConcurrentChecks has been set to 1500, leaving nothing overdue.

Best regards
Rocco