[Kde-pim] Excessive amount of queries
Pablo Sanchez
pablo at blueoakdb.com
Mon Dec 1 14:33:34 GMT 2014
[ Comments below, in-line ]
On 12/01/2014 06:29 AM, Milian Wolff wrote:
> Hello all,
Hi Milian,
> [ trimmed ]
> 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?
>
> [ trimmed ]
I'd like to note I'm currently using Thunderbird because I still
find Kmail2 non-performant.
I'm a Database Engineer and a KDE enthusiast and recently I've decided
to offer my DB help to KDE - "pablo_s" on IRC.
My findings are very preliminary however I think there's some
performance "low hanging fruit".
Using MySQL as the backend database, I enabled SQL logging to a table
and ran my Use Case: sync my gmail account's "Inbox" via IMAP. Below
(end of email) you can see the number of SQL's called per second:
1,200, 1,533, etc. Ouch!
During the sync, MySQL was consuming most of the CPU (in my VM).
Which is what I'd expect when I see this many SQL calls per second
being serviced. If I had less memory, I'd have expected to see more
disk I/O's
As a database engineer, the number of calls per second is way too
excessive. From a functional perspective, I don't see why we'd have
to make this many calls when sync'ing email.
I also created an "Entity Relationship Diagram" (ERD) of akonadi's
MySQL database schema. I did this primarily for myself so I could
"see" how the tables relate with one another and it'd help me with
tuning. A .pdf of the ERD can be pulled from here:
http://goo.gl/HuCjW2
btw, I use a proprietary tool to create the ERD: PowerDesigner. I
realize that some people may not like that but unfortunately the
OSS ones aren't nearly as powerful.
Before the Sprints, Daniel had gathered some data for me with a Use
Case of his (details not yet known to me :) ... I crunched the data he
sent me and we can see the SQL invoked by frequency here:
http://goo.gl/YJYZV8 I think it's quite illuminating to see the
frequency of SQL calls as well as the SQL itself.
At the moment, I'm on hold until I hear back from Daniel on the
outcome of "Akonadi Next"
Performance Thoughts
====================
1) Pace the number of calls to the database.
Expected gain: system CPU will be less taxed. The end-user will
be able to do other things on their system. I suspect if we made
no more than 20 - 40 DB calls per second, the end-user's experience
wouldn't be impacted. I'd suspect it'd be greatly improved because
their computer wouldn't become CPU bound.
2) Review frequently invoked SQL and determine whether it can be
refactored to reduce the demand on the DBMS.
Notes: Earlier I noticed some queries were running nearly
unbounded (minimal criterial in the WHERE-clause). The queries
were returning COUNT()'s. Aside from running these queries
exceedingly repetitively, I think we can reduce the demand on the
DBMS if we introduced a timestamp of when a row is affected (either
inserted or updated).
If the DB client tracks the last time it checked a count, it can
run another query and only ask for those rows between the last time
and "now", use the result to add it to its current tally.
By doing this, it'll reduce the demand on the DBMS.
This is a bit raw so I'd need to do more analysis with Daniel (? :)
to determine what's going on and a proper method to tune.
Gmail, Initial "Inbox" Sync Via IMAP - SQL's per Second
=======================================================
19:36:04 2> select date_format(event_time, '%Y-%m-%d %H:%i:%s') by_sec,
count(*) from mysql.general_log where command_type ='Execute' group by
date_format(event_time, '%Y-%m-%d %H:%i:%s') order by
date_format(event_time, '%Y-%m-%d %H:%i:%s');
+---------------------+----------+
| by_sec | count(*) |
+---------------------+----------+
| 2014-11-25 11:10:14 | 161 |
| 2014-11-25 11:10:15 | 1292 |
| 2014-11-25 11:10:16 | 1533 |
| 2014-11-25 11:10:17 | 1366 |
| 2014-11-25 11:10:18 | 1421 |
| 2014-11-25 11:10:19 | 1221 |
| 2014-11-25 11:10:20 | 1242 |
| 2014-11-25 11:10:21 | 1339 |
| 2014-11-25 11:10:22 | 1239 |
| 2014-11-25 11:10:23 | 1169 |
| 2014-11-25 11:10:24 | 1247 |
| 2014-11-25 11:10:25 | 1466 |
| 2014-11-25 11:10:26 | 1051 |
| 2014-11-25 11:10:27 | 1267 |
| 2014-11-25 11:10:28 | 1287 |
| 2014-11-25 11:10:29 | 1456 |
| 2014-11-25 11:10:30 | 72 |
| 2014-11-25 11:10:31 | 347 |
| 2014-11-25 11:10:32 | 1075 |
| 2014-11-25 11:10:33 | 16 |
| 2014-11-25 11:10:34 | 1125 |
| 2014-11-25 11:10:35 | 307 |
| 2014-11-25 11:10:37 | 1415 |
| 2014-11-25 11:10:38 | 1105 |
| 2014-11-25 11:10:39 | 1244 |
| 2014-11-25 11:10:40 | 1391 |
| 2014-11-25 11:10:41 | 1271 |
| 2014-11-25 11:10:42 | 913 |
| 2014-11-25 11:10:43 | 1247 |
| 2014-11-25 11:10:44 | 1427 |
| 2014-11-25 11:10:45 | 1140 |
| 2014-11-25 11:10:46 | 1176 |
| 2014-11-25 11:10:47 | 1165 |
| 2014-11-25 11:10:48 | 1246 |
| 2014-11-25 11:10:49 | 1014 |
| 2014-11-25 11:10:50 | 1095 |
| 2014-11-25 11:10:51 | 1353 |
| 2014-11-25 11:10:52 | 1330 |
| 2014-11-25 11:10:53 | 1419 |
| 2014-11-25 11:10:54 | 1151 |
| 2014-11-25 11:10:55 | 1239 |
| 2014-11-25 11:10:56 | 1125 |
| 2014-11-25 11:10:57 | 1038 |
| 2014-11-25 11:10:58 | 1022 |
| 2014-11-25 11:10:59 | 1324 |
| 2014-11-25 11:11:00 | 1022 |
| 2014-11-25 11:11:01 | 1142 |
| 2014-11-25 11:11:02 | 1286 |
| 2014-11-25 11:11:03 | 1261 |
| 2014-11-25 11:11:04 | 1078 |
| 2014-11-25 11:11:05 | 1104 |
| 2014-11-25 11:11:06 | 883 |
| 2014-11-25 11:11:07 | 1117 |
| 2014-11-25 11:11:08 | 979 |
| 2014-11-25 11:11:09 | 1378 |
| 2014-11-25 11:11:10 | 1178 |
| 2014-11-25 11:11:11 | 1328 |
| 2014-11-25 11:11:12 | 1132 |
| 2014-11-25 11:11:13 | 1229 |
| 2014-11-25 11:11:14 | 1278 |
| 2014-11-25 11:11:15 | 1249 |
| 2014-11-25 11:11:16 | 1107 |
| 2014-11-25 11:11:17 | 1083 |
| 2014-11-25 11:11:18 | 1227 |
| 2014-11-25 11:11:19 | 1134 |
| 2014-11-25 11:11:20 | 1322 |
| 2014-11-25 11:11:21 | 1264 |
| 2014-11-25 11:11:22 | 865 |
| 2014-11-25 11:11:23 | 1449 |
| 2014-11-25 11:11:24 | 1247 |
| 2014-11-25 11:11:25 | 1144 |
| 2014-11-25 11:11:26 | 1062 |
| 2014-11-25 11:11:27 | 960 |
| 2014-11-25 11:11:28 | 1045 |
| 2014-11-25 11:11:29 | 1089 |
| 2014-11-25 11:11:30 | 957 |
| 2014-11-25 11:11:31 | 1005 |
| 2014-11-25 11:11:32 | 1089 |
| 2014-11-25 11:11:33 | 1196 |
| 2014-11-25 11:11:34 | 1145 |
| 2014-11-25 11:11:35 | 1370 |
| 2014-11-25 11:11:36 | 1014 |
| 2014-11-25 11:11:37 | 1203 |
| 2014-11-25 11:11:38 | 1054 |
| 2014-11-25 11:11:39 | 412 |
| 2014-11-25 11:11:41 | 809 |
| 2014-11-25 11:11:42 | 648 |
| 2014-11-25 11:11:43 | 25 |
| 2014-11-25 11:11:44 | 15 |
| 2014-11-25 11:11:45 | 15 |
| 2014-11-25 11:11:46 | 5 |
| 2014-11-25 11:11:47 | 1159 |
| 2014-11-25 11:11:48 | 1033 |
| 2014-11-25 11:11:49 | 1337 |
| 2014-11-25 11:11:50 | 1203 |
| 2014-11-25 11:11:51 | 1236 |
| 2014-11-25 11:11:52 | 1193 |
| 2014-11-25 11:11:53 | 1227 |
| 2014-11-25 11:11:54 | 1202 |
| 2014-11-25 11:11:55 | 1083 |
| 2014-11-25 11:11:56 | 936 |
| 2014-11-25 11:11:57 | 1169 |
| 2014-11-25 11:11:58 | 1294 |
| 2014-11-25 11:11:59 | 1201 |
| 2014-11-25 11:12:00 | 1271 |
| 2014-11-25 11:12:01 | 724 |
| 2014-11-25 11:12:02 | 1540 |
| 2014-11-25 11:12:03 | 1139 |
| 2014-11-25 11:12:04 | 1165 |
| 2014-11-25 11:12:05 | 1254 |
| 2014-11-25 11:12:06 | 1232 |
| 2014-11-25 11:12:07 | 1091 |
| 2014-11-25 11:12:08 | 1342 |
| 2014-11-25 11:12:09 | 1091 |
| 2014-11-25 11:12:10 | 1209 |
| 2014-11-25 11:12:11 | 1263 |
| 2014-11-25 11:12:12 | 1215 |
| 2014-11-25 11:12:13 | 810 |
| 2014-11-25 11:12:14 | 1451 |
| 2014-11-25 11:12:15 | 1147 |
| 2014-11-25 11:12:16 | 1199 |
| 2014-11-25 11:12:17 | 1230 |
| 2014-11-25 11:12:18 | 1300 |
| 2014-11-25 11:12:19 | 1197 |
| 2014-11-25 11:12:20 | 1374 |
| 2014-11-25 11:12:21 | 1168 |
| 2014-11-25 11:12:22 | 1008 |
| 2014-11-25 11:12:23 | 1288 |
| 2014-11-25 11:12:24 | 1253 |
| 2014-11-25 11:12:25 | 1220 |
| 2014-11-25 11:12:26 | 1031 |
| 2014-11-25 11:12:27 | 1253 |
| 2014-11-25 11:12:28 | 72 |
| 2014-11-25 11:12:29 | 743 |
| 2014-11-25 11:12:30 | 683 |
| 2014-11-25 11:12:31 | 6 |
| 2014-11-25 11:12:33 | 5 |
| 2014-11-25 11:12:34 | 1432 |
| 2014-11-25 11:12:35 | 941 |
| 2014-11-25 11:12:36 | 1096 |
| 2014-11-25 11:12:37 | 1296 |
| 2014-11-25 11:12:38 | 1009 |
| 2014-11-25 11:12:39 | 1240 |
| 2014-11-25 11:12:40 | 1095 |
| 2014-11-25 11:12:41 | 1152 |
| 2014-11-25 11:12:42 | 1155 |
| 2014-11-25 11:12:43 | 1287 |
| 2014-11-25 11:12:44 | 1115 |
| 2014-11-25 11:12:45 | 1133 |
| 2014-11-25 11:12:46 | 1226 |
| 2014-11-25 11:12:47 | 1139 |
| 2014-11-25 11:12:48 | 1165 |
| 2014-11-25 11:12:49 | 1038 |
| 2014-11-25 11:12:50 | 1264 |
| 2014-11-25 11:12:51 | 1064 |
| 2014-11-25 11:12:52 | 1080 |
| 2014-11-25 11:12:53 | 1011 |
| 2014-11-25 11:12:54 | 1153 |
--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph: 819.459.1926 Blog: http://pablo-blog.blueoakdb.com
iNum: 883.5100.0990.1054
_______________________________________________
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/
More information about the kde-pim
mailing list