About mysql_pconnect

[=?ISO-8859-1?Q?J=F8rn?= Dahl-Stamnes]

Hello,

I have been using mysql_connect in a script that display a lot of thumbnails
for an album. Each thumbnail is displayed using the code:

<IMG SRC="thm.php?id=some_id" ALT="some title">

thm.php use a mysql_connect to the database to access the info about the
picture based on the id.

This worked fine. However, the SQL server is located on a different network
than the web-server with a firewall between. When I looked into the
firewalls log I saw that there was large amount of new connections when
someone accessed the page where all the thumbnails was displayed.

I then changed mysql_connect to mysql_pconnect on the scripts and viola...
the amount of new connections to the SQL server dropped to only two.

Good, I thought. But later I discovered that the SQL server had a large
amount of childs running. I had 50-60 mysqld running on the system. This
number was constant to below 10 before I changed to persistent mode.

Any settings in the config file for the sql-server I need to be aware of?

I'm using MyISAM tables.

mysqlselect version();
+----------------+
| version() * * *|
+----------------+
| 4.1.8-standard |
+----------------+
--
Jørn Dahl-Stamnes
http://www.dahl-stamnes.net/dahls/

[seaside]

Jørn Dahl-Stamnes schrieb:

Hello,

I have been using mysql_connect in a script that display a lot of thumbnails
for an album. Each thumbnail is displayed using the code:

<IMG SRC="thm.php?id=some_id" ALT="some title">

thm.php use a mysql_connect to the database to access the info about the
picture based on the id.

This worked fine. However, the SQL server is located on a different network
than the web-server with a firewall between. When I looked into the
firewalls log I saw that there was large amount of new connections when
someone accessed the page where all the thumbnails was displayed.

I then changed mysql_connect to mysql_pconnect on the scripts and viola...
the amount of new connections to the SQL server dropped to only two.

Good, I thought. But later I discovered that the SQL server had a large
amount of childs running. I had 50-60 mysqld running on the system. This
number was constant to below 10 before I changed to persistent mode.

Any settings in the config file for the sql-server I need to be aware of?

>From the docs:

>>>

This means that for every child that opened a persistent connection
will have its own open persistent connection to the server. For
example, if you had 20 different child processes that ran a script that
made a persistent connection to your SQL server, you'd have 20
different connections to the SQL server, one from each child.
<<<

Do you always connect using identical hostname, userid and password? If
not, you'll get new persistent connections too.

[seaside]

seaside schrieb:

Jørn Dahl-Stamnes schrieb:

I then changed mysql_connect to mysql_pconnect on the scripts and viola....
the amount of new connections to the SQL server dropped to only two.

Additionally, this excerpt from
http://www.php.net/manual/en/feature...onnections.php may be
of interest for others, who read this thread:

###
There are a couple of additional caveats to keep in mind when using
persistent connections. One is that when using table locking on a
persistent connection, if the script for whatever reason cannot release
the lock, then subsequent scripts using the same connection will block
indefinitely and may require that you either restart the httpd server
or the database server. Another is that when using transactions, a
transaction block will also carry over to the next script which uses
that connection if script execution ends before the transaction block
does. In either case, you can use register_shutdown_function() to
register a simple cleanup function to unlock your tables or roll back
your transactions. Better yet, avoid the problem entirely by not using
persistent connections in scripts which use table locks or transactions
(you can still use them elsewhere).
###

[=?ISO-8859-1?Q?J=F8rn?= Dahl-Stamnes]

seaside wrote:

>>>>

This means that for every child that opened a persistent connection
will have its own open persistent connection to the server. For
example, if you had 20 different child processes that ran a script that
made a persistent connection to your SQL server, you'd have 20
different connections to the SQL server, one from each child.
<<<

Do you always connect using identical hostname, userid and password? If
not, you'll get new persistent connections too.

Every php-script do include a common function that is used to establish the
connection to the SQL server.

I tried to change the wait_timeout under the [mysqld] section in the
config-file for the SQL-server. It seemed to help on one server, but not on
the production server.

--
Jørn Dahl-Stamnes
http://www.dahl-stamnes.net/dahls/

[=?ISO-8859-1?Q?J=F8rn?= Dahl-Stamnes]

seaside wrote:

>
seaside schrieb:

>Jørn Dahl-Stamnes schrieb:

I then changed mysql_connect to mysql_pconnect on the scripts and
viola... the amount of new connections to the SQL server dropped to
only two.

Additionally, this excerpt from
http://www.php.net/manual/en/feature...onnections.php may be
of interest for others, who read this thread:

I have read it. I do not use any kind of locking on my SQL code. I assume
that the only kind of locking I do is on record level when doing an UPDATE.

--
Jørn Dahl-Stamnes
http://www.dahl-stamnes.net/dahls/

[seaside]

Jørn Dahl-Stamnes schrieb:

seaside wrote:

>>>

