Not all that much better with PostgreSQL

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Not all that much better with PostgreSQL

Martin Steigerwald
Hi.

Since five (!) minutes I am trying to access a mail in a folder that has
been indexed before and just got the "Retrieving …" thing. Now, it
finally came up.

Why? Likely cause I happened to click on a folder that has not been
indexed and then Akonadi prioritized indexing the folder over my most
recent request.

This in my opinion has been the major issue with Akonadi for years and
still is the major issue:

Whenever Akonadi decides that it needs to do something in the
background, it often *completely* stops responding to any user requests.

However what is Akonadi's purpose? To serve the user of the applications
that are using it.

For me after the rule to store all data safely and reliably the second
most important rule would be:

The user goes first. Whenever the user clicks on a mail, a folder, an
appointment, I let everything pause if need be in order to serve the
other in a timely manner. I will always prioritize user requests above
all background processing I do. And if the user decides differently after
the moment, the user is always right and I stop or background the
processing the user requested before in order to serve his/her most
recent request promptly.

Basta.

The third most important rule, that is an addition to the first one is:

I do only work when it is useful. For example Akonadi currently thinks
it has to re-index a folder just cause it filtered some mails into it.
However, as a user I may not even intend to look into the folder. It
just has to stuff those mails into it and *be done with it*. And when I
click the folder, it can still apply rule 1 as follows:

Make sure the first mails are shown as quickly as possible and extend
when the user likes to see more or uses the search facility. Display
incrementally and remain responsive while doing so.


I believe in order for Akonadi to be enjoyable by users it absolutely
must abide by these rules.

Always and not just sometimes.

Also with a lot of mail.

I have seen this before – with Zimbra Collaboration Server which uses
MySQL/MariaDB and Lucene. Even with a folder with 450000 mails.

So it can be done.

Rant over.

Thanks,
--
Martin


Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Martin Steigerwald
Hi.

Martin Steigerwald - 12.04.19, 19:39:
> Since five (!) minutes I am trying to access a mail in a folder that
> has been indexed before and just got the "Retrieving …" thing. Now,
> it finally came up.

Shortly after I killed akonadi_indexing_agent once again.

Maybe after when Dan comes around to rewrite the indexing architecture
as he planned, things will be a lot better.

>
> Why? Likely cause I happened to click on a folder that has not been
> indexed and then Akonadi prioritized indexing the folder over my most
> recent request.
>
> This in my opinion has been the major issue with Akonadi for years and
> still is the major issue:
>
> Whenever Akonadi decides that it needs to do something in the
> background, it often *completely* stops responding to any user
> requests.
>
> However what is Akonadi's purpose? To serve the user of the
> applications that are using it.
>
> For me after the rule to store all data safely and reliably the second
> most important rule would be:
>
> The user goes first. Whenever the user clicks on a mail, a folder, an
> appointment, I let everything pause if need be in order to serve the
> other in a timely manner. I will always prioritize user requests above
> all background processing I do. And if the user decides differently
> after the moment, the user is always right and I stop or background
> the processing the user requested before in order to serve his/her
> most recent request promptly.
>
> Basta.
>
> The third most important rule, that is an addition to the first one
> is:
>
> I do only work when it is useful. For example Akonadi currently thinks
> it has to re-index a folder just cause it filtered some mails into
> it. However, as a user I may not even intend to look into the folder.
> It just has to stuff those mails into it and *be done with it*. And
> when I click the folder, it can still apply rule 1 as follows:
>
> Make sure the first mails are shown as quickly as possible and extend
> when the user likes to see more or uses the search facility. Display
> incrementally and remain responsive while doing so.

In addition to that:

Why on Earth re-scan a folder that has already been indexed just after
filtering a few mails on it, anyway? I'd like to be able to tell Akonadi:
You are the only one accessing that Maildir folder structure. Stop
needlessly re-scanning folders that you already scanning. You know you
moved some mails there. You know what mails are in there already. That
is what you have that database for to begin with. So there is no need to
re-scan anything. I still know how to tell you do re-scan manually if
need be or, it at all, you can re-scan when I actually access the
folder.

Akonadi still does a huge ton of completely needless work causing a lot
of additional wear to flash based media while doing so.

> I believe in order for Akonadi to be enjoyable by users it absolutely
> must abide by these rules.
>
> Always and not just sometimes.
>
> Also with a lot of mail.
>
> I have seen this before – with Zimbra Collaboration Server which uses
> MySQL/MariaDB and Lucene. Even with a folder with 450000 mails.
>
> So it can be done.
>
> Rant over.

Thanks,
--
Martin


Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

René J.V. Bertin
On Friday April 12 2019 20:11:09 Martin Steigerwald wrote:

Hi,

>filtering a few mails on it, anyway? I'd like to be able to tell Akonadi:
>You are the only one accessing that Maildir folder structure.

You know that, but akonadi has no way of knowing it (esp. if you're using an external db server that other apps could connect to too).

I agree that akonadi has a long-standing dire need for job manager interface a la the activity window that Apple's Mail.app has or used to have; something with a big button to stop all processing, and buttons for each individual job to stop or pause it. (Where a job is something that could be a collaboration between several agents; there's probably little point in continuing a filter job after you cancel the fetch that feeds it.)

