Experiences with mysql_pconnect?

[John Murtari]

Folks,

We have a fairly busy web site present using PHP 5 to
talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
spread out over several physical hosts (one DB server), we use
DB tracking of sessions and there is some transaction usage also.

Right now we keep it simple using mysql_connect. I'd
looked at mysql_pconnect a few years but got scared away by the
warnings about problems. Somebody here wanted to revisit that
issue and I still see warning regarding transaction problems and
dangling connections to the server at the php.net site.

Is anybody using this on a busy site and finding it
worthwhile? I'd appreciate any tips to handle potential problems.
It sounds like it should be a performance boost if configured
properly.

Thanks.
--
John
_______________ _______________ _______________ _______________ ________
Customer Service Software Workshop Inc.
johnm@thebook.c om "software that fits!" (TM)
Toll Free (877) 635-1968(x-211) http://www.thebook.com/

[Jerry Stuckle]

Re: Experiences with mysql_pconnect?

John Murtari wrote:

Folks,
>
We have a fairly busy web site present using PHP 5 to
talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
spread out over several physical hosts (one DB server), we use
DB tracking of sessions and there is some transaction usage also.
>
Right now we keep it simple using mysql_connect. I'd
looked at mysql_pconnect a few years but got scared away by the
warnings about problems. Somebody here wanted to revisit that
issue and I still see warning regarding transaction problems and
dangling connections to the server at the php.net site.
>
Is anybody using this on a busy site and finding it
worthwhile? I'd appreciate any tips to handle potential problems.
It sounds like it should be a performance boost if configured
properly.
>
Thanks.

What is "busy"? 10 connections/sec? 100 connections/sec?

Yes, there can be problems with using mysql_pconnect( ). But if you're
running hundreds of connections per second, you might find it
advantageous to use it. You might start with just the SELECT
statements, though, to limit transaction problems. And leave the
statements which change the database using non-persistent connections.

Just remember - you have to have all the possible persistent connections
you might ever need allocated all the time. For instance, if your
average is 100 connections/second but you peak at 1,000 during busy
times, you need at least 1,000 persistent connections available at all
times. This can be a drain on the system resources, also.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Alex Weber]

Re: Experiences with mysql_pconnect?

John,

My bad for not directly answering your question but I suggest that if
you're looking to optimize your DB stuff and learn new commands, etc I
would look into the PDO extension (usually included by default):

http://www.php.net/manual/en/class.pdo.php

Besides abstracting specific database implementations (ie: same code
for different DBs) it has good tools for preparing statements,
persistence, etc.

Using prepared statements will probably speed up your app a lot.

Oh and also, you should really be using "mysqli" instead of "mysql" by
now! ;)

Alex


Jerry Stuckle wrote:

John Murtari wrote:

Folks,

We have a fairly busy web site present using PHP 5 to
talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
spread out over several physical hosts (one DB server), we use
DB tracking of sessions and there is some transaction usage also.

Right now we keep it simple using mysql_connect. I'd
looked at mysql_pconnect a few years but got scared away by the
warnings about problems. Somebody here wanted to revisit that
issue and I still see warning regarding transaction problems and
dangling connections to the server at the php.net site.

Is anybody using this on a busy site and finding it
worthwhile? I'd appreciate any tips to handle potential problems.
It sounds like it should be a performance boost if configured
properly.

Thanks.

>
What is "busy"? 10 connections/sec? 100 connections/sec?
>
Yes, there can be problems with using mysql_pconnect( ). But if you're
running hundreds of connections per second, you might find it
advantageous to use it. You might start with just the SELECT
statements, though, to limit transaction problems. And leave the
statements which change the database using non-persistent connections.
>
Just remember - you have to have all the possible persistent connections
you might ever need allocated all the time. For instance, if your
average is 100 connections/second but you peak at 1,000 during busy
times, you need at least 1,000 persistent connections available at all
times. This can be a drain on the system resources, also.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: Experiences with mysql_pconnect?

