[Kde-pim] Akonadi MySQL backend: tuning for larger accounts or switching to MariaDB with a different storage engine?

[Daniel Vrátil]

On Wednesday 26 of March 2014 13:06:23 Martin Steigerwald wrote:
> Hi!
> 
> I reported
> 
> Bug 332626 - MySQL tuning: adaption of MySQL tuning options for larger
> accounts
> https://bugs.kde.org/show_bug.cgi?id=332626
> 
>
> But its somewhat arguable, I know.
> 

Hi,

I actually talked about optimizing mysql.conf with MariaDB people on FOSDEM in 
February.

> I think I saw quite an improvement with raising the default low innodb
> buffer pool size of Akonadi of 80 MiB, which is even below the MySQL
> default of 128 MiB, to 256 MiB helped with a 11 GiB large POP3 account,
> especially when synchronizing linux-kernel-ml folder with about 223000
> unread mails. And also on retrieving and filtering new mails there, which
> can make KMail quite unresponsive on a quite powerful machine (Sandybridge,
> 8 GB RAM, dual SSD BTRFS RAID 1). I will do some testing, yet it is
> difficult to know for sure. mysqltuner.pl script gave some clear
> recommendations tough, which are to high for a desktop machine tough, I
> think (>1 GiB of innodb buffer pool size). It seems it recommends adjusting
> to actual data size in database, which would only be suitable if all of the
> data is accessed often.

The problem with innodb_buffer_pool_size is, that it directly impacts the 
amount of RAM the mysqld process takes, which  makes it difficult to find a  
good compromise between what LKML subscribers need and what users with couple 
of calendars and an adressbook need.

> What is the amount data Akonadi accesses frequently? Are there any
> experiences with that? I bet it depends highly on how the user uses it and
> the amount of mails in a single folder to be synchronized.

This is difficult to say and I don't think anyone ever tried to benchmark or 
measure it. Indeed it does depend on how you use Akonadi, an

When you open a folder in KMail, we run

SELECT * FROM PartTable WHERE parentCollection = XY AND partType = 
"PLD:ENVELOPE"

which will return all envelopes in the collection (that's subject, date, from, 
to, cc, bcc, ...)

akonadi=# SELECT MIN(datasize), MAX(datasize), AVG(datasize) FROM PartTable 
WHERE partTypeId = 3;
 min |  max  |         avg          
-----+-------+----------------------
  45 | 23354 | 487.8883434774372617
(1 row)

akonadi=# SELECT datasize AS median FROM PartTable WHERE partTypeId =3 ORDER 
BY datasize DESC OFFSET (SELECT (COUNT(*) + 1) / 2 FROM PartTable WHERE 
partTypeId = 3) LIMIT 1;
 median 
--------
    451

(partTypeId = 3 == SELECT id FROM PartTypeTable WHERE ns = 'PLD' AND 
name='ENVELOPE')

Which makes roughly 6.5MB of data in a collection of 15 000 items. I don't 
think we have any application (except for the indexer) that would request the 
full payload of all items in a collection of such size. I know KAddressbook 
and KOrganizer do that, but the collections there are much smaller, and so are 
the actual payloads.

> 
> What do you think?

I think we can try to play with the values a bit, having some kind of 
benchmark or test tool would be nice :-)

> Do you have any plans to switch to MariaDB and probably investigating
> storage engines which may be more suitable for tuning to desktop loads?

MariaDB is a drop-in replacement for MySQL, so it's supported by default (I've 
been using MariaDB instead of MySQL for over year before I switched to PSQL)

AFAIK the only engine that we can really use is XtraDB, which is just an 
enhanced fork of InnoDb.

> InnoDB is difficult as innodb_buffer_pool_size needs to take free memory
> into account which can change quite rapidly on desktops or anything else
> than a dedicated database server. A engine which uses Linux pagecache for
> most of its caching would be interesting I think.

Maybe we could have some kind of "initial setup" tool that would check 
available memory and would update innodb_buffer_pool_size depending on that 
(taking in account some max limit, min limit, max percentage, ...)


Cheers,
Dan

> 
> 
> Just wanted to raise this topic.
>
> Ciao,
> Martin

-- 
Daniel Vrátil | dvratil at redhat.com | dvratil on #kde-devel, #kontact, #akonadi
KDE Desktop Team
Associate Software Engineer, Red Hat, Inc.

GPG Key: 0xC59D614F6F4AE348
Fingerprint: 4EC1 86E3 C54E 0B39 5FDD B5FB C59D 614F 6F4A E348
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 490 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kde-pim/attachments/20140326/7b8fa4ab/attachment.sig>
-------------- next part --------------
_______________________________________________
KDE PIM mailing list kde-pim at kde.org
https://mail.kde.org/mailman/listinfo/kde-pim
KDE PIM home page at http://pim.kde.org/