>Akonadi still does a huge ton of completely needless work causing a lot
>of additional wear to flash based media while doing so.

This does remind me a bit of KDevelop's scanner/parser, which by default will do a "quick" scan through all files belonging to that project you just opened, even if all that parsing data is already cached. In that case the scan will take a *lot* longer if there's no such cached data; maybe that's true for akonadi too?

R
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Erik Quaeghebeur
On vrijdag 12 april 2019 20:55:41 CEST, René J.V. Bertin wrote:
>
> I agree that akonadi has a long-standing dire need for job
> manager interface […] a big button to stop all
> processing, and buttons for each individual job to stop or pause
> it. […]

Akonadiconsole has something a bit like that: Right-click an agent/resource
and select ‘Abort activity’. It has actually come in handy once or twice
for me, even though it is not fine-grained.

Erik
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

René J.V. Bertin
On Friday April 12 2019 21:00:32 Erik Quaeghebeur wrote:

>Akonadiconsole has something a bit like that: Right-click an agent/resource
>and select ‘Abort activity’. It has actually come in handy once or twice
>for me, even though it is not fine-grained.

Yeah, it could make a good first approximation if it could be launched from Kontact or whatever KDE PIM app you're using (and if the abort feature worked 100% of the time!). I've also been able to unblock more delicate situations with it, IIRC deleting emails that no longer showed up in KMail, via the browser feature.

R.
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Martin Steigerwald
In reply to this post by Erik Quaeghebeur
Erik Quaeghebeur - 12.04.19, 21:00:
> On vrijdag 12 april 2019 20:55:41 CEST, René J.V. Bertin wrote:
> > I agree that akonadi has a long-standing dire need for job
> > manager interface […] a big button to stop all
> > processing, and buttons for each individual job to stop or pause
> > it. […]
>
> Akonadiconsole has something a bit like that: Right-click an
> agent/resource and select ‘Abort activity’. It has actually come in
> handy once or twice for me, even though it is not fine-grained.

Thanks for the hint.

KMail often also has this when you expand progress indicator bar. Just I
found it often to have no effect whatsoever.

Well… after that longer lasting indexing completed, it is still
considerably better with PostgreSQL. So I am happy I changed.

I have the impression it can do more in parallel. I was able to delete a
mail while filtering was ongoing. That did not work before. I read in a
article about improvements in just released major PostgreSQL version
that PostgreSQL can do some queries in parallel that MariaDB/MySQL would
so sequentially. So this may help.

It just seems longer lasting indexing operations still seems to
completely stall Akonadi.

--
Martin


Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Daniel Vrátil -2
In reply to this post by Martin Steigerwald
On Friday, 12 April 2019 19:39:04 CEST Martin Steigerwald wrote:

> Hi.
>
> Since five (!) minutes I am trying to access a mail in a folder that has
> been indexed before and just got the "Retrieving …" thing. Now, it
> finally came up.
>
> Why? Likely cause I happened to click on a folder that has not been
> indexed and then Akonadi prioritized indexing the folder over my most
> recent request.
>
> This in my opinion has been the major issue with Akonadi for years and
> still is the major issue:
>
> Whenever Akonadi decides that it needs to do something in the
> background, it often *completely* stops responding to any user requests.
This is a known issue with maildir/online imap folders, has been known since
forever. I do understand it is frustrating to wait for an email to appear just
because something else triggered a long-running background operation.

There's a fix for this in the making, aiming at 19.08 if everything goes
according to the plan.

>
> However what is Akonadi's purpose? To serve the user of the applications
> that are using it.
>
> For me after the rule to store all data safely and reliably the second
> most important rule would be:
>
> The user goes first. Whenever the user clicks on a mail, a folder, an
> appointment, I let everything pause if need be in order to serve the
> other in a timely manner. I will always prioritize user requests above
> all background processing I do. And if the user decides differently after
> the moment, the user is always right and I stop or background the
> processing the user requested before in order to serve his/her most
> recent request promptly.
>
> Basta.
>
> The third most important rule, that is an addition to the first one is:
>
> I do only work when it is useful. For example Akonadi currently thinks
> it has to re-index a folder just cause it filtered some mails into it.
> However, as a user I may not even intend to look into the folder. It
> just has to stuff those mails into it and *be done with it*. And when I
> click the folder, it can still apply rule 1 as follows:
>
> Make sure the first mails are shown as quickly as possible and extend
> when the user likes to see more or uses the search facility. Display
> incrementally and remain responsive while doing so.
>
>
> I believe in order for Akonadi to be enjoyable by users it absolutely
> must abide by these rules.
>
> Always and not just sometimes.
>
> Also with a lot of mail.
>
> I have seen this before – with Zimbra Collaboration Server which uses
> MySQL/MariaDB and Lucene. Even with a folder with 450000 mails.
>
> So it can be done.
>
> Rant over.
>
> Thanks,

