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)