I am experiencing massive lag, as well, on my forum. The site has only a few users (@40), and though they are prolific posters, there is not enough traffic to cause load times to slow to as much as 2 minutes per page.
I suspect the simple fact is that the database is about maxed out - do you have any suggestions about shaving the majority off to archive, whilst keeping the current active thread content accessible?
I am not knowledgeable enough (yet) to determine if any of the internal settings need to be tweaked...but here is a copy of the status.php results, if it helps provide any clues...
March 27, 2008, 09:09:54 AM
Operating System: Linux 2.4.21-27.ELsmp
Processor: Intel® Xeon(TM) CPU 3.00GHz (2992.854MHz)
Load averages: 8.48, 9.28, 10.04
Current processes: 464 (454 sleeping, 9 running, 1 zombie)
Processes by CPU: php (35) 100.4%, search.cgi (1) 62.0%, check_runaways. (1) 2.9%, htcached (1) 1.9%, pure-ftpd (17) 1.7%, httpd (352) 1.0%
Memory usage: 98.632% (6077440k / 6161704k)
Swap: 4.083% (85588k / 2096120k)
MySQL Statistics
MySQL 4.0.x
Connections per second: 4.0778
Kilobytes received per second: 0.0174
Kilobytes sent per second: 0.0743
Queries per second: 118.6098
Percentage of slow queries: 0.0002
Opened vs. Open tables:
(table_cache) 779484.6758 (should be <= 80)
Table cache usage:
(table_cache) 1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.0287 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.2163 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 37.5267 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.3691 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0.0005 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 0.3105 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0.6216 (should be <= 0.05)
MySQL status
Aborted_clients: 1555498
Aborted_connects: 120165
Bytes_received: 420429933
Bytes_sent: 1792853324
Com_admin_commands: 35977872
Com_alter_table: 9678
Com_analyze: 14
Com_backup_table: 0
Com_begin: 1287873
Com_change_db: 274021084
Com_change_master: 0
Com_check: 10362
Com_commit: 1277067
Com_create_db: 183
Com_create_function: 0
Com_create_index: 3694
Com_create_table: 460448
Com_delete: 20503308
Com_delete_multi: 61
Com_drop_db: 435
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 11059
Com_flush: 41156
Com_grant: 3
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_insert: 39213703
Com_insert_select: 2567568
Com_kill: 77639
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 71266
Com_optimize: 91253
Com_purge: 1
Com_rename_table: 11
Com_repair: 10
Com_replace: 1207968
Com_replace_select: 4
Com_reset: 0
Com_restore_table: 0
Com_revoke: 1
Com_rollback: 315
Com_savepoint: 0
Com_select: 682566075
Com_set_option: 467091
Com_show_binlog_events: 0
Com_show_binlogs: 3336
Com_show_create: 27194
Com_show_databases: 2933
Com_show_fields: 3427438
Com_show_grants: 165
Com_show_innodb_status: 0
Com_show_keys: 89812
Com_show_logs: 0
Com_show_master_status: 1
Com_show_new_master: 0
Com_show_open_tables: 0
Com_show_processlist: 1074740
Com_show_slave_hosts: 86
Com_show_slave_status: 546293
Com_show_status: 1009455
Com_show_tables: 4613892
Com_show_variables: 625035
Com_slave_start: 0
Com_slave_stop: 0
Com_truncate: 42681
Com_unlock_tables: 71280
Com_update: 147697576
Com_update_multi: 150106
Connections: 96126669
Created_tmp_disk_tables: 24252393
Created_tmp_files: 2176775
Created_tmp_tables: 65703041
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 7510
Flush_commands: 1
Handler_commit: 11437
Handler_delete: 47233347
Handler_read_first: 89245210
Handler_read_key: 2900110721
Handler_read_next: 833463346
Handler_read_prev: 1265409393
Handler_read_rnd: 3667352292
Handler_read_rnd_next: 386253626
Handler_rollback: 4857722
Handler_update: 1988516419
Handler_write: 3445467031
Key_blocks_used: 360922
Key_read_requests: 115373845524
Key_reads: 3308301076
Key_write_requests: 1463865113
Key_writes: 316704783
Max_used_connections: 433
Not_flushed_delayed_rows: 0
Not_flushed_key_blocks: 0
Open_files: 410
Open_streams: 0
Open_tables: 256
Opened_tables: 199548077
Qcache_free_blocks: 1560
Qcache_free_memory: 4603872
Qcache_hits: 1516009835
Qcache_inserts: 642966658
Qcache_lowmem_prunes: 424257839
Qcache_not_cached: 38898332
Qcache_queries_in_cache: 4691
Qcache_total_blocks: 12622
Questions: 2796001679
Rpl_status: NULL
Select_full_join: 8178607
Select_full_range_join: 137070
Select_range: 32918362
Select_range_check: 7287
Select_scan: 249398988
Slave_open_temp_tables: 0
Slave_running: OFF
Slow_launch_threads: 22
Slow_queries: 438432
Sort_merge_passes: 962245
Sort_range: 55274520
Sort_rows: 1845255340
Sort_scan: 115176027
Table_locks_immediate: 1540584118
Table_locks_waited: 1676906
Threads_cached: 0
Threads_connected: 42
Threads_created: 2561557
Threads_running: 35
Uptime: 23573104
MySQL variables
back_log: 50
basedir: /usr/local/mysql-max-4.0.27-unknown-linux-gnu-x86_64-glibc23/
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set: latin1
character_sets: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert: ON
connect_timeout: 20
convert_character_set:
datadir: /var/lib/mysql/57/data57/
default_week_format: 0
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
flush: OFF
flush_time: 0
ft_boolean_syntax: + -><()~*:""&|
ft_max_word_len: 254
ft_max_word_len_for_sort: 20
ft_min_word_len: 4
ft_stopword_file: (built-in)
have_bdb: NO
have_crypt: YES
have_innodb: YES
have_isam: YES
have_openssl: NO
have_query_cache: YES
have_raid: NO
have_symlink: YES
init_file:
innodb_additional_mem_pool_size: 10485760
innodb_autoextend_increment: 8
innodb_buffer_pool_size: 134217728
innodb_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir: /var/lib/mysql/57/tables/
innodb_fast_shutdown: ON
innodb_file_io_threads: 4
innodb_flush_log_at_trx_commit: 1
innodb_flush_method: O_DSYNC
innodb_force_recovery: 0
innodb_lock_wait_timeout: 50
innodb_log_arch_dir: /var/lib/mysql/57/logs/
innodb_log_archive: OFF
innodb_log_buffer_size: 8388608
innodb_log_file_size: 41943040
innodb_log_files_in_group: 2
innodb_log_group_home_dir: /var/lib/mysql/57/logs/
innodb_max_dirty_pages_pct: 90
innodb_max_purge_lag: 0
innodb_mirrored_log_groups: 1
innodb_table_locks: ON
innodb_thread_concurrency: 8
interactive_timeout: 30
join_buffer_size: 131072
key_buffer_size: 402653184
language: /usr/local/mysql-max-4.0.27-unknown-linux-gnu-x86_64-glibc23/share/mysql/english/
large_files_support: ON
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_bin: ON
log_error:
log_slave_updates: OFF
log_slow_queries: OFF
log_update: OFF
log_warnings: 1
long_query_time: 10
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 16776192
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 10000
max_connections: 1000
max_delayed_threads: 20
max_heap_table_size: 16777216
max_insert_delayed_threads: 20
max_join_size: 18446744073709551615
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_tmp_tables: 32
max_user_connections: 100
max_write_lock_count: 4294967295
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 9223372036854775807
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 67108864
net_buffer_length: 8192
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
open_files_limit: 5010
pid_file: /var/lib/mysql/57/data57//hmysql15.prod.mesa1.gdg.pid
port: 3306
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_size: 20971520
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 1044480
read_only: OFF
read_rnd_buffer_size: 262144
rpl_recovery_rank: 0
server_id: 57
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slave_net_timeout: 3600
slow_launch_time: 2
socket: /tmp/mysql-57.sock
sort_buffer_size: 1048568
sql_mode: 0
table_cache: 256
table_type: MYISAM
thread_cache_size: 8
thread_stack: 196608
timezone: MST
tmp_table_size: 33554432
tmpdir: /tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: READ-COMMITTED
version: 4.0.27-max-log
version_comment: Official MySQL-max binary
version_compile_os: unknown-linux-gnu
wait_timeout: 20