For our recent project, we're experimenting with the performance and advantages/disadvantages of different cache backends... again. We're running Magento on multiple servers and so there's always the additional issue if every node should have its own caches, or if they should share a common one (or if they have a local 1st-level cache and a shared 2nd-level cache, or ...).
2-Level-Caching is not bugfree (I've posted that before and filed a patch to Magento's contributors repository before).
Today I've struggled with the database cache backend. And that's a big mess:
Magento does not care about cache entries whose lifetime has expired. This is not specific to the database cache backend, but valid for all types of caches leading to exploding database tables or directory sizes. I've created a module that takes care of that and will run Zend Framework's "clear old cache" method regularly in a scheduler task.
Problem is, that the database cache backend's clean old entries method has invalid sql statements. (Showing me, that noone has ever tested that before). I've fixed that some time ago (by adding some "?" to the correct position) and committed that to the contributor's repository. That patch was accepted and found its way into the current Community Edition 220.127.116.11, but it has not arrived to the Enterprise Version 1.11. Not even to the version 18.104.22.168 that was released a couple of days ago.
This leads me to the question: What other (important) patches have not been added to the corresponding Enterprise Edition, and why?
So I patched the sources and ran into another problem:
If you won't configure the full_page_cache in the local.xml file seperatly (all option as in <cache> are available) the full_page_cache will be using Magento's default: the file cache backend. And it seem's to overwrite the previosly correctly create database connections with new ones using the file backend. So never forget to also configure the full_page_cache section in your local.xml - even if you're not using fpc at all!
The database cache backend supports tagging. In theory. Currently the whole cache backend will throw errors because of a bug with a foreign key constraint in the table core_cache_tag to the table core_cache.
This foreign key contraint was introduced in app/code/core/Mage/Core/sql/core_setup/mysql4-upgrade-0.8.21-0.8.22.php some time ago and had the name FK_CORE_CACHE_TAG. It was supposed to take care of clearing attached tags when a cache entry was deleted. That's why there is no php code taking care of deleting the records in the core_cache_tag table.
Some time later someone has noticed that this foreign key contraint did not work as expected and removed that constraint using the update script app/code/core/Mage/Core/sql/core_setup/mysql4-upgrade-0.8.26-0.8.27.php.
Fair enough, at least the database backend started working again. But there was still no code taking care of emptying the tags leading into increasing table sizes.
I've created a patch that handles tag record deletion in php. This fixed the problem of tags and allowed to use the database cache backend in production again.
But then - with version 22.214.171.124 (valid for the community and the enterprise edition) the foreign key contraint was added again in the update script app/code/core/Mage/Core/sql/core_setup/install-126.96.36.199.php. In the meantime the installer method "getFkName()" was added so the new foreign key got a new name: FK_CORE_CACHE_TAG_CACHE_ID_CORE_CACHE_ID instead of FK_CORE_CACHE_TAG. Check your databases and you'll find this key in the core_cache_tag table. That of course is not deleted by the update script mysql4-upgrade-0.8.26-0.8.27.php and brings up the same errors they had before removing the key.
You could follow until here? Great! If you really want to use the database cache backend...
alter table core_cache_tag drop FOREIGN KEY FK_CORE_CACHE_TAG;or put this in a update script:
alter table core_cache_tag drop FOREIGN KEY FK_CORE_CACHE_TAG_CACHE_ID_CORE_CACHE_ID;
$installer->getConnection()->dropForeignKey($installer->getTable('core/cache_tag'), $installer->getFkName('core/cache_tag', 'cache_id', 'core/cache', 'id'));
Other foreign key names have also changed in the latest version of Magento, because they are being created using the getFkName() method that returns names using a different convention than before. This might break other parts aswell and will definitly break something if keys had been removed or modified before (or in your modules). We should keep an eye on that and alwys remove foreign keys using both the old and the new name to make sure they're really dropped.