Performance issue and extreme resource usage after upgrading to v5 when moving topic to busy forum

[agoodm]

SQL query cache is disabled. INNODB pool is 4GB on the production system, 128MB on the cloud server due to memory constraints. Yes I've ran mysqltuner.pl - thats what pointed me to the fact expensive on disk tmp tables were being created in large quantity. I've been unable to prevent the creation of ondisk tmp tables despite tuning and subsequently reverting many sql settings including join buffers etc.

Having level 0 cache enabled (with apc) is causing unacceptable delay in posts appearing on the forum. For level 0 cache, which method causes less delay in changes appearing on the site?

[Wayne Luke]

This site uses Memcached. We haven't recommended APC since vBulletin 5 was released.

[agoodm]

Do you use memcache for level 0 and 2 and memory for level 1?

[Wayne Luke]

As far as I am aware we use Memcached for level 0, Memory for level 1, Database for level 2. Memcache does not work. You must use MemcacheD. There is a difference between the two.

[agoodm]

Currently struggling to get memcached (yes sorry earlier typo) working. From the errors I see it looks like its not finding the config; however I have configured it I think!

PHP Notice: Memcache::connect(): Server (tcp 0, udp 0) failed with: Failed to parse address "" (0) in /usr/share/nginx/html/wearetherangersboys.com/webroot/core/vb/memcache.php on line 119
PHP Warning: Memcache::connect(): Can't connect to :0, Failed to parse address "" (0) in /usr/share/nginx/html/wearetherangersboys.com/webroot/core/vb/memcache.php on line 119
PHP Fatal error: Unable to connect to memcache server in /usr/share/nginx/html/wearetherangersboys.com/webroot/core/vb/cache/memcached.php on line 78

$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.

fast ds cache commented out

$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;
*/
//If you are using Memcache you should can set a prefix. This is needed only if the memcache server is shared. If dedicated you don't need it.
$config['Cache']['memcacheprefix'] = 'watrb';

I do also have rh-php56-php-pecl-memcache installed. Memcache is running, tested using telnet. Memcache is also listening on UDP, firewalled just for localhost...

[agoodm]

*expletive* the memcache block in the config is commented out as a multi line comment preceeded by /* and proceeded by \*. Not being a PHP dev I hadnt spotted this immediately.

[agoodm]

Please could you explain how the caching should work if using memcache?

There is a seriously long delay from when someone makes a post for example; to when that post appears as the most recent post (on the thread list or forum list) regardless of if you're logged in or out. Users are really noticing this and kicking up a big fuss about it. I think a minute delay would probably be accepted, but its 10-20 minutes delay as a guess which makes conversation really challenging.

[Wayne Luke]

There shouldn't be a delay with memcached. The only settings for vBulletin in this regard are the ones in the /core/includes/config.php file. This is something unique to your configuration. Without a support contract and access to your server and database, we can't say what the issue is.

[agoodm]

It seems to be caused to some extent by not clearing the system cache after altering the cache settings in config.php. After clearing the cache last night the most the delay appears to be in the forum list updating is a few minutes - about 3 which seems acceptable.

The site is now running on dedicated server with SSD storage. In the 9 hours since I moved the site over we've done an expensive disk based tmp table every 6 seconds on average. This is 9 hours through the night - ie off peak. That will no doubt be tmp tables per second at peak... Less of an issue with SSD storage however would still like to optimise this if possible. Willing to provide access if the client agrees.

[Wayne Luke]

You should have your database adminstrator and server administrator look at the configuration of your server. We don't actually have control over when temp tables are created by the server or when they are cleaned up. We rely on MySQL, Apache, and PHP to make that determination within their configuration settings.

You already created a Bug Report about the query and we can look at that. Though we will need a copy of the database to recreate it the issue.

That query is unrelated to when topics show on the page though. What do you have the "Search Results Cache Lifetime" set to under Settings -> Options -> Server Settings and Optimization Options. The default (and lowest value) is 1 minute. On the same page is a setting for caching results for Guest users, this is set to 0 by default.

[agoodm]

Hi,

Back from my holiday now. I am the server admin. Your client purchases managed hosting services from me.

New server has MariaDB 10.2 which appears to perform far better than earlier version (ie 10.0). I am still concerned about the amount of temp tables created on disk, but its about 20% less than previously experienced: [OK] Temporary tables created on disk: 8% (282K on disk / 3M total) (in 10 days - about 1 temp table per 3 seconds average)

Search cache lifetime is 1, guest user cache is set to 0.

The client continues to report issues whereby on forumdisplay pages are showing out of date data. For example you might have the first topic showing last post after the next post down, viewing the topic will then generally show correct information at the end of the thread. A work around was found which involves clearing system cache in admin whenever the issue occurs.

[Wayne Luke]

Do they have "Cache Node Data" turned off under Settings -> Options -> Server Settings and Optimization Options?

Honestly, I don't even know why this is an option but I am sure there is a reason. However, turning it off will have detrimental affects and delay content from appearing.

[Wayne Luke]

Is the site is storing its attachments and avatars in the file system? If not, then it should do so. Storing BLOBs in the database will almost always result in temporary files.

In the AdminCP go to Attachments -> Attachment Storage Type and Avatars -> User Picture Storage Type.

Another enhancement is to make sure that Style Variables and SVG files are stored in the file system under Settings -> Options -> Style & Language Options. While these are just text, reading them from the database on every page load isn't the best solution. If stored in the file system, it is easier for the browser to cache them and the system doesn't need to render this semi-static data on every page load.

[agoodm]

Cache node data is on. Attachments are stored on the file system (one of the changes I made back in VB3). Interestingly I found that pictures and avatars were set to be stored in the database, however I see new files in the customavatars folder since I moved the servers which is a bit strange.

I attempted to move images to the file system and somehow ended up with all avatars in the database and huge increase in resource usage to php scripts which are clearly serving profile pictures up. Once the move into the database completed I moved them back to the file system which appears to have worked OK. Perhaps we had half and half or something before I did this?

Style variables and SVG files are stored as files in the default path. Another option on that page caught my eye 'cache templates as files' which is currently turned off. Would it help to turn this on? Are there any caveats to just swapping the option other than ensuring the path selected is writable?

[Wayne Luke]

I don't normally recommend caching the templates. It can work but unless you're using very fast drives, it can cause a performance bottleneck.