Hi all,
Newbie here i have production issue where mysql responding quite slow. Below is my.cnf and mysqltuner.pl ouput and tuning-primer.sh. any help highly appreciated. I need help and guide which variable should is change and add. Thanks in advance.
#mysql perfomances
key_buffer = 2G
max_allowed_packet = 1M
table_cache = 4000
sort_buffer_size = 1M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_concurrency = 8
max_connections = 500
max_user_connections = 1000
join_buffer_size = 512M
read_buffer_size = 512M
wait_timeout = 28000
connect_timeout = 1000
sort_buffer_size = 2M
query_cache_limit = 2M
query_cache_type = 1
query_cache_size = 128M
thread_cache_size = 512
open_files_limit = 8192
thread_stack = 128K
#log-slow-queries = /var/log/mysql/log-slow-queries.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/log-slow-queries.log
long_query_time=1
skip-locking
[root]# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at
http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-enterprise-commercial-advanced-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10G (Tables: 39)
[--] Data in InnoDB tables: 128K (Tables: 8)
[!!] Total fragmented tables: 9
-------- Performance Metrics -------------------------------------------------
[--] Up for: 31d 12h 17m 22s (245M q [90.150 qps], 284K conn, TX: 150B, RX: 92B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 2.2G global + 1.0G per thread (500 max threads)
[!!] Maximum possible memory usage: 511.0G (1626% of installed RAM)
[OK] Slow queries: 0% (8K/245M)
[OK] Highest usage of available connections: 47% (239/500)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.7G
[OK] Key buffer hit rate: 100.0% (7B cached / 838K reads)
[OK] Query cache efficiency: 63.0% (118M cached / 188M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (169 temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 0% (880 on disk / 289K total)
[OK] Thread cache hit rate: 99% (239 created / 284K connections)
[OK] Table cache hit rate: 68% (629 open / 916 opened)
[OK] Open file limit used: 8% (687/8K)
[OK] Table locks acquired immediately: 98% (114M immediate / 116M locks)
[OK] InnoDB data size / buffer pool: 128.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
tuning-primer.sh output
=========================
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 1.000000 sec.
You have 8428 out of 245633920 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is enabled
WORKER THREADS
Current thread_cache_size = 512
Current threads_cached = 137
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 102
Historic max_used_connections = 239
The number of used connections is 47% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 32 K
Current InnoDB data space = 128 K
Current InnoDB buffer pool free = 93 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 245.37 G
Configured Max Per-thread Buffers : 508.86 G
Configured Max Global Buffers : 2.13 G
Configured Max Memory Limit : 511.00 G
Physical Memory : 31.42 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 1.66 G
Current key_buffer_size = 2.00 G
Key cache miss rate is 1 : 8766
Key buffer free ratio = 26 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 4 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 3.13 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 512.00 M
You have had 44 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.
OPEN FILES LIMIT
Current open_files_limit = 8510 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 = 4000 tables
Current table_definition_cache = 256 tables
You have a total of 70 tables
You have 629 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 288965 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 512 M
Current table scan ratio = 521 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer
TABLE LOCKING
Current Lock Wait ratio = 1 : 53
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.