This one is driving me mad:

I have a site that fortunately is turning out to be very busy

Running dedicated server 2 xtwin XEON2800 4GB ram 4x73GB SCSI. Main use of site is to query 5 tables of one million rows each split across 3 of the SCSIs to increase disc read.

What happens after reboot is number of users quickly builds to 5000-6000 using 1024 available connections. Then everything starts to slow down until there are only 2500 users and the site is dead slow and does not respond to outside connections - trys to get data but page stalls.

My guess is that a query cache, query log or temp file somewhere is getting overloaded but I can't find anything. Number of file descriptors is fine, watermark for file descriptors is good.

Anyone got any brainwaves as to what could be causing this? I'm almost certain it is my MYSQL setup affecting apache badly as opposed to the other way round.

Show status print is below.

Regards

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 25425 |
| Aborted_connects | 7 |
| Bytes_received | 1239832829 |
| Bytes_sent | 2892098686 |
| Com_admin_commands | 0 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1302518 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 25 |
| Com_create_table | 2 |
| Com_delete | 499985 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 19 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 218936 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 1 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 7325 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1116708 |
| Com_set_option | 379 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 387 |
| Com_show_databases | 84 |
| Com_show_fields | 466 |
| Com_show_grants | 25 |
| Com_show_keys | 15 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 157 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 501581 |
| Com_show_variables | 13 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 7325 |
| Com_update | 732663 |
| Connections | 552077 |
| Created_tmp_disk_tables | 1946 |
| Created_tmp_tables | 2254 |
| Created_tmp_files | 143 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_commit | 0 |
| Handler_delete | 95281 |
| Handler_read_first | 3440 |
| Handler_read_key | 299959920 |
| Handler_read_next | 334522417 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 5850626 |
| Handler_read_rnd_next | 2385133957 |
| Handler_rollback | 0 |
| Handler_update | 20571540 |
| Handler_write | 25032005 |
| Key_blocks_used | 249380 |
| Key_read_requests | 1021222322 |
| Key_reads | 145997 |
| Key_write_requests | 29980146 |
| Key_writes | 620105 |
| Max_used_connections | 434 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 2 |
| Open_files | 7 |
| Open_streams | 0 |
| Opened_tables | 20763 |
| Questions | 12195014 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 1046783 |
| Qcache_hits | 7254511 |
| Qcache_lowmem_prunes | 166434 |
| Qcache_not_cached | 69925 |
| Qcache_free_memory | 134208832 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Rpl_status | NULL |
| Select_full_join | 57 |
| Select_full_range_join | 0 |
| Select_range | 136324 |
| Select_range_check | 2 |
| Select_scan | 74579 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 28446 |
| Sort_merge_passes | 35 |
| Sort_range | 95178 |
| Sort_rows | 5846650 |
| Sort_scan | 6468 |
| Table_locks_immediate | 1080116 |
| Table_locks_waited | 1599200 |
| Threads_cached | 0 |
| Threads_created | 544 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 47086 |
+--------------------------+------------+