I'm trying to optimize some database app running on a CentOS server and wanted to confirm some things about the disk/file caching mechanism.
From what I've read, Linux has a Virtual Filesystem layer that sits
between the physical file system and everything else. So no matter what FS is used, applications are still addressing the VFS. Due to this, disk caching is done on an inode/block basis.
I'm assuming that this is still the case in CentOS or am I badly mistaken?
If that is correct, then here is my scenario and hypothesis.
Assuming the server has xxx MB of free memory and the database consist of several tables more than xxx MB in size. So no table will fit entirely into memory. And assuming other processes do not interfere with the caching behaviour or available memory etc.
Given the inode caching behaviour, if the DBMS only access a bunch of inodes that total less than xxx MB, is it therefore likely to be always using the cache, hence faster?
My thought is that if this is the case, then I could likely speed up the application behaviour if I further split the tables into parts that are more frequently accessed, and parts that are unlikely touched.
e.g. the table may currently have rows with 20 fields and total 1KB/row, but very often say only 5/20 fields are used in actual processing. Reading x rows from this table may access x inodes which would not fit into the cache/memory.
However if now I break the table into two parts with those 5 fields into a smaller table, there would be a speed increase since the reading the same x rows from this table would only access 1/x inodes. Further more, these would more likely fit into the disk/memory cache for even faster access.
Or would I simply be duplicating what the DBMS's index files would already be doing and therefore see no improvement?