[Kde-pim] akonadi: release unused connections to qpsql

[Daniel Vrátil]

On Sunday 09 of November 2014 12:34:49 you wrote:
> Hello,

Hi,

(CC'ing kde-pim mailing list, so that other devs can know about this)

> I've been trying akonadi from KDE SC 4.12.5 last weeks, trying to optimize
> it for my usage.
> 
> In short, I want to use the postgres driver, be independent of mysql. This
> brings a few design problems with akonadi.
> 
> One major issue is that akonadi keeps too many persistent connections to db
> (I have many collections, rich configuration of resources) , which
> consume/exhaust the available postgres processes.

I am using Akonadi with PSQL, but never saw this problem. But then again, I 
wasn't even aware that this can happen with PSQL :) You have a valid point 
though, so let's look into it :)

> 
> I thought of using pgpool[1] or pgbouncer[2], but they won't do[3]
> 
> So, next thing would be to hack akonadiserver and try to optimize it. After
> a quick look, it seems I could hack "server/src/storage/datastore.cpp", put
> an idle timer in it and close()/open() connections on demand. Then count
> the number of bugs born by this change :) .
> 
> Do you have any comments/objections before I try that?

The way this works is that each connection to Akonadi Sever (be it a 
connection from client, or from Resource) has it's own DataStore, and thus 
it's own database connection and many of those connections are indeed idle 
most of the time, so some kind of timeout makes a lot of sense.

You approach would IMO require more code and would be more error prone, so I 
would suggest a slightly different solution:

Let's have the timeout implemented in server/src/connection.cpp, which 
represents individual client connection to the Server. There's this method 
called Connection::slotNewData(), which is invoked whenever a new command is 
received from the client, and blocks until the Server sends the entire 
response back, then returns. This would be a good place to start the timer - 
right before the method returns, and reset the timer whenever the method is 
called again. In the QTimer::timeout slot, you could then call 
DataStore::self()->close(), which closes the DB connection. At the beginning 
of the method, you would then call something like 

if (DataStore::existsForThread(QThread::currentThread()) && 
		!DataStore::self()->isOpened()) { 
	DataStore::self()->open();
}

This should be a fairly small patch, and thanks to the timer being controlled 
from slotNewData(), it would ensure, that we will never close the connection 
while in the middle of processing a command, so this should not introduce any 
regressions at all.

Additional optimization could be to have the same close-open mechanism in the 
persistent worker threads, which however "work" only from time to time: 
SearchManager, IntervalCheck and CacheCleaner (the last two have common 
ancestor CollectionScheduler, so potentially it could be implemented there). 
But the timeout in Connection is a good start.

You can work against 1.13 branch (the current stable), as this can be 
considered as a bug fix, that I'm willing to ship in next update (after it's 
properly tested).

Let me know if you need any help :)

Cheers,
Daniel

> 
> 
> Thanks for akonadi.
> 
> 
> 
> 
> [1] does the opposite: keeps many db backend connections open, allows
> occasional clients to reuse them
> [2] does "group" many clients into little backends, but drops prepared
> statements, by design
> [3] even relying on these projects, for akonadi, would be unspeakable for
> the average desktop user.

-- 
Daniel Vrátil | dvratil at redhat.com | dvratil on #kde-devel, #kontact, #akonadi
Software Engineer - KDE Desktop Team, Red Hat Inc.

GPG Key: 0xC59D614F6F4AE348
Fingerprint: 4EC1 86E3 C54E 0B39 5FDD B5FB C59D 614F 6F4A E348
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kde-pim/attachments/20141110/a741cbeb/attachment.sig>
-------------- next part --------------
_______________________________________________
KDE PIM mailing list kde-pim at kde.org
https://mail.kde.org/mailman/listinfo/kde-pim
KDE PIM home page at http://pim.kde.org/