Review of database aspect of Akonadi, Akonadi concepts and a master plan

[Pablo Sanchez]

[ Comments below, in-line ]


On Tue, 20 Mar 2018 09:33:29 +0100, Martin Steigerwald wrote:
> Hello again.
> 
> Pablo for some reason was not subscribed to kde-pim mailing list and
> just resubscribed, so just reposting by replying in order to allow
> him to reply with his summary of the database stuff. Full quote so
> that he also can read the other mail contents.

Hi everyone,

Err, I vaguely recall unsubscriibing.  At the time it seemed like a
good idea ... but ... anyway ... I can't recall why.  *grin*

> Martin Steigerwald - 18.03.18, 13:48:
>> Dear PIMsters,
>>
>>> [ trimmed ]
>>>
>>> Pablo is currently on the review for MySQL / MariaDB backend on
>>> the Phabricator tasks
>>>
>>> akonadi > MySQL ERD Review
>>> https://phabricator.kde.org/T7846
>>>
>>> akonadi > MySQL configuration settings
>>> https://phabricator.kde.org/T7874
>>
>> Pablo, I lost track on the current work on that, would you be
>> willing to give us a short summary? I know you and Dan have been
>> quite active on this :)
> 

On the database side our goal is to reduce the demand where we
can. This can be accomplished by using Database Program Units
(e.g. Stored Procedures), reducing the number of calls to the
database, etc.

::: https://phabricator.kde.org/T7846 :::

o Topic:  akonadi > MySQL ERD Review

>> Summary <<

The database was analyzed for correctness and to ensure it
scaled. Overall, the design is good.  No glaring issues were
discovered.

One thing to keep in mind is that the database is a cache.  If it is
lost, while it may be painful, it can be rebuilt.

>> Status <<

Several actionable items resulted which are listed in the ticket.
None of these items are critical.

::: https://phabricator.kde.org/T7874 :::

o Topic:  akonadi > MySQL configuration settings

>> Summary <<

Review the mysql.conf settings.

>> Summary <<

As the database is a cache, we have latitude normally not afforded to
database applications.  For example, we don't strictly need to adhere
to durability constraints.  However, while it is possible to recreate
the database, it can be painful.  In the interest of minimizing impact
to the user's, perhaps, at some point in the future, we have a simple
'rebuild widget' rather than incur the cost of ensuring durability.

::: https://phabricator.kde.org/T8150 :::

o Topic:  SQL analysis > kmail2 > initial get email

>> Summary <<

Side note:  The goal is to come up with a process that works between
the database engineer and the application engineer.  Once we have a
rhythm, it is a lot easier to move things along.  Below is my first
cut at establishing a rhythm.  :)

This task involved doing an analysis of the SQL generated when kmail2
fetches an initial set of emails.  In this particular case, there were
1,599 emails which needed to be fetched.

The analysis shows an excess amount of SQL's being issued per
second. Upwards of 7,000.  As most of the information is
memory-resident, this manifests itself has high CPU utilization by
mysqld (the database engine).

High CPU == CPU fans running, battery drain on a portable device,
etc.

Further analysis shows duplicate SQL being submitted.

>> Status <<

Before proceeding further we need to refactor the upper layer to
mitigate the SQL-storm.  After this is done, we'll re-perform this
analysis and determine our next steps.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926        iNum:  883.5100.0990.1054