Please help not sure how to move on from here seems like it says the same thing each time.
I have adjusted a lot and let it run for a long while on this result so it can get a better report, and here is the results of MySQLTuner 1.6.18
here is the my.cnf
thanks
Mitch
I have adjusted a lot and let it run for a long while on this result so it can get a better report, and here is the results of MySQLTuner 1.6.18
Code:
>> MySQLTuner 1.6.18 - 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
[OK] Currently running supported MySQL version 10.1.17-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 191M (Tables: 581)
[--] Data in InnoDB tables: 1G (Tables: 3048)
[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 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12d 6h 30m 38s (116M q [109.754 qps], 802K conn, TX: 527G, RX: 32G)
[--] Reads / Writes: 75% / 25%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 27.8G
[--] Other process memory: 1.5G
[--] Total buffers: 26.4G global + 2.9M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 26.7G (85.25% of installed RAM)
[!!] Maximum possible memory usage: 27.8G (88.94% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 2% (2M/116M)
[OK] Highest usage of available connections: 18% (93/500)
[OK] Aborted connections: 0.06% (513/802359)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 43.4% (62M cached / 144M selects)
[!!] Query cache prunes per day: 497764
[OK] Sorts requiring temporary tables: 0% (54 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 11737
[!!] Temporary tables created on disk: 63% (4M on disk / 6M total)
[OK] Thread cache hit rate: 99% (133 created / 802K connections)
[!!] Table cache hit rate: 0% (300 open / 743K opened)
[OK] Open file limit used: 1% (141/10K)
[OK] Table locks acquired immediately: 99% (56M immediate / 56M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.1.17-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.0% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/32.5M
[OK] Read Key buffer hit rate: 98.8% (133M cached / 1M reads)
[!!] Write Key buffer hit rate: 56.7% (21M cached / 9M writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.0% (142M cached / 4M reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 26.0G/1.3G
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (3825353586 hits/ 3825389008 total)
[!!] InnoDB Write Log efficiency: 45.77% (9506050 hits/ 20770097 total)
[OK] InnoDB log waits: 0.00% (0 waits / 11264047 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
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (10000) variable
should be greater than table_open_cache ( 300)
Variables to adjust:
query_cache_size (> 100M)
join_buffer_size (> 256.0K, or always use indexes with joins)
tmp_table_size (> 150M)
max_heap_table_size (> 150M)
table_open_cache (> 300)
innodb_buffer_pool_instances(=26)
Code:
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name 3Aliens_New_SSD_Server generated for - Removed - at 2016-08-19 22:59:00
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/kc29.example.com.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
sql-mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate-is-now = 1
innodb = FORCE
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
#log-bin = /var/lib/mysql/mysql-bin
#expire-logs-days = 14
#sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 150M
max-heap-table-size = 150M
query-cache-type = 1
query-cache-limit = 256K
query-cache-min-res-unit = 2k
query-cache-size = 100M
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 300
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 26G
# LOGGING #
log-error = /var/log/mysql/mysql_error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql_slow.log
max_allowed_packet=268435456
open_files_limit=10000Mitch
Last edited by a moderator:
