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)
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?
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]