Frequent Increase of cache table dramatically

[sandrose]

Hello,

I have problem in the server whenever I keep the cache more than couple of days without clearing it manually from admincp. Due to this problem the server ram reached to almost 90% of usage and CPU almost 20 as well as the swap file reached to almost 90% although the Ram is around 24Gb.

I have already optimized my server as per the URL: http://www.vbulletin.com/forum/forum...zation-request Please see the images for further information. Could you please help me to fix this frequent problem.

Regards,

[sandrose]

When I changed the cache and cacheevent tables to innodb, I got the following issue:
146 eximstats localhost eximstats Sleep 0 NULL
147 DELAYED localhost eximstats Delayed insert 71 Waiting for INSERT
468596 root localhost xxxx_xxxxnew Query 43630 copy to tmp table ALTER TABLE `closure` ENGINE = InnoDB
521860 root localhost xxxx_xxxxnew Query 22047 copy to tmp table ALTER TABLE `node` ENGINE = InnoDB
560397 DELAYED localhost eximstats Delayed insert 1 Waiting for INSERT
562010 DELAYED localhost eximstats Delayed insert 0 Waiting for INSERT
564401 root localhost xxxx_xxxx Query 86 Sending data SELECT /*!40001 SQL_NO_CACHE */ * FROM `node`
564838 xxxx_xxxx localhost xxxx_xxxxnew Query 15 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
564852 xxxx_xxxx localhost xxxx_xxxxnew Query 59 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565006 xxxx_xxxx localhost xxxx_xxxxnew Query 2 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565122 xxxx_xxxx localhost xxxx_xxxxnew Sleep 113 NULL
565179 xxxx_xxxx localhost xxxx_xxxxnew Query 17 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565191 xxxx_xxxx localhost xxxx_xxxxnew Query 21 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565195 xxxx_joomla3 localhost xxxx_joomla3 Query 13 Updating UPDATE `mmp_en8d6_session` SET `data` = '__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.
565220 xxxx_xxxx localhost xxxx_xxxxnew Query 1 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565221 xxxx_xxxx localhost xxxx_xxxxnew Query 1 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565228 xxxx_joomla3 localhost xxxx_joomla3 Sleep 31 NULL
565235 xxxx_xxxx localhost xxxx_xxxxnew Sleep 45 NULL
565240 xxxx_xxxx localhost xxxx_xxxxnew Query 35 update /** saveDbCache */REPLACE INTO cacheevent (cacheid, event) values ('getSearchResults_44225d3887ebae
565258 xxxx_joomla3 localhost xxxx_joomla3 Sleep 36 NULL
565261 xxxx_xxxx localhost xxxx_xxxxnew Query 1 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565277 xxxx_xxxx localhost xxxx_xxxxnew Query 29 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565279 xxxx_xxxx localhost xxxx_xxxxnew Query 30 Updating UPDATE session SET `lastactivity`=1392543216,`styleid`=0,`languageid`=6 WHERE (`sessionhash` = '23
565280 xxxx_xxxx localhost xxxx_xxxxnew Query 30 Updating UPDATE session SET `wol`='a:2:{s:6:\"action\";s:15:\"viewing_topic_x\";s:6:\"params\";a:2:{i:0;s:55:
565307 xxxx_joomla3 localhost xxxx_joomla3 Sleep 15 NULL
565309 xxxx_xxxx localhost xxxx_xxxxnew Query 14 update /** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data) VALUES (
565313 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('vB_NamedRoutes')
565318 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('vb_types.types')
565322 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('channelperms_1')
565323 xxxx_joomla3 localhost xxxx_joomla3 Query 1 Copying to tmp table SELECT COUNT FROM mmp_en8d6_content AS a LEFT JOIN mmp_en8d6_categories AS c ON c.id = a.catid LE
565324 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('vb_types.types')
565327 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('vb_types.types')
565328 xxxx_xxxx localhost xxxx_xxxxnew Query 1 statistics SELECT * FROM cache WHERE `cacheid` IN ('vB_NamedRoutes')
565329 xxxx_joomla3 localhost xxxx_joomla3 Query 1 Updating UPDATE `mmp_en8d6_session` SET `data` = '__default|a:9:{s:15:\"session.counter\";i:25;s:19:\"session
565333 xxxx_joomla localhost xxxx_joomla17 Sleep 1 NULL
565335 root localhost NULL Query 0 NULL SHOW PROCESSLIST
Is it required to change the cache table to innodb?
Could you please provide me a list of tables which need to be converted to innodb?

[Wayne Luke]

All tables should be innodb.

[sandrose]

Including search tables as well as cache and cacheevent.

Because when I change the tables cache and cacheevent the server consume all resources.

Regards

[sandrose]

??????????????????????????????????????////////////////

[Zachery]

How big is your cache table?

[sandrose]

It reach 2.5gb,
after I change the cache setting in the config file to memcached instead of "db" the server become fast and the CPU load dropped to reasonable number.

[pjclas]

Was this ever resolved because I am hitting the same problem. My cache table just seems to grow out of control until there is no hard disk space left. We are talking like 40G size cache table! Also, my vb tables are all MyISAM. I upgraded from vb4 and I didn't change any default settings so was there something I missed when installing vb5? I added some backtracing to the writeCache function in db.php, but I'm not too sure what to make of it... Any help would be appreciated!

[Zachery]

We've taken some steps to make the cache table better. However, if you have a dedicated server memcache and using sphinx will help performance greatly.

[pjclas]

We've taken some steps to make the cache table better. However, if you have a dedicated server memcache and using sphinx will help performance greatly.

That's good to hear, but I don't think the problem I'm seeing is a performance related issue. There is something causing the cache to grow indefinitely and rapidly. It grows 20=40G in one day. I do have a few third party products installed, including a shoutbox so they may be the cause. I'm just not sure how to go about isolating what is responsible for these constant cache writes and I was hoping you could give me some advice to figure it out.

[fsmex]

I have the same problem, Running 5.1.2

[Zachery]

You've disabled the guest cache?

[fsmex]

You've disabled the guest cache?

No I haven't

Is this done here?

-Minutes to Cache Content for Guests-?

Or somewhere else?

[Zachery]

That setting, set it to 0, and clear the system cache.

[fsmex]

Ok Thank you Done that, will wait for results.