Bug 332626

Summary: MySQL tuning: adaption of MySQL tuning options for larger accounts
Product: [Frameworks and Libraries] Akonadi Reporter: Martin Steigerwald <martin.steigerwald>
Component: serverAssignee: kdepim bugs <kdepim-bugs>
Status: REPORTED ---    
Severity: wishlist CC: dvratil, Martin
Priority: NOR    
Version: 4.12   
Target Milestone: ---   
Platform: Debian unstable   
OS: Linux   
Latest Commit: Version Fixed In:
Sentry Crash Report:
Attachments: default configuration as of Akonadi 1.11.0-1 debian package

Description Martin Steigerwald 2014-03-26 11:53:57 UTC
I do think innodb_buffer_pool_size default of 80 MiB is very low, even lower than the default value of 128 MiB. With that setting I have seen minute long read bursts of mysqld process on laptop with huge POP3 account which seemed to get way better after raising to 256 MiB. On my workstation I had severe performance issues which raising buffer pool size to 1 GiB did not solve, but they turned out to be mostly NFS related (probably with storing payloads in file_db_data, see bug #332013 and #331848). After moving Akonadi to local SoftRAID 1 based Ext4 I didn't test with 80 MiB buffer pool size yet, but I will do so now, will need some time to get an impression tough.

The default 80 MiB value may be enough for small accounts, but I think it may be good to offer some tuning options / auto-reconfiguring for larger mail accounts.

Alternatively: Evaluate whether MariaDB may provide a more suitable storage engine for Akonadi and switch to MariaDB. I would look into engines that use the Linux page cache for most caching, as Linux autotunes the page cache to the maximum of unused memory as needed. Actually this might be the most satisfying approach as I think auto-tuning InnoDB on desktop machines is difficult as amount of free RAM may change quite suddenly.

Setting as wishlist. To consider. I read MySQL devs have helped with initial MySQL configuration, so there may be reasons for the values I do not know.

Reproducible: Always

Steps to Reproduce:
Review default configuration that Akonadi MySQL backend created. Attached.
Actual Results:  
Pretty low sizes of innodb_buffer_pool_size of 80 MB and other issues. Three examples made with mysqltuner.pl script[1].

[1] git clone https://github.com/major/MySQLTuner-perl.git

Especially innodb_buffer_pool_size IMHO is very low. Granted I wouldn't use the recommended 80% of memory on a dedicated database server[2], as a Akonadi started MySQL isn't usually running on such a server. Yet, 80 MiB appears to be quite low to me.

[2] http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size



Workstation at work, 12 GB of RAM, meanwhile local SoftRAID 1 Ext4 storage, IMAP account of about 30GB of size according to Exchange server, but I think its more about 10 GB as Zimbra which we used previously reported (on the other hand Zimbra deduplicated identical mails, i.e. mails to mutiple mailing lists):

./mysqltuner.pl --socket=/tmp/akonadi-ms.RdWrrY/mysql.socket

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.5.35-2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 1G (Tables: 11)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@mango' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@mango' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 15s (18K q [250.307 qps], 46 conn, TX: 21M, RX: 799K)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 98.0M global + 2.8M per thread (256 max threads)
[OK] Maximum possible memory usage: 802.0M (6% of installed RAM)
[OK] Slow queries: 0% (0/18K)
[OK] Highest usage of available connections: 15% (40/256)
[!!] Key buffer size / total MyISAM indexes: 16.0K/97.0K
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21 sorts)
[!!] Joins performed without indexes: 11
[OK] Temporary tables created on disk: 5% (120 on disk / 2K total)
[!!] Thread cache hit rate: 13% (40 created / 46 connections)
[OK] Table cache hit rate: 37% (75 open / 201 opened)
[OK] Open file limit used: 3% (48/1K)
[OK] Table locks acquired immediately: 100% (25K immediate / 25K locks)
[!!] InnoDB  buffer pool / data size: 80.0M/1.2G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 97.0K)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (> 3)
    innodb_buffer_pool_size (>= 1G)

=> I bet I will set innodb_buffer_pool_size to 512 MB or even 1 GB again.




Sandybridge Laptop, 8 GB RAM, company user account, located on ecryptfs, located on BTRFS RAID 1 on mSATA + regular SATA SSD, same IMAP account:

