MySQL Variables, Commands, and Alias Summary

1. Variables

• innodb_buffer_pool_size: Allocates memory for caching InnoDB data and indexes. Default is typically 128 MB; recommended to set it to 50-75% of total RAM.
• table_open_cache: Controls the number of table handles MySQL can keep open in memory. Default is usually set to 400; increase based on workload (e.g., 15,000 or 20,000).
• table_definition_cache: Controls the number of table definitions MySQL can cache in memory. Default is often set to 400; consider increasing it to 4,000-5,000.
• Open_table_definitions: Current number of table definitions cached in memory.
• Open_tables: Current number of table handles open in memory (can exceed the number of tables due to multiple connections).

2. Monitoring Commands

• SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
• SHOW VARIABLES LIKE 'table_open_cache';
• SHOW VARIABLES LIKE 'table_definition_cache';
• SHOW GLOBAL STATUS LIKE 'Open_table_definitions';
• SHOW GLOBAL STATUS LIKE 'Open_tables';
• SELECT COUNT(*) FROM information_schema.tables;

3. Bash Alias for Monitoring

Alias for Watching MySQL Cache: alias watch_mysql_cache='watch -n 1 "mysqladmin extended-status | grep -E \"Open_tables|Open_table_definitions\"'

4. Configuration Changes

Modify my.cnf (MySQL configuration file):

innodb_buffer_pool_size = 8G      # Set to 8 GB
table_open_cache        = 20000   # Increase as needed
table_definition_cache  = 5000    # Increase as needed
            

Conclusion

Monitor these variables and adjust as necessary to optimize MySQL performance, especially under high load. Use the provided commands and alias for quick status checks and informed adjustments.