Announcement

Announcement Module
Collapse
No announcement yet.

MySQL crash ("opening tables" status)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL crash ("opening tables" status)

    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
Working...
X