I’ve been searching around for some mechanisms to perform database caching within a PHP/MySQL solution I am building.
There are a number of solutions around (in addition to the official one).
Most seem to store the cache as an XML file, but that seems to be a really inefficient way of doing things (you have to write the XML and then parse it back in each time — almost as lengthy has making the original SQL request instead).
But then I managed to find class_db.php.
Its light – just a single class.php file to include, and it fits into your code effortlessly. It also (seems to) store your data in a format similar that of a raw SQL dump, making it really quick to parse out into an array.
The sum total of the integration of the cache into my php script was as follows:
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
...
}
became:
$d = new db(0);//define the db the cache should use.
$d->dir = "/path/to/dbcache/"; //cache location.
$rows = $d->fetch($sql, 1800);//1800 = 5 mins (in secs) for caching
foreach ($rows as $row){
...
}
For more information check out class_db.php by Troy Wolf.
To clarify, the dump is a while loop which stores the information into an array and then dumped to a file using serialize.
[CODE]
while ($row[]=$db->fetchRow()){}
$fp=fopen(“file”,”w”);
fputs($fp,serialize($row));
fclose($fp);
[/CODE]
It’s a pretty decent idea – i’ve been experimenting with in-memory databases (mysql) where the entire mysql installation is put in Ram Disk – its VERY fast.
How would you save back the tables from RAM? I mean, if you have all the data in the ram and something crashes, you lose all the change you’ve made?!
Not true, it depends what you are doing but you can ensure that a standard db exists and mysql does logging which can then be added back into the standard database.
We currently log all changes and these are carried out on a different mysql install so our data is kept up to date. There are potential problems but for my purposes I use the ramdisk option for a search system and i rebuild the index once per day.
another popular caching method in higher-end solutions is memcached – http://www.danga.com/memcached/
Memcached is great, and of course Zend is also cool (although that caches functions rather than DB calls).
My app (for the moment) will be running in a shared hosting environment, so it’s not of the scale to require memcache.
Memcache is also really useful to use on servers running MediaWiki. If your wiki gets popular it can soon consume a lot of system resource.
I’ve been tweaking my MySQL v4.0.x settings recently and pondering this same concept — should I create a function to replace mysql_query that handles query caching?
I thought about this … perhaps it could scan the query first determine if it is a SELECT statement … if so, run the query and store the results with a timestamp… then if it was an insert query, check the tables affected and clear all records of cached info for those tables.
Sounds pretty good… and I could choose to save my cached results in either another table or a text file.
But here’s where I threw the breaks on — MySQL v4.0.x+ actually performs query caching and can be configured.
After logging into MySQL from my Linux Shell:
set global query_cache_size=50000000;
mysql> show status like ‘qc%’;
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 49987440 |
| Qcache_hits | 4356552 |
| Qcache_inserts | 837145 |
| Qcache_lowmem_prunes | 379085 |
| Qcache_not_cached | 115510 |
| Qcache_queries_in_cache | 3 |
| Qcache_total_blocks | 8 |
+————————-+———-+
8 rows in set (0.00 sec)
See:
http://www.databasejournal.com/features/mysql/article.php/3110171
As a second note:
http://www.danga.com/memcached/
Has anyone here tested the performance of this caching function versus the inbuilt MySQL query caching of MySQL 4.0+ ? I would guess that you would only really start to see a major performance increase if you were using a seperate database server to your application server, as it is effective shifting resource from one to the other. I think the effects would be most beneficial if you were using multiple application servers. The database server(s) resource could then be mostly reserved for INSERTS and UPDATES and maintaining the central authoritative db.
Hi,
I am trying to a save a simple multi-dimensional array in a mysql database in a single field.
I have used the serialize(), but the output I am getting in the database field is “N;”.
Can you please let me know, where the error is?
Thanks.