Cache the result of a MySQLdb database query in memory

Question

Our application fetches the correct database server from a pool of database servers. So each query is really 2 queries, and they look like this:

1. Fetch the correct DB server
2. Execute the query

We do this so we can take DB servers online and offline as necessary, as well as for load-balancing.

But the first query seems like it could be cached to memory, so it only actually queries the database every 5 or 10 minutes or so.

What's the best way to do this?

Thanks.

EDIT this is for a Pylons web application

Answer 1

Just make a cache(python dict) which stores the first query and return it everytime, clear the cache every N mins, for this you make a decorator or cache class e.g.

import time

cache = {}
lastTime = time.time()

def timedCacheDecorator(func):

    def wrap(*args, **kwargs):

        key = str(args)+str(kwargs)

        # cache for 5 seconds
        global lastTime
        if key not in cache or time.time() - lastTime > 5:
            lastTime = time.time()
            cache[key] = func(*args, **kwargs)

        return cache[key]

    return wrap


# lets test it

@timedCacheDecorator
def myquery():
    return time.time()

print myquery()
time.sleep(1)
print myquery()
time.sleep(5)
print myquery()
time.sleep(1)
print myquery()

Output:

1270441034.58
1270441034.58
1270441040.58
1270441040.58

Now this decorator can be used on any function whose results are to be cached upto a time or may be upto a event, I would make this decorator a class , so that it can take how many seconds to wait before cache refresh, and also you can see how best to generate key.

Answer 2

The easiest way would be to use the beaker cache, which is a library built into the pylons framework.

In your model class import the beaker decorator:

from pylons.decorators.cache import beaker_cache

Then on your function header, where you make the database call, add the following decorator:

@beaker_cache(expire = 300, type='memory')

Change the expire value to the amount of seconds you want (currently set to 5 minutes).