View Full Version : Large forums and persistent connections

31 Jan 2007, 06:57
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).

24 Feb 2007, 10:30
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 SHOW VARIABLES. See section 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 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.