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

[Martin Steigerwald]

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.

Martin Steigerwald - 18.03.18, 13:48:
> Dear PIMsters,
> 
> Martin Steigerwald - 04.02.18, 21:53:
> > In the kdepim-users thread "kmail sigh" with a report on unpredictable
> > behavior of Akonadi Pablo offered to review the database handling of
> > Akonadi. I also thought I took the chance to do something productive about
> > the repeated user reports of performance issues and erroneous behavior and
> > asked Daniel and Sandro about whether they would like to have such an
> > review.
> 
> I like to give an update about the progress we made on this: First off, we
> all had been quite busy, nonetheless I have some news for you:
>
> > 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
> :)

Pablo, this is a good place to put your summary of your and Dan´s work into 
the thread :). Feel free to drop the Cc to your address on your reply :).

> > But this also lead to a longer communication off list also about a VM with
> > KDEPIM for testing purposes and about information Pablo would need for an
> > effective review. During which Daniel provided one gem after another on
> > insight about Akonadi´s concepts and on how to effectively improve it.
> > After a short time of questions and answers it became obvious that this
> > information would benefit others and that it makes sense to continue this
> > in public. […]
> 
> […]
> 
> > So look forward to mails from Dan about the concepts and inner workings of
> > Akonadi, how the change-recorder works and about three, well if I counted
> 
> > correctly four major improvements of Akonadi:
> Dan posted his text about the concepts and different components and items in
> Akonadi onto the community wiki:
> 
> https://community.kde.org/KDE_PIM/Akonadi/Architecture
> 
> It is not yet totally complete, there are some bits missing in "How the
> whole thing works together". Dan or I happily update you when this is also
> complete.
> 
> I follow up with some information on the major tasks to improve the overall
> KDEPIM on Akonadi experience considerably, I took together from various
> mails – I added links to Phabricator tasks as far as I am aware of them.
> > 1. rewriting the entire indexing infrastructure
> 
> Dan already worked on this quite a lot, let me quote him about the current
> 
> state:
> > One part of the fix is rewriting the entire indexing infrastructure, which
> > I've been slowly working on for the past half a year or so until I got
> > stuck on some issues and kinda lost interest. I should probably try to
> > pick
> > up the work agin at some point. The new indexing mechanism will reduce the
> > IO considerably by indexing the data in the Resource when it's sending the
> > data to the server (via ItemCreateJob) and the server then only writes the
> > indexed data into Xapian database - no more indexing agent that triggers
> > agent-server-resource-server-agent roundtrips for each Item with over two
> > dozens of SQL queries for each one. 
> 
> Dan tracks the progress of this one in Phabricator already:
> 
> Make Indexing Great Again
> https://phabricator.kde.org/T7014
> 
> > 2. notification payloads. So that change notifications have the changed
> > data inside already.
> 
> Dan describes this task as follows:
> > Second part of the fix is notification payloads - sending the entire Items
> > as  part of change notifications so that every client that gets notified
> > about an  Item change does not need to query Akonadi for the actual Item
> > but get it  right away. If you have 5 clients receiving the notifications,
> > that's 5  ItemFetchJobs and at least 15 SQL queries on the major tables (3
> > per client).  We usually have most of the data already in memory when
> > doing
> > the change on  the server, so just sending the data and the payload to the
> > clients as part of  the notification would be a massive speed up and would
> > ease up on SQL. 
> 
> I did not find a Phabricator task for this one yet. I will ask Dan about it
> and look into creating one if its not there yet.
> 
> > 3. foreign payloads, for example tell KMail to access directly the mail in
> > the maildir.
> 
> This one requires the implementation of the first two changes in order to
> work reliably and not to loose any mails – thanks for the wonders of a
> asynchronous multi process architecture. As far as I understand this change
> mostly helps with Maildir. I am not yet sure whether it could also be used
> to access mails on an IMAP account without stuffing them in the cache and
> whether that would be beneficial.
> 
> Dan describes it as:
> > Third part of the fix are foreign payloads - in other words Akonadi
> > database directly refering to the maildir files, instead of having copies
> > of the maildir emails in file_db_data. As such when you open an email in
> > KMail, right now Akonadi checks the DB, talk to the resource, resource
> > finds the maildir  message, uploads it to Akonadi which copies it to
> > file_db_data and sends it to  client which then reads it from
> > file_db_data (and the copy is eventually  removed by Akonadi after some
> > timeout) which probably also trigger the  Indexing Agent. With foreign
> > payloads Akonadi would simply have a path to the  actual maildir file, so
> > that KMail would access the file directly in the  maildir, saving at
> > least another dozen or so SQL queries  and massively reduce  IO and
> > copying (and disk-space wasting). 
> Its also in Phabricator already:
> 
> [Akonadi] Foreign payload
> https://phabricator.kde.org/T630
> 
> Last but not least we have:
> > 4. and server-side change recording which as Dan will tell you fixes
> > everything
> 
> Let me share the description of this feature Dan with you:
> > It's a major new change that will save our souls, this planet and possibly
> > the entire universe.
> > 
> > It's related to how Akonadi solves the problem when you change something
> > (mark an email as read etc.) but the IMAP resource is offline so it can't
> > upload the change on the IMAP server. Right now the Resource remembers the
> > change (this is called "change recording") and when it goes online it
> > "replays" the change to the IMAP resource.
> > 
> > Server-side change recording is a major plan to make the changes recorded
> > on the Akonadi Server and the Resource requests those changes when it can
> > replay  them. This, among other things, will allow the changes to be re-
> > replayed when they failed to be replayed the first time as well as keep
> > consistency between the notification and the actal state of the change
> > Entity. 
> 
> So this is also the fix for: If a resource fails to replay a change, you end
> up with "item has no rid", which means the item is only inside the database
> and nowhere else. So this really has an impact on consistency between
> Akonadi as a cache and the resources it manages.
> 
> This is:
> 
> [Akonadi] Server-side change recording
> https://phabricator.kde.org/T638
> 
> > The good news is that these four changes have the potential to remove the
> > reason for the majority of all KDEPIM related bug reports. And some of
> > these changes are partly done already.
> > 
> > The challenge is: These changes are big, scary and have a good potential
> > for regressions. But especially they need time. A lot of time for one
> > freelance KDEPIM developer alone.
> 
> And of course, all of what I wrote does not yet solve the above issue. Its
> still a huge effort and we appreciate any help on these topics. Thing with
> this is: I am not sure whether its feasible to divide each of those tasks
> into sub tasks in a way that could allow volunteers to help with parts of
> the implementation. I will be asking Dan about this as well.
> 
> 
> Of course, these four changes aren´t the only ways to improve Akonadi, but
> from what I understand so far, these four have a really huge impact.
[…]
-- 
Martin