Combing PDO with mysql_pconnect connections in one application --will this degrade performance?

Hi,

We are about to convert all of our queries using mysql_pconnect to
prepared statements using PDO database connections. It will take some
time to convert the hundreds of SQL statements so the plan is to do it
in phases. Is it a bad idea to have both a mysql_pconnect and a PDO
connection open for the same application? Will this doubling of our
database connections for every page hit degrade performance? Any tips
on the best way to transition to PDO/prepared statements with minimal
disruption?

-Sara




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Sara Leavitt wrote:
> Hi,
>
> We are about to convert all of our queries using mysql_pconnect to
> prepared statements using PDO database connections. It will take some
> time to convert the hundreds of SQL statements so the plan is to do it
> in phases. Is it a bad idea to have both a mysql_pconnect and a PDO
> connection open for the same application? Will this doubling of our
> database connections for every page hit degrade performance? Any tips
> on the best way to transition to PDO/prepared statements with minimal
> disruption?

As long as you test it (hopefully using some sort of automated tests)
you should be right.

Since you're using persistent connections (make sure you set pdo to do
the same), I don't think you're really doubling the number of connections.

Check your mysql logs to see if you are doing double connections or not
- that way you'll know for sure.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?

--Apple-Mail-7-770625751
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii


>=20
> As long as you test it (hopefully using some sort of automated tests) =
you should be right.
>=20
> Since you're using persistent connections (make sure you set pdo to do =
the same), I don't think you're really doubling the number of =
connections.

Yeah, I think PDO uses the underlying mysql/pgsql functions anyway - it =
just provides a common API layer across all database types.

Therefore if you call mysql_pconnect outside of PDO, then call the same =
database server with PDO, technically it should realise the connection =
has already been made to that server and use your previous connection.

As Chris pointed out, make sure PDO will also use persistent =
connections.

Regards,
Andy



--Apple-Mail-7-770625751--

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Sara Leavitt wrote:
> Hi,
>
> We are about to convert all of our queries using mysql_pconnect to
> prepared statements using PDO database connections. It will take some
> time to convert the hundreds of SQL statements so the plan is to do it
> in phases. Is it a bad idea to have both a mysql_pconnect and a PDO
> connection open for the same application? Will this doubling of our
> database connections for every page hit degrade performance? Any tips
> on the best way to transition to PDO/prepared statements with minimal
> disruption?

One thing to be careful is if you are relying on 'transactions' to handle
anything. Obviously the transaction has to be in the same connection just to
work. Despite what others have said, the PDO connection will be different to the
generic mysql connection as it is a separate process. Persistent connections
will only reuse an already open connection, you can't connect two processes to
the same connection. You would not know which process was accessing things.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Hi Lester,

Our application is not using php transactions (e.g., COMMIT, ROLLBACK
etc.), so I think we are safe there. Are you saying that even if both
the mysql_pconnect and PDO connection are persistent that they will be
completely separate? (Havn't had a chance to test this empirically just
yet.)

Thanks, Sara

Lester Caine wrote:
> Sara Leavitt wrote:
>> Hi,
>>
>> We are about to convert all of our queries using mysql_pconnect to
>> prepared statements using PDO database connections. It will take some
>> time to convert the hundreds of SQL statements so the plan is to do it
>> in phases. Is it a bad idea to have both a mysql_pconnect and a PDO
>> connection open for the same application? Will this doubling of our
>> database connections for every page hit degrade performance? Any tips
>> on the best way to transition to PDO/prepared statements with minimal
>> disruption?
>
> One thing to be careful is if you are relying on 'transactions' to
> handle anything. Obviously the transaction has to be in the same
> connection just to work. Despite what others have said, the PDO
> connection will be different to the generic mysql connection as it is
> a separate process. Persistent connections will only reuse an already
> open connection, you can't connect two processes to the same
> connection. You would not know which process was accessing things.
>


--
Sara Leavitt, UC Berkeley Event Calendar Coordinator
Public Affairs, University of California
2200 Bancroft Way
Berkeley, CA 94720-4204
Phone: 510 643-6163
http://events.berkeley.edu


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Sara Leavitt wrote:
> Hi Lester,
>
> Our application is not using php transactions (e.g., COMMIT, ROLLBACK
> etc.), so I think we are safe there. Are you saying that even if both
> the mysql_pconnect and PDO connection are persistent that they will be
> completely separate? (Havn't had a chance to test this empirically just
> yet.)

