So I am following the suggestions of mysqltuner, and so far I've had great results, the server is performing well (dedicated VPS running a large Magento eCommerce store)
There is only two issues left which I cannot seem to tackle, but the values are starting to become dangerously large (judging of what the normal ranges by reading some articles).
The questions I have:
- What causes the need for such large values?
- Should I keep increasing them or should I look for a problem somewhere else?
- Perhaps this is not a problem given the size of the database, and I should expect this to happen and should just ignore it?
- Perhaps I should optimize the tables as that could be causing it?
- Should I be worried about any of the things reported by primer.sh?
mysqltuner.pl output:
>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> 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.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MEMORY tables: 1M (Tables: 35)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 255M (Tables: 50)
[--] Data in InnoDB tables: 2G (Tables: 828)
[!!] Total fragmented tables: 832
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 3h 48m 20s (32M q [117.873 qps], 114K conn, TX: 134B, RX: 16B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 5.4G global + 6.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 6.4G (39% of installed RAM)
[OK] Slow queries: 0% (686/32M)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 64.0M/155.7M
[OK] Key buffer hit rate: 99.9% (147M cached / 124K reads)
[OK] Query cache efficiency: 83.2% (25M cached / 30M selects)
[!!] Query cache prunes per day: 744793
[OK] Sorts requiring temporary tables: 0% (544 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 22938
[OK] Temporary tables created on disk: 17% (418K on disk / 2M total)
[OK] Thread cache hit rate: 99% (303 created / 114K connections)
[OK] Table cache hit rate: 21% (1K open / 5K opened)
[OK] Open file limit used: 0% (171/1M)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
[OK] InnoDB buffer pool / data size: 5.0G/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_size (> 300M) [see warning above]
join_buffer_size (> 4.0M, or always use indexes with joins)
My my.conf:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 10.0.0.1
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 2G
innodb_buffer_pool_size = 5G
table_open_cache = 8192
open_files_limit = 1024000
join_buffer_size = 4M
tmp_table_size = 32M
innodb_lock_wait_timeout = 120
myisam-recover = BACKUP
query_cache_limit = 300M
query_cache_size = 300M
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3
expire_logs_days = 10
max_binlog_size = 100M
skip_name_resolve
skip-external-locking
Also other issues reported by primer.sh:
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 8227 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.
TABLE CACHE
Current table_open_cache = 8192 tables
Current table_definition_cache = 400 tables
You have a total of 954 tables
You have 1242 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.
JOINS
Current join_buffer_size = 4.00 M
You have had 23441 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
KEY BUFFER
Current MyISAM index space = 155 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 1178
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 3.000000 sec.
You have 704 out of 32971589 that take longer than 3.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
