I am not having any performance problems per se but want to improve my sites' performance if possible. Would appreciate any advice for how I can improve my.cnf. I have just one server, not exclusively dedicated for database. My sites are forums running XenForo software.
Server Specs:
- CPU: Intel(R) Xeon(R) CPU E5-1620 v3 @ 3.50GHz (4 cores, 8 threads)
- RAM: 64 GB DDR4
- CentOS 6.7
- PHP Version 5.6.13
- LiteSpeed Web Server 5.0.7
- MariaDb 10.0.21
- Memcached
Current my.cnf:
[mysqld]
local-infile=0
tmpdir = /var/mysqltmp
character-set-server=utf8
bind-address=127.0.0.1
skip-federated
skip-archive
back_log = 75
max_connections = 300
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1024M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 40000
table_open_cache = 40000
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet=536870912
max_seeks_for_key = 1000
group_concat_max_len = 102400
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB
log_warnings=1
innodb_purge_threads=1
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 16G
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
innodb_io_capacity = 4600
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_neighbors = 0
open_files_limit=14000
free -m
total used free shared buffers cached
Mem: 64321 63925 395 270 1235 45936
-/+ buffers/cache: 16753 47567
Swap: 4204 451 3753
root@server [~]# perl mysqltuner.pl
>> MySQLTuner 1.6.1 - 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.0.21-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 1G (Tables: 113)
[--] Data in InnoDB tables: 3G (Tables: 1535)
[--] Data in MEMORY tables: 2M (Tables: 33)
[!!] Total fragmented tables: 238
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 23h 22m 44s (58M q [42.243 qps], 5M conn, TX: 492B, RX: 50B)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is disabled
[--] Total buffers: 17.1G global + 800.0K per thread (300 max threads)
[OK] Maximum reached memory usage: 17.2G (27.31% of installed RAM)
[OK] Maximum possible memory usage: 17.4G (27.65% of installed RAM)
[OK] Slow queries: 0% (78/58M)
[OK] Highest usage of available connections: 7% (22/300)
[OK] Aborted connections: 0.00% (158/5240320)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (34K temp sorts / 3M sorts)
[!!] Joins performed without indexes: 167178
[OK] Temporary tables created on disk: 8% (196K on disk / 2M total)
[OK] Thread cache hit rate: 99% (22 created / 5M connections)
[!!] Table cache hit rate: 2% (2K open / 66K opened)
[OK] Open file limit used: 0% (295/80K)
[OK] Table locks acquired immediately: 99% (89M immediate / 89M locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (99M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/545.4M
[OK] Read Key buffer hit rate: 99.9% (51M cached / 51K reads)
[!!] Write Key buffer hit rate: 47.6% (14M cached / 7M writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0G/3.2G
[OK] InnoDB buffer pool instances: 16
[!!] InnoDB Used buffer: 18.64% (195406 used/ 1048560 total)
[OK] InnoDB Read buffer efficiency: 100.00% (25814174259 hits/ 25814306598 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5363748 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
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 (80311) variable
should be greater than table_open_cache ( 40000)
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 64.0K, or always use indexes with joins)
table_open_cache (> 40000)
root@server [~]# tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 10.0.21-MariaDB x86_64
Uptime = 15 days 23 hrs 23 min 37 sec
Avg. qps = 42
Total Questions = 58307091
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/10.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 78 out of 58307112 that take longer than 10.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/10.0/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 21
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 22
The number of used connections is 7% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 16.53 G
Configured Max Per-thread Buffers : 234 M
Configured Max Global Buffers : 16.51 G
Configured Max Memory Limit : 16.74 G
Physical Memory : 62.81 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 545 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 1003
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
SORT OPERATIONS
Current sort_buffer_size = 128 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
/usr/local/bin/tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 68.00 K
You have had 167181 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 80311 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 40000 tables
Current table_definition_cache = 40000 tables
You have a total of 1767 tables
You have 2015 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 2228513 temp tables, 8% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 64 K
Current table scan ratio = 114 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 7207
Your table locking seems to be fine
Thanks for any help!
