Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

table_cache on db with many tables

alst74alst74 EntrantInactive User Role Beginner
Intro:
I do have a db with pretty many tables, from 5k-20k. Each table has 20k rows.
Tables contain info from a certain date and time.

I'm not sure exactly where to start tuning, but I did run tuning-primer script which among a couple of other parameters did recommend me to change "table_cache".

It gave me this recommendation for table_cache:
TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 6551 tables
You have 1024 open tables.
Current table_cache hit rate is 2%, while 100% of your table cache is in use
You should probably increase your table_cache

Not sure I do understand this table_cache parameter. I mean, if I understood correctly I do cache tables that is not in use. And what does table_cache really contain, does it only cache the table's physical location?

Also, I guess on systems like this there's only waste with memory to try to apply it to the query cache since no query's look the same. They are very often with different timestamps.
I guess it would be better to try to make it fast to read from disk instead?

Here's some more info and would be happy if someone could give me some advice eek:

Uptime: 19056 Threads: 29 Questions: 6044010 Slow queries: 6 Opens: 52758 Flush tables: 4 Open tables: 6 Queries per second avg: 317.171


mysql> show global status
-> ;
+
+
+
| Variable_name | Value |
+
+
+
| Aborted_clients | 4 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2764303709 |
| Bytes_sent | 109773773 |
| Com_admin_commands | 92 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 15 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 311 |
| Com_dealloc_sql | 0 |
| Com_delete | 240 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 240 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 3 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 6042636 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 7723 |
| Com_set_option | 264 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 88 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 11 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 1 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 150 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2523 |
| Com_show_triggers | 0 |
| Com_show_variables | 240 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 616 |
| Com_stmt_execute | 6047898 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 684 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 308 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 448 |
| Created_tmp_disk_tables | 40 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 3148 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 4 |
| Handler_commit | 0 |
| Handler_delete | 240 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 445 |
| Handler_read_next | 108 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 113999 |
| Handler_read_rnd_next | 73083021 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 308 |
| Handler_write | 6168493 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 211665 |
| Key_blocks_used | 172847 |
| Key_read_requests | 49317023 |
| Key_reads | 220681 |
| Key_write_requests | 18672989 |
| Key_writes | 18672989 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 32 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 14 |
| Open_streams | 0 |
| Open_tables | 7 |
| Opened_tables | 52760 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759744 |
| Qcache_hits | 163 |
| Qcache_inserts | 2715 |
| Qcache_lowmem_prunes | 1648 |
| Qcache_not_cached | 8020 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Questions | 6056630 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 445 |
| Select_range_check | 0 |
| Select_scan | 9959 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 6 |
| Sort_merge_passes | 0 |
| Sort_range | 445 |
| Sort_rows | 119980 |
| Sort_scan | 1980 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 6051191 |
| Table_locks_waited | 28 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 3 |
| Threads_connected | 29 |
| Threads_created | 33 |
| Threads_running | 1 |
| Uptime | 19092 |
+
+
+
245 rows in set (0.00 sec)

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    table_cache doesn't really have anything to do with caching of the table itself.

    The value defines how many tables MySQL can hold open at the same time.
    For each opened table MySQL requires a couple of file descriptors from the OS. And since some OS's put a limit on how many file descriptors a process are allowed to open you have a limit for this in MySQL.
    Each file descriptor takes actually a pretty small amount of memory so you can usually safely increase this.



    But the real question is why you have so many small tables?
    Are all these tables identical in design it's just that they store data from different times or?
    Because whenever I hear about someone having so many small tables I associate it with an overly partitioned table due to a poor design.
  • erkuleserkules Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
    For each opened table MySQL requires a couple of file descriptors from the OS.
    </td></tr></table>

    Im courios. On Linux/UNIX you open a File and get 3FD (STDIN,STDOUT and STDERR). So if you open a Table in MySQL you (if we count the Indexfile also) have 6 FD?

    So if open tables means FDs it seems to be smaler as you would expect...
    Or is MySQL openening the file with 2 FD?

    couriositiy killed the cat:-)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.