[Akonadi] [Bug 338571] Performance Regression: Folder synchronisation in Akonadi 16.08 (actually in any release, starting with KDE 4.14) very slow, compared to kMail from KDE 4.13.x

[Gunter Ohrner via KDE Bugzilla]

https://bugs.kde.org/show_bug.cgi?id=338571

--- Comment #21 from Gunter Ohrner <kdebugs at CustomCDROM.de> ---
Wow, it's much worse than one query per mail...

Activating the MySQL query log and just opening my Sent folder leaves me with a
log file which is - 190 MByte (!) in size... Admittedly there are also some
timestamps in there, but at least half of that is SQL...

Apparently, kMail runs the following set of queries *twice* for *each* single
mail:

2016-08-26T22:57:43.864393Z   88 Execute        SELECT PimItemTable.id,
PimItemTable.rev, PimItemTable.remoteId, PimItemTable.remoteRevision,
PimItemTable.gid, PimItemTable.collectionId, PimItemTable.mimeTypeId,
PimItemTable.datetime, PimItemTable.atime, PimItemTable.dirty,
PimItemTable.size FROM PimItemTable WHERE ( collectionId = 602 AND remoteId =
'24314' )
2016-08-26T22:57:43.864535Z   88 Reset stmt
2016-08-26T22:57:43.864561Z   88 Execute        SELECT FlagTable.id,
FlagTable.name FROM FlagTable INNER JOIN PimItemFlagRelation ON (
PimItemFlagRelation.Flag_id = FlagTable.id ) WHERE (
PimItemFlagRelation.PimItem_id = 653279 )
2016-08-26T22:57:43.864667Z   88 Reset stmt
2016-08-26T22:57:43.864693Z   88 Execute        SELECT FlagTable.id,
FlagTable.name FROM FlagTable INNER JOIN PimItemFlagRelation ON (
PimItemFlagRelation.Flag_id = FlagTable.id ) WHERE (
PimItemFlagRelation.PimItem_id = 653279 )
2016-08-26T22:57:43.864793Z   88 Reset stmt
2016-08-26T22:57:43.864819Z   88 Execute        SELECT PartTable.id,
PartTable.pimItemId, PartTable.partTypeId, PartTable.data, PartTable.datasize,
PartTable.version, PartTable.external FROM PartTable WHERE ( pimItemId = 653279
)
2016-08-26T22:57:43.864946Z   88 Reset stmt
2016-08-26T22:57:43.864980Z   88 Execute        UPDATE PimItemTable SET rev =
1, remoteId = '24314', remoteRevision = NULL, gid =
'<201101172158.07416.XxXxXx at example.com>', collectionId = 602, mimeTypeId = 2,
datetime = '2016-08-26 22:57:43', atime = '2016-08-27 00:57:43', dirty = 0,
size = 967 WHERE ( id = 653279 )
2016-08-26T22:57:43.865197Z   88 Reset stmt

That sums up to 5 queries times 40,000 mails times 2 = 400,000 queries just to
*open* a single folder. Whoa.

In addition to the sheer number of queries one notes that the following
identical query (with same parameters!) is executed twice in this batch, and is
even a *join*:

SELECT FlagTable.id, FlagTable.name FROM FlagTable INNER JOIN
PimItemFlagRelation ON ( PimItemFlagRelation.Flag_id = FlagTable.id ) WHERE (
PimItemFlagRelation.PimItem_id = 653279 )

Hopefully, the MySQL query cache is able to optimize the second execution away
but you still lose some time to send the query and retrieve the response.

So we could already save 80,000 join executions just by removing the second
redundant call and re-using the result the first call returned. That would
still leave us with 320,000 queries however. If kMail would scan the folder
only once, we would be down to 160,000 queries - still a lot for 40,000 mails,
but already about 2.5 times faster than at the moment.

All queries properly use indices, but given the sheer number of simple
statements and a necessary join, I'm not surprised it's *that* slow.

All I know about relational databases would suggest it would be much better to
use larger batch-selects and updates instead of tons of individual key lookups.
This kind of workload would be more suited for KVP/hash based data stores - and
preferrably an in-memory cache, at least for read accesses...

-- 
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.