Announcement

Collapse
No announcement yet.

Timeouts on Cache Inserts

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

  • [Bug / Issue] Timeouts on Cache Inserts

    I continue to have database errors on my site. Virtually all these errors involve cache operations, the most common being flagged as occurring in /** saveDbCache. Timeouts are the most common reason given.

    Attached is a typical failed query. It's over 250,000 characters (not bytes) in length. Here is the error portion of the error message

    Code:
    MySQL Error : Lock wait timeout exceeded; try restarting transaction
    Error Number : 1205
    Request Date : Wednesday, November 9th 2022 @ 02:08:16 AM
    Error Date : Wednesday, November 9th 2022 @ 02:12:32 AM
    Script : https://organforum.com/forums/forum/electronic-organs-midi/classic-church-electronic-organs/6249-rodgers-900b-information
    Referrer : https://www.bing.com/
    IP Address : 173.64.81.124
    Username : Guest
    Classname : vB_Database_MySQLi
    MySQL Version :
    
    
    Stack Trace:
    #0 vB_Database->getErrorData() called in phar://[path]/vb/vb.phar/database.php on line 1203
    [NODE="1"]Home[/NODE] vB_Database->halt() called in phar://[path]/vb/vb.phar/database/mysqli.php on line 236
    [NODE="2"]Forum[/NODE] vB_Database_MySQLi->execute_query() called in phar://[path]/vb/vb.phar/database.php on line 587
    #3 vB_Database->query_write() called in phar://[path]/vb/vb.phar/db/mysql/querydefs.php on line 7454
    [NODE="4"]Blogs[/NODE] vB_dB_MYSQL_QueryDefs->saveDbCache() called in phar://[path]/vb/vb.phar/db/query/method.php on line 67
    [NODE="5"]Groups[/NODE] vB_dB_Query_Method->execSQL() called in phar://[path]/vb/vb.phar/db/assertor.php on line 245
    [NODE="6"]Uncategorized Groups[/NODE] vB_dB_Assertor->assertQuery() called in phar://[path]/vb/vb.phar/cache/db.php on line 379
    [NODE="7"]Special[/NODE] vB_Cache_Db->shutdown() called in phar://[path]/vb/vb.phar/shutdown.php on line 92
    [NODE="8"]Visitor Messages[/NODE] vB_Shutdown->shutdown() called in [path]/vb/vb.php on line 471
    [NODE="9"]Private Messages[/NODE] vB::shutdown() called in on line
    Questions
    1. Is the size of this query in an expected normal range?
    2. If the transaction fails, is it retried? How many times? What are the consequences if transaction is never completed?
    3. Does vB:shutdown() signal the MySQL server to shutdown?
    Thanks.
    Attached Files
    VB 5.7.1
    PHP 7.4
    MySQL 8.0.28

  • #2
    Are you actually using MySQL or MariaDB? In my experience, this error only occurs on MariaDB.

    Though this is the most information, we've received on this error.
    1. The insert is actually a series of queries built into a transaction. The maximum packet size on most installations of MySQL/MariaDB is between 1 MB and 8 MB. This is per query and each query in the file provided is much smaller.
    2. No. There is an open improvement request to add retries. If the transaction isn't completed, I suspect that whatever triggered it would never be created in the database. We have never actually been able to recreate the error in a test environment though.
    3. I certainly hope not. That would be a badly configured server. A web application should never be able to control a server daemon like that. The shutdown method updates the cache, updates the user's session, and possibly runs some delayed queries that weren't as important at the time they were triggered.

    What are the cache settings in your /core/includes/config.php file?
    Translations provided by Google.

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

    Comment


    • #3
      Current MySQL server is 1 GB RAM, 2 vCPUs, 40 GB SSD MySQL database (8.0.28), CPU usage is typically 15%, number of connections averages around 10 per 10 minute period and available free space is around 33 GB.

      config.php cache settings:
      Code:
      $config['Cache']['class'][0] = 'vB_Cache_Memcached'; //regular cache
      $config['Cache']['class'][1] = 'vB_Cache_Memory'; //fastest cache
      $config['Cache']['class'][2] = 'vB_Cache_Db'; //largest cache and longest life.​
      
      $config['Datastore']['class'] = 'vB_Datastore_Memcached';
      $i = 0;
      // First Server
      $i++;
      $config['Misc']['memcacheserver'][$i] = '127.0.0.1';
      $config['Misc']['memcacheport'][$i] = 11211;
      $config['Misc']['memcachepersistent'][$i] = true;
      $config['Misc']['memcacheweight'][$i] = 1;
      $config['Misc']['memcachetimeout'][$i] = 1;
      $config['Misc']['memcacheretry_interval'][$i] = 15;
      $config['Cache']['memcacheprefix'] = 'vb_organforum';
      I've confirmed that data is being placed in memcached, but my Apache webserver does not have a lot of RAM. I assume the software will use memcached as available and revert to the database when full?

      On the question of a MySql server reset, I ask it because I see this line in the stack trace above
      Code:
      [NODE="7"]Special[/NODE] vB_Cache_Db->shutdown() called in phar://[path]/vb/vb.phar/shutdown.php on line 92
      and I also see this message in the MySql error log
      Code:
      2022-11-09T11:29:50.945866Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.28)
      VB 5.7.1
      PHP 7.4
      MySQL 8.0.28

      Comment


      • #4
        shutdown.php and the functions it calls are related to shutting down vBulletin. This handles things that didn't need to be handled while the page is being built and sent to the end-user. It happens on every single page load. PHP is called, it processes what needs to be done for vBulletin, PHP shuts down. Repeat.

        Memcached will hold the amount of memory that it is configured to do so. vBulletin doesn't have control over that. When you try to add something new and Memcached doesn't have space, it will get rid of the old to make room. If vBulletin cannot find what it needs in Memcached, it will fall back to the database cache. If it isn't in there, it runs additional queries to retrieve the data.
        Translations provided by Google.

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

        Comment

        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
        Working...
        X