I am attempting to optimize the database I am running, it's sitting on a system with 8GB of RAM, and would like it to be able to use about 5GB-6GB of it. I've ran the mysqltuner.pl script, and made some changes, but before doing too much to it, I'd like some feedback. I've included the output of mysqltuner, it has made some suggestions, and there are a few warnings listed, I'd like to resolve those:
Issues:
[!!] InnoDB is enabled but isn't being used
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 473K selects)
[!!] Key buffer used: 19.0% (398M used / 2B cache)
[!!] Write Key buffer hit rate: 93.4% (362K cached / 338K writes)
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
[!!] Aria pagecache hit rate: 69.9% (308K cached / 92K reads)
My config (my.cnf) currently looks as such:
[client-server]
#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve=1
key_buffer_size = 2000M
innodb_buffer_pool_size = 0
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Here is the full output of mysqltuner, however I haven't added the variables it suggests to adjust at the end. I can't actually locate them in my.cnf or in the files in the include directory. I am guessing there's a default value to them if they are not declared. Should I bother with those?
>> MySQLTuner 1.7.4 - Major Hayden <[email protected]>
>> 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.1.26-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(47K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 203 warning(s).
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 6 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2017-11-01 10:37:53 140638578129152 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 2017-11-01 9:36:43 140283458062592 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 2017-10-29 1:32:36 140572083362048 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 2017-10-25 17:00:42 140183589882112 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 2017-10-25 16:50:06 140142160664832 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 2017-10-13 16:53:26 140715390277888 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2017-11-01 10:37:50 140283457219328 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 2017-11-01 1:04:54 140572008069888 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 2017-10-29 1:32:23 140183531240192 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 2017-10-25 17:00:31 140142159518464 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 2017-10-25 16:49:53 140715330726656 [Note] /usr/libexec/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 1G (Tables: 96)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User ‘********HIDDEN******@%’ hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 15h 30m 50s (811K q [5.705 qps], 31K conn, TX: 1G, RX: 298M)
[--] Reads / Writes: 63% / 37%
[--] Binary logging is disabled
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 2.6G
[--] Other process memory: 53.2M
[--] Total buffers: 2.1G global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.1G (27.42% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (32.74% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/811K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Aborted connections: 0.52% (164/31387)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 473K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 154K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 99% (92K on disk / 92K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 97% (251 open / 257 opened)
[OK] Open file limit used: 31% (324/1K)
[OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.1.26-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.0% (398M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.0G/619.0M
[OK] Read Key buffer hit rate: 100.0% (92M cached / 11K reads)
[!!] Write Key buffer hit rate: 93.4% (362K cached / 338K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 69.9% (308K cached / 92K reads)
-------- 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:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Add skip-innodb to MySQL configuration to disable InnoDB
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
The following suggestions need your research before implementing ONLY one item per day. Some may be applied dynamically. Suggestions for your [mysqld] section cfg/ini values follow, could be modify, add or remove.
log_warnings=2 # for additional info in error log on failed connections
max_connections=50 # from 151, until you need more concurrent connections
have_query_cache=0 # from YES default, not used, avoid QC overhead
next TWO lines apply as a pair on the same day
tmp_table_size=64M # from default 16M for RAM use vs HDD
max_heap_table_size=64M # from default 16M for RAM use vs HDD
Unless you know you need to allocate memory for an identified purpose, avoid the change. MySQLTuner in many cases is only providing (normally helpful) information. Such as the query_cache Recommendations when you are not using QC.
After implementing the suggestions and around 7-10 days of continuous uptime, in your Question, please post text results of
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
and our complete my.cnf/ini
for additional suggestions.
Your two minutes of time to explore mysqlcalculator.com will be very helpful in confirming RAM utilization and the values that affect your server the most.