Alex Weber wrote:

Jerry Stuckle wrote:

>John Murtari wrote:

>>Folks,
>>>
>> We have a fairly busy web site present using PHP 5 to
>>talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
>>spread out over several physical hosts (one DB server), we use
>>DB tracking of sessions and there is some transaction usage also.
>>>
>> Right now we keep it simple using mysql_connect. I'd
>>looked at mysql_pconnect a few years but got scared away by the
>>warnings about problems. Somebody here wanted to revisit that
>>issue and I still see warning regarding transaction problems and
>>dangling connections to the server at the php.net site.
>>>
>> Is anybody using this on a busy site and finding it
>>worthwhile? I'd appreciate any tips to handle potential problems.
>>It sounds like it should be a performance boost if configured
>>properly.
>>>
>> Thanks.

>What is "busy"? 10 connections/sec? 100 connections/sec?
>>
>Yes, there can be problems with using mysql_pconnect( ). But if you're
>running hundreds of connections per second, you might find it
>advantageous to use it. You might start with just the SELECT
>statements, though, to limit transaction problems. And leave the
>statements which change the database using non-persistent connections.
>>
>Just remember - you have to have all the possible persistent connections
>you might ever need allocated all the time. For instance, if your
>average is 100 connections/second but you peak at 1,000 during busy
>times, you need at least 1,000 persistent connections available at all
>times. This can be a drain on the system resources, also.
>>

John,
>
My bad for not directly answering your question but I suggest that if
you're looking to optimize your DB stuff and learn new commands, etc I
would look into the PDO extension (usually included by default):
>

http://www.php.net/manual/en/class.pdo.php

>
Besides abstracting specific database implementations (ie: same code
for different DBs) it has good tools for preparing statements,
persistence, etc.
>
Using prepared statements will probably speed up your app a lot.
>
Oh and also, you should really be using "mysqli" instead of "mysql" by
now! ;)
>
Alex
>
>

(top posting fixed)

Wrong person to respond to - but actually, tests have shown that
mysqli_xxx and PDO are both slower than mysql_xxx. Of course, they have
other advantages, but performance is not one of them.

Prepared statements are also slower than non-prepared statements,
especially when the server is remote as it requires multiple calls to MySQL.

P.S. Please don't top post. Thanks.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Alex Weber]

Re: Experiences with mysql_pconnect?

On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Alex Weber wrote:

Jerry Stuckle wrote:

John Murtari wrote:
>Folks,

>

>        We have a fairly busy web site present using PHP 5 to
>talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
>spread out over several physical hosts (one DB server), we use
>DB tracking of  sessions and there is some transaction usage also.

>

>        Right now we keep it simple using mysql_connect.  I'd
>looked at mysql_pconnect a few years but got scared away by the
>warnings about problems.   Somebody here wanted to revisit that
>issue and I still see warning regarding transaction problems and
>dangling connections to the server at the php.net site.

>

>        Is anybody using this on a busy site and finding it
>worthwhile?  I'd appreciate any tips to handle potential problems.
>It sounds like it should be a performance boost if configured
>properly.

>

>        Thanks.
What is "busy"?  10 connections/sec?  100 connections/sec?

>

Yes, there can be problems with using mysql_pconnect( ).  But if you're
running hundreds of connections per second, you might find it
advantageous to use it.  You might start with just the SELECT
statements, though, to limit transaction problems.  And leave the
statements which change the database using non-persistent connections.

>

Just remember - you have to have all the possible persistent connections
you might ever need allocated all the time.  For instance, if your
average is 100 connections/second but you peak at 1,000 during busy
times, you need at least 1,000 persistent connections available at all
times.  This can be a drain on the system resources, also.

