Announcement

Announcement Module
Collapse
No announcement yet.

table_cache on db with many tables

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

  • table_cache on db with many tables

    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)

  • #2
    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.

    Comment


    • #3
      Quote:

      For each opened table MySQL requires a couple of file descriptors from the OS.


      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:-)

      Comment


      • #4
        erkules wrote on Sat, 08 December 2007 02:05


        Im courios. On Linux/UNIX you open a File and get 3FD (STDIN,STDOUT and STDERR).


        What you are referring to here is not _opening_ a file, it is _executing_ a binary program file.
        That is when you get these 3 standard POSIX file descriptors internally within this process.
        But if you from _within_ this process open a file, you will get one additional FD for each file that you open.

        erkules wrote on Sat, 08 December 2007 02:05


        So if you open a Table in MySQL you (if we count the Indexfile also) have 6 FD?


        No, unfortunately that's not how you calculate it.

        One MyISAM table is actually three separate files and each file need to be opened, since you need to read data from each file. One file is the structure of the table, one is the index data and one is the table itself.
        So for each table you need 3 FD.

        And then you have to add that MySQL is a multithreaded application. Which in this case should mean that each thread needed it's own copy of FD for each table.
        So 20 clients using 3 tables each and 3 FD per table = 180 FD.

        Which is a lot of FD. _But_ (there is always something right ) ) , index data as the only FD is actually shared between each MySQL thread which means that the Index files are only opened once for each client thread that uses it.

        So to summarize:
        One table is 3 FD
        Each thread needs to open at least 2 FD (unless the index file isn't already open).
        So if you have a lot of connections you can essentially calculate 2 FD per table and thread + 1 FD per table for the index files.

        So 3 tables with 5 client threads reading from these tables will consume 2*5+3 = 13 FD.

        erkules wrote on Sat, 08 December 2007 02:05


        couriositiy killed the cat:-)

        You are not dead yet, are you?

        Comment


        • #5
          (bow)

          thx a lot serin. i was always how to calculate this open file thing )

          Comment

          Working...
          X