Fresh vB import, dedicated MySQL server optimisation request

[chompy]

I've recently imported a fairly large vB forum to Xen, and I would like some advice on configuration.

Stats:
1-3000 online (5000 on weekends back on vB)
~1m threads
~2m members
~70GB attachments
~6GB db size

2 Servers, Server 1 just points at Server 2 (they're on the same subnet) in config.php

NB: I tried on just one server, and there just weren't enough resources

Server 1: Windows Server, IIS 7.5, PHP 5.3
E3-1240 v2
16GB RAM
SATA HDD
1Gb unmetered

Server 2: Windows Server, MySQL 5.5
E3-1240 v2
16GB RAM
SATA HDD
1Gb unmetered

my.ini looks a little like this:

default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=800
max_allowed_packet=256M

query_cache_size=350M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=548M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size=2G
innodb_log_file_size=213M

Right now it's about a day since the import, and performance is inconsistent. It seemed to be working rather well for a few hours, albeit with constant errors in the error log, and now it's crawling. Periodically, the site just stops connecting to the MySQL server (it's still pingable) and then connects again.

Am I missing anything glaringly obvious for the config?

UPDATE: it was an addon causing the mysql drops mentioned before, now it's just a case of tuning a stable-ish forum with a few quirks yet to be ironed out.

 

[rdn]

@eva2000 or @MattW

 

[Mike Edge]

Shouldn't need 2 servers for that.. Suggestion to move it to one server.. Ditch Windows.. Go Linux. Any E3 1230-1270 v1-3 would be fine, 16 GB RAM, SATA3 HHD for / and SSD for MySQL.

 

[TPerry]

I agree with @Mike Edge, one server should be plenty of horse power... if you ditch windows (lots of overhead there you don't need) and go with a Linux box (centOS or Debian). Use nginx/php5-fpm (if wanting to use a no-cost solution) or invest in LiteSpeed (a very good paid solution). I would also suggest (since you will have some cost savings by ditching one server) to seriously look at getting a hardware RAID solution (RAID 10 with SSD's would be ideal).

 

[chompy]

@eva2000 or @MattW

I recall that first name from a vBulletin server optimisation request I made probably close to a decade ago... it seems rude to PM him though.

... Ditch Windows.. Go Linux. ...

... ditch windows (lots of overhead there you don't need) and go with a Linux box (centOS or Debian). Use nginx/php5-fpm (if wanting to use a no-cost solution) or invest in LiteSpeed (a very good paid solution). I would also suggest (since you will have some cost savings by ditching one server) to seriously look at getting a hardware RAID solution (RAID 10 with SSD's would be ideal).

Thanks for the input. And you're not far off with the RAID suggestion.

This is a rather old forum, and we've been on debian for several years of our history. At one point we had to employ all sorts of wizardry involving serving static html from varnish via nginx to all guests, and we were still pushing it for resources. To the point that we employed artificial restrictions and custom 502 error pages were par for the course at peak times. That was on a dual Xeon 5410 (admittedly slower than a single E3-1240, but not massively so) and we've grown since those days. Actually we have a pattern of growing to server capacity, then upgrading, that's been going on for 9 years.

Updated original post with a bit more info (Xenforo and the server are stable).

I'll run MySQL tuner when it's been live for a bit longer.

For what it's worth, I do plan to switch to a single server with a bit more grunt (the server directly before these was dual-SSD in RAID-0, and I miss that) but that will be in a couple of months (primarily because I prefer single points of failure). When that time comes I may re-examine switching back to debian/nginx, especially for sphinx and general compatibility.

For now I need to keep it together on what we got...