vb.org Archive - Large forums and persistent connections

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) :-

Code:

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/s

There 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).

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00931 seconds Memory Usage 1,741KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (2)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: