[Bug / Issue] Timeouts on Cache Inserts

[OrganForum]

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

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.

[Wayne Luke]

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?

[OrganForum]

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:

$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

[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

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)

[Wayne Luke]

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.