>
 John,
 >
 My bad for not directly answering your question but I suggest that if
 you're looking to optimize your DB stuff and learn new commands, etcI
 would look into the PDO extension (usually included by default):
 >
 >http://www.php.net/manual/en/class.pdo.php
 >
 Besides abstracting specific database implementations (ie: same code
 for different DBs) it has good tools for preparing statements,
 persistence, etc.
 >
 Using prepared statements will probably speed up your app a lot.
 >
 Oh and also, you should really be using "mysqli" instead of "mysql" by
 now! ;)
 >
 Alex
 >
 >
>
(top posting fixed)
>
Wrong person to respond to - but actually, tests have shown that
mysqli_xxx and PDO are both slower than mysql_xxx.  Of course, they have
other advantages, but performance is not one of them.
>
Prepared statements are also slower than non-prepared statements,
especially when the server is remote as it requires multiple calls to MySQL.
>
P.S. Please don't top post.  Thanks.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attgl obal.net
=============== ===

Jerry,

A couple points:
- i disagree about PDO being slower than mysql (not sure about
mysqli), anyway i'd like to see some source to back that up (PDO is a
pure-C class which is inherently faster)

- i completely disagree with "prepared statements are slower than non-
prepared statements". its all about context. if you have similar
queries that you execute on a regular basis, preparing it and then
just executing it passing different variables each time is MUCH faster
than manually executing the statement each time. The ONLY scenario
where a prepared a statement might be slower than a non-prepared
statement is if its an uncommon query (ie: one that you don't execute
regularly or that varies a lot in its syntax)

most "generic" queries would greatly benefit from being prepared, for
example:

- login: "SELECT id, name FROM users WHERE email = ? AND password = ?"
- logging: "INSERT INTO log (event, time) VALUES (?, NOW())"
- etc.

since those two are queries you would presumably execute regularly the
benefits are that they only need to be parsed once, so after preparing
it every execution is faster. (it also protects against sql-injection
by avoiding malformed queries)

there's a caveat though: "Using prepared SELECT statements on a MySQL
database prior to MySQL 5.1.17 can lead to SERIOUS performance
degradation." source: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

further reading: http://www.mysqlperformanceblog.com/...ed-statements/

Anyway apologies for the tangent off your original post but there's
good info in here if you'd just read it and be less conservative.

Alex

[Jerry Stuckle]

Re: Experiences with mysql_pconnect?

Alex Weber wrote:

On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

>Alex Weber wrote:

>>Jerry Stuckle wrote:
>>>John Murtari wrote:
>>>>Folks,
>>>> We have a fairly busy web site present using PHP 5 to
>>>>talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
>>>>spread out over several physical hosts (one DB server), we use
>>>>DB tracking of sessions and there is some transaction usage also.
>>>> Right now we keep it simple using mysql_connect. I'd
>>>>looked at mysql_pconnect a few years but got scared away by the
>>>>warnings about problems. Somebody here wanted to revisit that
>>>>issue and I still see warning regarding transaction problems and
>>>>dangling connections to the server at the php.net site.
>>>> Is anybody using this on a busy site and finding it
>>>>worthwhil e? I'd appreciate any tips to handle potential problems.
>>>>It sounds like it should be a performance boost if configured
>>>>properly.
>>>> Thanks.
>>>What is "busy"? 10 connections/sec? 100 connections/sec?
>>>Yes, there can be problems with using mysql_pconnect( ). But if you're
>>>running hundreds of connections per second, you might find it
>>>advantageo us to use it. You might start with just the SELECT
>>>statements , though, to limit transaction problems. And leave the
>>>statements which change the database using non-persistent connections.
>>>Just remember - you have to have all the possible persistent connections
>>>you might ever need allocated all the time. For instance, if your
>>>average is 100 connections/second but you peak at 1,000 during busy
>>>times, you need at least 1,000 persistent connections available at all
>>>times. This can be a drain on the system resources, also.
> John,
> >
> My bad for not directly answering your question but I suggest that if
> you're looking to optimize your DB stuff and learn new commands, etc I
> would look into the PDO extension (usually included by default):
> >
> >http://www.php.net/manual/en/class.pdo.php
> >
> Besides abstracting specific database implementations (ie: same code
> for different DBs) it has good tools for preparing statements,
> persistence, etc.
> >
> Using prepared statements will probably speed up your app a lot.
> >
> Oh and also, you should really be using "mysqli" instead of "mysql" by
> now! ;)
> >
> Alex
> >
> >