ms@merkaba:~/Performance/MySQL/MySQLTuner-perl> ./mysqltuner.pl --socket=/tmp/akonadi-ms.Y8yWXp/mysql.socket

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.5.35-2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 1G (Tables: 11)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@merkaba' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@merkaba' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 32m 30s (31K q [16.341 qps], 37 conn, TX: 64M, RX: 2M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 98.0M global + 2.8M per thread (256 max threads)
[OK] Maximum possible memory usage: 802.0M (10% of installed RAM)
[OK] Slow queries: 0% (0/31K)
[OK] Highest usage of available connections: 12% (31/256)
[!!] Key buffer size / total MyISAM indexes: 16.0K/97.0K
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 284 sorts)
[!!] Joins performed without indexes: 11
[OK] Temporary tables created on disk: 10% (352 on disk / 3K total)
[!!] Thread cache hit rate: 16% (31 created / 37 connections)
[OK] Table cache hit rate: 38% (78 open / 204 opened)
[OK] Open file limit used: 3% (48/1K)
[OK] Table locks acquired immediately: 100% (44K immediate / 44K locks)
[!!] InnoDB  buffer pool / data size: 80.0M/1.5G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 97.0K)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (> 3)
    innodb_buffer_pool_size (>= 1G)

=> I think I try raising innodb_buffer_pool_size to 256 MiB here, more is problematic, as the 8 GiB is used up quite nicely already.


Sandybridge Laptop, 8GB of RAM, 11GB large POP3 account and some small POP3 account, same BTRFS RAID 1, no ecryptfs:

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.5.35-2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 1G (Tables: 11)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@merkaba' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@merkaba' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18s (8K q [484.333 qps], 46 conn, TX: 28M, RX: 363K)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 98.0M global + 2.8M per thread (256 max threads)
[OK] Maximum possible memory usage: 802.0M (10% of installed RAM)
[OK] Slow queries: 0% (0/8K)
[OK] Highest usage of available connections: 15% (40/256)
[!!] Key buffer size / total MyISAM indexes: 16.0K/97.0K
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 20 sorts)
[!!] Joins performed without indexes: 11
[OK] Temporary tables created on disk: 8% (115 on disk / 1K total)
[!!] Thread cache hit rate: 13% (40 created / 46 connections)
[OK] Table cache hit rate: 39% (81 open / 207 opened)
[OK] Open file limit used: 3% (48/1K)
[OK] Table locks acquired immediately: 100% (12K immediate / 12K locks)
[!!] InnoDB  buffer pool / data size: 80.0M/1.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 97.0K)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (> 3)
    innodb_buffer_pool_size (>= 1G)

=> I think I'd  go for 256 MiB innodb_buffer_pool_size


Expected Results:  
Tuning MySQL InnoDB performance variables to actual database size – while taking into account the available RAM. Or: Switch to MariaDB if it has a more suitable storage engine.
Comment 1 Martin Steigerwald 2014-03-26 11:55:53 UTC
Created attachment 85762 [details]
default configuration as of Akonadi 1.11.0-1 debian package

I will try my setups with with default configuration for today for a while, and then raise some values to try to get a comparison.
Comment 2 Martin Steigerwald 2014-03-26 12:05:52 UTC
 I think mysqltuner recommendations (>1 GiB of innodb buffer pool size) are too high. It seems it recommends adjusting to actual data size in database, which would only be suitable if all of the data is accessed often.

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.
Comment 3 Martin Steigerwald 2014-03-26 20:07:33 UTC
See

[Kde-pim] Akonadi MySQL backend: tuning for larger accounts or switching to MariaDB with a different storage engine?
http://lists.kde.org/?l=kde-pim&m=139583561813493&w=2

for further discussion. Actually so far I found no trace as to default InnoDB buffer size being a problem.
Comment 4 Martin Steigerwald 2014-03-26 20:27:38 UTC
I cannot prove any issues with default InnoDB buffer size, instead I found KMail being blocked out on mail indexing, so feel free to close. I cannot in Status drop down.

See the mail thread mentioned in last comment.
Comment 5 Martin Steigerwald 2014-03-26 20:47:13 UTC
Scratch that: I saw some small buffer pool miss rate:

mysql> SHOW ENGINE INNODB STATUS\G;
[…]

----------------------
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]




So a little raising for my setup might be beneficial. This as I saw akonadi maildir agent and MySQL at 100% CPU usage for a while after restarting Akonadi due to bug with retrieving Items I report in a moment as I disabled Nepomuk mail indexing, downloaded mail and then quitted it cause maildir agent crashed.
Comment 6 Martin Steigerwald 2014-03-26 21:04:22 UTC
This bug:

Bug 332653 - After mail receive on filtering: Unable to retrieve item from resource: NO ImapParserException: Unable to read more data
Comment 7 Justin Zobel 2021-03-09 05:46:52 UTC
Thank you for the bug report.

As this report hasn't seen any changes in 5 years or more, we ask if you can please confirm that the issue still persists.

If this bug is no longer persisting or relevant please change the status to resolved.