Storing frequently accessed data in a file rather than MySQL

Question

I'm working on a PHP content management system and, in testing, have noticed that quite a few of the system's MySQL tables are queried on almost every page but are very rarely written to. What I'm wondering is will this start to weigh heavily on the database as site traffic increases, and how can I solve/prevent this?

My initial thoughts were to start storing some of the more static data in files (using PHP serialization) but does this actually reduce server load? What I'm worried about is that I'd be simply transferring the high load from the database to the file system!

If somebody could clue me in on the better approach, that would be great. In case the volume of data itself has a large effect, I've detailed some of the data I'll be storing below:

• Full list of Countries (including ISO country codes)
• Site options (skin, admin email, support URLs etc.)
• Usergroups (including permissions)

Answer 1

Databases are much better at handling large data volumes than the native file system.

Don't worry about optimizing your site to reduce server load, until you actually have a server load problem. :-)

Answer 2

If your database is properly indexed, it will be much faster to query data from the database. If you want to speed that up, look into memcached or similar.

Answer 3

The tables you mentioned (countries and users) will normally be cached in memory by MySQL directly unless you are expecting quite a few millions of records in these tables.

In case where these tables will not fit in memory, you may want to consider a general-purpose distributed memory caching system, such as memcached.

Answer 4

1. Optimize your queries (using mysql slow query log) and EXPLAIN function.

2. If tables are really rarely written to you can use native MySQL caching. You have nothing to change in you code, just enable mysql caching in my.conf.

3. Try out using template engine like Smarty (smarty.net). It has it's own caching system that works pretty well and will REALLY reduce server load.

4. You can also use Memcache, but it is really worth using only with really high load websites. (I think that Smarty will be enough.)

Answer 5

You have to remember that reading a table from a database on a powerful server and on a fast connection is likely to be faster than reading it from disk on your local machine. The database will cache the entirety of these small, regularly accessed tables in memory.

By implementing the same functionality yourself in the file system, there is only a small possible speed up, but a huge chance to mess it up and make it slower.

It's probably best to stick with using the database.

Answer 6

Databases are exactly for this purpose.. To store and provide data. Filesystem is for scripts and programming.

If you encounter load problems, consider using Memcached or another utility for database.

You may also consider trying to cache different parts of your page directly into database as whole sections (eg. a sidebar, that doesn't change too much, generated header section, ..)

Answer 7

you could cache output (flush(), ob_flush() etc.) to a file and include that instead of having multiple MySQL reads. caching is definitely faster than accessing MySQL multiple time.

reading a static file is much faster than adding overhead via php and mysql processing.

Answer 8

You need to evaluate the performance via load testing to avoid prematurely optimising.

It would be foolish and quite possibly increase overall load to store data in files with serialization, databases are really good at retrieving data.

If after analysis there is a true performance hit (which I doubt unless you are talking about massive loading), then caching is a better solution.

It's more important to have a well designed system that facilitates changes as needs arise.

Answer 9

Here's a link to a couple script that will essentially do what dusoft is talking about and cache the output buffer to a file:

http://www.addedbytes.com/articles/caching-output-in-php/

Used this way, it's more of a bolt-on-after-the-fact type of solution, but this same behavior can certainly be implemented in a more integrated fashion if considered earlier in the process. Many frameworks also have this kind of thing built in.