Database Caching questions...

Grofit

Hey there,

Im currently doing some work on a project where i am getting quite a bit of data from the database. There are quite a few pages all of which need different information but most require the same underlying data.

The data is primarily just numbers linking to types or IDs, however there are a few fields which can bring back quite a bit of text.

I thought rather than querying the database every page getting the data thats needed i would cache it in the session when the user first logs in. That way the data is persistant accross all pages and it minimizes doing lots of joins and queries on the DB.

The session data currently comes to between 20-35kb per person, depending on the page they are in (some data is just cached for a few pages in one area then dropped).

Im currently thinking of re-writing some core parts of the system and i was thinking about adding another method of caching in. However im not sure if its worthwhile doing... and i know there would be alot of variable factors such as database server speed/size and how many users are online at once. However im just trying to get a general opinion of some other PHP developers on what they would think...

I think out of the 30(ish)kb stored in the session about 20kb of it will be strings and other data that is not DIRECTLY required, but will be used on alot of pages to display information about the related item.

So i was thinking that rather than wasting 20kb of text per session per person, why not just cache alot of this text based information into a text file with the serialized array, so like the session whenever someone needs that information they can check if the file exists relating to the ID stored in the session, then unserialize the data into an array and use it. That way if there are 100 users on the system the sessions will only be 10kb per person and on the pages that need this text data rather than querying the database it will just read it from the file.

Now at this point im thinking, a database is made for being used... so would it be faster to do about 30 small joined queries or open up about 10 files and unserialize the data into arrays....

Sorry if im being a bit vauge, and there may not be a concrete answer... but i thought that i would ask anyway... let me know if you have any questions or advice!

NogDog

You might want to look into query caching, still using the database but letting it cache static queries for you. If using MySQL, see http://dev.mysql.com/doc/refman/5.1-maria/en/query-cache.html.

Grofit

ah cheers for that...

Only problem is some sections of the query may be slightly different, i.e containing different IDs or different links... it will ultimatly bring back the same sorta stuff though...

As i dont think im explaining this very well i will try to think of an example of what would happen and why i want to cache it...

A game would be a perfect example...

In your game you have a character and items. When you log in with your character you are going to need details on your character and your items. Now either you do a query each time the page is loaded to retrieve all this information, or you cache it in the session on the first page load. This way when you display your inventory you would be able to just look at the cached information rather than having to query the database, however imagine that when you click on the item you are going to need to know more than just the name/image/amount of the item you have.

Now at this point you can either query the database using AJAX when the user presses on it to retrieve its description, any bonuses or requirements it may have or you could have it pre-cached within the session when you retrieve your character. However at this point you now have your session containing your core character details as well as his inventory item list, and text data relating to each item within the inventory...

This way you only cache data that you will be requiring on most pages to display and you will be minimizing the amount of DB calls through AJAX as it would be able to retrieve most of the data from the session requiring alot less joins and lookups, and ultimatly less file including on the AJAX files because you only need to know the item type thats in the session, you dont have to include any DB related classes or anything else that could slow it down.

Now this all sounds great but then there are 100 characters with the same item, and all of them are in there inventories clicking away to view more information on the items they have. If everyone clicked on one item using this caching method will save 100 queries and speed up the php execution time as its only including a minimal amount of includes in the file.

However each persons session will now be bloated as they are no longer going to be storing purely character - inventory information such as id, name, image, amount. They will also be storing lots of generic string data and other information which they will only need when they click on it.

This extra 10-20kb on the session per person will minimize LOADS of database queries, as if each person of the 100 clicked on each of their 10 unique items once thats 1000 queries that have been saved at the cost of 10-20kb on the webserver.

This is where my question would come in, would it be even better to cache the generic data that bloats the session within a text file on the server, so each time they click on the item it will still be saving the queries, however it will instead be having to do a file_exists/file_get_contents/unserialise call every time the item is clicked. Although this is taking 20kb per person off the session sizes... so given the example above this would be 2Mb saved off the session size with the 100 people.

So basically which would you guys say is the best implementation:

A) Querying the database each time the user clicks on the item creating 1000 queries over the space of a few seconds.

