MySQL Tuning Advice

[m1ne]

Your thread_cache_size is fine
Threads created % connections should be no higher than 0.01, and yours is 0.01.

 

[nrep]

So sorry, I thought those values were a little low - I ran those last 3 commands on the dev server by mistake. Here are the real values:

Connections:61164
Threads_created:81
Max_user_connections:81

 

[m1ne]

No problem, it's still fine.

 

[nrep]

Thanks Solidus

 

[Xon]

Do you have XenForo caching setup? This will help considerably.

I've tried disabling it a couple of times, but page generation times increase by about 30% when I disable it. Would that mean I've got a problem elsewhere? Here's what I used to disable it in real-time to perform the test:

set global query_cache_type=0;
flush query cache;
reset query cache;

A large query query_cache_size is actually harmful. The MySQL manual claims 16mb is enough in most cases (ref).

This thread has some good details on tuning; http://dba.stackexchange.com/questions/7344/optimizing-mysql-query-cache-size

Additionally; myisam_sort_buffer_size is hilariously large. It starts at 2mb and is generally find to stay there.

If you are willing to accept a second or two of data loss on a catastrophic failure of the OS; use:
innodb_flush_log_at_trx_commit =2

 

[m1ne]

^ I was just about to ask the same. Is the reason for optimizing MySQL because of slow load times?

 

[nrep]

Thanks guys, I'll have a read of that now and adjust the myisam_sort_buffer_size.

The forums appear to load fine, but page generation times are around 0.4s in debug mode, which seems considerably higher than other sites.

I've got no XF caching enabled - but I do have WinCache and OpCache running.

 

[Moscato]

You may want to enable XF caching

 

[m1ne]

You may want to enable XF caching

Opcache is fine.

 

[Moscato]

Those are entirely different things

Opcache makes php run faster
xf cache prevents things from running at all

They can work together

 

[nrep]

I tried using this (https://xenforo.com/community/threads/wincache-support.6494/) Wincache plugin for the XF cache backend, but it didn't seem to make much difference compared to having an opcache running (which made a huge difference).

I'm happy to give an XF cache a try as an experiment, if there's something Windows compatible.

 

[Xon]

Do you have "Fetch public templates as files" set?

 

[m1ne]

I tried using this (https://xenforo.com/community/threads/wincache-support.6494/) Wincache plugin for the XF cache backend, but it didn't seem to make much difference compared to having an opcache running (which made a huge difference).

I'm happy to give an XF cache a try as an experiment, if there's something Windows compatible.

Memcached is available for Windows.

 

[Moscato]

If memcached is too annoying to setup, just use filecache

$config['cache']['enabled'] = true;
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
$config['cache']['cacheSessions'] = true;
$config['cache']['backend'] = 'File';
$config['cache']['backendOptions'] ['cache_dir'] = '/srv/http/techraptor/community/file_cache';

This is what I have setup on linux (that directory is ramdisk, actually)

I'm sure you can do similar on windows

 

[hellreturn]

Adjust wait time out.. i mean 300 is still too long. I have mine set to 90 seconds. 1/3 of your's and i generally have 50-70 QPS. Your avg is 40 QPS. It should work fine for XF and IPB forums. This way you wouldn't be maxing your connection. If results are not served within 90 seconds then chances are it will take longer time and user will either refresh or move on to another page.

 

[nrep]

Do you have "Fetch public templates as files" set?

I've tested it for a couple of hours, but that's about it. Are there tangible performance improvements to be had with that and OpCache? If so, I'll enable it.

Memcached is available for Windows.

I've only been able to find really old versions and broken links (i.e. http://code.jellycan.com/memcached/) - do you know where I can find a recent build for Windows and I'll give it a try .

If memcached is too annoying to setup, just use filecache

$config['cache']['enabled'] = true;
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
$config['cache']['cacheSessions'] = true;
$config['cache']['backend'] = 'File';
$config['cache']['backendOptions'] ['cache_dir'] = '/srv/http/techraptor/community/file_cache';

This is what I have setup on linux (that directory is ramdisk, actually)

I'm sure you can do similar on windows

Thanks, I'll see if I can give memcached a try first, but I'll benchmark this if memcached isn't possible.

 

[Xon]

I've tested it for a couple of hours, but that's about it. Are there tangible performance improvements to be had with that and OpCache? If so, I'll enable it.

Opcode caching, and "Fetch public templates as files" are designed to work together and they provide a decent performance gain.

Public templates are compiled from the template syntax into php. If you instruct it to save to disk, then opcode caching is a great way to preventing php from having to re-parse all the php-template code on each page request.

 

[nrep]

Thanks Xon, I'll enable that setting now and give it a week or two .

 

[nrep]

Although it's only been a few days since all of these tweaks, the site loading times haven't improved unfortunately. Here's the first few lines from debug mode (below), does this mean that MySQL can be ruled out from being the problem and that it's likely to be IIS/PHP?:

Page Time: 0.5102s
Memory: 3.6099 MB (Peak: 10.4630 MB)
Queries (14, time: 0.0160s, 3.1%)

If MySQL queries are taking only 3.1% of the total time, then the problem must lie elsewhere?

 

[Marcus]

If MySQL queries are taking only 3.1% of the total time, then the problem must lie elsewhere?

Yes i would disable all addons for a minute and try the same page again. It should be faster.