[Akonadi] [Bug 332626] New: MySQL tuning: adaption of MySQL tuning options for larger accounts

Wed Mar 26 11:53:57 GMT 2014

https://bugs.kde.org/show_bug.cgi?id=332626

            Bug ID: 332626
           Summary: MySQL tuning: adaption of MySQL tuning options for
                    larger accounts
    Classification: Unclassified
           Product: Akonadi
           Version: 4.12
          Platform: Debian unstable
                OS: Linux
            Status: UNCONFIRMED
          Severity: wishlist
          Priority: NOR
         Component: general
          Assignee: kdepim-bugs at kde.org
          Reporter: ms at teamix.de

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 at 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 at 127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root at localhost' has no password set.
[!!] User 'root at 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 at merkaba:~/Performance/MySQL/MySQLTuner-perl> ./mysqltuner.pl
--socket=/tmp/akonadi-ms.Y8yWXp/mysql.socket

 >>  MySQLTuner 1.3.0 - Major Hayden <major at 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 at 127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root at localhost' has no password set.
[!!] User 'root at 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 at 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 at 127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root at localhost' has no password set.
[!!] User 'root at 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.

-- 
You are receiving this mail because:
You are the assignee for the bug.