--
Daniel Vrátil
www.dvratil.cz | [hidden email]
IRC: dvratil on Freenode (#kde, #kontact, #akonadi, #fedora-kde)

GPG Key: 0x4D69557AECB13683
Fingerprint: 0ABD FA55 A4E6 BEA9 9A83 EA97 4D69 557A ECB1 3683

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Daniel Vrátil -2
In reply to this post by Martin Steigerwald
On Friday, 12 April 2019 21:57:21 CEST Martin Steigerwald wrote:
> Erik Quaeghebeur - 12.04.19, 21:00:
> > On vrijdag 12 april 2019 20:55:41 CEST, René J.V. Bertin wrote:
> > > I agree that akonadi has a long-standing dire need for job
> > > manager interface […] a big button to stop all
> > > processing, and buttons for each individual job to stop or pause
> > > it. […]

Then plan is to rather be able to run multiple tasks in parallel so the
indexing agent requesting the maildir resource to load every single email you
have from the storage would no longer block requests from KMail to retrieve a
single email to display to the user.

> >
> > Akonadiconsole has something a bit like that: Right-click an
> > agent/resource and select ‘Abort activity’. It has actually come in
> > handy once or twice for me, even though it is not fine-grained.
>
> Thanks for the hint.
>
> KMail often also has this when you expand progress indicator bar. Just I
> found it often to have no effect whatsoever.
>
> Well… after that longer lasting indexing completed, it is still
> considerably better with PostgreSQL. So I am happy I changed.
>
> I have the impression it can do more in parallel. I was able to delete a
> mail while filtering was ongoing. That did not work before. I read in a
> article about improvements in just released major PostgreSQL version
> that PostgreSQL can do some queries in parallel that MariaDB/MySQL would
> so sequentially. So this may help.
David Faure did a *lot* of work to track down and fix the database deadlock
issues we had with MySQL (and with PostgreSQL as well, they just did not show
up that much), you should see a lot of improvement in speed and reliability in
the upcoming releases.

> It just seems longer lasting indexing operations still seems to
> completely stall Akonadi.



--
Daniel Vrátil
www.dvratil.cz | [hidden email]
IRC: dvratil on Freenode (#kde, #kontact, #akonadi, #fedora-kde)

GPG Key: 0x4D69557AECB13683
Fingerprint: 0ABD FA55 A4E6 BEA9 9A83 EA97 4D69 557A ECB1 3683

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

René J.V. Bertin
In reply to this post by Martin Steigerwald
So the latest version of pgloader (github:dimitri/pgloader) seems to be able to transfer the akonadi mysql db to a postgresql db successfully (though with some errors reported by the postgres server).

This *could* open the doors to a lossless migration, but only if this conversion actually makes sense. I guess it would if akonadi writes its database to sqlite/mysql/postgresql with just a thin backend that sends the proper commands for the engine in use. But maybe it does additional things to optimise the database to the engine? Or it could use the backend name in the stored metadata which would also make migration pointless?

Daniel, what can you say about this?

R.
Reply | Threaded
Open this post in threaded view
|

Re: Not all that much better with PostgreSQL

Martin Steigerwald
In reply to this post by Daniel Vrátil -2
Hi Daniel.

Thank you for your answer.

Daniel Vrátil - 17.04.19, 15:46:

> On Friday, 12 April 2019 21:57:21 CEST Martin Steigerwald wrote:
> > Erik Quaeghebeur - 12.04.19, 21:00:
> > > On vrijdag 12 april 2019 20:55:41 CEST, René J.V. Bertin wrote:
> > > > I agree that akonadi has a long-standing dire need for job
> > > > manager interface […] a big button to stop all
> > > > processing, and buttons for each individual job to stop or pause
> > > > it. […]
>
> Then plan is to rather be able to run multiple tasks in parallel so
> the indexing agent requesting the maildir resource to load every
> single email you have from the storage would no longer block requests
> from KMail to retrieve a single email to display to the user.

That sounds awesome.

I hope this brings a major relief.

For now I keep Akonadi with PostgreSQL. Its better than with MariaDB so
far. Quite a bit better and with above issue fixed… let's see.

> > > Akonadiconsole has something a bit like that: Right-click an
> > > agent/resource and select ‘Abort activity’. It has actually come
> > > in
> > > handy once or twice for me, even though it is not fine-grained.
> >
> > Thanks for the hint.
> >
> > KMail often also has this when you expand progress indicator bar.
> > Just I found it often to have no effect whatsoever.
> >
> > Well… after that longer lasting indexing completed, it is still
> > considerably better with PostgreSQL. So I am happy I changed.
> >
> > I have the impression it can do more in parallel. I was able to
> > delete a mail while filtering was ongoing. That did not work
> > before. I read in a article about improvements in just released
> > major PostgreSQL version that PostgreSQL can do some queries in
> > parallel that MariaDB/MySQL would so sequentially. So this may
> > help.
>
> David Faure did a *lot* of work to track down and fix the database
> deadlock issues we had with MySQL (and with PostgreSQL as well, they
> just did not show up that much), you should see a lot of improvement
> in speed and reliability in the upcoming releases.

David just blogged about it:

https://blogs.kde.org/2019/04/17/2019-toulouse-pim-sprint-report

Thanks,
--
Martin