3

I need to clear system cache in order to record the time taken by a postgres query in cold cache. For clearing the system cache I am executing the following commands as root on Ubuntu server 12.04:

sync; echo 1 > /proc/sys/vm/drop_caches
sync; echo 2 > /proc/sys/vm/drop_caches
sync; echo 3 > /proc/sys/vm/drop_caches

I am running the following SQL commands along with clearing caches:

1. select col1 from my_table;  //Time Taken= 3000 ms
2. sync; echo 1 > /proc/sys/vm/drop_caches
   sync; echo 2 > /proc/sys/vm/drop_caches
   sync; echo 3 > /proc/sys/vm/drop_caches
3. Now again running the same select command and clearing caches:
   select col1 from my_table;  //Time Taken= 200 ms
4. sync; echo 1 > /proc/sys/vm/drop_caches
   sync; echo 2 > /proc/sys/vm/drop_caches
   sync; echo 3 > /proc/sys/vm/drop_caches
5. Now again running the same select command and clearing caches:
   select col1 from my_table;  //Time Taken= 10 ms

From what I can interpret from the reduced query execution time is, even after clearing the caches, in effect caches are not getting cleared. Is my interpretation correct? Is there some other way of clearing caches in Ubuntu 12.04 server?

1 Answers1

3

I think you've made a bad assumption. With the drop_caches interface you can clear the caches that are managed by the kernel, but that doesn't reset all userspace processes back to their previous state.

The SQL server is probably caching some intermediate results, or maybe even the final result of the query, in its own memory. Or maybe it has dynamically adjusted its execution plan for the query based on statistics gathered during the first 2 runs.

You can partially compensate by restarting the daemon, but with a complex system like an SQL server, it's going to be really hard to exactly reproduce any previous state other than "freshly installed". Unless you snapshot the entire machine.