Max Performance: vbulletin on Windows web and DB Servers
 

Over the past years I've gain a lot of help on vb.com with vb issues I've had - mostly due to plugins. :) However, a lot for the performance tuning on vb.cm is very specific/best for Linux setups and not as much out there for Windows servers, esp for large forums.

We run a 1M visits per month forum with 600k members and over 1M posts (pruned) all on Windows 2003. I thought it would be nice to share some of the stability and performance tuning tips I've come up with and tested over the last few years.

Lets start with the database (MySQL)

Don't install on the Windows OS drive [C:]. Have web host setup RAID drives on D: and NOT on the same drive as OS. This will increase your read/write performance. E.g. datadir="D:/mysql/Data/"

To maximize data throughput on the server:
In Control Panel, double-click Network Connections, right-click Local Area Connection, and then click Properties.Select File and Printer Sharing for Microsoft Networks, and then click Properties.
Under Optimization, select Maximize data throughput for network applications.

Install stable version of MySql (Currently 5.0.81)
Download the "Windows Essentials" edition.
Mysql 5.1.x has been very buggy on windows to date.

Install MySQL Administrator GUI (Currently 1.2)
This is a GREAT tool! It will help make sense of the my.ini settings I will provide.

Make sure to configure vb config.php to use mysqli and NOT mysql. Persistent connections do not perform well on Windows servers and of course its not supported with mysqli anyway.

Configuring my.ini
There are some important differences between how windows handles the my.ini config file.

max_connections = x - Do not set this above 100, yes seriously! Our max connection setting is 25 and we avg about 4 connections (threads) and peak at about 15 - 20. Set back_log = 10 for any extra threads/connections.

Every time you raise max_connections windows will lower the max available table_cache.

So on our 3GB server with max_connections = 25 windows allots a max of 1006 for table_cache. Thus, we've set table_cache to 1000. Setting table_cache to more than 1006 will invoke:
Code:
Changed limits: max_open_files: 2048 max_connections: 100 table_cache: [less than 1006]
Or setting max_connections higher than 25 with table_cache still set to 1000 will also invoke the changed limits message. If you get that message your settings are pretty useless. If you have max_connections set to 200, 300, 400 etc then you know this message. ;)

Sample my.ini config for 3GB dedicated mysql server:
Code:
[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
basedir="D:/mysql/"
datadir="D:/mysql/Data/"
default-character-set=latin1
default-storage-engine = MyISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_E NGINE_SUBSTITUTION"
myisam_sort_buffer_size=369M
myisam_max_sort_file_size=100G

skip-innodb
skip-name-resolve
skip-show-database

back_log = 10
max_connections = 25
connect_timeout = 1800
wait_timeout= 300

key_buffer_size = 512M
join_buffer_size = 1M
read_buffer_size = 64k
read_rnd_buffer_size = 256k
sort_buffer_size = 1M
table_cache = 1000
thread_cache_size = 16
thread_concurrency = 8
tmp_table_size=185M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096

log-slow-queries=
long_query_time=2
low-priority-updates
bulk_insert_buffer_size = 16M
concurrent_insert = 2
table_lock_wait_timeout = 20
max_write_lock_count = 4
low_priority_updates=1

[mysqld_safe]
nice = -5

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 512M
sort_buffer = 128M
read_buffer = 128M
write_buffer = 128M
Don't overkill my.ini buffers:
Remember buffers like sort_buffer_size are allocated per thread (connection). Also, remember all in all they are only "buffers" so don't set too large.

log-slow-queries= & long_query_time=2:
These lines automatically add a slow log file in D:\mysql\data and will also log slow queries in your MySQL Administrator GUI.

More soon (IIS, php.ini, fastcgi with opcode caching, etc)... with screenshots.


Attached Images http://www.vbulletin.com/forum/images/attach/jpg.gif conections_threads_example.jpg‎ (55.0 KB)