high load of mysql on my server

محروم.كوم · #1 06-17-2009, 12:40 AM

1. i am on dedicated server

2.server specs:

cpu speed/type single or dual cpus): 2 x 2.8ghz xeon
how much memory installed: 2 x 2gb ram
hard drive type/configuration: 2x 72GB 10,000 RPM SCSI
linux distributor or windows version: CentOS 5.3 i386
apache/IIS version: Apache/2.2.3
PHP version: 5.2.6
MySQL version: 5.0.45

3. vbulletin Version 3.7.3

6. top command stats

top - 0925 up 1 day, 21:31, 2 users, load average: 45.79, 34.31, 20.58
Tasks: 233 total, 3 running, 230 sleeping, 0 stopped, 0 zombie
Cpu(s): 68.7%us, 31.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4154192k total, 3996436k used, 157756k free, 175220k buffers
Swap: 5931000k total, 0k used, 5931000k free, 2887988k cached

top - 09:33:03 up 1 day, 21:33, 2 users, load average: 103.50, 60.47, 32.37
Tasks: 240 total, 5 running, 235 sleeping, 0 stopped, 0 zombie
Cpu0 : 67.9%us, 32.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 61.9%us, 37.7%sy, 0.0%ni, 0.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 71.3%us, 28.1%sy, 0.0%ni, 0.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 71.3%us, 28.4%sy, 0.0%ni, 0.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4154192k total, 4066928k used, 87264k free, 175124k buffers
Swap: 5931000k total, 0k used, 5931000k free, 2885844k cached

7. my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
set-variable = old_passwords=1
skip-bdb
set-variable = query_cache_size=24M
set-variable = tmp_table_size=48M
set-variable = max_heap_table_size=24M
set-variable = thread_cache_size=4
table_cache=3072
max_connections=150
wait_timeout=20000
interactive_timeout=20000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

8. mysql stats from ssh as root

Code:
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 60 |
| 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 | 386 |
| Com_set_option | 0 |
| 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 | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 1 |
| 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 | 2 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 8 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| 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 | 30 |
| 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 | 72 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 69 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 10 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 86 |
| Handler_read_key | 18001907 |
| Handler_read_next | 8668303 |
| Handler_read_prev | 97 |
| Handler_read_rnd | 4637 |
| Handler_read_rnd_next | 2851835 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 7640876 |
| Handler_write | 955200 |
| Innodb_buffer_pool_pages_data | 40 |
| Innodb_buffer_pool_pages_dirty | 1 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 472 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 2 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 1617 |
| Innodb_buffer_pool_reads | 24 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2838528 |
| Innodb_data_reads | 40 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 40 |
| Innodb_pages_written | 0 |
| 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 | 3592 |
| Key_blocks_used | 3656 |
| Key_read_requests | 30162885 |
| Key_reads | 3656 |
| Key_write_requests | 92 |
| Key_writes | 55 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 8 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 654 |
| Open_streams | 0 |
| Open_tables | 349 |
| Opened_tables | 355 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 4 |
| Qcache_free_memory | 22110448 |
| Qcache_hits | 413 |
| Qcache_inserts | 369 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 26 |
| Qcache_queries_in_cache | 251 |
| Qcache_total_blocks | 563 |
| Questions | 1088 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 117 |
| Select_range_check | 0 |
| Select_scan | 110 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 8 |
| Sort_merge_passes | 0 |
| Sort_range | 66 |
| Sort_rows | 5737 |
| Sort_scan | 61 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |

| Table_locks_immediate | 967 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 12 |
| Threads_running | 1 |
| Uptime | 162 |
| Uptime_since_flush_status | 162 |
+-----------------------------------+----------+
Uptime: 164 Threads: 2 Questions: 1092 Slow queries: 8 Opens: 355 Flush tables: 1 Open

tables: 349 Queries per second avg: 6.659
mysqladmin Ver 8.41 Distrib 5.0.45, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.45
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 2 min 44 sec

Threads: 2 Questions: 1093 Slow queries: 8 Opens: 355 Flush tables: 1 Open tables: 349

Queries per second avg: 6.665
+----+--------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |

+----+--------+-----------+--------+---------+------+-------+------------------+
| 71 | root | localhost | | Query | 0 | | show processlist |
| 72 | series | localhost | series | Sleep | 0 | | |
+----+--------+-----------+--------+---------+------+-------+------------------+
9. my vb is only thing right now on server

10. avg ppl online 100-200 Session Timeout 900

11. 18series.com/phpinfo.php __DEFINE_LIKE_SHARE__

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Forum High Server load after Updating	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-01-2010 03:20 PM
MYSQL problems due to high CPU load, What is the solution?	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	01-25-2010 01:50 AM
High server load?	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	09-13-2009 07:00 PM
my server got high load causing vb forum	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	07-12-2009 01:10 AM
Very High Server Load \|\| Optimization Request!	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	05-10-2009 02:40 PM