This means that for every child that opened a persistent connection
will have its own open persistent connection to the server. For
example, if you had 20 different child processes that ran a script that
made a persistent connection to your SQL server, you'd have 20
different connections to the SQL server, one from each child.
<<<

Do you always connect using identical hostname, userid and password? If
not, you'll get new persistent connections too.

Every php-script do include a common function that is used to establish the
connection to the SQL server.

I tried to change the wait_timeout under the [mysqld] section in the
config-file for the SQL-server. It seemed to help on one server, but not on
the production server.

The number of server should somehow correlate to the number of Apache
worker
pocesses. How many worker processes does your production system create?

[=?ISO-8859-1?Q?J=F8rn?= Dahl-Stamnes]

seaside wrote:

>I tried to change the wait_timeout under the [mysqld] section in the
config-file for the SQL-server. It seemed to help on one server, but not
on the production server.

The number of server should somehow correlate to the number of Apache
worker
pocesses. How many worker processes does your production system create?

Currently I got 7 httpd servers and 21 mysqld servers running.

I have added a cron-job that counts the number of mysqld servers every
minutes and append the results to a file. During the last 12 hours it has
been between 21 and 24 mysqld servers.

--
Jørn Dahl-Stamnes
http://www.dahl-stamnes.net/dahls/

[seaside]

Jørn Dahl-Stamnes schrieb:

seaside wrote:

I tried to change the wait_timeout under the [mysqld] section in the
config-file for the SQL-server. It seemed to help on one server, but not
on the production server.

The number of server should somehow correlate to the number of Apache
worker
pocesses. How many worker processes does your production system create?

Currently I got 7 httpd servers and 21 mysqld servers running

While I wonder why there are 3 times more mysqlds than httpds, it still
seems to be ok.
Moste likely the mysqlds are sleeping and just waiting to servie the
next request.

In case you need to get more performance in general, you might wish to
have a look at memcached http://www.danga.com/memcached/

[Jerry Stuckle]

Jørn Dahl-Stamnes wrote:

seaside wrote:

>>seaside schrieb:

>>>Jørn Dahl-Stamnes schrieb:
I then changed mysql_connect to mysql_pconnect on the scripts and
viola... the amount of new connections to the SQL server dropped to
only two.

Additionally, this excerpt from
http://www.php.net/manual/en/feature...onnections.php may be
of interest for others, who read this thread:

I have read it. I do not use any kind of locking on my SQL code. I assume
that the only kind of locking I do is on record level when doing an UPDATE.

Not necessarily. For instance, a SELECT needs to get a lock to keep the
selected rows from being changed during the process. If you use a
MYISAM table, this is done by locking the entire table.

mysql_pconnect() is good when you have a lot of connections constantly
in use. But when you only have a couple of connections going at a time,
it's going to waste a lot of server resources.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[=?ISO-8859-1?Q?J=F8rn?= Dahl-Stamnes]

Jerry Stuckle wrote:

Jørn Dahl-Stamnes wrote:

>I have read it. I do not use any kind of locking on my SQL code. I assume
that the only kind of locking I do is on record level when doing an
UPDATE.

Not necessarily. For instance, a SELECT needs to get a lock to keep the
selected rows from being changed during the process. If you use a
MYISAM table, this is done by locking the entire table.

mysql_pconnect() is good when you have a lot of connections constantly
in use. But when you only have a couple of connections going at a time,
it's going to waste a lot of server resources.

"To pconnect or not to pconnect - that is the question.

If the load on my web application is very low, connect is the best. And if
the load increase I should choose pconnect instead. But how do I know when
pconnect is best?

--
Jørn Dahl-Stamnes
http://www.dahl-stamnes.net/dahls/

[seaside]

Jørn Dahl-Stamnes schrieb:

Jerry Stuckle wrote:

"To pconnect or not to pconnect - that is the question.

If the load on my web application is very low, connect is the best. And if
the load increase I should choose pconnect instead. But how do I know when
pconnect is best?

As often if performance is of concern, there is no strict rule, which
let you decide when to do what. You need to try and play around with
both options.

[Jerry Stuckle]

Jørn Dahl-Stamnes wrote:

Jerry Stuckle wrote:

>>Jørn Dahl-Stamnes wrote:

>>>I have read it. I do not use any kind of locking on my SQL code. I assume
that the only kind of locking I do is on record level when doing an
UPDATE.

Not necessarily. For instance, a SELECT needs to get a lock to keep the
selected rows from being changed during the process. If you use a
MYISAM table, this is done by locking the entire table.

mysql_pconnect() is good when you have a lot of connections constantly
in use. But when you only have a couple of connections going at a time,
it's going to waste a lot of server resources.

"To pconnect or not to pconnect - that is the question.

If the load on my web application is very low, connect is the best. And if
the load increase I should choose pconnect instead. But how do I know when
pconnect is best?

I don't have a solid rule - but I generally don't even start looking at
mysql_pconnect() until I'm seeing 100 connects/sec.

It just takes too many resources from the system permanently otherwise.
You might speed up MySQL connections - but at the expense of something
else.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================