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

Wed Mar 26 19:53:20 GMT 2014

Am Mittwoch, 26. März 2014, 20:22:28 schrieb Martin Steigerwald:
> Am Mittwoch, 26. März 2014, 15:04:27 schrieb 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.
> > 
> > I actually talked about optimizing mysql.conf with MariaDB people on
> > FOSDEM
> > in February.
> 
> Thanks for your prompt reply, Daniel.
> 
> What were the results of your talk? Did you receive any recommendations? I
> am a Linux Performance analysis and tuning generalist, so I just have the
> basic knowledge about it.
> 
> > > 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.
> 
> Interestingly after moving Akonadi data to local storage (Ext4 SoftRAID1), I
> did not find much difference between 80 MiB and 512 MiB of innodb buffer
> size.
> 
> Also as I reduced the POP3 setup account to 80 MiB default again which I am
> still using now, I do not see any visible differences either. I will observe
> this for a bit more, as I really had the impression that raising it on that
> setup helped. Sure thing, some benchmarks make sense.
> 
> Christian Mollekopf wrote in a bug report, he is even just using 8 MiB
> without issues, so…
> 
> > > 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.
> 
> Interesting. I think there are some MySQL status variables that may help
> with estimation. Here some examples from the POP3 setup with currently 80
> MiB InnoDB buffer pool size[1]:

Ho, there is more[1]:

SHOW ENGINE INNODB STATUS\G;

[1] http://dev.mysql.com/doc/refman/5.5/en/show-engine.html
https://mariadb.com/kb/en/show-engine/

which gives a heck of a lot of output. Browsing through it a bit to see 
whether there is anything interesting.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 85852160; in additional pool allocated 0
Dictionary memory allocated 103892
Buffer pool size   5120
Free buffers       0
Database pages     4869
Old database pages 1777
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19328232, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18648345, created 2768, written 27262
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 
0.00/s
LRU len: 4869, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Old database pages and the young stuff may give a hint, but I didn´t find an 
explaination to it in the docs. But there are some hints:

http://dba.stackexchange.com/questions/56494/making-sense-of-innodb-buffer-pool-stats

And yada: We have a buffer pool hit rate there:

Buffer pool hit rate 1000 / 1000

Well according to the stack exchange link my point is mood, cause the buffer 
pool is perfectly utilized. Thus probably good to close bug report I made.

I will observe this for a bit still, cause if restarted recently, the values 
are not that meaning full.

> > > 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, ...)
> 
> Well I am backing off a bit at the moment. Maybe those read bursts I
> observed where not related to InnoDB buffer pool size, but to something
> else. If I see it again, I try to look in Akonadiconsole as to what Akonadi
> is doing there.

I am thankful for any hints on where to look regarding Akonadi related 
performance issues. I think I want to dig this a bit :). 

I found information on testing framework[2], but not regarding performance 
issues. I think I read through the techbase documentation to understand 
Akonadi a bit better.

[2] http://techbase.kde.org/Projects/PIM/Akonadi/Testing

Ciao,
-- 
Martin 'Helios' Steigerwald - http://www.Lichtvoll.de
GPG: 03B0 0D6C 0040 0710 4AFA  B82F 991B EAAC A599 84C7
_______________________________________________
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/