Create Task
Maniphest T8150

SQL analysis > kmail2 > initial get email
Open, Needs TriagePublic
Actions

Description

Hi,

This Task is an analysis of a SQL trace of kmail2 fetching 1599 email messages.

The graphs show that the aggregate SQL processed exceeds over 8,000+ per second. I did not capture top data while kmail2 was downloading the messages but I recall seeing mysqld consuming nearly an entire processor. This makes sense given the rate of SQL being submitted.

Attached is also a tar-ball with the SQL trace split into different files by thread_id. For convenience, the files are tab-delimited.

Our goal should be to minimize the amount of chatter between the client and the (DB) server. Ideally, if we could either batch and/or push some of the logic into one or more Stored Procedures, that would be ideal. Using SP's would mean we would not have to call Prepare's (more on this in a subsequent post to this thread).

The next step for me is to strip out search-arguments in the predicate to see if we I can see any patterns from which we can figure out one or more SP's.

sql_per_sec.ods49 KBDownload

sql_log_by_thread.tar.bz23 MBDownload

Related Objects
Search...

pablos created this task.Mar 5 2018, 7:52 PM
pablos added a comment.Mar 6 2018, 3:48 PM

As I was preparing to strip the search-arguments I noticed many repeated SQL calls within the thread_id's (each independent SQL connection).

I counted unique SQL calls by thread_id. The tar-ball below contains the results.

The BEGIN WORK and COMMIT statements (obviously) point out to a database transaction. If/when we push this SQL to a Stored Procedure, these calls will go away.

I suggest our first priority should be to reduce the redundant SQL calls and re-gather the SQL log trace with the new bits.

To get an idea of the findings, below is a head -4 of each file in the tar-ball:

==> 219.uniq_sql_count.tab <==
3289	COMMIT
3289	BEGIN WORK
2	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 ( ( PimItemTable.id = 999 ) )
2	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 ( ( PimItemTable.id = 998 ) )

==> 220.uniq_sql_count.tab <==
4	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
4	SELECT PimItemTable.id, PimItemFlagRelation.Flag_id FROM PimItemTable INNER JOIN PimItemFlagRelation ON ( PimItemTable.id = PimItemFlagRelation.PimItem_id ) WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
3	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 999 ) ) ORDER BY PimItemTable.id DESC
3	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 998 ) ) ORDER BY PimItemTable.id DESC

==> 221.uniq_sql_count.tab <==
4	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
4	SELECT PimItemTable.id, PimItemFlagRelation.Flag_id FROM PimItemTable INNER JOIN PimItemFlagRelation ON ( PimItemTable.id = PimItemFlagRelation.PimItem_id ) WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
3	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 999 ) ) ORDER BY PimItemTable.id DESC
3	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 998 ) ) ORDER BY PimItemTable.id DESC

==> 222.uniq_sql_count.tab <==
4879	SELECT id, isVirtual FROM ResourceTable WHERE ( name = 'MailFilter Kernel ETM' )
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 938 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 903 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 372 ) ) ORDER BY PimItemTable.id DESC

==> 223.uniq_sql_count.tab <==
4835	SELECT id, isVirtual FROM ResourceTable WHERE ( name = 'KMail Kernel ETM' )
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 385 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 326 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 385 ) ) ORDER BY PimItemTable.id DESC

==> 224.uniq_sql_count.tab <==
4	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
4	SELECT PimItemTable.id, PimItemTable.remoteId, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.remoteRevision, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 65 ) ) ORDER BY PimItemTable.id DESC
4	SELECT PimItemTable.id, PimItemFlagRelation.Flag_id FROM PimItemTable INNER JOIN PimItemFlagRelation ON ( PimItemTable.id = PimItemFlagRelation.PimItem_id ) WHERE ( ( PimItemTable.id = 823 ) ) ORDER BY PimItemTable.id DESC
4	SELECT PimItemTable.id, PimItemFlagRelation.Flag_id FROM PimItemTable INNER JOIN PimItemFlagRelation ON ( PimItemTable.id = PimItemFlagRelation.PimItem_id ) WHERE ( ( PimItemTable.id = 65 ) ) ORDER BY PimItemTable.id DESC

==> 225.uniq_sql_count.tab <==
4907	SELECT id, isVirtual FROM ResourceTable WHERE ( name = 'Archive Mail Kernel ETM' )
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 903 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 829 ) ) ORDER BY PimItemTable.id DESC
5	SELECT PimItemTable.id, TagTable.id FROM PimItemTable INNER JOIN PimItemTagRelation ON ( PimItemTable.id = PimItemTagRelation.PimItem_id ) INNER JOIN TagTable ON ( TagTable.id = PimItemTagRelation.Tag_id ) WHERE ( ( PimItemTable.id = 824 ) ) ORDER BY PimItemTable.id DESC

==> 226.uniq_sql_count.tab <==
4895	SELECT id, isVirtual FROM ResourceTable WHERE ( name = 'akonadi_indexing_agent' )
4895	COMMIT
4895	BEGIN WORK
6	SELECT PimItemTable.id, PimItemTable.mimeTypeId, PimItemTable.rev, PimItemTable.size, PimItemTable.collectionId FROM PimItemTable WHERE ( ( PimItemTable.id = 926 ) ) ORDER BY PimItemTable.id DESC

uniq_sql_count.tar.bz237 KBDownload

dvratil added a subscriber: dvratil.Mar 9 2018, 11:52 AM

I assume you are referring to the fact that there are almost identical queries executed in multiple threads simultaneously. But the situation with Akonadi is similar to having a website. When 5 different users come to the website at the same time the result is the server application will run a thread for each of the users (technically, for each of the TCP connections from user's PC to the server) and will query the database for each of the users independently, and some of the queries may be customized since the users might be logged in so you want to present them slightly different data). This makes query deduplication rather hard.

However, I will assume that those queries are related to each client requesting data from Akonadi because it was notified that a new Item was created (because this is trace from mail sync, right?). There's a solution to this, which I talked about in the email discussion we had at the start. I call it Notification Payloads, and the idea is to send not just the notification but also the relevant data as part of the notification to all clients so that they don't have to go back to Akonadi begging for the data afterward.

dvratil added a comment.Mar 9 2018, 11:54 AM

Implementing that is no easy task, so if you want to wait for Notification Payloads to be implemented, it may take a bit (although I do have a free weekend coming up, at the same time I just bought a new graphics card...damn!)

pablos added a comment.Mar 9 2018, 10:22 PM

Hey Dan,

I suggest you enjoy the new graphics card! :)

Yes, all these queries were related to an initial mail sync. At this point I think the sheer number of queries is creating a performance shadow. In other words, if we were to push some of the logic into Stored Procedures, it would help mitigate the issue somewhat but we'd want to reduce the number of SP calls too. ;)

It does sound like we might have to wait for the Notification Payload work to get done. But, you work really hard so this is why I think you should enjoy your graphics card. I'm committed to helping out for the long haul. I won't go away. Well, unless you tell me to! :) :) :)

dvratil added a comment.Mar 11 2018, 9:38 PM

I realized that I started working on Notification Payloads about a year ago, so I revived the branch and implemented the missing bits over the weekend. Most the code is in place now, but it's not working yet and this week is going to be super-busy, so I won't probably be able to finish it, but sooner than next week. In any case, it's probably going to be done sooner than I expected :-)