cacheevent table lock wait timeout

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • OrganForum
    Senior Member
    • Feb 2013
    • 131
    • 5.7.X

    cacheevent table lock wait timeout

    I'm experiencing the following error multiple times per day:
    Code:
    /** saveDbCache */REPLACE INTO vb_cacheevent (cacheid, event) values ('getSearchResults_8694f35f08bcfadeaf4f978b93d6f52a','perms_changed'),
    ('getSearchResults_8694f35f08bcfadeaf4f978b93d6f52a','vB_ChannelStructure_chg'),
    ('getSearchResults_8694f35f08bcfadeaf4f978b93d6f52a','vB_SearchResults_chg_0'),
    ('getSearchResults_8694f35f08bcfadeaf4f978b93d6f52a','nodeChg_11641'),
    ('vBAtchmnts_309032','nodeChg_309032'),
    ('vBAtchmnts_309040','nodeChg_309040'),
    ('node_309032_lvl3data','nodeChg_309032'),
    ('node_309040_lvl3data','nodeChg_309040'),
    ('node_309032_lvl1data','nodeChg_309032'),
    ('node_309040_lvl1data','nodeChg_309040'),
    ('vBUserRep_63230','fUserContentChg_63230'),
    ('vBUserRep_63230','userChg_63230'),
    ('node_641272_lvl1data','nodeChg_641272'),
    ('node_641787_lvl1data','nodeChg_641787'),
    ('node_641578_lvl1data','nodeChg_641578'),
    ('node_641572_lvl1data','nodeChg_641572'),
    ('node_641495_lvl1data','nodeChg_641495'),
    ('node_641447_lvl1data','nodeChg_641447'),
    ('node_641609_lvl1data','nodeChg_641609'),
    ('node_641376_lvl1data','nodeChg_641376'),
    ('node_641353_lvl1data','nodeChg_641353'),
    ('node_641293_lvl1data','nodeChg_641293'),
    ('node_641288_lvl1data','nodeChg_641288'),
    ('vbPre_conversation114.98.38.11641','');
    
    MySQL Error : Lock wait timeout exceeded; try restarting transaction
    Error Number : 1205
    Request Date : Thursday, January 10th 2019 @ 07:06:14 AM
    Error Date : Thursday, January 10th 2019 @ 07:07:07 AM
    Script : https://organforum.com/forums/forum/organ-building-repair-restoration/electronic-organs/11641-music-mate-portable-suitcase-organ
    Referrer :
    IP Address : 180.76.15.152
    Username : Guest
    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
    I'm running VB5.4.5 on a VPS; PHP version 7.2.11; MySQL
    5.7.24-log

    The Forum was migrated from VB4.2.5. At the time of migration the MySQL version was 5.6.34. These types of errors began immediately after taking the Forum live. As a result, I had my ISP move the database to MySQL version
    5.7.24 for better performance. That had no affect on the frequency of the errors. Before the error occurs, the Forum response becomes very sluggish to the point of becoming unusable. Clearing the cache in AdminCP or truncating the cacheevent table restores performance and quiets the errors for a time. If no action is taken, the errors increase in frequency, eventually preventing pages from loading at all.

    I can take the queries that error out and run them remotely and they execute in fractions of a second without errors. This also restores performance.

    I realize this appears to be a server performance issue, but the fact that it has occurred on two different servers running different versions of MySQL has me scratching my head.

    innodb_lock_wait_timeout = 50. Engine is innodb. Row format is Dynamic. Memcache is not an option.
    Any ideas on how to fix this?
    VB 5.7.2
    PHP 7.4
    MySQL 8.0.28
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73976

    #2
    Please increase the innodb_lock_wait_timeout value to 100. Please restart MySQL.
    Translations provided by Google.

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

    Comment

    • OrganForum
      Senior Member
      • Feb 2013
      • 131
      • 5.7.X

      #3
      I noticed last night that the errors would occur during the time the Hourly Cleanup task was running. Yeah, I guessed that running that query is part of that cleanup, but it did suggest to me that perhaps another scheduled task was locking the table around the same time. There were a couple of tasks that got migrated from the old Forum that were no longer applicable and I removed them.

      It's been twelve hours without the error occurring and the Forum response time is much improved. I made some other changes which also could account for the improvement so I don't know for sure whether the changes in the scheduled tasks are the sole factor. If the site remains stable for another 24 hours, I'll start rolling back some of the other changes and will update this thread with the results.

      Thanks.
      VB 5.7.2
      PHP 7.4
      MySQL 8.0.28

      Comment

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

        #4
        Are all of your tables (except language, phrases, and userfield) using INNODB?
        Translations provided by Google.

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

        Comment

        • OrganForum
          Senior Member
          • Feb 2013
          • 131
          • 5.7.X

          #5
          No. The following are MyISAM:
          apiclient, apilog, customprofile, phrase, impexerror, ipdata, userfield, and language

          Forum was migrated from 4.2.5 running MySQL 5.6. I thought I saw somewhere the table engine requirements for 5.6 had additional MyISAM tables than the three you mentioned. Now that I'm running under MySQL 5.7 I'm assuming it's OK to convert those tables bolded above to INNODB.

          Also, if there are any tables in the above list that are not required for VB5.4.5, or perhaps are not part of VB proper, e.g. a table used by a VB4 plugin, let me know so that I can drop them.

          Happy to say, knock wood, still no lock timeout errors after last night's changes.
          VB 5.7.2
          PHP 7.4
          MySQL 8.0.28

          Comment

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

            #6
            impexerror and ipdata aren't used in vBulletin 5. They can be deleted.

            The only table in that list that needs to be myisam on MySQL 5.6 is phrase.
            Translations provided by Google.

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

            Comment

            • Guest

              #7
              I have exactly the same problem, even after upping innodb_lock_wait_timeout to 100. I can confirm that all tables are InnoDB with the exception of the 3 mentioned which are language, phrase & userfield which are MyISAM

              Running 5.4.5

              Comment

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

                #8
                You have to restart MySQL to clear the error after updating the variable.
                Translations provided by Google.

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

                Comment

                • Guest

                  #9
                  I did

                  Comment

                  • OrganForum
                    Senior Member
                    • Feb 2013
                    • 131
                    • 5.7.X

                    #10
                    Unfortunately, the problem with the lock timeouts is still occurring when this query
                    Code:
                    [FONT=Calibri][SIZE=3]                                                Invalid SQL:[/SIZE][/FONT]
                    [FONT="Calibri"]/** saveDbCache */REPLACE INTO vb_cacheevent (cacheid, event) values …..
                    [/FONT]

                    is running during the hourly cleanup scheduled task. Yesterday, the problem had seemed to be resolved, but in the evening hours starting around 6 PM it started occurring again regularly and multiple times, with each Hourly Cleanup. Something in that task seems to locking the cacheevent table over 100 seconds causing the timeout.

                    This is the last major issue that I have with the migration to VB 5. Apart from updating to the current RC, I don't know what other steps to take or information to provide you to resolve the issue. I think VB5 has become a worthy improvement over VB4 and what is being offered by competing platforms, and I want to stick with it and get this problem resolved.
                    VB 5.7.2
                    PHP 7.4
                    MySQL 8.0.28

                    Comment

                    • OrganForum
                      Senior Member
                      • Feb 2013
                      • 131
                      • 5.7.X

                      #11
                      Situation Update: The frequency of these errors has dropped back to one or two a day after I turned of Akismet post scanning and moved the Fire Cloud scheduled task to run after the Hourly Cleanup.

                      Unfortunately, something still appears to be locking the cacheevent table and not letting go, as the site will periodically hang until I clear the System Cache from the AdminCP. There are times, like this morning, when I can't get the AdminCP to load and it's necessary to log in to the database remotely and truncate the cacheevent table after which everything returns to normal.

                      As I've said, I don't have memcached available, but is it possible to move the cache to RAM by some other means, or turn it off entirely to get some clue as to what's going on with this cacheing issue?
                      Last edited by OrganForum; Mon 14 Jan '19, 5:18am.
                      VB 5.7.2
                      PHP 7.4
                      MySQL 8.0.28

                      Comment

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

                        #12
                        What is Fire Cloud?

                        You should never truncate the cacheevent table without truncating the cache table.

                        As I've said, I don't have memcached available, but is it possible to move the cache to RAM by some other means, or turn it off entirely to get some clue as to what's going on with this cacheing issue?
                        We use PHP memory caching for those instances where the information is needed on that page rendering. Once the script ends, this cache is lost. For long term caching, we only support Database, Memcached and APCU caching mechanisms... Of these, Memcached is the best solution for performance benefits. In reality you shouldn't receive this error at all unless your MySQL server is overpopulated. This is a possibility on Shared Hosting.
                        Translations provided by Google.

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

                        Comment


                        • OrganForum
                          OrganForum commented
                          Editing a comment
                          Sorry, I meant Firebase.

                          My server is a VPS, but MySQL is on a shared host. The site has been running quite well for the past 48 hours with just a single hiccup. I'll consider upgrading MySQL to a VPS if the problems return. Thanks for the info on the table trunc and help.

                          Does it make any difference in the table truncation order if it becomes necessary?
                          Last edited by OrganForum; Mon 14 Jan '19, 10:48am. Reason: Added question.

                        • Wayne Luke
                          Wayne Luke commented
                          Editing a comment
                          Since you're not using the Mobile Suite, you don't even need to run the Firebase scheduled task. You could disable it without adverse affect at this time.

                          I just run these queries in sequence:
                          Code:
                          truncate table cache;
                          truncate table cacheevent;
                          I don't think order matters. They will start accruing data at the very next page load of your site.
                      • OrganForum
                        Senior Member
                        • Feb 2013
                        • 131
                        • 5.7.X

                        #13
                        How large should the cache tables typically grow? Over the coarse of 20 hours the number of rows in the cacheevent table has grown from 0 to 856,863 rows. I do see it going up and down, but the trend is continuously upward. Unsurprisingly, when the table reaches this size, I begin seeing the lockout errors and once these errors occur they cascade because the tables aren't being cleaned up properly on a cache refresh.

                        I've taken to clearing the cache on a cron job once a day, and that has pretty much eliminated the errors, but I'm curious if a cache table with over 3/4 of million rows is to be expected.
                        VB 5.7.2
                        PHP 7.4
                        MySQL 8.0.28

                        Comment

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

                          #14
                          There is no upper limit on the cache table sizes. Is your server storing each INNODB table in its own files?

                          If possible, upgrading your MySQL to 5.7 or higher will bring you significant performance boosts.
                          Translations provided by Google.

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

                          Comment

                          • OrganForum
                            Senior Member
                            • Feb 2013
                            • 131
                            • 5.7.X

                            #15
                            As stated in my initial post above, I'm running MySQL 5.7.24 and I agree that there was a noticeable difference in performance over 5.6 whose performance running VB5 was inadequate to the point of being unusable. I'm mostly pleased with the performance under 5.7 but I'm trying to understand whether the growth in table size, and the resulting lock timeouts, that I'm seeing is to be expected. Row format is Dynamic which I believe requires file-per-table INNODB configuration.
                            VB 5.7.2
                            PHP 7.4
                            MySQL 8.0.28

                            Comment

                            Related Topics

                            Collapse

                            Working...