fotografik
01-31-2007, 05:57 AM
Hi all,
After trawling through vb.com and vb.org and not finding anything relative to whether persistent connections are good/bad for a large forum, we went ahead and enabled persistent connections anyway. Here is a summary of our experience thus far :-
Background of our forums
# of members : 35,000
# of posts : 2.54 Million
# of online users : 400-1300 most days (1500 peak)
Web server is a Dual Xeon (3Ghz) with 4GB of memory
Database server is also a Dual Xeon (3.2Ghz) with 4GB of memory
All applicable cacheing tweaks and speed enhancements already applied.
Prior to enabling persistent connections, we were experiencing slowdowns and occasional disruptions of service at our peak periods which were brought on by massive process spawnings with a few hundred httpd processes on the web server. In turn, the database server would be swamped with mysql basically not responding.
Top stats for both web and db server would rise to the 70s and 80s and would require a restart of the processes.
After enabling persistent connections in the config.php for vb, things have stabilised significantly.
Although process spawns still do occur, they now peak at around 100-120 processes before settling back down to the low 50s.
The DB server loading has also come way down to average between 1.5-3 at peak and 0.5-1.2 non-peak.
Some other interesting information - prior to the change, we had noticed that some of the variables in my.cnf (specifically thread cache, query cache and table cache) would reach 100% in a couple of days), but now, even after a week, the same configuration has stayed roughly stable and not reached their max yet (see below) :-
MySQL 4.1.16 uptime 7 4:0:2 Wed Jan 31 14:45:47 2007
__ Key __________________________________________________ _______________
Buffer used 483.57M of 700.00M %Used: 69.08
Current 562.79M %Usage: 80.40
Write ratio 0.81
Read ratio 0.00
__ Questions __________________________________________________ _________
Total 25.19M 40.68/s
DMS 16.81M 27.15/s %Total: 66.74
QC Hits 6.07M 9.81/s 24.11
Com_ 3.65M 5.89/s 14.48
-Unknown 1.60M 2.58/s 6.35
COM_QUIT 257.55k 0.42/s 1.02
Slow 2.42k 0.00/s 0.01 %DMS: 0.01
DMS 16.81M 27.15/s 66.74
SELECT 11.94M 19.28/s 47.39 71.00
UPDATE 3.09M 5.00/s 12.28 18.40
INSERT 1.10M 1.77/s 4.36 6.53
REPLACE 559.79k 0.90/s 2.22 3.33
DELETE 123.81k 0.20/s 0.49 0.74
Com_ 3.65M 5.89/s 14.48
change_db 2.05M 3.31/s 8.13
admin_comma 1.60M 2.59/s 6.35
show_variab 157 0.00/s 0.00
__ SELECT and Sort __________________________________________________ ___
Scan 1.76M 2.85/s %SELECT: 14.77
Range 4.04M 6.53/s 33.85
Full join 204 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 46 0.00/s 0.00
Sort scan 592.05k 0.96/s
Sort range 4.01M 6.47/s
Sort mrg pass 154.06k 0.25/s
__ Query Cache __________________________________________________ _______
Memory usage 31.71M of 144.00M %Used: 22.02
Block Fragmnt 12.23%
Hits 6.07M 9.81/s
Inserts 10.99M 17.74/s
Prunes 577.41k 0.93/s
Insrt:Prune 19.03:1 16.81/s
Hit:Insert 0.55:1
__ Table Locks __________________________________________________ _______
Waited 131.49k 0.21/s %Total: 0.37
Immediate 35.52M 57.37/s
__ Tables __________________________________________________ ____________
Open 1.38k of 2600 %Cache: 53.12
Opened 1.40k 0.00/s
__ Connections __________________________________________________ _______
Max used 147 of 500 %Max: 29.40
Total 446.25k 0.72/s
__ Created Temp __________________________________________________ ______
Disk table 25.17k 0.04/s
Table 1.77M 2.86/s
File 119.46k 0.19/s
__ Threads __________________________________________________ ___________
Running 2 of 28
Cached 119 of 512 %Hit: 99.97
Created 147 0.00/s
Slow 0 0.00/s
__ Aborted __________________________________________________ ___________
Clients 377.40k 0.61/s
Connects 0 0.00/s
__ Bytes __________________________________________________ _____________
Sent 1.32G 2.13k/s
Received 879.05M 1.42k/sThere is only one thing that is niggling at the moment and that is the Aborted Clients readout which is stating that approximately 1 client is being aborted (disconnected) every 2 seconds or so. So far, this does not seem to be hampering the operations of the forums apart from some occasional very slight pauses in page loadings which may (or may not) be attributable to this.
Thanks for reading, and if anyone has any idea on the aborted clients thing, it would be much appreciated if they can share their thoughts (and possible causes, fixes).
After trawling through vb.com and vb.org and not finding anything relative to whether persistent connections are good/bad for a large forum, we went ahead and enabled persistent connections anyway. Here is a summary of our experience thus far :-
Background of our forums
# of members : 35,000
# of posts : 2.54 Million
# of online users : 400-1300 most days (1500 peak)
Web server is a Dual Xeon (3Ghz) with 4GB of memory
Database server is also a Dual Xeon (3.2Ghz) with 4GB of memory
All applicable cacheing tweaks and speed enhancements already applied.
Prior to enabling persistent connections, we were experiencing slowdowns and occasional disruptions of service at our peak periods which were brought on by massive process spawnings with a few hundred httpd processes on the web server. In turn, the database server would be swamped with mysql basically not responding.
Top stats for both web and db server would rise to the 70s and 80s and would require a restart of the processes.
After enabling persistent connections in the config.php for vb, things have stabilised significantly.
Although process spawns still do occur, they now peak at around 100-120 processes before settling back down to the low 50s.
The DB server loading has also come way down to average between 1.5-3 at peak and 0.5-1.2 non-peak.
Some other interesting information - prior to the change, we had noticed that some of the variables in my.cnf (specifically thread cache, query cache and table cache) would reach 100% in a couple of days), but now, even after a week, the same configuration has stayed roughly stable and not reached their max yet (see below) :-
MySQL 4.1.16 uptime 7 4:0:2 Wed Jan 31 14:45:47 2007
__ Key __________________________________________________ _______________
Buffer used 483.57M of 700.00M %Used: 69.08
Current 562.79M %Usage: 80.40
Write ratio 0.81
Read ratio 0.00
__ Questions __________________________________________________ _________
Total 25.19M 40.68/s
DMS 16.81M 27.15/s %Total: 66.74
QC Hits 6.07M 9.81/s 24.11
Com_ 3.65M 5.89/s 14.48
-Unknown 1.60M 2.58/s 6.35
COM_QUIT 257.55k 0.42/s 1.02
Slow 2.42k 0.00/s 0.01 %DMS: 0.01
DMS 16.81M 27.15/s 66.74
SELECT 11.94M 19.28/s 47.39 71.00
UPDATE 3.09M 5.00/s 12.28 18.40
INSERT 1.10M 1.77/s 4.36 6.53
REPLACE 559.79k 0.90/s 2.22 3.33
DELETE 123.81k 0.20/s 0.49 0.74
Com_ 3.65M 5.89/s 14.48
change_db 2.05M 3.31/s 8.13
admin_comma 1.60M 2.59/s 6.35
show_variab 157 0.00/s 0.00
__ SELECT and Sort __________________________________________________ ___
Scan 1.76M 2.85/s %SELECT: 14.77
Range 4.04M 6.53/s 33.85
Full join 204 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 46 0.00/s 0.00
Sort scan 592.05k 0.96/s
Sort range 4.01M 6.47/s
Sort mrg pass 154.06k 0.25/s
__ Query Cache __________________________________________________ _______
Memory usage 31.71M of 144.00M %Used: 22.02
Block Fragmnt 12.23%
Hits 6.07M 9.81/s
Inserts 10.99M 17.74/s
Prunes 577.41k 0.93/s
Insrt:Prune 19.03:1 16.81/s
Hit:Insert 0.55:1
__ Table Locks __________________________________________________ _______
Waited 131.49k 0.21/s %Total: 0.37
Immediate 35.52M 57.37/s
__ Tables __________________________________________________ ____________
Open 1.38k of 2600 %Cache: 53.12
Opened 1.40k 0.00/s
__ Connections __________________________________________________ _______
Max used 147 of 500 %Max: 29.40
Total 446.25k 0.72/s
__ Created Temp __________________________________________________ ______
Disk table 25.17k 0.04/s
Table 1.77M 2.86/s
File 119.46k 0.19/s
__ Threads __________________________________________________ ___________
Running 2 of 28
Cached 119 of 512 %Hit: 99.97
Created 147 0.00/s
Slow 0 0.00/s
__ Aborted __________________________________________________ ___________
Clients 377.40k 0.61/s
Connects 0 0.00/s
__ Bytes __________________________________________________ _____________
Sent 1.32G 2.13k/s
Received 879.05M 1.42k/sThere is only one thing that is niggling at the moment and that is the Aborted Clients readout which is stating that approximately 1 client is being aborted (disconnected) every 2 seconds or so. So far, this does not seem to be hampering the operations of the forums apart from some occasional very slight pauses in page loadings which may (or may not) be attributable to this.
Thanks for reading, and if anyone has any idea on the aborted clients thing, it would be much appreciated if they can share their thoughts (and possible causes, fixes).