Home  |  Linux  | Mysql  | PHP  | XML
Date:Tue Aug  2 18:27:39 2011
Subject:Re: [PDO] Client side cache for prepared queries
On Tue, Aug 2, 2011 at 5:26 PM, Lester Caine <lester@lsces.co.uk> wrote:
> Julien Palard wrote:
>>
>> On Tue, Aug 2, 2011 at 3:17 PM, Ferenc Kovacs<tyra3l@gmail.com>  wrote:
>>>
>>> if you can use persistent connection, the performance gain will
>>> accumulate between your requests.
>>
>> If and only if the undocumented client side cache really exists, if
>> calling pdo::prepare create a new one each time, there are no
>> performance gain at all albeit persistent connection are used, maybe
>> should I search in the source code to confirm it :P
>
> The base premiss of PDO is that all the core functions work identically for
> any database. MySQL is coming late into the game on prepared queries, and
> other databases do things differently,

"Support for server-side prepared statements was added in MySQL 4.1"
that was released in 2004 October,
PDO was around 0.1.1 beta, PDO_MYSQL at 0.1 alpha at that time, so
getting into the game is irrevelant for PDO imo.

> so if you want MySQL type operation,
> then it may be better to switch to the generic driver? In the case of
> Firebird and Oracle, the preparation has to be done on the at the server
> end, since only the parameters will be updated later, and the query has to
> be handled in the context of a transaction,

mysql_stmt_prepare/mysql_stmt_execute also works that way, or did I
misunderstood something?

> so actually making some of this
> persistent at all causes problems when many users are holding transactions
> open without any end in sight. While the connection can be held persistent,
> holding a transaction open is a no-no, and so anything prepared within that
> transaction also has to be closed.

thats acceptable, http://www.php.net/manual/en/pdo.transactions.php
also mentions that active transactions will be rolled back at the end
of the script execution.
and there was a good sum-up from Christopher Jones:
http://news.php.net/php.pdo/300

but you can still lock out yourself from a table(Locking a table from
a persistent connection then forgot to unlock before the end of the
execution), or you can have temporary tables lingering, or if you
change some mysql session variable, and forget that it will be a
permanent change for that permanent connection, etc.
so rollbacking the transaction fixes only just a small percent of the
problems that the persistent connections can cause in the wrong hands.

to properly reset a re-used mysql connection is calling the
mysql_change_user(), as it is also done in mysqli
http://php.net/manual/en/mysqli.persistconns.php
AFAIK we don't do this for ext/mysql or PDO.
if we would, the whole topic would be moot, as the prepared statements
are also released if mysql_change_user() is called:

"This command resets the state as if one had done a new connect. (See
always performs a ROLLBACK of any active transactions, closes and
drops all temporary tables, and unlocks all locked tables. Session
system variables are reset to the values of the corresponding global
system variables. Prepared statements are released and HANDLER
variables are closed. Locks acquired with GET_LOCK() are released.
These effects occur even if the user didn't change."
http://dev.mysql.com/doc/refman/5.1/en/mysql-change-user.html

> In these cases we rely on the internal
> caching within the database engine to speed things up, and asking the same
> query will pull data from memory normally, but with a current view based on
> what has happened in other transactions since.
>

yeah, usually running the same query multiple times is fast, either if
the db has some kind of cache like the query cache in mysql, or
expecting that the affected tables/rows are still in memory, as they
were used recently.
but this has nothing to do with prepared statements, but maybe it can
be a reason why was the emulated parameter binding used instead of the
native mysql prepared statements.

--
Ferenc Kovács
@Tyr43l - http://tyrael.hu
Navigate in group php.pdo at sever news.php.net
Previous Next





  
© No Copyright
You are free to use Anything, but please consult your advocate before doing so as this website
also list content from other sources which may be copyrighted.