😎 Caching all the data that would be needed per click, so it saves 1000 queries but uses up an extra 2Mb in session variables to compensate.

C) Caching the generic data in local files, which will save 1000 queries and will save the session 2Mb, however will use up about 200kb worth of local files to store the unique items and will then do 1000 file actions to get the data.

Please bear in mind that if you scaled each of these up to 200 people will 20 items you are then going to be dealing with alot larger load, and who knows how many times people will click on these things.

Making use of that query caching in MySQL would help in this example but it would still make use of 1000 queries to the server, just means it doesnt have to do as much work on each query.

I hope that is a decent example, i know its quite lengthy but i dont think its as simple as a yes or no answer and needs the explanation to get the decent answers...

laserlight

A cache is only effective when it is frequently reused.

Now this all sounds great but then there are 100 characters with the same item, and all of them are in there inventories clicking away to view more information on the items they have. If everyone clicked on one item using this caching method will save 100 queries and speed up the php execution time as its only including a minimal amount of includes in the file.

Now, you are talking about caching on a per user basis using sessions. As such, if everyone views the same item details once, the cache only adds overhead since you have a bunch of compulsory cache misses for each player and zero cache hits. However, if a player repeatedly views the details of the same item, the cache is effective since you have a bunch of compulsory cache misses the first time the player views the details of that item, but on each subsequent view you have a bunch of cache hits.

Grofit

