Look into default MariaDB server options

[carlalexander]

It might be worth the time to investigate the default options for MariaDB. Is there anything we can do to have some better defaults?

The debops.mariadb_server options are pretty barebone as well. We might be able to push some of these upstream too. I'll start by running MySQLTuner and see what it says.

[carlalexander]

Here's the output for perl mysqltuner.pl --buffers:

 >>  MySQLTuner 1.6.9 - 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.

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.24-MariaDB-1~trusty
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 39M (Tables: 12)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9d 5h 13m 49s (269K q [0.338 qps], 29K conn, TX: 808M, RX: 32M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 624.0M global + 7.4M per thread (100 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 32.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: ON - ON DEMAND
[--]  +-- Query Cache Size: 64.0M
[--] Per Thread Buffers
[--]  +-- Read Buffer: 2.0M
[--]  +-- Read RND Buffer: 1.0M
[--]  +-- Sort Buffer: 4.0M
[--]  +-- Thread stack: 288.0K
[--]  +-- Join Buffer: 128.0K
[--] Binlog Cache Buffers
[--]  +-- Binlog Cache: 32.0K
[!!] Maximum reached memory usage: 698.1M (142.45% of installed RAM)
[!!] Maximum possible memory usage: 1.3G (278.47% of installed RAM)
[OK] Slow queries: 0% (0/269K)
[OK] Highest usage of available connections: 10% (10/100)
[OK] Aborted connections: 0.00%  (0/29616)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 75% (4K on disk / 5K total)
[OK] Thread cache hit rate: 99% (10 created / 29K connections)
[OK] Table cache hit rate: 227% (107 open / 47 opened)
[OK] Open file limit used: 6% (62/1K)
[OK] Table locks acquired immediately: 100% (21K immediate / 21K locks)
[OK] Binlog cache memory access: 99.73% ( 6916 Memory / 6935 Total)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enougth for your version (10.0.24-MariaDB-1~trusty)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/121.0K
[OK] Read Key buffer hit rate: 96.0% (50 cached / 2 reads)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (20K cached / 39 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--]  +-- InnoDB Buffer Pool: 256.0M
[--]  +-- InnoDB Buffer Pool Instances: 8
[--]  +-- InnoDB Additional Mem Pool: 8.0M
[--]  +-- InnoDB Log Buffer: 8.0M
[--]  +-- InnoDB Log Buffer Free: 11.2K
[--]  +-- InnoDB Log Buffer Used: 16.0K
[OK] InnoDB buffer pool / data size: 256.0M/39.3M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 30.01% (4917 used/ 16383 total)
[OK] InnoDB Read buffer efficiency: 99.60% (678139 hits/ 680871 total)
[!!] InnoDB Write Log efficiency: 88.58% (76620 hits/ 86502 total)
[OK] InnoDB log waits: 0.00% (0 waits / 9882 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_instances (=1)

[carlalexander]

Not every recommendation makes sense. That said, I found the numbers under InnoDB to be a bit high for a 512meg droplet. I found this SO thread that discusses some of these values.

I might try adjusting innodb_buffer_pool_instances to match the number of cores and ensure innodb_buffer_pool_size is 50% of RAM. I'll start with this and see what I get out of it.

[drybjed]

@carlalexander Great, looking forward for the results. These seem like good defaults to put in the main role.

[carlalexander]

Yep, I agree!

[carlalexander]

General consensus seems to be that query_cache should be disabled by default now.

[carlalexander]

The defaults added to debops.mariadb_server were really the important ones here.