Storing setting information in Cache permanently until a change has been made?

Lord_Yggdrasill

Well I was wondering if it is possible to store the application settings and sub-settings in Cache until an admin has made a change to one or multiple of these settings. Lets say I have a few database tables storing these settings(such as system settings like whether site is turned on/off, site name/slogan and admin emails, and sub-settings for certain pages), it will be an expensive process to retrieve such information everytime a page loads. One reasonable way to make the site load faster is to store these information that each individual user/guest of the site cannot change to Cache, since the such information will be the same for every member unless admin has changed the settings in PHPMyadmin or Admin control panel.

So the question is, is this doable? And would you recommend it for softwares/applications to store this kind of information common to every individual users and can only be changed by admins?

Weedpacket

The first thing that springs to mind is [man]memcache[/man]/[man]memcached[/man].

traq

When I have data that was expensive to create, I often save it as json in a text file. You can use [man]filemtime[/man] to decide when the cached data is "too old" and recreate it.

Roger_Ramjet

Another option, assuming you are using mysql for your database, is to exploit the query cache: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
The cache needs some configuration but it is worth it when you get it right. Also, you can be selective as to which queries will be allowed to use the cache to prevent some of the problems that may arise http://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html. Basically only cache the results that do not change very often, like your settings information.

Lord_Yggdrasill

@ Weedpacket:
Thanks for your reference. I did take a look at both classes, although its kinda confusing what are the differences between Memcache and Memcached.

@ Traq:
Interesting, what types of data may be expensive to create though?

@ Roger Ramjet:
Thats another option for sure, but I wonder how I am going to achieve this with PDO.

Roger_Ramjet

From my second link: http://dev.mysql.com/doc/refman/5.1/...in-select.html

8.6.3.2. Query Cache SELECT Options

Two query cache-related options may be specified in SELECT statements:

SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.

SQL_NO_CACHE

The server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result. (Due to a limitation in the parser, a space character must precede and follow the SQL_NO_CACHE keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)

Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

Basically you include the no cache option in all your other queries.

That said, I am sure you are still building sql strings in your code. Don't. Learn how to use stored procedures. SQL belongs in the database: all you should have in any scripting language is the call to run the stored procedure, so the cache/no-cache directive will be in the stored procedure, not in the PDO processing.
Why stored procedures?
1. They can be developed and unit tested in isolation from any application code: being unit tested you know you are not going to get parse errors at runtime.
2. They are pre-compiled so the database does not have to parse the sql every time and calculate the execution plan: far more efficient and faster to execute.
3. Parametric stored procedures treat input params as data: they are safe from sql injection (unless you do dumb things like build sql query as a string and then call execute on it) and unescaped user input can not break the query.
4. They are easier to maintain: being all in one place, when you make changes to the database you do not have to search through all you code for the queries that need changing.

traq

Lord Yggdrasill;11026467 wrote:
@ Traq:
Interesting, what types of data may be expensive to create though?

collections of data where:
-- you need to pull from multiple tables
-- you need to perform multiple queries to all the info
[indent](or any other multi-step process, especially where you need to finish one step to determine what to do next)[/indent]
-- you need to do extensive data manipulation (sorting, pattern matching, etc.)
-- the data doesn't change much / you're likely to be using it repeatedly in the near future
[indent](i.e., doing a simple task over and over makes it expensive)[/indent]

Search Results are the first thing to come to mind. People are always clicking back and forth between result pages.

Depending on how you parse them, retrieving config settings could easily become expensive, especially if your DB is on another server / if you consider that you have to build them on every request.

Plenty of other stuff, I'm sure. Using JSON as a format (where possible) also has the side-effect of making data ready for consumption via any APIs (e.g., ajax front-ends) you might support.

Lord_Yggdrasill

@ Roger Ramjet:
Thanks for your detailed info, I will read more on the mysql official site. Sounds like an interesting option that has a chance to become the mainstream in not so distant future.

@ Traq:
I see, yeah these do seem to be situations in which storing database info can be costly.

Roger_Ramjet

Lord Yggdrasill;11026525 wrote:
@ Roger Ramjet:
Sounds like an interesting option that has a chance to become the mainstream in not so distant future.

Database caches have been around for 15+ years and are available in all major RDBMS (MySQL, MS SQL, ORACLE, SYBASE, DB2, etc). In a commercial environment with dedicated DBAs the servers will most likely have caching enabled anyway and the DBA will use the server logs to determine what to cache and when.

In your case the DEMAND cache option seems most suitable. Simply add SQL_CACHE to the queries that fetch your application setting and your your enum lists, by which I mean those tables that store type, status, and other lists you use to populate SELECT options and menus. When it comes to page content that you may store in the DB then memcache and the like are a better option.