473,739 Members | 4,930 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1677

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_shutdo wn_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.

Additionall y, 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*******@attgl obal.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

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

Similar topics

4
2548
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
2744
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 say the want to do an apache "include" to hide the password. What does this mean?
0
1489
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", "$user", "$pwd"); if($link && mysql_select_db("$db")) if($debug == 1) {
4
4177
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 Database frequently. Is there any configuration that I will have to do to my server in order to handle the load ? Thank you... I would appreciate answers from someone that already experienced that
1
1859
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 code: <?php mysql_pconnect ("localHost", "root", "wooty"); mysql_select_db ("blog_db");
1
1533
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 server for their work. If the script uses mysql_pconnect and then the normal user name and password, will this speed up execution of scripts on the server by any chance? Fut
2
1954
by: EsahakNoorul | last post by:
hi, I want to know the difference between mysql_connect and mysql_pconnect Regards, Noorullah
11
2151
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 SELECT VERSION() query on the mySQL database returns version 5.0.27 (exactly what we need). I think there's something wrong with the mySQL library in PHP. Is there any more thurough method of seeing php's settings for mySQL? Obviously I don't...
4
2076
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 pconnection's. But if the already set pconnection is too busy serving others, would the next request of pconnect create another connection then or would still wait for the first query to complete before serving it? This will actually end up...
0
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8794
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9483
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9269
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8216
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6756
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2748
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.