Hi,

I have 2 identical Fedora8 boxes with Apache 2.2.8, MySQL 5.0.45 and PhP 5.2.4. I use one as the Web+DB server (say box A), and the other (box B) just as the Web server connecting to the DB server on box A. I use this 2 machine configuration to test a LAMP based-Web application. I have a client program on other machines that can emulate a massive web workload to these 2 servers; it can emulate hundreds to thousands of users using the application simultaneously.

Before running the test, the connection to the DB server from both boxes seem fine. Using the mysql client program on either box A or B trying to connect to the DB, the connection goes through instantly. In the /etc/my.cnf file, I have max_connections=4096 and max_user_connections=4096. Note that the web app uses one single db user to connect to the db. To allow remote connection, I inserted one record into the user table of the mysql db whose the host field's value is '%' (allow connection from all remote hosts).

After running the test (which I found out that many requests sent to box B failed), mysql client program on box A is still able to connect to the db instantly; but the one on box B has a problem: it takes extremely long (5 - 10 minutes or even more) to establish the connection, it doesn't time out, just takes that long. I believe that's the cause for the failure of requests to box B.

Anyone has any idea about what the problem might be and how I can resolve it?

Thanks,
David.

Are you properly terminating your old connections? Could be your out of connections and a new connection is waiting for an older one to timeout. You might want to consider making a connection pool, say like 10-20 connections that you use over and over again, if your queries to the db are fairly small this will be much faster as you won't have the setup/teardown time added to each query.

I'm not sure how the web app handles connections, since I don't have much control over it (I didn't write the app). But your suggestion doesn't seem to explain the situation for box A. Still the same app spawning connections to the 2 boxes. Why connection from B needs to wait, whereas that from A doesn't have to?

I'm guessing the localbox is using the unix socket to connect instead of ip, the mysql client library tends to do that behind the scenes, it improves performance when they are on the same box.

That makes sense. So if I create the connection pool, will I have to modify the web app code? I hope not? Also, if currently the localbox is using the unix sockets as you said, will it use connections from the pool? And first, how can I create the connection pool? Could you pls point me to step by step instructions since I'm a mysql newbie.

Thanks,
David.

Well that kind of depends on the web app, most likely you'll have to rewrite at least a portion, how much will vary widely on what language and backend tools its using. Unfortunately I can't point you to step by step instructions, but here are some links that might help.

http://dev.mysql.com/doc/refman/5.0/...otes-j2ee.html
http://www.netbeans.org/kb/60/web/mysql-webapp.html
http://www.apachetutor.org/dev/reslist
http://kinetic.more.net/web/javaserv...uideline.shtml