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.

  • You should update the report of mysql Tuner with longer running time. 15 seconds is not enough! – Philippe Delteil Aug 31 '18 at 16:14
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.

  • your statements about Query cache also valid for MariaDB 10.x.x? – Ivanov Mar 16 '17 at 7:21
  • does that mean you recommend turning the cache off? Would that have impact on performance? If so, I'd be willing to make any measurements to set query_cache_limit and query_cache_min_res_unit correctly and have query_cache running. Although I can also use Redis to cache query results if that would be easier (but I suppose MariaDb would deal with cache invalidation on without additional work?). I'm not DBA really, any tips on measuring that size? I'm using ORM, so can't really measure it from within my application. – pzaj Mar 16 '17 at 12:42
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

  • 1
    Thank you for your comments. I will take a look at log file size and apply your recommendations. As for slow queries, you're right, that's a thing I will be reviewing, didn't ask about that in my question, because I know how to deal with that one. I can only tell you for now that they're all below 20ms, just doing full scans and some of them use tmp_table and tmp_disk_table due to order by clauses I suppose. This is something I will be trying to optimize once I found tools that help me identifying potentially missing indexes – pzaj Mar 16 '17 at 12:48
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.