How to Inspect and Clear Linux Buff/Cache and Optimize MySQL Joins

Scoring Question: Linux Memory Inspection

Interviewer: Have you operated Linux?

Me: Yes.

Interviewer: Which command would you use to view memory usage?

Me: free or top.

Interviewer: What information does the free command show?

Me: As shown in the image, it displays total memory, used memory, free memory, buff/cache, and available memory.

Interviewer: Do you know how to clear the used cache (buff/cache)?

Me: I don't.

Interviewer: Run sync; echo 3 > /proc/sys/vm/drop_caches to clear buff/cache. Would you run this on a production server?

Me: It frees a lot of memory, similar to a PC cleaning tool.

SQL Join Review

Interviewer: Explain your understanding of JOIN.

Me: JOIN combines tables based on conditions and returns the result set.

Review of Join Types

SQL supports several join types:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

Interviewer: How would you optimize join performance?

Me: For small data sets, keep everything in memory. For large data sets, add indexes, reduce the number of joins (preferably no more than five), and consider denormalization.

Interviewer: Why are joins performance‑intensive?

Me: Joins require row‑by‑row comparisons, which can be slow. Using buffers helps.

Buffer and Join Buffer

Running show variables like '%buffer%' reveals buffer settings such as join_buffer_size, which directly affects join performance.

Large join buffers improve performance, but other factors matter.

Disk I/O and Buffer Cache

Data ultimately resides on disk in 16KB pages (InnoDB). Each table has an .ibd file.

Frequent table joins require reading many files, causing disk head movement and affecting performance.

Linux uses memory as a cache for disk I/O, which explains the large buff/cache values.

"The essence of the memory hierarchy is that each level of storage acts as a cache for the level below it." – CSAPP

Reference: TLDP buffer‑cache documentation.

Join Algorithms

Without indexes, MySQL may use Nested Loop Join or Block Nested Loop Join.

Nested Loop Join reads one row at a time, leading to many I/O operations.

Block Nested Loop Join reads blocks of rows into memory to reduce I/O.

When indexes are unavailable, InnoDB automatically switches to this algorithm.

Conclusion

Understanding Linux buffer/cache and MySQL join mechanisms helps you tune performance: increase join_buffer_size, use SSDs, add appropriate indexes, and consider denormalization when necessary.

References

"Computer Systems: A Programmer's Perspective" – Chapter 6 "Experiments and fun with the Linux disk cache" "Linux ate my RAM" – explanation of free parameters "How to clear the buffer/pagecache (disk cache) under Linux" "How MySQL Works" – deep dive MariaDB documentation on Block‑Nested‑Loop algorithm