[Kde-pim] Excessive amount of queries

[Martin Steigerwald]

Am Freitag, 5. Dezember 2014, 18:50:14 schrieb Daniel Vrátil:
> On Thursday 04 of December 2014 23:53:48 Milian Wolff wrote:
> > On Thursday 04 December 2014 18:32:18 Daniel Vrátil wrote:
> > > 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 :)
> > 
> > That sounds very good. I also saw this with my query debugger patch, i.e.
> > just jumping to another mail triggered tons of queries. Hope to see the
> > above in 1.13 Dan, do add me as a reviewer. I'd also be willing to test it
> > at work, next week.
> 
> The change has been pushed now, feel free to test :-)

git pull & building now :)

You did awesome! Thanks.

-- 
Martin 'Helios' Steigerwald - http://www.Lichtvoll.de
GPG: 03B0 0D6C 0040 0710 4AFA  B82F 991B EAAC A599 84C7
_______________________________________________
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/