Big cache table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Dzi13
    New Member
    • Oct 2009
    • 28
    • 3.8.x

    Big cache table

    Hi,

    I noticed that one month after upgrading to vb5 my cache table in vbulletin database is 4.7GB big.
    I didn't make any changes in options. Doeas vb5 stores attachments to that table? Why is it so big?
    Download Windows Software
  • Dzi13
    New Member
    • Oct 2009
    • 28
    • 3.8.x

    #2
    Just to say - i tried with clear system cache option in Admin panel but it blocked my server. I had to restart mysql
    Download Windows Software

    Comment

    • Zachery
      Former vBulletin Support
      • Jul 2002
      • 59097

      #3
      The clear system cache doesn't use a delete from all, or truncate, so its not as quick as it should be.

      Comment

      • Dzi13
        New Member
        • Oct 2009
        • 28
        • 3.8.x

        #4
        I have truncated it from phpmyadmin. After that, in less than 12 hours table was 1GB big
        Download Windows Software

        Comment

        • softjury
          New Member
          • May 2008
          • 3

          #5
          This is still a problem with vBulletin 5.1.6

          With the default cache classes in config.php the DB tables 'cache' and 'cacheevent' grow bigger and bigger.

          Even though expired cache items are deleted, nobody does an OPTIMIZE TABLE so the innodb files on disk is kept big and is getting bigger as new entries are added.

          Running OPTIMIZE TABLE 'cache' and OPTIMIZE TABLE 'cacheevent' is no workaround as these commands will lock the tables as long as the OPTIMIZE statements are running.

          Disabling the cache in AdminCP Settings doesn't work, too - see http://tracker.vbulletin.com/browse/VBV-1399

          Comment

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

            #6
            Optimize doesn't do anything to actually optimize a table's performance. What it does is delete all information in the table that is marked as deleted. This allows the storage space to be reused. It performs no other function. As you've discovered, this can make things worse.

            When you have performance issues due to an abnormally large cache and cacheevents table, you need to empty it using the controls in the AdminCP or via truncate queries. The queries are generally faster and won't trigger any usage alarms in most cases.

            Code:
            truncate table cache;
            truncate table cacheevents;
            If you use table prefixes in your config.php file, you will need to use them on these queries as well.
            Translations provided by Google.

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

            Comment

            • Sylv1
              Senior Member
              • Jul 2013
              • 125

              #7
              in addition to size problem, the size of cache table is directly involved in queries that are bigger than 1Mb, whereas this is a limitation on most of hosters :
              - many errors in admincp
              - many errors in site builder
              - error in pm for users with lot of subscriptions
              - error in creation, deletion, update of topic



              I added a cron table to truncate those tables daily to workaround.

              Comment

              • chriske
                Senior Member
                • Oct 2008
                • 466
                • 5.6.3

                #8
                Can someone share how to make a cronjob to clear system cache?
                Please vote for:
                - Lightbox for all uploads
                - Attachment permissions for unregistered users

                Comment

                • lottidah
                  Senior Member
                  • Dec 2004
                  • 214

                  #9
                  I'd like that as well.

                  Comment

                  • Sylv1
                    Senior Member
                    • Jul 2013
                    • 125

                    #10
                    hello

                    i use a scheduled php file containing :

                    <?php
                    // on se connecte à MySQL
                    $db = mysql_connect('server', 'user', 'password');

                    // on sélectionne la base
                    mysql_select_db('database_name',$db);

                    // on crée la requête SQL
                    $sql = 'TRUNCATE table vb_cache';

                    // on envoie la requête
                    $req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

                    // on crée la requête SQL
                    $sql = 'TRUNCATE table vb_cacheevent';

                    // on envoie la requête
                    $req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());


                    // on ferme la connexion à mysql
                    mysql_close();
                    ?>

                    You have of course to adapt blue variables.

                    i tested this php file as included in code of vbulletin and scheduled by vbulletin but because of inclusion of clear login/password... i moved in private directory of server for security reasons.


                    at same level than www directory on my server, i have thus a admin directory with scripts including this one.

                    it is scheduled through control panel of my hoster.


                    it works well since years.... and let my db size stay reasonable.

                    hope that helps

                    Comment

                    Related Topics

                    Collapse

                    Working...