[Kde-pim] Akonadi MySQL backend: tuning for larger accounts or switching to MariaDB with a different storage engine?
Martin Steigerwald
Martin at lichtvoll.de
Thu Mar 27 13:19:04 GMT 2014
Hi,
Am Donnerstag, 27. März 2014, 12:24:39 schrieb Daniel Vrátil:
> 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,
[…]
> 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 :-)
Yeah, me too.
The buffer pool hit rate appears still quite good and I never have seen
anything below 950/1000 so far, so I think its no real big issue. But I also
need to understand this better :)
I enjoyed digging a bit deeper and learning more about Akonadi and what is
possible in Akonadiconsole. I think Akonadiconsole is a good place to test
basic operations.
I am still trying to find my way to analyse why KMail doesn´t respond for some
minutes for example in order to at least provide more useful bug reports. So
on how to get from an application bug to analyse what really is happening on
the surface below. I have some ideas meanwhile and I think my issues are more
with payload handling than the actual database operations. Especially
synchronizing folders seem to be expensive. Especially if they have tens of
thousands mails in it. Not talking about that kernel-ml folder with 200000
mails.
In KMail 1 I archived folders via having it move old mails to mbox folders via
folder archiving. I used a mixed maildir resource for that. Which I still did
not dare to integrate back into Akonadi. I am fine with splitting folders,
using mbox archive folder, but doing one mbox resource for each mbox folder I
want to have for archiving, does not seem to make good sense to me. So I
really like that mixedmaildir thing. Folder archive agent may be an
alternative, but I´d like to have the old mails read-accessible still and
searchable, which would only work by zgrep´ing through the archive files Folder
Archive Agent creates.
Starting with KDE SC 4.13 I may try to add back that old mixedmaildir resource
and setup archiving to it again. Well I may just try before, but *without*
mail indexing via Nepomuk :)
--
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/
More information about the kde-pim
mailing list