Yes ... I'm not totally sure about how MySQL works by default, but having
'committed' data on one connection, one would need to refresh the other
connection possibly to see that data. Firebird is a lot more manageable on
transaction control, and basically data entered on on connection will not be
visible on the other until properly committed. Having that activity hidden in
the drivers can lead to a little 'confusion' as to what state data is actually
in ....

Even just opening two connections via the MySQL driver will have the same effect.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

So I can see that using two connections for updating might get out of
synch; however, mixing the connections should be OK for a series of
SELECT queries on the read-only side of the application, right?

-Sara

Lester Caine wrote:
> Sara Leavitt wrote:
>> Hi Lester,
>>
>> Our application is not using php transactions (e.g., COMMIT, ROLLBACK
>> etc.), so I think we are safe there. Are you saying that even if
>> both the mysql_pconnect and PDO connection are persistent that they
>> will be completely separate? (Havn't had a chance to test this
>> empirically just yet.)
>
> Yes ... I'm not totally sure about how MySQL works by default, but
> having 'committed' data on one connection, one would need to refresh
> the other connection possibly to see that data. Firebird is a lot more
> manageable on transaction control, and basically data entered on on
> connection will not be visible on the other until properly committed.
> Having that activity hidden in the drivers can lead to a little
> 'confusion' as to what state data is actually in ....
>
> Even just opening two connections via the MySQL driver will have the
> same effect.
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Sara Leavitt wrote:
> So I can see that using two connections for updating might get out of
> synch; however, mixing the connections should be OK for a series of
> SELECT queries on the read-only side of the application, right?

Yes - if you are only reading already stored data there should not be a problem.

> -Sara
>
> Lester Caine wrote:
>> Sara Leavitt wrote:
>>> Hi Lester,
>>>
>>> Our application is not using php transactions (e.g., COMMIT, ROLLBACK
>>> etc.), so I think we are safe there. Are you saying that even if
>>> both the mysql_pconnect and PDO connection are persistent that they
>>> will be completely separate? (Havn't had a chance to test this
>>> empirically just yet.)
>>
>> Yes ... I'm not totally sure about how MySQL works by default, but
>> having 'committed' data on one connection, one would need to refresh
>> the other connection possibly to see that data. Firebird is a lot more
>> manageable on transaction control, and basically data entered on on
>> connection will not be visible on the other until properly committed.
>> Having that activity hidden in the drivers can lead to a little
>> 'confusion' as to what state data is actually in ....
>>
>> Even just opening two connections via the MySQL driver will have the
>> same effect.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?

--Apple-Mail-6-845394322
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii


>=20
> One thing to be careful is if you are relying on 'transactions' to =
handle anything. Obviously the transaction has to be in the same =
connection just to work. Despite what others have said, the PDO =
connection will be different to the generic mysql connection as it is a =
separate process. Persistent connections will only reuse an already open =
connection, you can't connect two processes to the same connection. You =
would not know which process was accessing things.

According to the documentation:

"First, when connecting, the function would first try to find a =
(persistent) link that's already open with the same host, username and =
password. If one is found, an identifier for it will be returned instead =
of opening a new connection."

Therefore, providing you've configured PHP's --with-mysql and =
--with-pdo-mysql options with the same MySQL library, then as long as =
the host, username and password are the same, the same connection will =
be re-used for both the native (mysql_pconnect) connection and the PDO =
connection.

I'm not sure about the mysqli library, but I believe that lies beneath =
the mysql_* family of functions anyway so it shouldn't make a difference =
whether you're using libmysqlclient or mysqli.

Regards,
Andy=

--Apple-Mail-6-845394322--

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Andy Shellam (Mailing Lists) wrote:
> "First, when connecting, the function would first try to find a
> (persistent) link that's already open with the same host, username
> and password. If one is found, an identifier for it will be returned
> instead of opening a new connection."
>
> Therefore, providing you've configured PHP's --with-mysql and
> --with-pdo-mysql options with the same MySQL library, then as long
> as the host, username and password are the same, the same connection
> will be re-used for both the native (mysql_pconnect) connection and
> the PDO connection.

