What are common MySQL performance tuning settings?
Why Interviewers Ask This
Mid-level MySQL / SQL roles require deep understanding of this topic. Interviewers ask this to separate candidates who truly understand the mechanics from those who only know surface-level concepts.
Answer
Key MySQL performance configuration settings (my.cnf): (1) innodb_buffer_pool_size: the most impactful setting — caches data and indexes in memory. Set to 60-80% of total RAM for a dedicated MySQL server. Default is only 128MB. innodb_buffer_pool_size = 8G (for 12GB server); (2) innodb_log_file_size: larger log files reduce checkpoint frequency and improve write throughput. innodb_log_file_size = 512M; (3) max_connections: maximum concurrent connections. Default 151. Size based on your app's pool size; (4) query_cache_size: set to 0 (disabled) in MySQL 8.0+; (5) innodb_flush_log_at_trx_commit: 1 = fully ACID (flush per commit, safest); 2 = flush per second (minor data loss risk, much faster writes); (6) innodb_io_capacity / innodb_io_capacity_max: tell InnoDB how fast your I/O is (default 200 is for HDD; set to 2000+ for SSDs); (7) tmp_table_size / max_heap_table_size: how large in-memory temp tables can grow before spilling to disk (set to 64M-256M); (8) sort_buffer_size / join_buffer_size: per-session buffers for sorting and joins. Monitor with SHOW STATUS; and the Performance Schema.
Pro Tip
Back up your answer with a specific project or situation. Saying 'In my last MySQL / SQL project, I used this when...' immediately makes your answer more credible and memorable.
Previous
What is database connection pooling?
Next
What is the difference between B-tree and Hash indexes in MySQL?