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:
- 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.
- 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?