When working with MySQL's table cache subsystem, we're dealing with one of the most fundamental yet often misunderstood performance parameters. The table_cache (called table_open_cache in MySQL 5.1+) controls how many table file descriptors the server keeps open simultaneously.

SHOW GLOBAL STATUS LIKE 'Open%tables';
SHOW VARIABLES LIKE 'table%cache';

Each open table consumes approximately:

• 64KB for the table definition cache (shared between all threads)
• Additional memory per connection for table handlers

The total memory impact can be estimated with:

SELECT (@@table_open_cache * 64 / 1024) AS 'MB used for cache';

For production systems with many tables, follow this calculation approach:

-- Calculate recommended size:
SET @tables = (SELECT COUNT(*) FROM information_schema.tables 
               WHERE table_schema NOT IN ('information_schema','mysql','performance_schema'));
SET @connections = @@max_connections;
SET @recommended_cache = @tables + @connections * 2;
SELECT @recommended_cache AS 'Recommended table_open_cache';

Track these key metrics:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Open_tables',
  'Opened_tables',
  'Table_open_cache_hits',
  'Table_open_cache_misses'
);

The cache hit ratio should be calculated as:

SELECT ROUND(Table_open_cache_hits/(Table_open_cache_hits+Table_open_cache_misses)*100,2) 
AS 'Cache hit ratio %' 
FROM performance_schema.global_status 
WHERE variable_name='Table_open_cache_hits'
UNION ALL
SELECT ROUND(Table_open_cache_misses/(Table_open_cache_hits+Table_open_cache_misses)*100,2) 
AS 'Cache miss ratio %' 
FROM performance_schema.global_status 
WHERE variable_name='Table_open_cache_misses';

For a busy e-commerce platform with 1,500 tables and 500 connections:

# my.cnf configuration
[mysqld]
table_open_cache = 5000
table_definition_cache = 4000
open_files_limit = 20000

Remember to adjust open_files_limit accordingly since each open table consumes a file descriptor.

You can change the setting without restart:

SET GLOBAL table_open_cache = 5000;
FLUSH TABLES;  -- Closes all open tables

However, for persistent changes, always update my.cnf.

Watch for these warning signs in your error log:

[Warning] Buffered warning: Could not open table: db.table
[Warning] Table cache is full: 4000 tables

If you see these, increase both table_open_cache and open_files_limit.

For systems with table_partitions:

-- Each partition counts as a separate table
SELECT COUNT(*) FROM information_schema.partitions 
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema');

Include this count in your sizing calculations.

---

The table_cache (known as table_open_cache in MySQL 5.1+) determines how many table handles can be kept open simultaneously. When MySQL needs to access a table, it checks this cache first. Misses force MySQL to close existing tables to make room, causing performance overhead.

Each open table consumes memory for metadata (typically 1-4KB per table). With your current 4096 cache size and 95% utilization, memory usage would be:

-- Sample memory calculation
SELECT 
  4096 * 4 / 1024 AS 'Current MB Usage',
  (4096 * 1.2) * 4 / 1024 AS 'Proposed MB Usage'

Follow this formula for most production systems:

max_connections * (tables_per_query + temp_tables) * 1.25

For example, with 200 connections and typical 5 joined tables:

200 * (5 + 2) * 1.25 = 1750

In your my.cnf:

[mysqld]
# For a server with 16GB RAM running complex queries
table_open_cache = 6000
table_definition_cache = 6000
open_files_limit = 20000

Check these metrics weekly:

SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Table_open_cache_misses';

-- Calculate hit ratio
SELECT 
  (1 - Variable_value / 
  (SELECT Variable_value FROM global_status WHERE Variable_name = 'Table_open_cache_hits')) * 100 
  AS 'Cache Hit Ratio %'
FROM global_status 
WHERE Variable_name = 'Table_open_cache_misses';

For NUMA systems, also configure:

innodb_open_files = 4000  # Typically 75% of table_open_cache

Remember to monitor Open_files status to ensure you're not hitting OS limits.