Hey all,
Recently one of the websites written by an x-coworker became really popular and due to some poor database design and indexing the site was basically unusable. One option was to get a bigger and badder db setup, or try to fix it.
Even after fixing some indexing problems and doing some work on the tables, the site was still really sluggish. Since the site was being killed primarily by complex search querys and some pretty static information like the number of members on and offline -- I decided to implement some type of query cache.
It just takes the query (stripping out any known dynamic data like a timestamp), then does an MD5 on it. It then creates a file with that name with a serialized mysql result in it. If it exists and the file modification time is within a certain limit the scripts will just load the data in the file.
Now the site is running REAL smooth, unfortunately I have yet to write clean up routines (the directory grows to over 500mb within 1 week)
Pretty soon we're going to be running a load balanced webserver setup and I was hoping somebody else has some input on this because I REALLY want to know a way around this. We can put all these files on an NFS server and it'll probably work ok. Idealy I want to put at least the most commonly accessed stuff (number of users online, the actual people who are online, members, etc.) in shared memory. As far as I know shared memory can't be shared across multiple servers 🙂 So does anybody know an alternative method?
I'm not sure how much of a problem this is with PHP but I remember coding in other languages I had to worry about locking files when writing to them. I've been doing that now with PHP and havent ran into too many problems (yet). When information is stored in shared memory do these problems arise too?
Another upcoming project is going to need to be load balanced and need the same sort of optimizations. Any reccomendations on how to cache data recieved from the database server?
