7

I'd like to ask you to clarify the report from mysqltuner regarding MariaDB database. The mysqltuner was called with --nogood flag!

 >>  MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: (0B)
[!!] Log file  doesn't exist
[!!] Log file  isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 380M (Tables: 417)

-------- Security Recommendations ------------------------------------------------------------------
[--] There are 605 basic passwords in the list.

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15s (812 q [54.133 qps], 275 conn, TX: 258K, RX: 108K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 10.0G
[--] Other process memory: 1.2G
[--] Total buffers: 8.4G global + 10.7M per thread (150 max threads)
[--] P_S Max memory usage: 34M
[--] Galera GCache Max memory usage: 0B
[!!] Slow queries: 27% (221/812)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 521 selects)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 35.0M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.0.29-MariaDB-0ubuntu0.16.04.1)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 80.0% (10 cached / 2 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 512.0M * 2/8.0G should be equal 25%
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_type (=0)
    query_cache_limit (> 256K, or use smaller result sets)
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible.

What makes me confused is the "Log file Recommendations" section. I don't really know what to do with it. Then also this line:

[!!] Query cache may be disabled by default due to mutex contention.

I am also very curious why it would recommend me to change query_cache_type to 0 and increase query_cache_limit?

I'm aware that it has not been running for at least 24 hours, that's because I already adjusted the config and restarted my database. I did adjustements according to my knowledge about MariaDB, but with these few things I feel confused.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
user119791
  • 71
  • 1
  • 1
  • 2

3 Answers3

7

I can explain this line

[!!] Query cache may be disabled by default due to mutex contention.

The InnoDB storage engine and the Query Cache are in a constant state of war (See my 1.5-year-old post Why query_cache_type is disabled by default start from MySQL 5.6?)

mysqltuner is recommending setting query_cache_type to 0 so you explicitly disable the query cache. Please don't forget to set query_cache_size to 0 as well. Otherwise, the mutexing behavior will passive occur anyway.

As I said in my old post, you don't necessarily have to disable the query cache if your reasonably know the size of your most common result sets. If you can figure out that size, then you can query_cache_limit and query_cache_min_res_unit as the upper and lower bound of result set sizes. Only then can you set query_cache_type to 1.

As for your Log File Recommendations

[--] Log file: (0B)
[!!] Log file  doesn't exist
[!!] Log file  isn't readable.

Perhaps this might explain it

[!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1

It is possible mysqltuner cannot relate to log files in this version of MariaDB like it does with supported versions.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
3

Do not trust (rely only on it) - for mysqltunner, all settings must be adjusted based on real monitoring and loading.

Log File size from one side - it recommended size 0.5-1Hr of all transactions during this period

but from other side - if it more than 1-2Gb restart after crash could take ages. As bigger logs - as longer start.

So it always balance between.

Start from 512M per file (Total 1G), then if loading high - increase up to 1024Gb

better to check what inside:

Slow queries: 27% (221/812)
  • which queries?
  • why slow?
  • is it by data size? or by wrong indexes?

this is could give more for performance

a_vlad
  • 3,705
  • 2
  • 13
  • 17
2

The output is virtually useless because the system has been running only 15 seconds. Wait at least a day.

Nevertheless, the percent of queries that are "slow" is terrible. Turn on the slowlog, wait a day, use pt-query-digest to find the worst couple of queries. Then let's discuss them.

I did not think that InnoDB could run without a log file! (Or maybe it is referring to a different log file??) Yet later it says innodb_log_file_size is 512M. That should be a decent size for now. (You have other issues to focus on for now.)

For production systems with lots of writes, this is the best QC setting:

query_cache_type = OFF
query_cache_size = 0

If you would like a different review, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (after being up at least a day).

Rick James
  • 80,479
  • 5
  • 52
  • 119