Hi,
I have a MySQL Server 4.0.26 Community Edition runnning in a Windows 2003 server. The hardware is 2 GB RAM, 120 GB IDE Disk, Pentium IV HT 3.06 Ghz
MySQL crashs often in this server. It stops responding for new new threads after a week after its last restart, until it reaches the max connection limits (500). When I see the process list, I find that many threads are stuck in the “Opening tables” status, or “Closing tables” status. I have to restart the server when it happens.
This server has around 200 active databases, the average number of simultaneous connections is 100, it runs both MyISAM and InnoDB tables.
I have read many articles suggesting that I should increase table_cache, key_buffer_size and other variables in order to get more cache hits. I did it but I still have these crashes. I read also that the file descriptors limit in Windows is 2048, but I don’t know how it relates to the problem.
Table_cache is 1500, but opened_tables remain huge and open_tables is low (!).
I post here my.cnf file and the show status display in the exact moment the server stops responding for new threads.
How should I improve server configuration in order to get this problem definitely solved?
==============my.cnf======================
[mysqld]
skip-name-resolve
log-slow-queries
skip-locking
set-variable = max_connections=1000
set-variable = max_connect_errors=10
set-variable = table_cache=800
set-variable = key_buffer_size=650M
set-variable = max_allowed_packet=1M
set-variable = sort_buffer_size=3M
set-variable = read_buffer_size=1M
set-variable = read_rnd_buffer_size=2M
set-variable = myisam_sort_buffer_size=64M
set-variable = query_cache_size=128M
set-variable = long_query_time=3
set-variable = tmp_table_size=64M
set-variable = thread_cache_size=128
set-variable = innodb_buffer_pool_size=550M
=============== show status ==========================
VALUE VARIABLE_NAME
21075 Aborted_clients
11997 Aborted_connects
1243908889 Bytes_received
107979292 Bytes_sent
560239 Com_admin_commands
133 Com_alter_table
0 Com_analyze
0 Com_backup_table
3467 Com_begin
2262303 Com_change_db
0 Com_change_master
0 Com_check
1463 Com_commit
24 Com_create_db
0 Com_create_function
33 Com_create_index
11423 Com_create_table
451702 Com_delete
2225 Com_delete_multi
0 Com_drop_db
0 Com_drop_function
0 Com_drop_index
289 Com_drop_table
24 Com_flush
24 Com_grant
0 Com_ha_close
0 Com_ha_open
0 Com_ha_read
769121 Com_insert
322 Com_insert_select
2 Com_kill
2 Com_load
0 Com_load_master_data
0 Com_load_master_table
18 Com_lock_tables
1368 Com_optimize
0 Com_purge
71 Com_rename_table
1 Com_repair
320608 Com_replace
0 Com_replace_select
0 Com_reset
0 Com_restore_table
0 Com_revoke
155 Com_rollback
0 Com_savepoint
3595129 Com_select
1378901 Com_set_option
0 Com_show_binlog_events
101 Com_show_binlogs
2633 Com_show_create
925 Com_show_databases
209715 Com_show_fields
222 Com_show_grants
180 Com_show_innodb_status
5545 Com_show_keys
0 Com_show_logs
3 Com_show_master_status
0 Com_show_new_master
0 Com_show_open_tables
14169 Com_show_processlist
0 Com_show_slave_hosts
0 Com_show_slave_status
2946 Com_show_status
50978 Com_show_tables
20121 Com_show_variables
0 Com_slave_start
0 Com_slave_stop
367 Com_truncate
18 Com_unlock_tables
8167201 Com_update
8 Com_update_multi
1434990 Connections
103319 Created_tmp_disk_tables
4926 Created_tmp_files
413624 Created_tmp_tables
0 Delayed_errors
0 Delayed_insert_threads
432 Delayed_writes
1 Flush_commands
668 Handler_commit
94084 Handler_delete
745913 Handler_read_first
156902358 Handler_read_key
345536042 Handler_read_next
5981052 Handler_read_prev
151094963 Handler_read_rnd
2738144011 Handler_read_rnd_next
109246 Handler_rollback
19945571 Handler_update
37095543 Handler_write
631884 Key_blocks_used
290843011 Key_read_requests
948670 Key_reads
5296913 Key_write_requests
1695036 Key_writes
266 Max_used_connections
0 Not_flushed_delayed_rows
0 Not_flushed_key_blocks
95 Open_files
0 Open_streams
73 Open_tables
205236 Opened_tables
8636 Qcache_free_blocks
45298184 Qcache_free_memory
22719701 Qcache_hits
3289899 Qcache_inserts
612440 Qcache_lowmem_prunes
303981 Qcache_not_cached
23491 Qcache_queries_in_cache
60583 Qcache_total_blocks
42272899 Questions
NULL Rpl_status
57927 Select_full_join
9 Select_full_range_join
115798 Select_range
32 Select_range_check
1379336 Select_scan
0 Slave_open_temp_tables
OFF Slave_running
0 Slow_launch_threads
663 Slow_queries
785 Sort_merge_passes
154019 Sort_range
151199174 Sort_rows
786228 Sort_scan
15130381 Table_locks_immediate
167130 Table_locks_waited
0 Threads_cached
267 Threads_connected
344 Threads_created
209 Threads_running
664448 Uptime
Thanks in advance!
Rogerio