Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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?

share|improve this question
    
What SQL server are you running? PostgreSQL, MySQL with InnoDB, etc.? Try restarting it before dropping caches. –  gertvdijk Jun 23 '13 at 16:19
add comment

migrated from stackoverflow.com Jun 22 '13 at 22:02

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.