The doc refers to the PHP end of the connection and only to any
existing PDO connection. PDO uses a hash table for open connections
which it scans for matches when a PDO connection call is executed.
The hash key is unique and begins with "PDO:DBH:DSN=". The mysql
extension uses "mysql_" for its hash key prefix. This mean the PHP
data structures for persistent connections won't be shared between the
two extensions.

Whether the MySQL client library or the MySQL database reuses any
connection data underneath, I don't know. In Oracle the answer would
be that PDO_OCI and OCI8 connections are distinct and transactionally
separate (though they could share the same database connection pool if
one was enabled).

Chris

--
Blog: http://blogs.oracle.com/opal
Twitter: http://twitter.com/ghrd

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

How about combining MySQLi with mysql_pconnect? Assuming both are
persistent, would those connections be shared?

The goal here is to use prepared statements. I read that MySQLi only
supports persistent connections in php version 5.3 which we are not yet
running, but perhaps it would be worth waiting for 5.3.

-Sara


Christopher Jones wrote:
>
>
> Andy Shellam (Mailing Lists) wrote:
> > "First, when connecting, the function would first try to find a
> > (persistent) link that's already open with the same host, username
> > and password. If one is found, an identifier for it will be returned
> > instead of opening a new connection."
> >
> > Therefore, providing you've configured PHP's --with-mysql and
> > --with-pdo-mysql options with the same MySQL library, then as long
> > as the host, username and password are the same, the same connection
> > will be re-used for both the native (mysql_pconnect) connection and
> > the PDO connection.
>
> The doc refers to the PHP end of the connection and only to any
> existing PDO connection. PDO uses a hash table for open connections
> which it scans for matches when a PDO connection call is executed.
> The hash key is unique and begins with "PDO:DBH:DSN=". The mysql
> extension uses "mysql_" for its hash key prefix. This mean the PHP
> data structures for persistent connections won't be shared between the
> two extensions.
>
> Whether the MySQL client library or the MySQL database reuses any
> connection data underneath, I don't know. In Oracle the answer would
> be that PDO_OCI and OCI8 connections are distinct and transactionally
> separate (though they could share the same database connection pool if
> one was enabled).
>
> Chris
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?

Sara Leavitt wrote:
> How about combining MySQLi with mysql_pconnect? Assuming both are
> persistent, would those connections be shared?
> The goal here is to use prepared statements. I read that MySQLi only
> supports persistent connections in php version 5.3 which we are not yet
> running, but perhaps it would be worth waiting for 5.3.
>
> -Sara

The mysqli extension appears to use "mysqli_" as the hash key prefix
so there won't be PHP connection structure sharing here either.

Chris


>
>
> Christopher Jones wrote:
>>
>>
>> Andy Shellam (Mailing Lists) wrote:
>> > "First, when connecting, the function would first try to find a
>> > (persistent) link that's already open with the same host, username
>> > and password. If one is found, an identifier for it will be returned
>> > instead of opening a new connection."
>> >
>> > Therefore, providing you've configured PHP's --with-mysql and
>> > --with-pdo-mysql options with the same MySQL library, then as long
>> > as the host, username and password are the same, the same connection
>> > will be re-used for both the native (mysql_pconnect) connection and
>> > the PDO connection.
>>
>> The doc refers to the PHP end of the connection and only to any
>> existing PDO connection. PDO uses a hash table for open connections
>> which it scans for matches when a PDO connection call is executed.
>> The hash key is unique and begins with "PDO:DBH:DSN=". The mysql
>> extension uses "mysql_" for its hash key prefix. This mean the PHP
>> data structures for persistent connections won't be shared between the
>> two extensions.
>>
>> Whether the MySQL client library or the MySQL database reuses any
>> connection data underneath, I don't know. In Oracle the answer would
>> be that PDO_OCI and OCI8 connections are distinct and transactionally
>> separate (though they could share the same database connection pool if
>> one was enabled).
>>
>> Chris
>>

--
Blog: http://blogs.oracle.com/opal
Twitter: http://twitter.com/ghrd

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php