Running a query without the OS cache

Question

I want to emulate a client site who doesn't have much caching because they have so much I/O all the time. I want to run a SQL query on my machine (on my VM to be precise) with the same limitations they have, so I thought I would flush the cache before runnning it everytime.

I need to flush the cache from postgres SQL (which I can do by restarting it, apparently). I also need to flush the OS cache in my VM (where the code is running) and on my host machine running the VM to make sure it won't optimize the whole VM. My goal is to able to run the query in a completely non-optimized fashion (without any kind of caching, not the OS, not postgres, nothing).

So I've been running these commands (which I found in forums), on my VM and on my host :

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

Do you think these are appropriate ? And do you think it's enough ? Or should I use another approach entirely ?

I'm running Red Hat 6.2.

Answer 1

First of all echo 3 > /proc/sys/vm/drop_caches is enough as it combines the echo 1 and echo 2 commands. Otherwise yes they are appropriate to clear the cache - http://linux-mm.org/Drop_Caches

These commands just drop the existing caches, e.g. you create a cold start environment but after you run your first queries the cache will be used. It may also help to de-optimize the postgres configuration and decrease things like shared_buffers.

If you are using kvm for your VM make sure to use a block device as the disk and to use virtio without caching - http://www.linux-kvm.org/page/Tuning_KVM