Announcement

Collapse
No announcement yet.

How to automatically Clear Cache

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

  • chriske
    replied
    Our site seems to load faster since changing the tables. Hopefully this solves some stability issues to.
    Thanks for your help VB staff!

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • chriske
    replied
    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)

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • Mark.B
    replied
    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.

    Leave a comment:


  • chriske
    replied
    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?

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • chriske
    replied
    Next day the size is 780,6 MiB.

    Leave a comment:


  • chriske
    replied
    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

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • chriske
    started a topic How to automatically Clear Cache

    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?

Related Topics

Collapse

Working...
X