high server load with mysql
my hosting server get high load with mysql,
can you please give me some suggestion about how to tune it ?
thanx
=================================================================
root@server [/etc]# /usr/local/cpanel/bin/mysqltuner.pl
>> MySQLTuner 1.2.0_1 - 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.73-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 16G (Tables: 18841)
[--] Data in InnoDB tables: 350M (Tables: 1872)
[--] Data in MEMORY tables: 27M (Tables: 103)
[!!] Total fragmented tables: 4657
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 41m 47s (2M q [190.036 qps], 75K conn, TX: 17B, RX: 635M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 2.0G global + 7.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 4.8G (31% of installed RAM)
[OK] Slow queries: 0% (607/2M)
[OK] Highest usage of available connections: 13% (52/400)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.1G
[OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
[OK] Query cache efficiency: 62.6% (1M cached / 1M selects)
[!!] Query cache prunes per day: 1207548
[OK] Sorts requiring temporary tables: 1% (3K temp sorts / 192K sorts)
[!!] Joins performed without indexes: 22347
[!!] Temporary tables created on disk: 35% (77K on disk / 214K total)
[OK] Thread cache hit rate: 99% (52 created / 75K connections)
[!!] Table cache hit rate: 0% (1K open / 1M opened)
[OK] Open file limit used: 3% (1K/50K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 350.6M/400.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 1024)
=================================================================
[mysqld]
datadir=/var/lib/mysql
local-infile=0
innodb_file_per_table=1
skip_external_locking
skip_networking
query_cache_limit=8M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=50
max_connections=400
innodb_buffer_pool_size=400M
table_cache=10240
interactive_timeout=100
wait_timeout=100
connect_timeout=100
thread_cache_size=256
key_buffer_size=1024M ## 128MB for every 1GB of RAM
join_buffer_size=1M
max_connect_errors=20
max_allowed_packet=64M
table_open_cache=1024 ## should be modified depending on the tables
table_definition_cache=1024
open_files_limit=50000
tmp_table_size=512M
max_heap_table_size=512M
sort_buffer_size=2M ## 1MB for every 1GB of RAM
read_buffer_size=2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM
myisam_sort_buffer_size=64M
server_id=1
#log_slow_queries=/var/log/mysql-slow-queries.log
# slow_query_log=1
# slow_query_log_file=/var/log/mysql-slow-queries.log
# Don't Forget To Do
# touch /var/log/mysql-slow-queries.log
# chown mysql.mysql /var/log/mysql-slow-queries.log
# long_query_time=2
collation_server=latin1_general_ci
[mysqldump]
max_allowed_packet=64M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer_size=64M
write_buffer_size=64M
[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer_size=64M
write_buffer_size=64M
[mysqlhotcopy]
interactive_timeout
=================================================================
