Server load spikes randomally

[fallenshortagai]

Our server load generally stays around 2-3, but occasionally it will skyrocket sometimes above 100. Not sure if it's mysql or apache that's causing the huge loads, but it's EXTREMELY frustrating to track.

Here are the question answers:

1. This is on a dedicated server.
2.
cpu speed/type single or dual cpus): Dual Intel(R) Xeon(TM) CPU 2.40GHz
how much memory installed: 1.5 GB ram
hard drive type/configuration: dual 80GB EIDE non raid
linux distributor or windows version: Red Hat Enterprise Linux ES release 3
apache/IIS version: apache 1.3.31
PHP version: php 4.3.8
MySQL version: mysql 4.0.20-standard

3. Not that I know of.
4. Installed via rpm
5.
01:04:19 up 8 days, 29 min, 3 users, load average: 0.24, 0.29, 0.25
138 processes: 136 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.4% 0.0% 0.2% 0.0% 0.0% 0.0% 99.2%
cpu00 0.5% 0.0% 0.1% 0.0% 0.0% 0.0% 99.2%
cpu01 0.5% 0.0% 0.0% 0.0% 0.0% 0.0% 99.4%
cpu02 0.1% 0.0% 0.3% 0.0% 0.0% 0.0% 99.4%
cpu03 0.5% 0.0% 0.3% 0.0% 0.0% 0.1% 98.8%
Mem: 1028448k av, 935636k used, 92812k free, 0k shrd, 38268k buff
667556k actv, 159164k in_d, 13188k in_c
Swap: 2040244k av, 170392k used, 1869852k free 529892k cached

6.
-bash-2.05b$ cat /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
max_connections = 500
log_slow_queries=/var/log/slow-queries.log
long_query_time=2
skip-locking
key_buffer = 16M
max_allowed_packet=1M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 16M
thread_concurrency = 4

log-bin
server-id = 1
safe-show-database

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

7.
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Bytes_received | 86506266 |
| Bytes_sent | 3526301467 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 58335 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 2430 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 10049 |
| Com_insert_select | 37 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 573 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1138 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 27 |
| Com_savepoint | 0 |
| Com_select | 65666 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 52 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 53 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 573 |
| Com_update | 48706 |
| Connections | 55968 |
| Created_tmp_disk_tables | 2773 |
| Created_tmp_tables | 3245 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 115 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5996 |
| Handler_read_first | 7139 |
| Handler_read_key | 57808054 |
| Handler_read_next | 27944316 |
| Handler_read_prev | 12446 |
| Handler_read_rnd | 834933 |
| Handler_read_rnd_next | 45234713 |
| Handler_rollback | 0 |
| Handler_update | 52966 |
| Handler_write | 19447790 |
| Key_blocks_used | 15586 |
| Key_read_requests | 190343451 |
| Key_reads | 9774 |
| Key_write_requests | 19867189 |
| Key_writes | 13496 |
| Max_used_connections | 52 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 308 |
| Open_files | 415 |
| Open_streams | 0 |
| Opened_tables | 314 |
| Questions | 499614 |
| Qcache_queries_in_cache | 1869 |
| Qcache_inserts | 64590 |
| Qcache_hits | 256073 |
| Qcache_lowmem_prunes | 1396 |
| Qcache_not_cached | 1076 |
| Qcache_free_memory | 5466584 |
| Qcache_free_blocks | 1034 |
| Qcache_total_blocks | 5079 |
| Rpl_status | NULL |
| Select_full_join | 19 |
| Select_full_range_join | 0 |
| Select_range | 12399 |
| Select_range_check | 0 |
| Select_scan | 12242 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 306 |
| Sort_merge_passes | 0 |
| Sort_range | 10504 |
| Sort_rows | 11427688 |
| Sort_scan | 4391 |
| Table_locks_immediate | 198402 |
| Table_locks_waited | 1344 |
| Threads_cached | 5 |
| Threads_created | 921 |
| Threads_connected | 5 |
| Threads_running | 1 |
| Uptime | 15554 |
+--------------------------+------------+

8. There are a few other php scripts that use mysql, but I checked for them in the slow-queries file and it was all vbulletin entries.

9.
record online users: 642
average: 432
cookie timeout: 900 seconds

10. http://www.idlehero.com/phpinfo.php

11.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 260

12. Running version 3.0.3

[eva2000]

you have mysql binary logging enabled in my.cnf which is causing you to swap to disk by a fair bit

this new my.cnf will disable it and should better although 400-600 users over 900s cookie timeout does look like needing to split to 2 servers - web + db server but your mysql extended-status looks fine

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
log_slow_queries=/var/log/slow-queries.log
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
safe-show-database

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[fallenshortagai]

would moving the cookie timeout higher or lower help at all? and are their any other suggestions in the configuration that would maybe help?

[eva2000]

nah cookie timeout doesn't affect performance only effects the how accurate you an indicator of vb users online to mysql concurrent connections there are..

try the new my.cnf for 2-5 days during peak and repost your mysql extended-status output with mysql server uptime of 2-5 days

[fallenshortagai]

Thanks alot Eva, we really appreciate the help :)

After we made the changes, it seemed to really clear things up for a while. Everything seemed stable... however about 18 hours later the load would spike again. While poking around, I noticed that servermatrix only put 1 gig of ram in our server instead of 1.5! I also noticed that every time the load issues started, we were out of memory and were writing to swap. That's GOT to be whats causing it, right?

Anyway, until servermatrix installs the correct amount of ram, is there any way to limit the amount of memory that mysql uses so it won't start using swap? I know there's some sort of formula you can use when you look at the my.cnf file to calculate the max amount of memory that mysqld will use, but I can't seem to find it anywhere... Any help would be greatly appreciated!

[eva2000]

try reducing number of concurrent server threads accepted by reducing maxclients in httpd.conf from 260 to 150 and restarting apache see if that helps some