[dev.icinga.com #10953] Allow multiple connections to the database

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/10953

Created by tobiasvdk on 2016-01-11 21:11:25 +00:00

Assignee: (none)
Status: Closed (closed on 2016-12-08 00:39:21 +00:00)
Target Version: (none)
Last Update: 2016-12-08 00:39:21 +00:00 (in Redmine)

Backport?: Not yet backported
Include in Changelog: 1

---

Allow a configurable amount of connections to the database. At least for PostgreSQL the performance significantly increases when using multiple database connections, e.g.

After creating a "pgbench" database, initialize the tables in it:

$ pgbench -i pgbench
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.05 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

Now run a single client test for 20 seconds:

$ pgbench -T 20 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 24724
latency average: 0.809 ms
tps = 1236.158960 (including connections establishing)
tps = 1236.364622 (excluding connections establishing)

Using four client connections simultaneously the performance is nearly three times as using a single connection:

$ pgbench -T 20 -c 4 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 20 s
number of transactions actually processed: 71298
latency average: 1.122 ms
tps = 3564.759014 (including connections establishing)
tps = 3565.421354 (excluding connections establishing)

---

Relations:

• relates #10953

[icinga-migration]

Updated by mfriedrich on 2016-01-12 16:56:39 +00:00

Currently especially queries during the config dump require a sequential order of updates as well as returned insert / sequence ids. That will cause trouble when using connection pools allowing SQL queries in parallel.

[icinga-migration]

Updated by tobiasvdk on 2016-01-26 20:28:47 +00:00

How about using a library, e.g. http://www.tildeslash.com/libzdb/ or http://www.tntnet.org/howto/tntdb.html?

[icinga-migration]

Updated by mfriedrich on 2016-01-29 09:31:33 +00:00

We need a sequential order for specific queries depending on the previous one (UPDATE -> INSERT, the infamous UPSERT not available in older MySQL/PostgreSQL releases, or the last_insert_id(), get_sequence() for config object relations). There is a reason we haven't been able to use connection pools with Icinga IDOUtils 1.x and the same problem applies here. Unless someone changes the schema and its dependencies I don't see any option to use connection pools and parallel query execution.

[icinga-migration]

Updated by tobiasvdk on 2016-01-29 13:45:13 +00:00

dnsmichi wrote:

We need a sequential order for specific queries depending on the previous one (UPDATE -> INSERT, the infamous UPSERT not available in older MySQL/PostgreSQL releases, or the last_insert_id(), get_sequence() for config object relations). There is a reason we haven't been able to use connection pools with Icinga IDOUtils 1.x and the same problem applies here. Unless someone changes the schema and its dependencies I don't see any option to use connection pools and parallel query execution.

Can't transactions be used for this?

[icinga-migration]

Updated by mfriedrich on 2016-03-24 11:20:36 +00:00

There are currently no plans to implement this in the near future.

Kind regards,
Michael

[icinga-migration]

Updated by tobiasvdk on 2016-08-18 19:57:44 +00:00

• Relates set to 12110

[icinga-migration]

Updated by mfriedrich on 2016-11-11 08:46:58 +00:00

• Parent Id deleted 10073

[icinga-migration]

Updated by mfriedrich on 2016-12-08 00:39:21 +00:00

• Status changed from New to Closed