MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Terence Date:July 26 2004 12:56am
Subject:table_cache size for large value in opened_tables
View as plain text  
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,

Thread
table_cache size for large value in opened_tablesTerence26 Jul
  • Re: table_cache size for large value in opened_tablesKen Menzel26 Jul