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

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

[Jacqueline]

This is great info! Thanks for posting it.

I don't know about aborted clients, but I found this information for you at http://www.hupo.org.cn/docs/mysql/manual_Problems.html

Starting with MySQL 3.23.40 you only get the Aborted connection error of you start mysqld with --warnings.

If you find errors like the following in your error log.

010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'

See section 5.7.1 The Error Log.

This means that something of the following has happened:

The client program did not call mysql_close() before exit.
The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. See section 13.5.3.4 SHOW VARIABLES. See section 13.5.3.4 SHOW VARIABLES.
The client program ended abruptly in the middle of the transfer.
When the above happens, the server variable Aborted_clients is incremented.

The server variable Aborted_connects is incremented when:

When a connection packet doesn't contain the right information.
When the user didn't have privileges to connect to a database.
When a user uses a wrong password.
When it takes more than connect_timeout seconds to get a connect package. See section 13.5.3.4 SHOW VARIABLES.
Note that the above could indicate that someone is trying to break into your database!

Other reasons for problems with Aborted clients / Aborted connections.

Usage of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via ftp between these two machines. If a transfer goes in burst-pause-burst-pause ... mode then you are experiencing a Linux duplex syndrome. The only solution is switching duplex mode for both your network card and Hub/Switch to either full duplex or to half duplex and testing the results to decide on the best setting.
Some problem with the thread library that causes interrupts on reads.
Badly configured TCP/IP.
Faulty Ethernets or hubs or switches, cables ... This can be diagnosed properly only by replacing hardware.
max_allowed_packet is too small or queries require more memory than you have allocated for mysqld. See section A.2.9 Packet too large Error.