How to automatically Clear Cache

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • chriske
    Senior Member
    • Oct 2008
    • 466
    • 5.6.3

    How to automatically Clear Cache

    The cache tables get very large very quick.

    How can I automatically Clear Cache. Via cronjob?
    Anyone can help me with this?
    Please vote for:
    - Lightbox for all uploads
    - Attachment permissions for unregistered users
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    Your database user has permission to delete content from the database correct? If so the cache tables should be self-limiting over time. However, once you delete the content, the system will immediately start rebuilding the content on every page load. The more active and larger your site is, the larger the cache will be.

    How big is "very large"?

    There is no script to automatically clear the cache at this time.
    Last edited by Wayne Luke; Mon 9 Jul '18, 10:45am.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • chriske
      Senior Member
      • Oct 2008
      • 466
      • 5.6.3

      #3
      I just cleared it so I will have to check tomorrow. 15 minutes after clearing cache table it is 61,3 MiB


      I have seen it become a few GB sometimes
      Please vote for:
      - Lightbox for all uploads
      - Attachment permissions for unregistered users

      Comment

      • chriske
        Senior Member
        • Oct 2008
        • 466
        • 5.6.3

        #4
        Next day the size is 780,6 MiB.
        Please vote for:
        - Lightbox for all uploads
        - Attachment permissions for unregistered users

        Comment

        • Wayne Luke
          vBulletin Technical Support Lead
          • Aug 2000
          • 73981

          #5
          And?

          I've already acknowledged that the cache will immediately start growing again once you delete it. That is its very specific purposes. It caches everything drawn on every page so that more intensive queries don't have to be consistently run. The more channels and usergroups you have, the larger your cache because it has to cache everything for each usergroup combination separately.
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment

          • chriske
            Senior Member
            • Oct 2008
            • 466
            • 5.6.3

            #6
            It is more then 1,2GB now. I will try to reduce unnecessary channels and usergroups.

            The database type of the cache table is not InnoDB but MyISAM, is that standard for VB?


            I am thinking about making a new feature request. Maybe you can tell me if it is worth it..

            What about a feature that clears old cache data automatically? Maybe provide a setting for admins to specify how long the cache data will be stored.

            Since you are more knowledgeable about this subject, do you have any thoughts about it?
            Please vote for:
            - Lightbox for all uploads
            - Attachment permissions for unregistered users

            Comment

            • Mark.B
              vBulletin Support
              • Feb 2004
              • 24286
              • 6.0.X

              #7
              It's always worth making a feature request.

              I would ask yourself if the cache being large has any impact on the site's performance.
              MARK.B
              vBulletin Support
              ------------
              My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
              My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

              Comment

              • Wayne Luke
                vBulletin Technical Support Lead
                • Aug 2000
                • 73981

                #8
                Originally posted by chriske
                The database type of the cache table is not InnoDB but MyISAM, is that standard for VB?
                All tables except the language, phrase, and userfield tables should be INNODB in vBulletin 5. Having your cache and cacheevent tables as MyISAM without using MySQL Enterprise Cluster will cause your site to fail.
                Last edited by Wayne Luke; Mon 23 Jul '18, 10:14am.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73981

                  #9
                  You can use this query to generate a list of queries to alter your tables to INNODB. Copy the output and run those queries against the database. You should backup before making any changes to the database.

                  Code:
                  SET @DATABASE_NAME = '%%YOURDATABASENAME%%';
                  
                  SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
                  FROM    information_schema.tables AS tb
                  WHERE   table_schema = @DATABASE_NAME
                  AND     `ENGINE` = 'MyISAM'
                  AND     `TABLE_TYPE` = 'BASE TABLE'
                  AND       `TABLE_NAME` NOT IN ('userfield','phrase','language')
                  ORDER BY table_name DESC;
                  Replace %%YOURDATABASENAME%% with the name of your database.
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API

                  Comment

                  • chriske
                    Senior Member
                    • Oct 2008
                    • 466
                    • 5.6.3

                    #10
                    Thanks Wayne,

                    Below are the results of the query. 163 tables are myISAM format.
                    Could it be that vb4 used this format and they were not changed when upgrading?
                    We have never changed them manualy.

                    Is your recommendation still to run the query?

                    mysql>
                    mysql> SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
                    -> FROM information_schema.tables AS tb
                    -> WHERE table_schema = @DATABASE_NAME
                    -> AND `ENGINE` = 'MyISAM'
                    -> AND `TABLE_TYPE` = 'BASE TABLE'
                    -> AND `TABLE_NAME` NOT IN ('userfield','phrase','language')
                    -> ORDER BY table_name DESC;
                    +--------------------------------------------------------+
                    | sql_statements |
                    +--------------------------------------------------------+
                    | ALTER TABLE `vbfields` ENGINE=InnoDB; |
                    | ALTER TABLE `usertitle` ENGINE=InnoDB; |
                    | ALTER TABLE `usertextfield` ENGINE=InnoDB; |
                    | ALTER TABLE `userpromotion` ENGINE=InnoDB; |
                    | ALTER TABLE `usernote` ENGINE=InnoDB; |
                    | ALTER TABLE `userlist` ENGINE=InnoDB; |
                    | ALTER TABLE `usergrouprequest` ENGINE=InnoDB; |
                    | ALTER TABLE `usergroupleader` ENGINE=InnoDB; |
                    | ALTER TABLE `usergroup` ENGINE=InnoDB; |
                    | ALTER TABLE `userchangelog` ENGINE=InnoDB; |
                    | ALTER TABLE `userban` ENGINE=InnoDB; |
                    | ALTER TABLE `useractivation` ENGINE=InnoDB; |
                    | ALTER TABLE `user` ENGINE=InnoDB; |
                    | ALTER TABLE `upgradelog` ENGINE=InnoDB; |
                    | ALTER TABLE `tournament_players_statut` ENGINE=InnoDB; |
                    | ALTER TABLE `tournament_players` ENGINE=InnoDB; |
                    | ALTER TABLE `tournaments` ENGINE=InnoDB; |
                    | ALTER TABLE `templatemerge` ENGINE=InnoDB; |
                    | ALTER TABLE `templatehistory` ENGINE=InnoDB; |
                    | ALTER TABLE `template` ENGINE=InnoDB; |
                    | ALTER TABLE `tagsearch` ENGINE=InnoDB; |
                    | ALTER TABLE `tachythreadpost` ENGINE=InnoDB; |
                    | ALTER TABLE `tachythreadcounter` ENGINE=InnoDB; |
                    | ALTER TABLE `tachyforumpost` ENGINE=InnoDB; |
                    | ALTER TABLE `tachyforumcounter` ENGINE=InnoDB; |
                    | ALTER TABLE `subscriptionpermission` ENGINE=InnoDB; |
                    | ALTER TABLE `subscriptionlog` ENGINE=InnoDB; |
                    | ALTER TABLE `subscription` ENGINE=InnoDB; |
                    | ALTER TABLE `subscribeevent` ENGINE=InnoDB; |
                    | ALTER TABLE `subscribediscussion` ENGINE=InnoDB; |
                    | ALTER TABLE `stylevardfn` ENGINE=InnoDB; |
                    | ALTER TABLE `stylevar` ENGINE=InnoDB; |
                    | ALTER TABLE `style` ENGINE=InnoDB; |
                    | ALTER TABLE `strikes` ENGINE=InnoDB; |
                    | ALTER TABLE `stats` ENGINE=InnoDB; |
                    | ALTER TABLE `spamlog` ENGINE=InnoDB; |
                    | ALTER TABLE `smilie` ENGINE=InnoDB; |
                    | ALTER TABLE `sigpic` ENGINE=InnoDB; |
                    | ALTER TABLE `sigparsed` ENGINE=InnoDB; |
                    | ALTER TABLE `settinggroup` ENGINE=InnoDB; |
                    | ALTER TABLE `setting` ENGINE=InnoDB; |
                    | ALTER TABLE `rsslog` ENGINE=InnoDB; |
                    | ALTER TABLE `rssfeed` ENGINE=InnoDB; |
                    | ALTER TABLE `reputationlevel` ENGINE=InnoDB; |
                    | ALTER TABLE `reputation` ENGINE=InnoDB; |
                    | ALTER TABLE `ranks` ENGINE=InnoDB; |
                    | ALTER TABLE `raffle_entries` ENGINE=InnoDB; |
                    | ALTER TABLE `raffle` ENGINE=InnoDB; |
                    | ALTER TABLE `profilevisitor` ENGINE=InnoDB; |
                    | ALTER TABLE `profilefieldcategory` ENGINE=InnoDB; |
                    | ALTER TABLE `profilefield` ENGINE=InnoDB; |
                    | ALTER TABLE `productdependency` ENGINE=InnoDB; |
                    | ALTER TABLE `productcode` ENGINE=InnoDB; |
                    | ALTER TABLE `product` ENGINE=InnoDB; |
                    | ALTER TABLE `prefixset` ENGINE=InnoDB; |
                    | ALTER TABLE `prefixpermission` ENGINE=InnoDB; |
                    | ALTER TABLE `prefix` ENGINE=InnoDB; |
                    | ALTER TABLE `postindex` ENGINE=InnoDB; |
                    | ALTER TABLE `postedithistory` ENGINE=InnoDB; |
                    | ALTER TABLE `pollvote` ENGINE=InnoDB; |
                    | ALTER TABLE `poll` ENGINE=InnoDB; |
                    | ALTER TABLE `picturecomment_hash` ENGINE=InnoDB; |
                    | ALTER TABLE `picturecomment` ENGINE=InnoDB; |
                    | ALTER TABLE `phrasetype` ENGINE=InnoDB; |
                    | ALTER TABLE `paymenttransaction` ENGINE=InnoDB; |
                    | ALTER TABLE `paymentinfo` ENGINE=InnoDB; |
                    | ALTER TABLE `paymentapi` ENGINE=InnoDB; |
                    | ALTER TABLE `passwordhistory` ENGINE=InnoDB; |
                    | ALTER TABLE `package` ENGINE=InnoDB; |
                    | ALTER TABLE `noticedismissed` ENGINE=InnoDB; |
                    | ALTER TABLE `noticecriteria` ENGINE=InnoDB; |
                    | ALTER TABLE `notice` ENGINE=InnoDB; |
                    | ALTER TABLE `moderatorlog` ENGINE=InnoDB; |
                    | ALTER TABLE `moderator` ENGINE=InnoDB; |
                    | ALTER TABLE `moderation` ENGINE=InnoDB; |
                    | ALTER TABLE `mailqueue` ENGINE=InnoDB; |
                    | ALTER TABLE `macro` ENGINE=InnoDB; |
                    | ALTER TABLE `legacyevent` ENGINE=InnoDB; |
                    | ALTER TABLE `itrader_comments` ENGINE=InnoDB; |
                    | ALTER TABLE `itrader` ENGINE=InnoDB; |
                    | ALTER TABLE `infractionlevel` ENGINE=InnoDB; |
                    | ALTER TABLE `infractiongroup` ENGINE=InnoDB; |
                    | ALTER TABLE `infractionban` ENGINE=InnoDB; |
                    | ALTER TABLE `infraction` ENGINE=InnoDB; |
                    | ALTER TABLE `impexerror` ENGINE=InnoDB; |
                    | ALTER TABLE `imagecategorypermission` ENGINE=InnoDB; |
                    | ALTER TABLE `imagecategory` ENGINE=InnoDB; |
                    | ALTER TABLE `icon` ENGINE=InnoDB; |
                    | ALTER TABLE `hvquestion` ENGINE=InnoDB; |
                    | ALTER TABLE `hvanswer` ENGINE=InnoDB; |
                    | ALTER TABLE `humanverify` ENGINE=InnoDB; |
                    | ALTER TABLE `holiday` ENGINE=InnoDB; |
                    | ALTER TABLE `games_settings` ENGINE=InnoDB; |
                    | ALTER TABLE `games_session` ENGINE=InnoDB; |
                    | ALTER TABLE `games_scores` ENGINE=InnoDB; |
                    | ALTER TABLE `games_savedGames` ENGINE=InnoDB; |
                    | ALTER TABLE `games_list` ENGINE=InnoDB; |
                    | ALTER TABLE `games_league` ENGINE=InnoDB; |
                    | ALTER TABLE `games_champs` ENGINE=InnoDB; |
                    | ALTER TABLE `games_cats` ENGINE=InnoDB; |
                    | ALTER TABLE `forumpermission` ENGINE=InnoDB; |
                    | ALTER TABLE `filedata` ENGINE=InnoDB; |
                    | ALTER TABLE `faq` ENGINE=InnoDB; |
                    | ALTER TABLE `externalcache` ENGINE=InnoDB; |
                    | ALTER TABLE `evbs_sstabs_childs` ENGINE=InnoDB; |
                    | ALTER TABLE `evbs_sstabs` ENGINE=InnoDB; |
                    | ALTER TABLE `editlog` ENGINE=InnoDB; |
                    | ALTER TABLE `discussion` ENGINE=InnoDB; |
                    | ALTER TABLE `deletionlog` ENGINE=InnoDB; |
                    | ALTER TABLE `datastore` ENGINE=InnoDB; |
                    | ALTER TABLE `customprofilepic` ENGINE=InnoDB; |
                    | ALTER TABLE `customprofile` ENGINE=InnoDB; |
                    | ALTER TABLE `customavatar` ENGINE=InnoDB; |
                    | ALTER TABLE `cronlog` ENGINE=InnoDB; |
                    | ALTER TABLE `cron` ENGINE=InnoDB; |
                    | ALTER TABLE `contentpriority` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_widgettype` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_widgetconfig` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_widget` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_sectionorder` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_rate` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_permissions` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_nodeinfo` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_nodeconfig` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_nodecategory` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_node` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_navigation` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_layoutwidget` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_layout` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_grid` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_category` ENGINE=InnoDB; |
                    | ALTER TABLE `cms_article` ENGINE=InnoDB; |
                    | ALTER TABLE `cannedreplies` ENGINE=InnoDB; |
                    | ALTER TABLE `calendarpermission` ENGINE=InnoDB; |
                    | ALTER TABLE `calendarmoderator` ENGINE=InnoDB; |
                    | ALTER TABLE `calendarcustomfield` ENGINE=InnoDB; |
                    | ALTER TABLE `calendar` ENGINE=InnoDB; |
                    | ALTER TABLE `cache` ENGINE=InnoDB; |
                    | ALTER TABLE `blog_attachmentviews` ENGINE=InnoDB; |
                    | ALTER TABLE `blog_attachment` ENGINE=InnoDB; |
                    | ALTER TABLE `bbcode_video` ENGINE=InnoDB; |
                    | ALTER TABLE `bbcode` ENGINE=InnoDB; |
                    | ALTER TABLE `badwords` ENGINE=InnoDB; |
                    | ALTER TABLE `avatar` ENGINE=InnoDB; |
                    | ALTER TABLE `attachmentviews` ENGINE=InnoDB; |
                    | ALTER TABLE `attachmenttype` ENGINE=InnoDB; |
                    | ALTER TABLE `attachmentpermission` ENGINE=InnoDB; |
                    | ALTER TABLE `attachmentcategoryuser` ENGINE=InnoDB; |
                    | ALTER TABLE `attachmentcategory` ENGINE=InnoDB; |
                    | ALTER TABLE `attachment` ENGINE=InnoDB; |
                    | ALTER TABLE `apilog` ENGINE=InnoDB; |
                    | ALTER TABLE `apiclient` ENGINE=InnoDB; |
                    | ALTER TABLE `announcementread` ENGINE=InnoDB; |
                    | ALTER TABLE `announcement` ENGINE=InnoDB; |
                    | ALTER TABLE `albumupdate` ENGINE=InnoDB; |
                    | ALTER TABLE `album` ENGINE=InnoDB; |
                    | ALTER TABLE `adminutil` ENGINE=InnoDB; |
                    | ALTER TABLE `adminmessage` ENGINE=InnoDB; |
                    | ALTER TABLE `adminlog` ENGINE=InnoDB; |
                    | ALTER TABLE `administrator` ENGINE=InnoDB; |
                    | ALTER TABLE `adminhelp` ENGINE=InnoDB; |
                    | ALTER TABLE `adcriteria` ENGINE=InnoDB; |
                    | ALTER TABLE `ad` ENGINE=InnoDB; |
                    +--------------------------------------------------------+
                    163 rows in set (0,20 sec)
                    Please vote for:
                    - Lightbox for all uploads
                    - Attachment permissions for unregistered users

                    Comment

                    • Wayne Luke
                      vBulletin Technical Support Lead
                      • Aug 2000
                      • 73981

                      #11
                      vBulletin would install using whatever database engine your database was configured to use. For many years this was MyISAM. Newer MySQL installations use INNODB. The recommendation is to convert the tables. Even vBulletin 4 receives performance benefits from using INNODB.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment

                      • chriske
                        Senior Member
                        • Oct 2008
                        • 466
                        • 5.6.3

                        #12
                        Our site seems to load faster since changing the tables. Hopefully this solves some stability issues to.
                        Thanks for your help VB staff!
                        Please vote for:
                        - Lightbox for all uploads
                        - Attachment permissions for unregistered users

                        Comment

                        Related Topics

                        Collapse

                        Working...