Hi all,
I am having a very high load in my server, I have manage to tweak mysql configuration but want to know if there is something else that I should improve and hope you can help me.
My server has REHL 6 / 64 bits with 12GB RAM and WHM says it has 16 processors (physically two Intel(R) Xeon(R) CPU E5520 @ 2.27GHz), what it is weird is that WHM shows the legend "Total processors: 10" but it shows 16, so, go figure.
Any way, here is what I have in my.cnf:
Sergio
I am having a very high load in my server, I have manage to tweak mysql configuration but want to know if there is something else that I should improve and hope you can help me.
My server has REHL 6 / 64 bits with 12GB RAM and WHM says it has 16 processors (physically two Intel(R) Xeon(R) CPU E5520 @ 2.27GHz), what it is weird is that WHM shows the legend "Total processors: 10" but it shows 16, so, go figure.
Any way, here is what I have in my.cnf:
I have run mysqltuner.pl and this is what is showing for more than 48 hours:skip-external-locking
query_cache_size = 150M
join_buffer_size = 5M
tmp_table_size = 175M
max_heap_table_size = 175M
table_open_cache = 50K
open_files_limit = 35K
key_buffer_size = 500M
max_allowed_packet = 32M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
innodb_buffer_pool_size = 200M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12
max_connections = 400
max_user_connections = 75
slow_query_log
local-infile = 0
Thanks in advance.>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> 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.61-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1021M (Tables: 24080)
[--] Data in InnoDB tables: 184M (Tables: 2665)
[--] Data in MEMORY tables: 1M (Tables: 64)
[!!] Total fragmented tables: 2879
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 53m 59s (6M q [27.391 qps], 192K conn, TX: 17B, RX: 1B)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 1.0G global + 17.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 7.7G (66% of installed RAM)
[OK] Slow queries: 0% (28/6M)
[OK] Highest usage of available connections: 10% (40/400)
[OK] Key buffer size / total MyISAM indexes: 500.0M/207.7M
[OK] Key buffer hit rate: 98.2% (22M cached / 396K reads)
[OK] Query cache efficiency: 76.3% (3M cached / 4M selects)
[!!] Query cache prunes per day: 38407
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 179K sorts)
[!!] Joins performed without indexes: 17449
[!!] Temporary tables created on disk: 34% (335K on disk / 979K total)
[OK] Thread cache hit rate: 98% (2K created / 192K connections)
[OK] Table cache hit rate: 51% (41K open / 80K opened)
[OK] Open file limit used: 58% (60K/102K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 184.9M/200.0M
-------- 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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 150M) [see warning above]
join_buffer_size (> 5.0M, or always use indexes with joins)
tmp_table_size (> 175M)
max_heap_table_size (> 175M)
Sergio