Combing PDO with mysql_pconnect connections in one application-- will this degrade performance?
in [Databases]
|
Prev: Combing PDO with mysql_pconnect connections in one application --will this degrade performance?
Next: Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
From: Chris on 9 Dec 2009 17:12 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/
From: Lester Caine on 10 Dec 2009 03:21 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
From: Sara Leavitt on 10 Dec 2009 12:30 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
From: Lester Caine on 10 Dec 2009 12:47 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
From: Sara Leavitt on 10 Dec 2009 13:00
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. > |