Master Linux Memory Checks and SQL Join Optimization: Interview Answers Explained

Linux Memory Monitoring and Cache Clearing

The interviewer asks how to view memory usage on a Linux system. The appropriate commands are free and top. The free output includes fields such as total (total memory), used (used memory), free (idle memory), buff/cache (memory used for buffers and cache), and available (memory readily available for new processes).

To clear the buffer/cache, the command sync; echo 3 > /proc/sys/vm/drop_caches can be executed, which forces the kernel to drop page cache, dentries and inodes. While this frees up memory, it should be used with caution in production environments.

SQL Join Overview

In relational databases, a JOIN combines rows from two or more tables based on a related column. The five common join types are: inner join – returns rows with matching keys in both tables. left join – returns all rows from the left table and matching rows from the right. right join – returns all rows from the right table and matching rows from the left. full join – returns rows when there is a match in either table.

When using JOIN statements, performance can degrade, especially with large data sets.

Optimization Strategies

For small data sets, loading everything into memory is acceptable.

For large data sets, add appropriate indexes, reduce the number of joins (preferably fewer than five per query), and avoid redundant joins.

Adjust join_buffer_size in MySQL; a larger buffer can improve join performance.

MySQL InnoDB stores data in 16 KB pages and creates a .ibd file per table. Each join may require reading multiple files, causing disk‑head movement and slowing queries.

Join Algorithms

When no suitable index exists, InnoDB may use:

Nested Loop Join : reads one row from the outer table and scans the entire inner table for each row, leading to outerRows × innerRows reads.

Block Nested Loop Join : reads blocks of rows into memory to reduce I/O, used when indexes are unavailable.

Example tables t_a and t_b illustrate how InnoDB falls back to the block nested loop algorithm for unindexed joins.

Key Takeaways

Understanding memory usage on Linux and the internal workings of MySQL joins helps diagnose performance bottlenecks. Increase join_buffer_size when appropriate, consider SSDs for faster I/O, and design schemas that minimize costly joins.

References

"深入理解计算机系统" – Chapter on memory hierarchy.

Linux memory cache articles (e.g., linuxatemyram.com).

MySQL performance guides and MariaDB block‑based join documentation.