Re: Akonadi dsaster

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
> 
> <snip>
> 
> 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
> that.

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.

-- 
:wq

-----BEGIN PGP SIGNATURE-----

iQEcBAABCAAGBQJYAPodAAoJED5TcEBdxYwQIaEIAIkSQn2QYl+YReSxWyfoe2aU
ZQHc7H5e12bgsPS56rFkxeQJ01nUnRcKynvAZ1rF383RTFYXcidzCfOMbGEg0bn+
Y8l2WA9gfjTxPNfip/n7O2sDyhENwL5RPSaaE97ez1Bl5yR7E6Yc1MxrIhrqtUJX
Zr7+ChI1v6BqSCvRG46WgmJJHdTjBZqCheaunA09X0OyLUK3DuCYvFCodfN9Wqvq
hUwWmmjq9O4y+etZrlgH9c1T4FbLC3wP40TdRvdDoYOy7o84mIMPBBDylQvgJlGo
f3JHyXV/qH1m801Prnw0Jbboi4ohTRm05wnRZSQJnP+xwI5SwTffnRWOG1V8Y6M=
=0Ew6
-----END PGP SIGNATURE-----