mikemol at gmail.com
Fri Oct 14 16:30:37 BST 2016
On Friday, October 14, 2016 09:12:10 AM Daniel Vrátil wrote:
> On Monday, October 10, 2016 10:12:42 AM CEST Michael Mol wrote:
> > A *huge* chunk of this, for me, turned out to be in the abysmal MySQL
> > configuration it defaults to. I posted about it on Google+ and on /r/kde
> > several months ago. https://plus.google.com/+MichaelMolG/posts/HQQH6RHhLNw
> > I'll note my understanding of Akonadi has improved slightly since I wrote
> > that; I wasn't able to find where Akonadi's MySQL configuration was kept,
> > so I switched out to using a system mysqld.That's not necessary if you
> > know where Akonadi sources its MySQL configuration from.
> > I'll also add that I'm syncing nearly a decade's worth of email from GMail
> > into Akonadi, hundreds of thousands of emails. People without as much
> > email
> > history likely won't see the same kind of performance difficulties I do.
> > Here's the relevant excerpt:
> > Altogether, here are the changes I've made:
> > In my.cnf
> Oooo, this looks quite interesting, thanks.
> The major problem with the default database configuration is to find the
> right balance. If you go too high, regular users who only have a gmail with
> a few emails with funny cat gifs and powerpoint presentations will complain
> that we use to much resources. On the other hand if we go too low, power
> users will complain that their 2 million emails from 10 years of kernel
> mailing list takes ages to load. Striking the right balance is hard.
> There is a good point that we could probably increase all the default values
> a bit since the computers are generally better equipped nowadays that they
> were some 5 years ago, but again, we need to be rather conservative about
Based on some subsequent discussions on-list (and also accidentally off-list),
I've rethought some of my settings.
Not sure how much I favor cranking up the buffer pool any more. For sure, it
will speed things up by reserving resources for Akonadi, but it does take
resources away from other applications. Keeping a smallish buffer pool would,
indeed, have MySQL depend more on the system page cache, which is, at least,
shared with other applications.
Also, strike the skip_innodb-double-buffer-write. My information on that was
apparently old, and it *will* corrupt data if mysqld is terminated before it
finishes writing data to disk. (MySQL will write data in chunks larger than the
size Linux will guarantee atomic disk I/O, so you can have partially-completed
writes. Not the fault of the filesystem, but rather of the VFS layer, AIUI.)
Really, you should look at per-collection table partitioning. This would
massively speed up Akonadi where it deals with a large collection at the same
time as any other collection; even if your indexes are effective, they have to
contemplate all the data in a table, which means a lookup on a tiny collection
will be impacted by sharing the index with all the other collections in the
table. Per-collection partitioning would mean each collection gets an index
dedicated to it, which means hot collections will tend to have their index
already present in the buffer pool.
> I would like to see some sort of a self-balancing configuration, where the
> database could "suggest" optimal settings based on long-time load and
> available hardware resources. That would magically solve the issue described
> above, but that's more of a day-dreaming :-)
That'd be nice if peoples' usage patterns were constant, but power users' tend
not to be, IME.
My preference would be for there to be some sliders hidden away under an
"Advanced/Performance" tab for configuring Akonadi. You might look at how
scripts like mysqltuner.pl work, and see about using the logic you find there
to come up with baseline recommended settings.
> Pablo made a good point elsewhere in this thread about the ridiculous amount
> of queries we do. I've added some more optimizations to master recently to
> speed up some things, but there's still a long way to go :(
Yeah, I'd have to learn a bit more about Akonadi's architecture, but I get a
strong impression there's a lot you could do with batching.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 455 bytes
Desc: This is a digitally signed message part.
More information about the kdepim-users