[Kde-pim] Excessive amount of queries

[Daniel Vrátil]

On Thursday 04 of December 2014 16:55:58 Daniel Vrátil wrote:
> On Monday 01 of December 2014 14:40:58 Christian Mollekopf wrote:
> > On Monday 01 December 2014 13.39:50 Milian Wolff wrote:
> > > On Monday 01 December 2014 12:50:36 Christian Mollekopf wrote:
> > > > On Monday 01 December 2014 12.29:39 Milian Wolff wrote:
> > > > > Hello all,
> > > > > 
> > > > > I know you are all thinking about Akonadi Next, but I'd love to see
> > > > > some
> > > > > work on improving the Akonadi we have now, and will have for quite
> > > > > some
> > > > > time.
> > > > > 
> > > > > When you enable the query debugger in akonadiconsole, you'll see
> > > > > that
> > > > > just
> > > > > switching to a different email in KMail, will easily end up
> > > > > triggering
> > > > > ~200
> > > > > queries. Can someone with more knowledge tell me if really all of
> > > > > that
> > > > > is
> > > > > required?
> > > > 
> > > > I doubt it but would have to look at the actual queries.
> > > 
> > > with this patch, it's trivial for you to check it: There is also a
> > > screenshot attached of some results for me.
> > > 
> > > https://git.reviewboard.kde.org/r/121307/
> > 
> > Jup, that could very well be LIST with statistics and attributes.
> 
> Stats are very expensive. I want to try to cache the statistics internally,
> and flush the cache when collection changes. I believe that could be some
> performance improvement and query reduction.

Just FYI: I just wrote a global cache for collection statistics, and it seems 
to be super effective:

================= COLLECTIONSTATISTICS ============  
Queries: 1342
Hits: 1080 ( 80.4769 %) 
Misses: 262 ( 19.5231 %)
SQL Query count: 262 
Average SQL query runtime: 93 ms 
==================================================== 

The cache reduces the amount of SQL queries for collection statistics by 80% 
(after about 10 minutes of working with KMail - sync, going through many 
folders, marking mails as read, etc.).

Considered the average time of each query being 93ms, this is a *lot*. 
(Actually it's two queries: in one query we get COUNT(pimitem.id) and 
SUM(pimitem.size), and in the other one we get number of read emails).

Marking a single email as read causes around 8 queries to collection 
statistics (KMail + various resources that listen to all changes). Reducing 8 
queries to one is pretty good in my books :)

I still need to figure out how to properly invalidate the cache though, 
because for now I put it to NotificationCollector::itemNotification(), which 
is the best place performance-wise, but it feels very hacky and dirty :)

Dan

> 
> > > > > I'm in the process of improving the query debugger interface, to
> > > > > aggregate information on queries. That should show us better which
> > > > > queries
> > > > > are triggered repeatedly and take a long time.
> > > > 
> > > > One part that I improved in the kolab branches that did not yet make
> > > > it
> > > > upstream is refactoring LIST so it doesn't do recursive queries. This
> > > > is
> > > > where a single CollectionFetchJob can easily trigger N queries where N
> > > > is
> > > > the number of folders you have. So in case you stumble over that, be
> > > > aware
> > > > that this should be already solved somewhere (and that I will merge
> > > > that
> > > > upstream as well).
> > > 
> > > Yes, I think that is the main culprit. Can you please backport that to
> > > 1.13?
> > 
> > I'll have to discuss with Daniel how we go about that.
> 
> I can manually cherry-pick stuff from your branches into upstream, which
> means I will also review them. Unfortunately we can't get all the changes
> in, because some of them require changes in kdepimlibs too.
> 
> Dan
> 
> > Cheers,
> > Christian
> > 
> > _______________________________________________
> > 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/

-- 
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/20141204/13caa587/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/