Data base cache errors slow forum and high use of database resoucres

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ienajah.com
    Member
    • May 2018
    • 92
    • 5.7.5

    [Bug / Issue] Data base cache errors slow forum and high use of database resoucres

    Dears,

    please note that the errors issue raised up after upgrade to 5.4.5 , there was no issue with 5.4.2

    After upgrade to 5.4.5 I start receiving database errors below some, errors keep coming from time to time

    I noticed that the cache table size keeps increasing, and when I flush the cache from admincp the browsing improves but still not to as it was before.

    I noticed that the Searchlog table size keeps increasing more that 2 GB, I checked the scheduled tasks from adminCP seems to be not working, the table size decreases once I made the daily clearing and hourly cleaning tasks manually, however after optimizing the data base the scheduled tasks return to function and the searchlog table went back to its original task

    I got a recommendation to restart mysql server, I managed to do that hardly with the host since I'm running on shared hosting


    after that the forum became too slow and until now it is

    then I followed the recommendation and I converted the Data base tables to INNODB, keeping only 3 tables (language, Phrase, userfield) as MyISAM

    now the speed went back to normal until the cache table size became big also the errors keep coming and cache file keep increasing until it is cleared from Admincp

    and some new errors came up below

    PHP Code:
    Database error in vBulletin 5.4.5:

                            
    Invalid SQL:
    INSERT  INTO userban (`userid`,`usergroupid`,`displaygroupid`,`customtitle`,`usertitle`,`adminid`,`bandate`,`liftdate`,`reason`)
                            
    VALUES('32381','2','2','0','','1',1543499402,0,'اعلانات')
    /**userban**/;

                            
    MySQL Error   Duplicate entry '32381' for key 'PRIMARY'
                            
    Error Number  1062
                            Request Date  
    ThursdayNovember 29th 2018 08:50:02 AM
                            Error Date    
    ThursdayNovember 29th 2018 08:50:08 AM
                            Script        
    https://www.ienajah.com/vb/ajax/api/node/deleteNodesAsSpam
                            
    Referrer      https://www.ienajah.com/vb/privatemessage/pendingposts/35156/1
                            
    IP Address    86.97.252.8
                            Username      
    HaMooooDi
                            Classname     
    vB_Database_MySQLi
                            MySQL Version 
    10.2.18-MariaDB-cll-lve


    Stack Trace
    :
    #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1203
    #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 201
    #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 557
    #3 vB_Database->query_write() called in [path]/vb/db/query/insert.php on line 115
    #4 vB_dB_Query_Insert->doInserts() called in [path]/vb/db/query/insert.php on line 98
    #5 vB_dB_Query_Insert->execSQL() called in [path]/vb/db/assertor.php on line 301
    #6 vB_dB_Assertor->assertQuery() called in [path]/vb/db/assertor.php on line 453
    #7 vB_dB_Assertor->insert() called in [path]/vb/api/user.php on line 6624
    #8 vB_Api_User->banUsers() called in [path]/vb/api/node.php on line 486
    #9 vB_Api_Node->deleteNodesAsSpam() called in [path]/vb/api/wrapper.php on line 199
    #10 vB_Api_Wrapper->__call() called in [path]/vb/api/wrapper.php on line 164
    #11 vB_Api_Wrapper->callNamed() called in /home/XXXXX/public_html/vb/includes/api/interface/collapsed.php on line 97
    #12 Api_Interface_Collapsed->callApi() called in /home/XXXXX/public_html/vb/includes/vb5/frontend/applicationlight.php on line 327
    #13 vB5_Frontend_ApplicationLight->handleAjaxApi() called in /home/XXXXX/public_html/vb/includes/vb5/frontend/applicationlight.php on line 186
    #14 vB5_Frontend_ApplicationLight->execute() called in /home/XXXXX/public_html/vb/index.php on line 41 
    Host information

    Server version: 10.2.18-MariaDB-cll-lve - MariaDB Server
    Database client version: libmysql - 5.1.73
    PHP version is 7.2
    Database Server connection collation is Utfmb4_unicode_ci
    data base tables connection in utf8_general_ci
    link for database screenshot :
    link : https://www.ienajah.com/up/do.php?id=701
    link for the reply I did on support form topic :
    link : https://www.vbulletin.com/forum/forum/vbulletin-sales-and-feedback/licensed-customer-feedback/4400636-discuss-the-vbulletin-5-4-5-release-here?p=4402865#post4402865


    the errors for cache

    PHP Code:
    Database error in vBulletin 5.4.5:

                            
    Invalid SQL:
    /** saveDbCache */REPLACE INTO cacheevent (cacheideventvalues
     
    ('getSearchResults_df288b57275d986da411e54acec3d3f7','perms_changed'),
    (
    'getSearchResults_df288b57275d986da411e54acec3d3f7','vB_ChannelStructure_chg'),
    (
    'getSearchResults_df288b57275d986da411e54acec3d3f7','vB_SearchResults_chg_0'),
    (
    'getSearchResults_df288b57275d986da411e54acec3d3f7','nodeChg_24425'),
    (
    'getSearchResults_1e98588142df14146f20cc511aab97b5','perms_changed'),
    (
    'getSearchResults_1e98588142df14146f20cc511aab97b5','vB_ChannelStructure_chg'),
    (
    'getSearchResults_1e98588142df14146f20cc511aab97b5','vB_SearchResults_chg_0'),
    (
    'getSearchResults_1e98588142df14146f20cc511aab97b5','nodeChg_24425'),
    (
    'node_218368_lvl1data','nodeChg_218368'),
    (
    'node_218313_lvl1data','nodeChg_218313'),
    (
    'node_218316_lvl1data','nodeChg_218316'),
    (
    'node_218310_lvl1data','nodeChg_218310'),
    (
    'node_218301_lvl1data','nodeChg_218301'),
    (
    'node_218300_lvl1data','nodeChg_218300'),
    (
    'node_218298_lvl1data','nodeChg_218298'),
    (
    'node_218271_lvl1data','nodeChg_218271'),
    (
    'vbnodetext24425_21_0:963474f4a512e7c80c23b609c6e6cefc','nodeChg_24425'),
    (
    'vbnodetext24425_21_0:963474f4a512e7c80c23b609c6e6cefc','nodeChg_62'),
    (
    'vbnodetext24425_21_0:963474f4a512e7c80c23b609c6e6cefc','vB_Language_languageCache'),
    (
    'vbnodetext_pre_24425_21_0:963474f4a512e7c80c23b609c6e6cefc','nodeChg_24425'),
    (
    'vbnodetext_pre_24425_21_0:963474f4a512e7c80c23b609c6e6cefc','nodeChg_62'),
    (
    'vbnodetext_pre_24425_21_0:963474f4a512e7c80c23b609c6e6cefc','vB_Language_languageCache'),
    (
    'vBPage_cd1e2c469d83c121b77dfb456fafb9c6_4','vbCachedFullPage'),
    (
    'vbPre_conversation161.144.62.24425','');

                            
    MySQL Error   Lock wait timeout exceeded; try restarting transaction
                            Error Number  
    1205
                            Request Date  
    ThursdayNovember 29th 2018 08:47:54 AM
                            Error Date    
    ThursdayNovember 29th 2018 08:48:49 AM
                            Script        
    https://www.ienajah.com/vb/node/24425?p=24425
                            
    Referrer      :
                            
    IP Address    46.229.168.130
                            Username      
    : ضيف
                            
    Classname     vB_Database_MySQLi
                            MySQL Version 
    :


    Stack Trace:
    #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1203
    #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 201
    #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 557
    #3 vB_Database->query_write() called in [path]/vb/db/mysql/querydefs.php on line 7328
    #4 vB_dB_MYSQL_QueryDefs->saveDbCache() called in [path]/vb/db/query/method.php on line 175
    #5 vB_dB_Query_Method->execSQL() called in [path]/vb/db/assertor.php on line 301
    #6 vB_dB_Assertor->assertQuery() called in [path]/vb/cache/db.php on line 390
    #7 vB_Cache_Db->shutdown() called in [path]/vb/shutdown.php on line 93
    #8 vB_Shutdown->shutdown() called in [path]/vb/vb.php on line 486
    #9 vB::shutdown() called in  on line 
    PHP Code:
    Database error in vBulletin 5.4.5:

                            
    Invalid SQL:
    /** saveDbCache */REPLACE INTO cacheevent (cacheideventvalues
     
    ('c7fd45a683d42446f64446e83d05d381','');

                            
    MySQL Error   Lock wait timeout exceeded; try restarting transaction
                            Error Number  
    1205
                            Request Date  
    ThursdayNovember 29th 2018 03:36:14 AM
                            Error Date    
    ThursdayNovember 29th 2018 03:37:07 AM
                            Script        
    https://www.ienajah.com/vb/search?r=9673603
                            
    Referrer      https://www.ienajah.com/vb/node/26
                            
    IP Address    41.44.243.40
                            Username      
    : ضيف
                            
    Classname     vB_Database_MySQLi
                            MySQL Version 
    :


    Stack Trace:
    #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1203
    #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 201
    #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 557
    #3 vB_Database->query_write() called in [path]/vb/db/mysql/querydefs.php on line 7328
    #4 vB_dB_MYSQL_QueryDefs->saveDbCache() called in [path]/vb/db/query/method.php on line 175
    #5 vB_dB_Query_Method->execSQL() called in [path]/vb/db/assertor.php on line 301
    #6 vB_dB_Assertor->assertQuery() called in [path]/vb/cache/db.php on line 390
    #7 vB_Cache_Db->shutdown() called in [path]/vb/shutdown.php on line 93
    #8 vB_Shutdown->shutdown() called in [path]/vb/vb.php on line 486
    #9 vB::shutdown() called in  on line 
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    The first issue is a duplicate entry error. Does the banid have an auto-increment value on it?

    For the others, you need to restart MYSQL and then truncate the contents of the cache and cacheevent tables.
    Translations provided by Google.

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

    Comment

    • ienajah.com
      Member
      • May 2018
      • 92
      • 5.7.5

      #3
      Mysql server restarted and tables truncated as requested , the isse disappeared for few days ... then again cameback aggressively every day 5 or 6 errors ... data base size reached 6 GB couse of cache, cachevent, search log tables and i had to truncate them manualy to get the website speed and stop errors reciving

      Comment

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

        #4
        Are you using INNODB tables on your database?
        Translations provided by Google.

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

        Comment

        • ienajah.com
          Member
          • May 2018
          • 92
          • 5.7.5

          #5
          Yes except the tables mentioned above
          language, Phrase, userfiel

          Comment

          • ienajah.com
            Member
            • May 2018
            • 92
            • 5.7.5

            #6
            In 12 hours from trancating the cache table the data bace size reached 14 GB

            see attached large tables size
            Attached Files

            Comment

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

              #7
              The size of the database is irrelevant here. The size of a table shouldn't return the error your seeing. What version of MySQL are you using? You should be using MySQL 5.7.X or 8.0 (better).

              You could also have Memcached installed and configured for your site. This would remove some of the database load.
              Translations provided by Google.

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

              Comment

              • ienajah.com
                Member
                • May 2018
                • 92
                • 5.7.5

                #8
                @Wayne Luke

                I noticed once the size became big the errors start to come, when I truncated the tables the errors stopped until the size became big, for today only until now I received 20 errors

                below versions used
                innodb_version 5.7.23
                protocol_version 10
                version 10.2.18-MariaDB-cll-lve
                version_comment MariaDB Server
                version_compile_machine x86_64
                version_compile_os Linux
                version_malloc_library system
                I enabled Memcached on the site waiting for result
                Last edited by ienajah.com; Fri 7 Dec '18, 12:41pm.

                Comment

                • ienajah.com
                  Member
                  • May 2018
                  • 92
                  • 5.7.5

                  #9
                  Still im reciving many errors, it seems enabling memchashed didn’t stop it

                  Comment

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

                    #10
                    What are your cache classes set to in the /core/includes/config.php file? Where are you storing your datastore cache? It is also defined in that file.
                    Translations provided by Google.

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

                    Comment

                    • ienajah.com
                      Member
                      • May 2018
                      • 92
                      • 5.7.5

                      #11
                      I believe it is still in defult since i didnt change it

                      Please see attached screen shots for the config file showing the cache configeration

                      Comment

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

                        #12
                        Then you're not using Memcached. On your server. You need to configure the config.php to be able to access you server and then tell the system to use it. The instructions are in the comments of the config.php file.
                        Translations provided by Google.

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

                        Comment

                        • ienajah.com
                          Member
                          • May 2018
                          • 92
                          • 5.7.5

                          #13
                          Yes i enabled memchace for the cpanel and i forget to configure the system, now i did it and waiting for results,

                          in addition i discovered that I have one product displaying copywrites in threads, i disable it and since then no errors recived for cache !!!

                          Comment

                          Related Topics

                          Collapse

                          Working...