force minimum innodb_buffer_pool_size to avoid high mariadb 10.0 CPU usage

[pricejn2]

Proposed fix: set minimum innodb_buffer_pool_size = 5M

I have been experiencing results similar to https://www.drupal.org/node/2246691 on some servers running MariaDB 10.0 (Debian Wheezy on 2.2.9 and 2.3.2).

Here are the results of mysqltuner.perl

 >>  MySQLTuner 1.2.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
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.0.13-MariaDB-1~wheezy is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3K (Tables: 26)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 1M (Tables: 369)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 307

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 10s (3K q [9.322 qps], 573 conn, TX: 9M, RX: 884K)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 15.9G global + 4.2M per thread (30 max threads)
[OK] Maximum possible memory usage: 16.0G (12% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 13% (4/30)
[OK] Key buffer size / total MyISAM indexes: 3.0M/122.0K
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7 sorts)
[!!] Joins performed without indexes: 7
[OK] Temporary tables created on disk: 10% (4 on disk / 37 total)
[OK] Thread cache hit rate: 99% (4 created / 573 connections)
[OK] Table cache hit rate: 24% (128 open / 530 opened)
[OK] Open file limit used: 0% (6/196K)
[OK] Table locks acquired immediately: 100% (645 immediate / 645 locks)
[OK] InnoDB data size / buffer pool: 1.6M/5.0M

If you compare Data in InnoDB tables: 1M (this seems wrong to me anyways, but that's different altogether) with InnoDB data size / buffer pool: 1.6M/5.0M, you'll see the crux of the problem.

As https://github.com/omega8cc/boa/blob/master/BARRACUDA.sh.txt#L2492 uses Data in InnoDB tables to define innodb_buffer_pool_size, there should be a minimum enforced to avoid situations when mysqltuner reported data size < reported buffer pool.

In the above results, BARRACUDA.sh defined innodb_buffer_pool_size = 1M, but it should never be less than 5M or there is high CPU load.

[omega8cc]

That is a good find, thank you!