Cyrian
Sun 17th Feb '08, 5:00pm
This is my first attempt at setting up a WAMP environment. So I'm all ears. :)
We're a seasonal site. Right now is 'down' time. So we're moving to new platform with scalability in mind (future sites, etc.)
1. Dedicated - 2 web (load balanced), 1 db
2. Hardware
Webs
(2) Intel Xeon Quad-core 1.6GHz
2GB RAM
73GB SAS in RAID-1
Windows Server 2003 R2 (SP2)
Apache v2.2.8
PHP 5.2.5
DB
(2) Intel Xeon Quad-core 1.6GHz
8GB RAM
146GB SAS in RAID-10
Windows Server 2003 R2 (SP2)
Apache v2.2.8
PHP 5.2.5
MySQL 5.0.45-community-nt (x64)3. vB v3.7.0, Beta 5
4. It appears we do have one InnoDB, but it is a 'test' site we could just as easily delete. All others say MyISAM. phpMyAdmin says InnoDB is the default storage engine.
5. MySQL: Win32 installer w/ setup.exe
6. Threads: 181,730, Posts: 3,808,846, Members: 29,696 (is this what you're looking for?)
7. my.ini
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="G:/dbs"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
max_connections=800
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
#skip-innodb
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=188. MySQL stats (sorry for lack of formatting)
auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 50
basedir C:\Program Files\MySQL\MySQL Server 5.0\
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type 0
concurrent insert 1
connect timeout 5
datadir G:\dbs\
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
div precision increment 4
engine condition pushdown OFF
expire logs days 0
flush OFF
flush time 1,800
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive YES
have bdb NO
have blackhole engine YES
have compress YES
have crypt NO
have csv NO
have dynamic loading YES
have example engine NO
have federated engine YES
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl DISABLED
have ssl DISABLED
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
hostname db1
init connect
init file
init slave
innodb additional mem pool size 33,554,432
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 2,147,483,648
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 16,777,216
innodb log file size 426,770,432
innodb log files in group 2
innodb log group home dir .\
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 18
innodb thread sleep delay 10,000
interactive timeout 28,800
join buffer size 131,072
key buffer size 57,671,680
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language C:\Program Files\MySQL\MySQL Server 5.0\share\english\
large files support ON
large page size 0
large pages OFF
lc time names en_US
license GPL
local infile ON
log OFF
log bin OFF
log bin trust function creators OFF
log error G:\dbs\db1.err
log queries not using indexes OFF
log slave updates OFF
log slow queries OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system ON
lower case table names 1
max allowed packet 1,048,576
max binlog cache size 4,294,967,295
max binlog size 1,073,741,824
max connect errors 10
max connections 800
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 4,294,967,295
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max sp recursion depth 0
max tmp tables 32
max user connections 0
max write lock count 4,294,967,295
multi range count 256
myisam data pointer size 6
myisam max sort file size 107,374,182,400
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 131,072,000
myisam stats method nulls_unequal
named pipe OFF
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 2,048
optimizer prune level 1
optimizer search depth 62
pid file G:\dbs\db1.pid
port 3,306
preload buffer size 32,768
profiling OFF
profiling history size 15
protocol version 10
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 367,001,600
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 2,048
read buffer size 61,440
read only OFF
read rnd buffer size 258,048
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
secure file priv
shared memory OFF
shared memory base name MYSQL
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave load tmpdir C:\WINDOWS\TEMP\
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
sort buffer size 262,136
sql big selects ON
sql mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
sql notes ON
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine InnoDB
sync binlog 0
sync frm ON
system time zone Eastern Standard Time
table cache 619
table lock wait timeout 50
table type InnoDB
thread cache size 38
thread stack 262,144
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 131,072,000
tmpdir C:\WINDOWS\TEMP\
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
updatable views with limit YES
version 5.0.45-community-nt
version comment MySQL Community Edition (GPL)
version compile machine unknown
version compile os Win64
wait timeout 28,8009. We're also going to install Joomla. Nothing more in the foreseeable future.
10. Avg. users = 400. Max about 2000. Timeout = 900 secs
11. phpinfo:
(http://dolphinsnation.com/inf/test.php)
12. Apache: The only one I have is MaxRequestsPerChild = 0. However I also have ThreadsPerchild = 700. Not sure if this is relevant.
13. Apache logs: All good. We monitor this.
14. I saw someone give a sysinfo dump here. ;)
Web
OS Name Microsoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
OS Manufacturer Microsoft Corporation
System Type x64-based PC
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Total Physical Memory 2,042.76 MB
Available Physical Memory 1.33 GB
Total Virtual Memory 3.86 GB
Available Virtual Memory 2.89 GB
Page File Space 1.99 GB
Page File C:\pagefile.sysDB
OS Name Microsoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
OS Manufacturer Microsoft Corporation
System Type x64-based PC
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Total Physical Memory 8,190.98 MB
Available Physical Memory 6.26 GB
Total Virtual Memory 9.56 GB
Available Virtual Memory 6.42 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys Thank you for your time and expertise. :)
We're a seasonal site. Right now is 'down' time. So we're moving to new platform with scalability in mind (future sites, etc.)
1. Dedicated - 2 web (load balanced), 1 db
2. Hardware
Webs
(2) Intel Xeon Quad-core 1.6GHz
2GB RAM
73GB SAS in RAID-1
Windows Server 2003 R2 (SP2)
Apache v2.2.8
PHP 5.2.5
DB
(2) Intel Xeon Quad-core 1.6GHz
8GB RAM
146GB SAS in RAID-10
Windows Server 2003 R2 (SP2)
Apache v2.2.8
PHP 5.2.5
MySQL 5.0.45-community-nt (x64)3. vB v3.7.0, Beta 5
4. It appears we do have one InnoDB, but it is a 'test' site we could just as easily delete. All others say MyISAM. phpMyAdmin says InnoDB is the default storage engine.
5. MySQL: Win32 installer w/ setup.exe
6. Threads: 181,730, Posts: 3,808,846, Members: 29,696 (is this what you're looking for?)
7. my.ini
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="G:/dbs"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
max_connections=800
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
#skip-innodb
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=188. MySQL stats (sorry for lack of formatting)
auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 50
basedir C:\Program Files\MySQL\MySQL Server 5.0\
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type 0
concurrent insert 1
connect timeout 5
datadir G:\dbs\
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
div precision increment 4
engine condition pushdown OFF
expire logs days 0
flush OFF
flush time 1,800
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive YES
have bdb NO
have blackhole engine YES
have compress YES
have crypt NO
have csv NO
have dynamic loading YES
have example engine NO
have federated engine YES
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl DISABLED
have ssl DISABLED
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
hostname db1
init connect
init file
init slave
innodb additional mem pool size 33,554,432
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 2,147,483,648
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 16,777,216
innodb log file size 426,770,432
innodb log files in group 2
innodb log group home dir .\
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 18
innodb thread sleep delay 10,000
interactive timeout 28,800
join buffer size 131,072
key buffer size 57,671,680
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language C:\Program Files\MySQL\MySQL Server 5.0\share\english\
large files support ON
large page size 0
large pages OFF
lc time names en_US
license GPL
local infile ON
log OFF
log bin OFF
log bin trust function creators OFF
log error G:\dbs\db1.err
log queries not using indexes OFF
log slave updates OFF
log slow queries OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system ON
lower case table names 1
max allowed packet 1,048,576
max binlog cache size 4,294,967,295
max binlog size 1,073,741,824
max connect errors 10
max connections 800
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 4,294,967,295
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max sp recursion depth 0
max tmp tables 32
max user connections 0
max write lock count 4,294,967,295
multi range count 256
myisam data pointer size 6
myisam max sort file size 107,374,182,400
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 131,072,000
myisam stats method nulls_unequal
named pipe OFF
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 2,048
optimizer prune level 1
optimizer search depth 62
pid file G:\dbs\db1.pid
port 3,306
preload buffer size 32,768
profiling OFF
profiling history size 15
protocol version 10
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 367,001,600
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 2,048
read buffer size 61,440
read only OFF
read rnd buffer size 258,048
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
secure file priv
shared memory OFF
shared memory base name MYSQL
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave load tmpdir C:\WINDOWS\TEMP\
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
sort buffer size 262,136
sql big selects ON
sql mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
sql notes ON
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine InnoDB
sync binlog 0
sync frm ON
system time zone Eastern Standard Time
table cache 619
table lock wait timeout 50
table type InnoDB
thread cache size 38
thread stack 262,144
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 131,072,000
tmpdir C:\WINDOWS\TEMP\
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
updatable views with limit YES
version 5.0.45-community-nt
version comment MySQL Community Edition (GPL)
version compile machine unknown
version compile os Win64
wait timeout 28,8009. We're also going to install Joomla. Nothing more in the foreseeable future.
10. Avg. users = 400. Max about 2000. Timeout = 900 secs
11. phpinfo:
(http://dolphinsnation.com/inf/test.php)
12. Apache: The only one I have is MaxRequestsPerChild = 0. However I also have ThreadsPerchild = 700. Not sure if this is relevant.
13. Apache logs: All good. We monitor this.
14. I saw someone give a sysinfo dump here. ;)
Web
OS Name Microsoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
OS Manufacturer Microsoft Corporation
System Type x64-based PC
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Total Physical Memory 2,042.76 MB
Available Physical Memory 1.33 GB
Total Virtual Memory 3.86 GB
Available Virtual Memory 2.89 GB
Page File Space 1.99 GB
Page File C:\pagefile.sysDB
OS Name Microsoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
OS Manufacturer Microsoft Corporation
System Type x64-based PC
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Processor EM64T Family 6 Model 15 Stepping 7 GenuineIntel ~1596 Mhz
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Total Physical Memory 8,190.98 MB
Available Physical Memory 6.26 GB
Total Virtual Memory 9.56 GB
Available Virtual Memory 6.42 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys Thank you for your time and expertise. :)