Announcement

Collapse
No announcement yet.

How to automatically Clear Cache

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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 9th Jul '18, 10:45am.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud customization and demonstration site.
    vBulletin 5 Documentation - Updated every Friday. Report issues here.
    vBulletin 5 API - Full / Mobile
    I am not currently available for vB Messenger Chats.

    Comment


    • #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


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

        Comment


        • #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 customization and demonstration site.
          vBulletin 5 Documentation - Updated every Friday. Report issues here.
          vBulletin 5 API - Full / Mobile
          I am not currently available for vB Messenger Chats.

          Comment


          • #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


            • #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

              TalkNewsUK - My vBulletin 5.5.4 Demo
              AdminAmmo - My Cloud Demo

              Comment


              • #8
                Originally posted by chriske View Post
                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 23rd Jul '18, 10:14am.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud customization and demonstration site.
                vBulletin 5 Documentation - Updated every Friday. Report issues here.
                vBulletin 5 API - Full / Mobile
                I am not currently available for vB Messenger Chats.

                Comment


                • #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 customization and demonstration site.
                  vBulletin 5 Documentation - Updated every Friday. Report issues here.
                  vBulletin 5 API - Full / Mobile
                  I am not currently available for vB Messenger Chats.

                  Comment


                  • #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


                    • #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 customization and demonstration site.
                      vBulletin 5 Documentation - Updated every Friday. Report issues here.
                      vBulletin 5 API - Full / Mobile
                      I am not currently available for vB Messenger Chats.

                      Comment


                      • #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...
                        X