>>
>(top posting fixed)
>>
>Wrong person to respond to - but actually, tests have shown that
>mysqli_xxx and PDO are both slower than mysql_xxx. Of course, they have
>other advantages, but performance is not one of them.
>>
>Prepared statements are also slower than non-prepared statements,
>especially when the server is remote as it requires multiple calls to MySQL.
>>
>P.S. Please don't top post. Thanks.
>>
>--
>============== ====
>Remove the "x" from my email address
>Jerry Stuckle
>JDS Computer Training Corp.
>jstuck...@attg lobal.net
>============== ====

>
Jerry,
>
A couple points:
- i disagree about PDO being slower than mysql (not sure about
mysqli), anyway i'd like to see some source to back that up (PDO is a
pure-C class which is inherently faster)
>

The mysql interface is also written in pure-C - and doesn't have
overhead of having to figure out which interface to use. Try benchmarks
for both of them - you will find PDO is slower.

- i completely disagree with "prepared statements are slower than non-
prepared statements". its all about context. if you have similar
queries that you execute on a regular basis, preparing it and then
just executing it passing different variables each time is MUCH faster
than manually executing the statement each time. The ONLY scenario
where a prepared a statement might be slower than a non-prepared
statement is if its an uncommon query (ie: one that you don't execute
regularly or that varies a lot in its syntax)
>

No, it's not "all about context". The prepared statement and each bind
call results in a call to the database. This can cause significant
delays if the database is remote.

Also, it is very seldom in web apps that you prepare a statement once
and call it multiple times with different bind parameters. The vast
majority of the calls are one-time only. If they aren't, you're doing
something seriously wrong in your SQL statements - and increasing
overhead unnecessarily.

Remember - in a web app, each page is its own transaction - which is
different from non-web apps.

most "generic" queries would greatly benefit from being prepared, for
example:
>
- login: "SELECT id, name FROM users WHERE email = ? AND password = ?"
- logging: "INSERT INTO log (event, time) VALUES (?, NOW())"
- etc.
>

And how often do you log in? More than once per page?

The same thing with logging - how often do you actually log something
(other than for debugging purposes)? Not that often.

since those two are queries you would presumably execute regularly the
benefits are that they only need to be parsed once, so after preparing
it every execution is faster. (it also protects against sql-injection
by avoiding malformed queries)
>

But they are not executed multiple times by one page. Once the
connection is closed (at the end of the script), the page must be
prepared again. And don't even think of going to persistent connections
- which have other problems.

there's a caveat though: "Using prepared SELECT statements on a MySQL
database prior to MySQL 5.1.17 can lead to SERIOUS performance
degradation." source: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
>
further reading: http://www.mysqlperformanceblog.com/...ed-statements/
>
Anyway apologies for the tangent off your original post but there's
good info in here if you'd just read it and be less conservative.
>
Alex
>

No, I understand completely. However, most of this blog is not at all
applicable to web pages, which are transactional. They are much more
appropriate to applications which use the same connection for long
periods of time.


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Alex Weber]

Re: Experiences with mysql_pconnect?

On Oct 17, 8:32 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Alex Weber wrote:

