472,779 Members | 2,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,779 software developers and data experts.

About mysql_pconnect

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/
Dec 21 '06 #1
11 1632

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.

Dec 22 '06 #2

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

Dec 22 '06 #3
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/
Dec 22 '06 #4
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/
Dec 22 '06 #5

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?

Dec 22 '06 #6
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/
Dec 23 '06 #7

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/

Dec 23 '06 #8
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
==================
Dec 24 '06 #9
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/
Dec 24 '06 #10

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.

Dec 24 '06 #11
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
==================
Dec 24 '06 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Yun Guan | last post by:
Hello folks, In my php scripts, those mysql functions, like mysql_pconnect() and mysql_connect() are said to be undefined. What do I miss here? Thanks. -- Allen Guan 281-489-2314
1
by: Hal Halloway | last post by:
mysql_pconnect("localhost","user","password") Is there a way so that password will not be text? So someone looking at the file would not be able to know the password? Thanks Ps my sysadmin...
0
by: anders thoresson | last post by:
Hi, I have this function I call everytime I need to make a query from within my php-scripts: function db_connect ($user, $pwd, $db, $debug = 0) { $link = @mysql_pconnect("localhost",...
4
by: Angelos | last post by:
I get this error mysql_pconnect Too many connections ... every now and then. Does anyone knows where it comes from ? There are a lot of sites running on the server and all of them use the...
1
by: tom_b | last post by:
I get this error when I try to connect to my database Fatal error: Call to undefined function mysql_pconnect() in c:\program files\apache group\Apache\htdocs\flash.php on line 11 Here's the...
1
by: WhatsPHP | last post by:
Can I use mysql_pconnect on an environment like this? There is just 1 server on which both PHP and MYSQL runs. It is an intranet server. At any point atleast 2 people will be actively using the...
2
by: EsahakNoorul | last post by:
hi, I want to know the difference between mysql_connect and mysql_pconnect Regards, Noorullah
11
by: Robin S. | last post by:
I've used phpinfo() to confirm settings for mySQL on our host server, and it's not returning accurate info. phpinfo() returns a mySQL client API version of 3.23.54 (uselss for me), where as a...
4
hsriat
by: hsriat | last post by:
Got an issue regarding this... As said above (same as what php.net says), if we use mysql_pconnect(), it doesn't create more connections, if the connection parameters are same as the existing...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.