[Kde-pim] Excessive amount of queries

Sat Dec 6 10:48:25 GMT 2014

Am Samstag, 6. Dezember 2014, 10:54:39 schrieb 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:
[…]
> > > > 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.

Wow. Daniel, from a first impression:

You totally crushed MySQL load on my system.

On first access of a huge folder I can see is at 150% for a short period of 
time reading 500-600 MiB from SSDs (dual BTRFS RAID 1 still) within 10 
seconds, but then its quiet. And even then, accessing that one 260000 mail 
kernel-ml folder… I only saw mysqld slightly above 100% for a short time and 
then it was first akonadiserver and then kmail working. And on second access it 
was at 20-30% only, and then disappeared completely.

And usually it is just about 20-30% of the two core hyperthreading Sandybridge 
i5 as well. I easily saw it at 200-300% before, even for longer amount of 
times.

For a more extensive analysis it is too early, but it could be that you 
shifted the performance bottle neck from Akonadi + MySQL quite a bit more 
towards KMail´s threading implementation. Now move threading into Akonadi and… 
this could fly even with insanely large folders :). This has the potential to 
become what I call the Zimbra experience where it doesn´t matter that much 
whether a folder has 10000, 100000 or 500000 mails :).

That said, Akonadi Next may still make a lot of sense. But dependening on 
backend store it may need *similar* caching to rock like this :)

Heck, I will want akonadi server package for Debian being updated for Jessie.

But first lets give this a good amount of testing :)

Daniel, is there a way to get the cache hit ratio like you did above? How much 
does it cache? Is the memory usage visible in akonadiserver process? I have no 
problem with it caching a lot, this machine has 16 GiB of RAM.

Lol, no, KMail uses 2,1 GiB RSS now, atop output (downsized horizontally, so 
that atop leaves out some columns, to fit in mail width as when I change to no 
word wrap I have to format above paragraphs manually or save as draft, reload 
and then switch to wordwrap).

  PID VSTACK  VSIZE  RSIZE  PSIZE  VGROW  RGROW SWAPSZ  MEM CMD
 4389   208K   4.0G   2.1G     0K     0K     0K     0K  13% kmail
 3928   136K   3.5G 204.1M     0K     0K     0K     0K   1% plasma-desktop
 4287   136K   3.9G 183.8M     0K     0K     0K     0K   1% amarok
 4066   136K   2.5G 138.5M     0K     0K     0K     0K   1% akonadiserver
 3963   136K   1.6G 125.4M     0K     0K     0K     0K   1% krunner
 4080   136K   2.2G 121.7M     0K     0K     0K     0K   1% mysqld
 3911   136K   3.0G 90732K     0K -9348K -8676K     0K   1% kwin
 3165   136K 287.1M 88064K     0K   192K     0K     0K   1% Xorg
 3991   136K 662.0M 80700K     0K     0K     0K     0K   0% dolphin
 3851   136K   1.3G 78132K     0K     0K     0K     0K   0% kded4
 3969   136K 447.0M 51312K     0K     0K     0K     0K   0% konsole
 4036   136K 444.0M 47988K     0K     0K     0K     0K   0% konsole

akonadiserver is harmless, so I bet the cache doesn´t even need much memory?

Ciao,
-- 
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/