Cacheing database data

[richietheg]

I apologise, for a first post this is quite a long one...

 

I seem to be making this more complicated than it needs to be, so am wondering if there's any hints out there that will make it not so! I'm not new to PHP, but there are probably some bits of it that I don't know as well as I probably ought...

 

I am working on a web player for an Internet radio station with the schedule data held in a mySQL database. The player updates on the screen according to the schedule with what show is currently on air and what is up next etc. That whole part of things works. A jquery refresh script calls the PHP file that queries the database every 10 seconds or so (the old schedule data was kept in a very large PHP array which was difficult to edit unless you knew PHP, and I've kept the same basic code for displaying it even if extracting the data has changed!).

 

This method though queries the database too much to gain the same data as before. As the data retrieved includes the time the next programme starts, it seems that I should be able to only query the database when the programme has changed. An obvious way of doing this is to write a cache file that the player page calls, and use that data unless the programme has changed.

 

The bit I'm currently struggling to wrap my head around is how to know when to change the cache, which - as I said - given that the data retrieved contains this information seems a little odd.

 

Say the file that gets the data from the database is 'getcache.php' and the file that contains the data is 'onair-cache.php' (because, that actually is their names!).

 

When 'getcache.php' is called, it should check 'onair-cache' for the time of next show. If this time has passed, then it should query the database for the new information and write it to 'onair-cache.php'. However, surely a file can't be written to if it's open, so putting something like:

if ($programme['end_time'] <= now) {
  include ('getcache.php');
}

into 'onair-cache.php' wouldn't work as 'getcache.php' needs to be able to write the new information into 'onair-cache.php'. Also, what would happen if there are many users who are connected at the same time, each one will end up then querying and writing at the same time?

 

(Btw, that code was just some pseudocode that probably won't work as it is, but would just give the idea of what's needed!) 

 

As I said right at the beginning, I seem to be making this more complicated than it needs to be, so if there's a simpler way of doing it then I'd be grateful to know!

 

Many thanks!

[kicken]

Are you actually having a problem with the database, or do you just "feel" like it's being queried too much? Databases are designed to be queried, and some will even cache the results of queries automatically for you.

 

If you are actually experiencing problems with quering the database, most likely it is due to either bad DB design, or poorly written queries. Before you even worry about trying to get some cache solution setup, make sure you have setup the DB and the queries as well as you possibly can. Post the relevant info here if you want help or someone to check your setup.

 

Lastly, if you decide you need to do a cache, using something like memcache would be good. If that is not available, you'll want to make sure you implement proper file locking on your cache file to prevent one page from trying to read it while another is writing it.

[richietheg]

To be honest, yes I am just 'feeling' that the database is being queried too much. It just seems bad practice to me that the database is being queried every 10 seconds or so by each user and, while currently the particular radio station I'm working with doesn't get too many listeners, the more people who are on the player the more it's being queried (I am hoping to get it so it only refreshes the data if the player is the currently active window, but one step at a time!) - it just seems to be a waste when really all that's needed is one query per show... just the shows don't start at regular times to make this possible. I guess I'm just not wanting to piss off my host with an inordinate amount of DB queries should our listenership increase!

 

I'm not getting any problems with this number of DB queries, but this is only working on an internal server at the moment. I can't guarantee the good-ness of the DB design or its queries, though I am currently working on the queries to streamline it somewhat (I suddenly realised that I made a schoolboy error yesterday... why the hell aren't I using a 'JOIN' in my code?!  ).

 

Thanks for your reply!

[n1concepts]

You could resolve the need for MySQL queries by implementing memcache.

Note: configure memcache to be called and set a function to check if dat has passed set timeframe - if date older than set criteria, then you pull from the database.

 

I have this running on a setup and where I was having to query the db seven to twelve times - per instance - it only queries on the initial 'column' INSERT and/or an UPDATE.

Rest of the time, the php script is pulling data straight from memcache and MySQL never touched - i even wrote function to only open (and close) the db connection if/when the db query required.

 

As a result: where I was averaging over 70 open connections - handling around 25,000 http requests to the php script, max connections only 1 now so works for me using method I've stated above.

Here's an example to review to come up w/your own solution.

 

Example: http://net.tutsplus.com/tutorials/php/faster-php-mysql-websites-in-minutes

 

Good luck!

Edited July 25, 2013 by n1concepts