On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Alex Weber wrote:
>Jerry Stuckle wrote:
>>John Murtari wrote:
>>>Folks,
>>>        We have a fairly busy web site present using PHP 5 to
>>>talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
>>>spread out over several physical hosts (one DB server), we use
>>>DB tracking of  sessions and there is some transaction usage also..
>>>        Right now we keep it simple using mysql_connect.  I'd
>>>looked at mysql_pconnect a few years but got scared away by the
>>>warnings about problems.   Somebody here wanted to revisit that
>>>issue and I still see warning regarding transaction problems and
>>>dangling connections to the server at the php.net site.
>>>        Is anybody using this on a busy site and finding it
>>>worthwhile ?  I'd appreciate any tips to handle potential problems..
>>>It sounds like it should be a performance boost if configured
>>>properly.
>>>        Thanks.
>>What is "busy"?  10 connections/sec?  100 connections/sec?
>>Yes, there can be problems with using mysql_pconnect( ).  But if you're
>>running hundreds of connections per second, you might find it
>>advantageou s to use it.  You might start with just the SELECT
>>statements, though, to limit transaction problems.  And leave the
>>statements which change the database using non-persistent connections.
>>Just remember - you have to have all the possible persistent connections
>>you might ever need allocated all the time.  For instance, if your
>>average is 100 connections/second but you peak at 1,000 during busy
>>times, you need at least 1,000 persistent connections available at all
>>times.  This can be a drain on the system resources, also.
 John,

>

 My bad for not directly answering your question but I suggest that if
 you're looking to optimize your DB stuff and learn new commands, etc I
 would look into the PDO extension (usually included by default):

>

 >http://www.php.net/manual/en/class.pdo.php

>

 Besides abstracting specific database implementations (ie: same code
 for different DBs) it has good tools for preparing statements,
 persistence, etc.

>

 Using prepared statements will probably speed up your app a lot.

>

 Oh and also, you should really be using "mysqli" instead of "mysql" by
 now! ;)

>

 Alex

>

(top posting fixed)

>

Wrong person to respond to - but actually, tests have shown that
mysqli_xxx and PDO are both slower than mysql_xxx.  Of course, they have
other advantages, but performance is not one of them.

>

Prepared statements are also slower than non-prepared statements,
especially when the server is remote as it requires multiple calls to MySQL.

>

P.S. Please don't top post.  Thanks.

>

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attgl obal.net
=============== ===

>

Jerry,

>

A couple points:
- i disagree about PDO being slower than mysql (not sure about
mysqli), anyway i'd like to see some source to back that up (PDO is a
pure-C class which is inherently faster)

>
The mysql interface is also written in pure-C - and doesn't have
overhead of having to figure out which interface to use.  Try benchmarks
for both of them - you will find PDO is slower.
>

- i completely disagree with "prepared statements are slower than non-
prepared statements".  its all about context.  if you have similar
queries that you execute on a regular basis, preparing it and then
just executing it passing different variables each time is MUCH faster
than manually executing the statement each time.  The ONLY scenario
where a prepared a statement might be slower than a non-prepared
statement is if its an uncommon query (ie: one that you don't execute
regularly or that varies a lot in its syntax)

>
No, it's not "all about context".  The prepared statement and each bind
call results in a call to the database.  This can cause significant
delays if the database is remote.
>
Also, it is very seldom in web apps that you prepare a statement once
and call it multiple times with different bind parameters.  The vast
majority of the calls are one-time only.  If they aren't, you're doing
something seriously wrong in your SQL statements - and increasing
overhead unnecessarily.
>
Remember - in a web app, each page is its own transaction - which is
different from non-web apps.
>

most "generic" queries would greatly benefit from being prepared, for
example:

>

- login: "SELECT id, name FROM users WHERE email = ? AND password =?"
- logging: "INSERT INTO log (event, time) VALUES (?, NOW())"
- etc.

>
And how often do you log in?  More than once per page?
>
The same thing with logging - how often do you actually log something
(other than for debugging purposes)?  Not that often.
>

since those two are queries you would presumably execute regularly the
benefits are that they only need to be parsed once, so after preparing
it every execution is faster.  (it also protects against sql-injection
by avoiding malformed queries)

