Understanding Linux Memory Usage and SQL Join Optimization in Technical Interviews

An interview scenario begins with a question about checking memory usage on Linux. The candidate mentions using free or top and explains the output fields: total, used, free, buff/cache, and available memory.

The interviewer then asks how to clear the buff/cache area, and the candidate is shown the command sync; echo 3 > /proc/sys/vm/drop_caches , which frees the page cache, dentries, and inodes. The discussion highlights that clearing this cache can quickly free memory but may affect system performance.

Switching topics, the interview moves to SQL join concepts. The candidate reviews join types—inner, left, right, and full joins—and describes how they combine rows from multiple tables based on matching conditions.

Performance considerations are addressed: for small data sets, loading everything into memory is sufficient, while for large data sets the candidate suggests adding indexes, reducing redundant joins, and limiting the number of table joins per query.

The conversation then explores MySQL’s internal buffers. Running show variables like '%buffer%' reveals the join_buffer_size setting, which directly influences join execution speed. Larger buffers can reduce I/O by holding more rows in memory.

Further, the article explains InnoDB’s storage architecture—16 KB pages, .ibd files per table, and the impact of reading multiple files during joins, which can cause frequent disk‑head movement and degrade performance.

To mitigate this, the candidate mentions sequential I/O optimizations used by systems like HBase and Kafka, and notes that Linux treats unused memory as disk cache, which can be reclaimed when needed.

Finally, the interview covers join algorithms. Without indexes, MySQL may fall back to Nested Loop Join, reading one row at a time, or Block Nested Loop Join, which processes data in blocks to reduce I/O. When indexes are available, the engine can use index lookups to speed up joins.

The summary emphasizes that understanding both operating‑system memory management and database join mechanics is crucial for performance‑focused development.