Need Help , High load avg and high cpu usage

[minitech]

Hello.
i am owner of dedicated server and i would like you to help me please.

i have very high load avg and very high cpu usage by mysql , around 150%+

i am running on the server 3 sites , one with 10000 visitors a day , and the other two are around 200+- visitors.

here is the info:

root@server [~]# mysqladmin extended-status
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 7082045 |
| Bytes_sent | 175569634 |
| Com_admin_commands | 10 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 1364 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 4 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 57 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 1 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 184 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 58524 |
| Com_set_option | 1361 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 2 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_storage_engines | 3 |
| Com_show_tables | 11 |
| Com_show_triggers | 0 |
| Com_show_variables | 4 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 99 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1398 |
| Created_tmp_disk_tables | 1718 |
| Created_tmp_files | 3839 |
| Created_tmp_tables | 4918 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 4 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 15 |
| Handler_read_key | 7131054 |
| Handler_read_next | 9547923 |
| Handler_read_prev | 1033704 |
| Handler_read_rnd | 218022 |
| Handler_read_rnd_next | 85161390 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 6555201 |
| Handler_write | 17095101 |
| Innodb_buffer_pool_pages_data | 51 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1 |
| Innodb_buffer_pool_pages_free | 461 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 3 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 5908 |
| Innodb_buffer_pool_reads | 26 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
| Innodb_data_fsyncs | 7 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 3608576 |
| Innodb_data_reads | 78 |
| Innodb_data_writes | 7 |
| Innodb_data_written | 35328 |
| Innodb_dblwr_pages_written | 1 |
| Innodb_dblwr_writes | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 2 |
| Innodb_os_log_fsyncs | 5 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1024 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 51 |
| Innodb_pages_written | 1 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6854 |
| Key_blocks_used | 2697 |
| Key_read_requests | 4316324 |
| Key_reads | 7848 |
| Key_write_requests | 754597 |
| Key_writes | 9613 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 32 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 99 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 2101 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 63004 |
| Questions | 63004 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 8237 |
| Select_range_check | 0 |
| Select_scan | 3479 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 14 |
| Sort_merge_passes | 1917 |
| Sort_range | 6053 |
| Sort_rows | 46684233 |
| Sort_scan | 5929 |
| Table_locks_immediate | 64903 |
| Table_locks_waited | 6 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 14 |
| Threads_created | 1397 |
| Threads_running | 11 |
| Uptime | 7858 |
+-----------------------------------+-----------+


>> 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.0.91-community
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 33M (Tables: 158)
[--] Data in InnoDB tables: 144K (Tables: 9)
[!!] Total fragmented tables: 1

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 11m 33s (65K q [8.339 qps], 1K conn, TX: 183M, RX: 7M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (68% of installed RAM)
[OK] Slow queries: 0% (14/65K)
[OK] Highest usage of available connections: 6% (32/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/9.5M
[OK] Key buffer hit rate: 99.8% (4M cached / 8K reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 15% (1K temp sorts / 12K sorts)
[OK] Temporary tables created on disk: 25% (1K on disk / 6K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 2% (64 open / 2K opened)
[OK] Open file limit used: 4% (101/2K)
[OK] Table locks acquired immediately: 99% (67K immediate / 67K locks)
[OK] InnoDB data size / buffer pool: 144.0K/8.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
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 1M)
read_rnd_buffer_size (> 256K)
thread_cache_size (start at 4)
table_cache (> 64)


root@server [~]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database

processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
stepping : 11
cpu MHz : 2133.000
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
apicid : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips : 4266.76

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
stepping : 11
cpu MHz : 2133.000
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
apicid : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips : 4266.06

processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
stepping : 11
cpu MHz : 2133.000
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
apicid : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips : 4266.08

processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
stepping : 11
cpu MHz : 2133.000
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
apicid : 3
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips : 4266.08




Please help me with it.

[cPanelTristan]

For this suggestion:

Run OPTIMIZE TABLE to defragment tables for better performance

Run this command:

mysqlcheck -u root --auto-repair --optimize --all-databases

For this suggestion:

MySQL started within last 24 hours - recommendations may be inaccurate

Wait next time 24 hours to run the script after a prior MySQL restart. Normally, I will not provide advice if you haven't waited the 24 hours to run the script after the last MySQL restart due to the fact it is utterly inaccurate if you don't wait long enough, which is why it provides that recommendation.

For this suggestion:

Enable the slow query log to troubleshoot bad queries

Put the following into your /etc/my.cnf file (this is the MySQL 5.0 entry as you appear to be running MySQL 5.0):

log-slow-queries=/var/lib/mysql/slow.log

Save the file, then run these commands:

touch /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log

For the other variables to adjust, put the following into the /etc/my.cnf so it looks like the following:

[mysqld]
key_buffer_size=16M
log-slow-queries=/var/lib/mysql/slow.log
max_connections=500
query_cache_limit=1M
query_cache_size=8M
query_cache_type=1
read_rnd_buffer_size=512K
sort_buffer_size=2M
table_cache=80
thread_cache_size=4

After revising /etc/my.cnf file, then restart MySQL for the changes to take effect. After restarting MySQL, wait at least 24 hours before re-running mysqltuner.pl

[minitech]

Thanks you very much!
lets hope everything will be alright!