Memory behaviour on W2K

[Francis Cox]

I'm using MySQL as an embedded db within another product (Netcool SM Reporter) on W2K: dual CPU, ram is 4Gb, swap of 2Gb, giving 6Gb available. I am finding the db running increasingly slowly. I've tried increasing the value of key_buffer but when I go beyond 2100M the vm size of the process starts to *reduce" from the value of ~1700M: this is strange.

The ratio of key_read to key_read_requests is ~0.03. The physical size on disc of the db is ~2.5Gb. A full "show status" is below.

Any clues to improve performance and to find the main cause of the problem? Unfortunately, as the product is embedded I don't have access to the query source codes.

Variable_name Value
Aborted_clients 13176
Aborted_connects 0
Bytes_received 3435155952
Bytes_sent 2911637578
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 6253
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 51577
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 5494393
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 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 1311276
Com_set_option 32
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 6
Com_show_fields 7
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 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 4
Com_show_innodb_status 0
Com_show_tables 431
Com_show_variables 190
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 80840
Connections 6689
Created_tmp_disk_tables 0
Created_tmp_tables 4829
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 3478121
Handler_read_first 206816
Handler_read_key 10170169
Handler_read_next 2147177100
Handler_read_prev 0
Handler_read_rnd 7289210
Handler_read_rnd_next 3763945991
Handler_rollback 0
Handler_update 9006388
Handler_write 162747472
Key_blocks_used 1528845
Key_read_requests 442401669
Key_reads 13143525
Key_write_requests 20368001
Key_writes 13629355
Max_used_connections 92
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 62
Open_files 119
Open_streams 0
Opened_tables 6556
Questions 6948605
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_free_memory 0
Qcache_free_blocks 0
Qcache_total_blocks 0
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 832824
Select_range_check 0
Select_scan 107551
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 2207
Sort_merge_passes 0
Sort_range 24034
Sort_rows 7289210
Sort_scan 80711
Table_locks_immediate 6904382
Table_locks_waited 7497
Threads_cached 0
Threads_created 100
Threads_connected 64
Threads_running 1
Uptime 181424