>>>>> "Ozlinx" == Ozlinx <Opec> writes:
Ozlinx> Hi all sorry about a long email.
Ozlinx> I am having some problem with MySQL server hanging and won't accept anymore
Ozlinx> connection. Let me explain my
Ozlinx> situation with a diagram:
Ozlinx> (web server Apache/PHP) server 1------+
Ozlinx> |---> server
Ozlinx> 3 MySQL server
Ozlinx> (web server Apache/PHP) server 2 -----+
Ozlinx> Server 1, 2 PHP scripts connect to server 3 (a dedicated MySQL server box)
Ozlinx> via TCP/IP. These 2 boxes gets a lot of hits and they need to make
Ozlinx> query to the database for various info for the template. The problem that I
Ozlinx> am having is that occasinally ( and it is getting more frequently)
Ozlinx> the connection to the DB can not be establish or conneted but taking a
Ozlinx> REALLY longtime to connect. Here's some details of the Server 3:
Ozlinx> Subscription DB contains approx +27000 rows, Click databases contains approx
Ozlinx> 1311 tables but they are not very large table aprrox 8-9 k each.
Ozlinx> When the problem occured, I check the process table and MySQL is using +90%
Ozlinx> of the CPU and noone was able to make any connection to
Ozlinx> the database. I checked that Mysqld is still running and it was. I also
Ozlinx> checked /tmp/mysql.sock and it was there. However, when I try to connect
Ozlinx> locally I just get this:
Ozlinx> mysql -u someuser -p
Ozlinx> Enter password:
Ozlinx> After I enter a password there was a long pause and eventually it just timed
Ozlinx> The only way that I could temporary fix the problem was to increase the
Ozlinx> back_log and max_connection vaules to be really large. However,
Ozlinx> the max_connection doesn't seem to solve the problem but increasing the
Ozlinx> back_log value seems to do the trick.
Ozlinx> My question are:
Ozlinx> 1) What settings should I have for MySQL to handle a large amount of
Ozlinx> simultanous connection.
It depends on what the connections are doing. If they are mostly
sleeping then 'max_connections' is good enough. If not, then it
depends on your queries. (Your current setup doesn't looks quite ok)
Ozlinx> 2) Are the settings that I have got now adequate to handle the task.?
As you have problems, something could probably be done better.
Can you post the output of 'mysqladmin variables' when your machine
has been up a day ?
Ozlinx> 3) Was the problem cause by the lare amout of connections? this seem pretty
Ozlinx> obvious but I would like it confirm.
Can't you change your program to use pconnect() instead of connect();
This will use persistent connections, which should give you much
The problem looks like a TCP/IP connection problem in FreeBSD (but
without more information it's very hard to verify this)
Ozlinx> Thankyou very much for any assistant, and once again I apologise for a
Ozlinx> lengthty email