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

Thu Mar 27 11:24:39 GMT 2014

On Wednesday 26 of March 2014 22:12:53 Martin Steigerwald wrote:
> Am Mittwoch, 26. März 2014, 21:22:33 schrieb Martin Steigerwald:
> > Am Mittwoch, 26. März 2014, 20:22:28 schrieb Martin Steigerwald:
> > > > > 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.
> > 
> > There is indeed some stuff in Akonadiconsole there.
> > 
> > And now I had this long wait for KMail to do anything again. After
> > downloading / filtering new mail.
> > 
> > According to Akonadiconsole akonadi nepomuk feeder was indexing and
> > according to atop akonadi maildir resource was hogging one Sandybridge
> > core
> > for 100% for minutes.
> > 
> > Akonadiconsole Query Debugger showed queries to get payload bodies, also
> > from that kernel-ml folder. And I have quite some wait times in Job
> > Tracker
> > now, for Akonadi::ItemFetchJob. I have wait times up to:
> > 
> > 00:05:33.069
> > 
> > Five of those wait times between 5 and 6 minutes.
> > 
> > Four between 2-3 minutes.
> > 
> > Five between 10-20 seconds.
> > 
> > Why this time is so long, I don´t know.
> > 
> > 
> > Akonadi seems to block out KMail due to this mail indexing background
> > activity, which I reported already:
> > 
> > Bug 331848 - displaying, moving, deleting mails takes 10-20 seconds when
> > Akonadi synchronizes in background
> > 
> > https://bugs.kde.org/show_bug.cgi?id=331848
> > 
> > But at work I do not have mail indexing enabled so far, yet this was on
> > NFS
> > still as I reported the bug.
> > 
> > 
> > I think I just disable mail indexing for now for this POP3 setup, until I
> > can install KDE SC 4.13 including new KDEPIM and Baloo.
> > 
> > Well, I learned about debugging performance issues with Akonadi.
> > 
> > 
> > I am inclined to close my MySQL tuning related report. I do not have the
> > impression anymore that it is very important at the moment. What do you
> > think?
> > 
> > So finishing analysis for now.
> 
> I got miss rates now:
> 
> 
> 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     4871
> Old database pages 1778
> Modified db pages  0
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages made young 659333, not young 0
> 3302.78 youngs/s, 0.00 non-youngs/s
> Pages read 619033, created 84, written 1204
> 3191.25 reads/s, 0.53 creates/s, 10.80 writes/s
> Buffer pool hit rate 977 / 1000, young-making rate 24 / 1000 not 0 / 1000
> Pages read ahead 79.79/s, evicted without access 21.00/s, Random read ahead
> 0.00/s
> LRU len: 4871, unzip_LRU len: 0
> I/O sum[267946]:cur[0], unzip sum[0]:cur[0]
> 
> 
> Another example:
> ----------------------
> 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     4942
> Old database pages 1804
> Modified db pages  10
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages made young 1076283, not young 0
> 10570.21 youngs/s, 0.00 non-youngs/s
> Pages read 1015530, created 110, written 1673
> 10471.76 reads/s, 0.50 creates/s, 10.00 writes/s
> Buffer pool hit rate 965 / 1000, young-making rate 36 / 1000 not 0 / 1000
> Pages read ahead 479.76/s, evicted without access 78.46/s, Random read ahead
> 0.00/s
> LRU len: 4942, unzip_LRU len: 0
> I/O sum[88144]:cur[7656], unzip sum[0]:cur[0]
> 
> see:
> 
> Bug 332626 - MySQL tuning: adaption of MySQL tuning options for larger
> accounts
> https://bugs.kde.org/show_bug.cgi?id=332626#c5
> 
> 
> And I got:
> 
> Bug 332653 - After mail receive on filtering: Unable to retrieve item from
> resource: NO ImapParserException: Unable to read more data
> 
> 
> This should have been with disabled mail indexing. I will observe further
> and really close analysis for now. Need to get a clear head again, before
> continuing.
> 
> Ciao,

Hi,

thanks a lot for all the analysis. I will need to read up on this and consult 
MySQL documentation first, so I'll go through it next week :-)

Dan

-- 
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/20140327/d972b7a6/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/