Data caching - files or a database?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Data caching - files or a database?

Post by kaisellgren »

Hi there,

I've been lately trying to solve this puzzle. Which one is faster, caching in files or in a database?

Right now I have a file based caching.

Code: Select all

$db -> exec(3600*24); // time to live aka expiration
That compared to with no caching

Code: Select all

$db -> exec(0); // false/0 = no caching
is always 20-30 times faster for same queries. So, in other words, caching into files seem to be faster than no caching. Maybe this applies to my local PC only? But after having a look at phpBB and other scripts, they also cache in files. They simple cache the SQL result set in a file.

I was now thinking, what if I cache into the database? I'm not caching SQL results in a db, but I would cache other vital information. I could create a PHP file containing lets say an array of 500 rows OR I could insert them to a database. But which one is faster to load? Loading the data from this arrayed file or from a database?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Data caching - files or a database?

Post by josh »

Depends what youre caching. Relational cache data is always going to be faster in a database, while large bodies of static ascii are going to be better in files
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Data caching - files or a database?

Post by kaisellgren »

jshpro2 wrote:Depends what youre caching. Relational cache data is always going to be faster in a database, while large bodies of static ascii are going to be better in files
I have static text, so I would store them in a file.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Data caching - files or a database?

Post by josh »

It also depends how complex your cache hit logic is, if the cache hit logic is going to be a performance bottleneck you could keep cache meta data in the database and contents in files
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Data caching - files or a database?

Post by kaisellgren »

jshpro2 wrote:It also depends how complex your cache hit logic is, if the cache hit logic is going to be a performance bottleneck you could keep cache meta data in the database and contents in files
That's a good idea to follow.

I'm just wondering if it's possible to somehow test my caching mechanism on some high loaded server so that I could see if it's useful there since if the file i/o is high maybe storing all at the SQL server might become faster, especially if any SQL queries are being cached into the memory.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Data caching - files or a database?

Post by Eran »

What you are looking for is called stress testing, google it. For your needs apache bench might give you very good benchmarks and is very simple to use - http://httpd.apache.org/docs/2.0/programs/ab.html
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Data caching - files or a database?

Post by kaisellgren »

pytrin wrote:What you are looking for is called stress testing, google it. For your needs apache bench might give you very good benchmarks and is very simple to use - http://httpd.apache.org/docs/2.0/programs/ab.html
Hey thanks a bunch for that one. Why did I not think about that earlier :P
Post Reply