Caching Issues

Store Builder Questions
1

First a little background. I manage a cart that has 2100 products, 375 categories, and 280 product filters. It’s running CS-Cart version 2.2.3. Here are my caching issues:


  1. When I enable sqlite as the cache_backend method, I notice the cache.db file reaches 125M and doesn’t get any bigger. The site starts to perform very poorly, and the web host mentioned that there were very high amounts of reads/writes to that file. Each apache process uses roughly between 40 and 180M of ram, and pages often take 10+ seconds to load.


  2. While using mysql as the cache_backend method, although the site speed is much faster the host again said that mysql’s binary logs were getting very big very quickly. There are currently about 8000 records in the cscart_cache table, but I’ve noticed something pretty alarming. While monitoring the database with ‘show full processlist’ I saw the following query:


<br />
DELETE FROM cscart_cache WHERE tags IN ('block_100_50', 'pfilters_7805193e2d475f64711de3e720cdb652', 'pfilters_07f40c27f057e54dd734dc2a1cf70392', 'pfilters_e5d19700dcefce274749d5da184b7b5b', 'pfilters_c9ac3723155ce2e3995e072667d802a3', 'pfilters_30ab048c5da9121853ae4cc368ccbadc', 'pfilters_9be70fe800d07bdfb0c054f2f9325365',...<br />

```<br />
<br />
... and so on.  The query was over 6M.  There were over 70,000 commas in the query meaning it was probably trying to delete every row in the cache table multiple times.  Immediately following that query, there was another huge query.  This time an insert that was also about 6M:<br />
<br />
```php
<br />
REPLACE INTO cscart_cache (`name`, `data`) VALUES ('cache_handlers', 'a:21:{s:11:\"static_data\";a:1:{s:8:\"top_menu\";b:1;}s:10:\"categories\";a:23752:{s:8:\"to...<br />

```<br />
<br />
... and so on.  So basically, every time a page is loaded there's a 6M query called to delete a ton of rows from the cscart_cache table, immediately followed by another 6M query to update the 'cache_handlers' value.  Another thing to note: the 'expiry' column in the cscart_cache table always reads 0.  This seems highly inefficient.  Is there any way to fix this?  Maybe I have a config setting wrong?
2

It's your filters that are chewing things up.

I would NOT use mysql as the cache under any circumstances. You are using the same db to cache data that you are trying to use the cache to prevent access to. I.e. you are caching within the same system you are trying to cache…



Your delete statement appears to be wanting to delete all tags so they can be rebuilt.



Note that the way the cache works is that it is read and re-written when entries change. I.e. some part of the cart may turn on/off database error reporting by using a registry entry. The cache will sense that it's changed and want to rewrite that section. Bigger sections have bigger overhead and filters, features and tags are the biggest culprits and very hard to index. So you end up doing a lot of table scans because those types use mostly free-form text as values.



In your case, you might want to try using the SHMEM method or go back to using files. But I don't think you would ever want to use mysql as the backend cache. No caching at all might also be a reasonable thing for you. Never hurts to try it. You can search for info. I think Tool has turned it off on his site with good results…

3

Thanks for the reply. I've seen your posts saying that you shouldn't use mysql to cache and while I completely agree, sqlite was totally bogging down the server so I had to do something quickly. Because it appeared to be working better with mysql, i kept it for the time being.



I was hesitant to use files just because we have so many filters and I know unix directories can get a bit slow with a huge amount of files. I was also worried that the cache might try to delete 70k files each request. But I'll try it out and see if there's any improvement.



I'm also interested in removing the cache and will look into it. I don't know why CS-Cart can't just add an option to disable the cache. Anyway, thanks again for the reply.

4

I've heard people getting really good results with SHMEM, but I've never gotten it to work for some reason.