Saturday, July 12, 2008

Pre-populate cache for faster performance

Recently I came across a scenario where load had to be shifted from an existing database server to a newer (faster) machine. This is usually not a problem but the challenge this time was to do this during peak-load.

I tried to shift queries to the new server a number of times, but found the load shooting up each time. Thus, the queries had to be shifted back to the old machine.

After a bit of investigation it was found that since the new server had not cached the database contents, it lead to high load and thus the server response time dropped drastically. To overcome this, I used a small trick. I cached the database contents (at least the indexes) before sending the queries back to the new server. To pre-populate the cache (RAM), use the following:

cat TABLE_FILES > /dev/null

(Considering that TABLE_FILES are the file names which contain the data/index)

This will make the OS read complete contents of the desired files and dump the output to /dev/null. Certainly this is useful only if your data/index size is less than RAM.

After this, the new machine worked like a breeze and there was no significant i/o.

2 comments:

Anonymous said...

Interesting hack.
Alternatively, you could have give "LOAD INDEX INTO CACHE" a try:
http://dev.mysql.com/doc/refman/5.0/en/load-index.html

Vivek said...

Thanks Kapil!
Load index also seems a useful option. Will keep that in mind.