[Kde-pim] Excessive amount of queries

[Pablo Sanchez]

[ Comments below, in-line ]

On 12/06/2014 05:48 AM, Martin Steigerwald wrote:
> 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 :)

On IRC Daniel and I discussed two immediate methods to relieve
pressure on the DBMS.  I believe given some constraints with akonadi's
code, Daniel elected to take the path of caching some of the most
flagrant pathological SQL calls.

If there was time to refactor the code, I believe the following two
changes are needed to scale.  Or at least designed into "akonadi next"

    Side note:

    I hate to "toot my own horn" (and especially here) but one of the
    things I do is resolve DB performance issues for clients on a
    /much/ larger scale.

Reducing the number of SQL Calls per second
===========================================
While the caching mechanism certainly makes sense in one regard, is it
the /real/ solution?

Is the dependent code now getting its calls satisfied by the cache yet
still making these /excessive/ calls?   If the latter is true, then
I'd expect to see bursts of CPU utilization.  Which is way better than
before but can we do better?  I believe we can but there's a cost.

The proper solution is to look at the functional part of the
code and ask critically why is it making so many SQL calls per
second.  And reduce it.  There is no way we need to make over 1,000
SQL calls per second.  Recall the snippet of my analysis:

    +---------------------+----------+
    | 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 |

Reducing the amount of data analyzed
====================================
The other area that seemed suspect was why we were nearly
re-SUM'ing/COUNT'ing huge sets of data.

We are issuing COUNT's against nearly all the rows in certain tables
and we're doing it excessively (see above).  Once the information is
cached in the DBMS, the DBMS will be CPU bound.

Again, this will take refactoring but adding a timestamp-like column
to the rows so "akonadi" only has to aggregate between the last time
it made its call and /now/ will reduce the amount of load on the DBMS.

Daniel mentioned on IRC it's not possible.  I believe given all the
demands on Daniel's time, it's not either.  But, perhaps for "akonadi
next"  :)

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