Yeah for the (😎 option it would be user based caching... i mean each user will have unique per person caches in the session such as their characters data...

However the stuff like items would require a small amount of per person data caching, to let people know the amount of items they have and the id. Im currently using option (😎 so adding a bit of per person overhead to the session, however as there are lots of instance throughout the site where data would have to be retrieved via AJAX i thought it would make more sense to just do the initial cache when they logged in of all this information. Then just keep it until they logged out.

My logic behind it all was that to do a DB request via AJAX i would have to include the PHP classes to allow DB interaction, then include the files that dealt with the item class then acctually drag out the id from the session and write the query. Then send it over and get the response and echo it out for the AJAX request to read and put on the page. However if i use the per person session caching it wouldnt need to include any database libraries, it would only need to include the base item class and drag the pre collected data out of the session and echo it... saving time on including DB classes and querying. However as you mention you get the overhead on the session, but as the session is just doing file operations behind the scenes i cant see it being too costly.

Unless there is a MAJOR reason why i shouldnt cache the data on login to save LOADS of queries per page load and AJAX requests after the page has loaded im going to keep this method. However the only thing im questioning now is if it would be quicker to maybe just do lots of the smaller requests or do lots of small file operations...

Im still not sure if im explaining this right, but my main concern is server overhead, database slowdown.

laserlight

However the stuff like items would require a small amount of per person data caching, to let people know the amount of items they have and the id.

It makes sense to cache the inventory listing since the player is likely to view that many times, so there will be a gain due to the cache hits.

My logic behind it all was that to do a DB request via AJAX i would have to include the PHP classes to allow DB interaction, then include the files that dealt with the item class then acctually drag out the id from the session and write the query. Then send it over and get the response and echo it out for the AJAX request to read and put on the page. However if i use the per person session caching it wouldnt need to include any database libraries, it would only need to include the base item class and drag the pre collected data out of the session and echo it... saving time on including DB classes and querying.

You may have to do all this anyway, since during the course of game play there may be actions that change the game state.

However as you mention you get the overhead on the session, but as the session is just doing file operations behind the scenes i cant see it being too costly.

If the data stored in the session is never read again, it does not matter how cheap the session handling is, it will still be overhead.

So, you have to determine the player usage patterns. If a player views the details of an item, is that player likely to view the details again during the course of the session? If so, then cache the item's details as soon as it is accessed. If not, do not cache item details at all. If players are likely to repeatedly view all the items at any particular session, then cache the details of all items (or if the number of items in play is huge, use a suitable caching scheme, e.g., cache the most frequently accessed items).

Grofit

Well one of the problems i have is that im introducing some new features... which i guess could be similar to a quest system for the example above...

Where it would need to check how much of item A the person had as well as display the items on other pages... Also this information would need to be accessed on certain events, imagine if we now implemented monsters as well... when you attack the monster you need to work out if you need quest items to drop from the creature, to do this you need to lookup the quest and then look up any items related to that quest... However thats going off at a tangent but thought i would mention it just so you can see the direction im coming from.

All the data i keep in the session at the moment is used on multiple pages, and although the text isnt used unless it is clicked on, when they would enter a shop for example to sell their things or look at a quest screen they would need to be able to gain access to this information... There are also other areas of the system which will need information on the items to calculate other various statistics, so everything needs the data in some way of other... the only part that ISNT needed on every page is the text, thats only NEEDED when they click on certain buttons...

So would you recommend keeping that text data out of the session and querying the database everytime they need the info, or cache it locally in a file that ANY user could access on click to save including any database files and querying. Or would you just stick with the session so it doesnt need to do any excess file requests or database queries and just cope with the extra few kb overhead?

I dont know how costly each kb is per session, however even if it saves a few database queries its done its job...

laserlight

I'm going to take this like an economist 🙂

On one hand, caching with a session is good because you save on query computation, and it does seem like you will have a good cache hit rate. On the other hand, with an optimised database, the data handling is simplified since you would not be accessing/modifying multiple data sources. Remember, caches do need to be updated from time to time, and you have to figure out how to efficiently handle that.

Also, consider external factors. If you are on a quad core VPS with few other users but with a hard limit of a relatively small amount of RAM, perhaps the trade-off of memory for CPU time is not so good (but the database engine will use memory too, of course). If you are offloading the database to a separate server, then CPU usage on the web server box is not a concern to begin with.

Grofit

Im not pro when it comes to DBA but i do alot of work with web based systems and databases, so ive optimised the database quite a bit. Ive done it so its bitesize tables that only join when they are needed so it really only gets the data that it NEEDS as well as making sure the datatypes and indexes match what is needed... however the text based session caches are the exception to the rule, as in my mind i just thought that reading one session array would be quicker than 1 database query (even if it is only a small one).

As far as updating the cache i have covered that... without going too in depth ive put a few layers into the system... so there is the database which obviously contains all the data... then there is another layer which basically takes a snapshot of the data needed from the database. This snapshot then populates some objects which store all the data and manage them with internal change recording...

This way the variables stored in the session and that are used page to page dont have any sort of overhead with database vars or includes, they are purely a set of classes to store and manage the snapshots. If something on the session snapshot changes this is recorded then on the next update cycle the snapshot manager will be called and will extract the single value thats changed (or multiple if there are a few changes) then run as few queries as possible to update.

Im currently running on a cheap shared server, which is why im trying to squeeze every bit of performance i can from it, in the future im planning on expanding to a self managed server, however i need to justify the cost of it. So for now im making it as optimised as possible while still keeping it as easy to expand and manage as possible, so when the time comes to expand it should be pre optimised...

I think im waffling now so i will stop...

Do you know if its more costly to...

A) Check for a 1KB file (filename based on session vars), read it in, unserialize it
😎 Connect to a DB, Create a query (making use of some session vars), getting the result

Some of it depends on server speed etc i know, but just try and think of an average setup... basically which would be faster...

Shrike

Just as an aside - this seems alot like premature optimisation to me. I would suggest writing the simplest thing that works at first - which would be to query the database each time. After that you can profile the code and identify the real bottlenecks.

NogDog

Shrike wrote:
Just as an aside - this seems alot like premature optimisation to me. I would suggest writing the simplest thing that works at first - which would be to query the database each time. After that you can profile the code and identify the real bottlenecks.

I was sort of thinking the same thing: create a class/method that gets the required data from the DB. Then if things are determined to be too slow that way and you need to implement something else, replace that class/method with a new one that provides the same interface to the client code.

Then you can do the eXtreme Programming thing of coding the simplest thing that works, and later if it's found to not work well enough, re-factor away! 🙂

Grofit

Ive already implemented the (😎 method explained earlier, however i was just trying to plan ahead... i think ive got all the info i need from this thread... so thanks for your time guys!