Understanding Linux Memory Usage, Buff/Cache Cleanup, and SQL Join Optimization

Linux Memory Inspection

The interview starts with a question about checking memory usage on Linux. The free or top commands display total, used, free, buff/cache, and available memory. An image shows the typical output of free .

To clear the buff/cache, the command sync; echo 3 > /proc/sys/vm/drop_caches is suggested, which frees page cache, dentries, and inodes.

SQL Join Overview

The conversation shifts to SQL joins. Various join types are introduced with diagrams: inner join, left join, right join, and full join.

Performance considerations are discussed: use indexes, avoid excessive joins (no more than five per query), and increase join_buffer_size for better performance.

InnoDB Storage Details

InnoDB stores data in 16 KB pages and creates an .ibd file per table. Images illustrate page layout and file structure.

When multiple tables are joined, each table’s file must be read, causing disk head movement; sequential I/O can mitigate this.

Join Algorithms

Two main algorithms are described:

Nested Loop Join – reads one row from the outer table and scans the inner table for each row, leading to many I/O operations.

Block Nested Loop Join – reads blocks of rows into memory to reduce I/O; used by MySQL when no suitable index exists.

Examples of tables t_a and t_b demonstrate how InnoDB falls back to Block Nested Loop when indexes are unavailable.

Buffer Variables

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

Conclusion

The article concludes that database performance often hinges on join efficiency; tuning buffer sizes, using indexes, and reducing unnecessary joins are key strategies, alongside understanding Linux’s memory caching behavior.