Hi Terence, > Hi all, > > My system reports: > > Open_tables 512 > Opened_tables 24,429 > > The docs say that if the latter is high I should increase the table > cache size. (currently at 512)
Looks like you should do that. > > How does one decide what size to increase it to? And is there a problem > with one of the applications that's making this figure so high? Or is > this normal behaviour? Whether this is a problem or not really depend on you application and how busy it is. Generally you want to avoid reopening tables on a busy server. There are two methods for knowing how to increase this number. 1) count how many tables you have in your databases and set this number larger. 2) Your app will probably not use all tables all the time so experiment with larger numbers. Remember to make sure the maximum open files limit of your OS and user that runs mysql is not exceeded. Run limit to determine what this is and make sure that you check for the user that runs MySQL. Hope this helps, Ken > > OS: RH9 > Dual 2.4 Xeon > 1 GIG RAM > > (btw, this kind of question i linked to my previous post for a > performance tuning guide) > > Thanks! > > Here's my status > > Variable_name,Value, > Aborted_clients,801, > Aborted_connects,14, > Bytes_received,1195564158, > Bytes_sent,1491507399, > Com_admin_commands,42960, > Com_alter_table,317, > Com_alter_db,0, > Com_analyze,0, > Com_backup_table,0, > Com_begin,0, > Com_change_db,1654089, > Com_change_master,0, > Com_check,0, > Com_commit,59, > Com_create_db,9, > Com_create_function,0, > Com_create_index,0, > Com_create_table,465, > Com_delete,100132, > Com_delete_multi,21, > Com_do,0, > Com_drop_db,2, > Com_drop_function,0, > Com_drop_index,0, > Com_drop_table,549, > Com_flush,13, > Com_grant,1, > Com_ha_close,0, > Com_ha_open,0, > Com_ha_read,0, > Com_help,0, > Com_insert,6887163, > Com_insert_select,48, > Com_kill,0, > Com_load,14, > Com_load_master_data,0, > Com_load_master_table,0, > Com_lock_tables,15, > Com_optimize,42, > Com_purge,0, > Com_purge_before_date,0, > Com_rename_table,0, > Com_repair,42, > Com_replace,7873, > Com_replace_select,0, > Com_reset,0, > Com_restore_table,0, > Com_revoke,0, > Com_rollback,17, > Com_select,3152624, > Com_set_option,27303, > Com_show_binlog_events,0, > Com_show_binlogs,0, > Com_show_charsets,0, > Com_show_column_types,0, > Com_show_create_table,456, > Com_show_create_db,0, > Com_show_databases,89, > Com_show_errors,0, > Com_show_fields,1775, > Com_show_grants,0, > Com_show_keys,1643, > Com_show_logs,0, > Com_show_master_status,0, > Com_show_new_master,0, > Com_show_open_tables,0, > Com_show_privileges,0, > Com_show_processlist,73, > Com_show_slave_hosts,0, > Com_show_slave_status,0, > Com_show_status,240, > Com_show_innodb_status,0, > Com_show_tables,2846, > Com_show_table_types,0, > Com_show_variables,96, > Com_show_warnings,0, > Com_slave_start,0, > Com_slave_stop,0, > Com_truncate,0, > Com_unlock_tables,15, > Com_update,5146761, > Com_update_multi,0, > Connections,108272, > Created_tmp_disk_tables,71315, > Created_tmp_tables,725937, > Created_tmp_files,0, > Delayed_insert_threads,0, > Delayed_writes,0, > Delayed_errors,0, > Flush_commands,1, > Handler_commit,219, > Handler_delete,941394, > Handler_read_first,483459, > Handler_read_key,102941817, > Handler_read_next,213625710, > Handler_read_prev,448018710, > Handler_read_rnd,3049520, > Handler_read_rnd_next,222363654, > Handler_rollback,16312, > Handler_update,37894671, > Handler_write,27010243, > Key_blocks_used,353833, > Key_read_requests,341367134, > Key_reads,1172755, > Key_write_requests,47942190, > Key_writes,38265419, > Max_used_connections,106, > Not_flushed_key_blocks,0, > Not_flushed_delayed_rows,0, > Open_tables,512, > Open_files,873, > Open_streams,0, > Opened_tables,30492, > Questions,25298875, > Qcache_queries_in_cache,23808, > Qcache_inserts,2368548, > Qcache_hits,8206562, > Qcache_lowmem_prunes,29375, > Qcache_not_cached,784005, > Qcache_free_memory,40477464, > Qcache_free_blocks,2311, > Qcache_total_blocks,50436, > Rpl_status,NULL, > Select_full_join,12285, > Select_full_range_join,16, > Select_range,70254, > Select_range_check,1, > Select_scan,1618573, > Slave_open_temp_tables,0, > Slave_running,OFF, > Slow_launch_threads,0, > Slow_queries,201, > Sort_merge_passes,0, > Sort_range,132898, > Sort_rows,2993422, > Sort_scan,576536, > Table_locks_immediate,17191738, > Table_locks_waited,70304, > Threads_cached,4, > Threads_created,12439, > Threads_connected,18, > Threads_running,1, > Uptime,1186406, > > > > Variable_name,Value, > back_log,50, > basedir,/usr/local/mysql/, > binlog_cache_size,32768, > bulk_insert_buffer_size,8388608, > character_set,latin1, > character_sets,big5 latin2_czech_ci dec8_swedish_ci cp850_general_ci > latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci > latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis > cp1251_bulgarian_ci latin1_danish_ci hebrew tis620 euckr > latin7_estonian_ci latin2_hungarian_ci koi8u_general_ci > cp1251_ukrainian_ci gb2312 greek cp1250_general_ci latin2_croatian_ci > gbk cp1257_lithuanian_ci latin5_turkish_ci latin1_german2_ci > armscii8_general_ci utf8 cp1250_czech_ci ucs2 cp866_general_ci keybcs2 > macce macroman cp852_general_ci latin7_general_ci latin7_general_cs > macce_bin macce_ci macce_cs latin1_bin latin1_general_ci > latin1_general_cs cp1251_bin cp1251_general_ci cp1251_general_cs > macroman_bin macroman_ci macroman_cs cp1256_general_ci cp1257_bin > cp1257_ci_ai cp1257_ci cp1257_cs binary armscii_bin ascii_bin cp1250_bin > cp1256_bin cp866_bin dec8_bin greek_bin hebrew_bin hp8_bin keybcs2_bin > koi8r_bin koi8u_bin latin2_bin latin5_bin latin7_bin cp850_bin cp852_bin > swe7_bin utf8_bin, > client_collation,latin1_swedish_ci, > concurrent_insert,ON, > connect_timeout,5, > convert_result_charset,ON, > datadir,/usr/local/mysql/data/, > default_week_format,0, > delay_key_write,ON, > delayed_insert_limit,100, > delayed_insert_timeout,300, > delayed_queue_size,1000, > expire_logs_days,0, > flush,OFF, > flush_time,0, > ft_boolean_syntax,+ -><()~*:""&|, > ft_min_word_len,4, > ft_max_word_len,254, > ft_max_word_len_for_sort,20, > ft_stopword_file,(built-in), > have_bdb,NO, > have_crypt,YES, > have_innodb,YES, > have_isam,YES, > have_raid,NO, > have_symlink,DISABLED, > have_openssl,NO, > have_query_cache,YES, > init_file,, > innodb_additional_mem_pool_size,1048576, > innodb_buffer_pool_size,402653184, > innodb_buffer_pool_awe_mem_mb,0, > innodb_data_file_path,ibdata1:10M:autoextend, > innodb_data_home_dir,, > innodb_file_io_threads,4, > innodb_force_recovery,0, > innodb_thread_concurrency,8, > innodb_flush_log_at_trx_commit,1, > innodb_fast_shutdown,ON, > innodb_flush_method,, > innodb_lock_wait_timeout,50, > innodb_log_arch_dir,./, > innodb_log_archive,OFF, > innodb_log_buffer_size,1048576, > innodb_log_file_size,5242880, > innodb_log_files_in_group,2, > innodb_log_group_home_dir,./, > innodb_mirrored_log_groups,1, > interactive_timeout,500, > join_buffer_size,131072, > key_buffer_size,402653184, > language,/usr/local/mysql/share/mysql/english/, > large_files_support,ON, > local_infile,ON, > locked_in_memory,OFF, > log,OFF, > log_update,OFF, > log_bin,OFF, > log_slave_updates,OFF, > log_slow_queries,ON, > log_warnings,OFF, > long_query_time,10, > low_priority_updates,OFF, > lower_case_table_names,OFF, > max_allowed_packet,2096128, > max_binlog_cache_size,4294967295, > max_binlog_size,1073741824, > max_connections,150, > max_connect_errors,10, > max_error_count,64, > max_delayed_threads,20, > max_heap_table_size,16777216, > max_join_size,4294967295, > max_prepared_statements,64, > max_sort_length,1024, > max_user_connections,0, > max_tmp_tables,32, > max_write_lock_count,4294967295, > myisam_max_extra_sort_file_size,268435456, > myisam_max_sort_file_size,2147483647, > myisam_recover_options,OFF, > myisam_sort_buffer_size,67108864, > net_buffer_length,16384, > net_read_timeout,30, > net_retry_count,10, > net_write_timeout,60, > new,OFF, > open_files_limit,0, > pid_file,/usr/local/mysql/data/seawolf.pid, > log_error,, > port,1111, > protocol_version,10, > pseudo_thread_id,0, > read_buffer_size,2093056, > read_rnd_buffer_size,262144, > rpl_recovery_rank,0, > query_cache_limit,1048576, > query_cache_min_res_unit,4096, > query_cache_size,67108864, > query_cache_type,ON, > server_id,0, > slave_net_timeout,3600, > skip_external_locking,ON, > skip_networking,OFF, > skip_show_database,OFF, > slow_launch_time,2, > socket,/tmp/mysql.sock, > sort_buffer_size,2097144, > sql_mode,, > table_cache,512, > table_type,MYISAM, > thread_cache_size,8, > thread_stack,126976, > tx_isolation,REPEATABLE-READ, > timezone,MYT, > tmp_table_size,33554432, > tmpdir,, > version,4.1.0-alpha-standard-log, > wait_timeout,500, > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
