3

Is it a reasonable/responsible idea to store a database result set in the user's session, as opposed to loading it fresh from the database on every page load? I am aware of the availability of caching the generated HTML code in a cache file on the server but that has too many convenience issues in my opinion.

For example, on a shopping page, the brands tab in the main nav has a drop down menu that lists all the brands that currently have products on the site. The query is optimized but it would still have to run at every page load. Instead of doing that I would like to save the result set in the user's session, thus only loading the brands once per session.

Alternatively, I could generate the HTML code for the subnav and store it in a cache file on the server. If the idea of storing the array in the session isn't beneficial to the server's performance, I might be able to see past the convenience issues.

Thanks for all the help!

Improve this question
1
  • Instead of caching the data to a session You should use memcache or some simple filesystem caching - so storing the data into a file that will be refreshed e.g. every 10 minutes. Your simple cache would then just read the serialized data from the file or refresh the file if it is too old... Huh? Commented Apr 24, 2013 at 15:54

4 Answers 4

Reset to default
3

Is there any reason you can't use a conventional cache like Memcached for this instead of trying to jam it into the session?

The problem with using the session cache in this manner is it creates clutter. Session data should be kept as lean as possible since it's loaded on every request. Dumping in large amounts of data can be a serious drag on performance.

It always seems "convenient" to store things in the session, but unless it's strictly related to the session itself, it's best avoided.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

ok, that makes sense to me and what I was worried you would say .. oh well, i better cache it then! I use PEAR::Cache
3

Yes, it's reasonable and recommended to cache data that doesn't change very often. The fastest SQL query is the one you don't run at all.

One tricky problem is deciding when to refresh the cached version of the data. There's a famous quote about this:

"There are only two hard problems in Computer Science: cache invalidation and naming things."
-- Phil Karlton

As for using the session, I agree with @tadman's answer, the session isn't necessarily the best place for this kind of data. There are other options, including a cache file as you mention, and also in-memory caching like memcached or APC.

Improve this answer

1 Comment

I've heard the amended one: "There are only two hard problems in Computer Science: cache invalidation, naming things, and off by one errors."
1

I would suggest go for Memcached in this case.

enter image description here

Instructions for setup & configuration can be found here: https://www.digitalocean.com/community/articles/how-to-install-and-use-memcache-on-ubuntu-12-04 (Please note this one is for Ubuntu but it will give you good idea)

Improve this answer

Comments

-1

Others adviced to use Memcached. It adds complexity and requires RAM (so it makes MySQL MUCH MUCH MUCH slower if they run on the same server). It's possible that it's the best solution for you, but since I don't know any solution which is "good for everyone", and since I don't know your workload, I just suggest alternatives.

Yes, MySQL query cache has many issues. But it may be good for you.

MEMORY tables could be a good solution too, if you don't have too much stuff to cache.

Storing a cache in session data is probably worse than not caching at all, because data are duplicate for every user, and cleanup occurse only when sessions expire. Don't waste your memory that way.

Improve this answer

5 Comments

Unless you can qualify your statements with benchmarks, I'm going to have to believe this is pure paranoia. Memcached with a proper TTL on your entries is not going to be a problem in most circumstances.
80%+ memory should be assigned to InnoDB's buffer (I hope I won't have to search this statement in the documentation...). The purpose is exactly avoiding to access the disk. Of course, in some circumstamces, using Memcached is better. Does it sound so strange the statement "it depends from your workload"? :)
You're presuming that there's only one server available, which is a huge assumption. Memcached works well when deployed on your application server, not interfering with MySQL at all.
But I've written "if they run on the same server". I agree that if memcached has a dedicated server memory can't be a problem.
It still has no hope of making your server run dramatically slower unless the server is misconfigured. This goes without saying. Why you're adding so much in the way of hysterics is not clear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.