>
But they are not executed multiple times by one page.  Once the
connection is closed (at the end of the script), the page must be
prepared again.  And don't even think of going to persistent connections
- which have other problems.
>

there's a caveat though: "Using prepared SELECT statements on a MySQL
database prior to MySQL 5.1.17 can lead to SERIOUS performance
degradation." source:http://dev.mysql.com/doc/refman/5.1/...ry-cache..html

>

further reading:http://www.mysqlperformanceblog.com/...epared-stateme...

>

Anyway apologies for the tangent off your original post but there's
good info in here if you'd just read it and be less conservative.

>

Alex

>
No, I understand completely.  However, most of this blog is not at all
applicable to web pages, which are transactional.  They are much more
appropriate to applications which use the same connection for long
periods of time.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attgl obal.net
=============== ===

Fair enough Jerry, I stand corrected.

oh and no, i don't have any experience with mysql_pconnect :P

Alex

[C. (http://symcbean.blogspot.com/)]

Re: Experiences with mysql_pconnect?

On 18 Oct, 00:32, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Alex Weber wrote:

On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Alex Weber wrote:
>Jerry Stuckle wrote:
>>John Murtari wrote:
>>>Folks,

<snip>

>>> Right now we keep it simple using mysql_connect. I'd
>>>looked at mysql_pconnect a few years but got scared away by the
>>>warnings about problems. Somebody here wanted to revisit that
>>>issue and I still see warning regarding transaction problems and
>>>dangling connections to the server at the php.net site.

<snip>

Prepared statements are also slower than non-prepared statements,
especially when the server is remote as it requires multiple calls to MySQL.

>

<snip>

- i completely disagree with "prepared statements are slower than non-
prepared statements". its all about context. if you have similar
queries that you execute on a regular basis, preparing it and then
just executing it passing different variables each time is MUCH faster
than manually executing the statement each time. The ONLY scenario
where a prepared a statement might be slower than a non-prepared
statement is if its an uncommon query (ie: one that you don't execute
regularly or that varies a lot in its syntax)

>
No, it's not "all about context". The prepared statement and each bind
call results in a call to the database. This can cause significant
delays if the database is remote.
>
Also, it is very seldom in web apps that you prepare a statement once
and call it multiple times with different bind parameters. The vast
majority of the calls are one-time only. If they aren't, you're doing
something seriously wrong in your SQL statements - and increasing
overhead unnecessarily.
>

Nearly right.

On some DBMSs the server will cache execution plans and reuse the same
plan for different sessions - and it can sometimes be computationally
and I/O expensive to generate such plans. So there is a potential
performance benefit - however MYSQL DOES NOT CURRENTLY CACHE PLANS.

(the query cache caches results of queries - not execution plans)

To use a prepared statement takes at least 2 round trips to the DBMS -
one to submit the statement, and one to provide the parameters - so
there is a potential LOSS of performance here - at least as much as
any gain from using _pconnect instead of _connect.

There are databases which make use of cached plans - I currently have
to contend with an Oracle based system - if your DBMS (like Oracle)
implement bind-peeking for optimization then sooner or later you're
going to learn about execution plan poisoning - if the plan gets
generated for an edge case based on histogram indices, any subsequent
query using the plan will be far from effective. I've seen it bring
systems to their knees and leaving developers scratching their heads
for weeks.

In the OPs case there's no performance benefit using prepared
statements, but there are losses.

IME the performance benefit of using _pconnect rather than _connect is
very small, and more than outweighed by potential functional issues.
Its very much the same issues as using the PHP execution model vs the
application server way of doing things. I'd really only consider using
_pconnect on MySQL if the DBMS was a long RTT away from the webserver.

I'd start by exhausting all other avenues of investigation into
performance problems - but thats a whole book in itself.

C.

[Jerry Stuckle]

Re: Experiences with mysql_pconnect?

C. (http://symcbean.blogspot.com/) wrote:

On 18 Oct, 00:32, Jerry Stuckle <jstuck...@attg lobal.netwrote:

>Alex Weber wrote:

>>On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:
>>>Alex Weber wrote:
>>>>Jerry Stuckle wrote:
>>>>>John Murtari wrote:
>>>>>>Folks,

<snip>

>>>>>> Right now we keep it simple using mysql_connect. I'd
>>>>>>looked at mysql_pconnect a few years but got scared away by the
>>>>>>warning s about problems. Somebody here wanted to revisit that
>>>>>>issue and I still see warning regarding transaction problems and
>>>>>>danglin g connections to the server at the php.net site.

<snip>

>>>Prepared statements are also slower than non-prepared statements,
>>>especially when the server is remote as it requires multiple calls to MySQL.

<snip>

>>- i completely disagree with "prepared statements are slower than non-
>>prepared statements". its all about context. if you have similar
>>queries that you execute on a regular basis, preparing it and then
>>just executing it passing different variables each time is MUCH faster
>>than manually executing the statement each time. The ONLY scenario
>>where a prepared a statement might be slower than a non-prepared
>>statement is if its an uncommon query (ie: one that you don't execute
>>regularly or that varies a lot in its syntax)

>No, it's not "all about context". The prepared statement and each bind
>call results in a call to the database. This can cause significant
>delays if the database is remote.
>>
>Also, it is very seldom in web apps that you prepare a statement once
>and call it multiple times with different bind parameters. The vast
>majority of the calls are one-time only. If they aren't, you're doing
>something seriously wrong in your SQL statements - and increasing
>overhead unnecessarily.
>>

>
Nearly right.
>
On some DBMSs the server will cache execution plans and reuse the same
plan for different sessions - and it can sometimes be computationally
and I/O expensive to generate such plans. So there is a potential
performance benefit - however MYSQL DOES NOT CURRENTLY CACHE PLANS.
>

Which doesn't change my statement.

(the query cache caches results of queries - not execution plans)
>
To use a prepared statement takes at least 2 round trips to the DBMS -
one to submit the statement, and one to provide the parameters - so
there is a potential LOSS of performance here - at least as much as
any gain from using _pconnect instead of _connect.
>
There are databases which make use of cached plans - I currently have
to contend with an Oracle based system - if your DBMS (like Oracle)
implement bind-peeking for optimization then sooner or later you're
going to learn about execution plan poisoning - if the plan gets
generated for an edge case based on histogram indices, any subsequent
query using the plan will be far from effective. I've seen it bring
systems to their knees and leaving developers scratching their heads
for weeks.
>

Yes, this is a poor design on Oracle's part. Fortunately, other RDBMS's
haven't followed suit. But that doesn't change my comments.

In the OPs case there's no performance benefit using prepared
statements, but there are losses.
>
IME the performance benefit of using _pconnect rather than _connect is
very small, and more than outweighed by potential functional issues.
Its very much the same issues as using the PHP execution model vs the
application server way of doing things. I'd really only consider using
_pconnect on MySQL if the DBMS was a long RTT away from the webserver.
>

Using pconnect can instead of connect can actually hurt performance, for
many reasons.

I'd start by exhausting all other avenues of investigation into
performance problems - but thats a whole book in itself.
>
C.
>

Again, there are some misconceptions in your statements, which I
recommend you discuss in comp.databases. mysql.


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[John Murtari]

Re: Experiences with mysql_pconnect?

Gentleman,

Thanks for a very useful exchange of information. I learned
a lot (and I'm sure others did also) from the discussion.

I think we'll just still with mysql_connect for now.
Best regards!
--
John
_______________ _______________ _______________ _______________ ________
Customer Service Software Workshop Inc.
johnm@thebook.c om "software that fits!" (TM)
Toll Free (877) 635-1968(x-211) http://www.thebook.com/