Vincent de Lau schrieb:
-----Oorspronkelijk bericht-----
Van: [email protected] [mailto:[email protected]]
Verzonden: woensdag 24 februari 2010 18:23
That blog posting is puzzling me, if not to say, I believe it is
misleading.
Hector Virgen schrieb:
Not the most scientific analysis but this guy seems to get better
performance out of PDO:
Any of the PHP APIs are just tiny wrappers on top of the underlying C
libraries. Mapping from the C call to a PHP call is always about about
equally fast no matter how you do the mapping - in ext/mysql, ext/mysqli
or PDO_MYSQL. If you compare equivalent PHP API calls with each other
they should be very, very close together in performance.
I assume the blog posting is comparing apples and oranges, likely
without the blog author being aware of it. For example, PDO is using a
prepared statement emulation by default for MySQL. It could well be that
the blog posting compares a native prepared statement and a non-prepared
statement. Or, the blog posting has been written in 2009, it could be
that for PDO persistent connections had been used whereas for ext/mysqli
non-persistent connections had been used.
Moral is, forget about API performance. There's not much to squeeze out
of how you map the very same underlying C library calls into PHP API
calls. But it can help a lot to use proper API calls for the task.
One thing I think you should keep in mind with this, is that there is a
layer in between which is Zend_Db. Although the bare PHP speed might be
equal, it might well be that the PDO_MYSQL aligns better with the Zend_Db
architecture then ext/mysqli.
Vincent,
that is a good and important point.
Look, I'm no longer a PHP application developer myself. Back in the past
I loved all those abstraction layers so much. Nowadays, I am the test
slave of Andrey Hristov, the only person in the world that knows the C
implementation of mysqlnd inside out. Therefore, I'm tend to look at
things from an implementors and not from a users perspective.
Let's you issue a query "SELECT a FROM test WHERE b = 1 LIMIT :myvalue"
with PDO_MYSQL. By default PDO will use its prepared statement emulation
to map this query into a non-prepared statement. It will do placeholder
substitution and map your PHP call into something like this (pseudo
C-API calls):
mysql_query("SELECT a FROM test WHERE b = 1 LIMIT 2")
No more. OK, I'm omitting some string escape calls because they are
irrelevant for our discussion. On the line you get:
PHP sends query to MySQL --> MySQL runs query
PHP buffers all results <-- MySQL sends result
Now, let's say you do the same with ext/mysqli. First, you need to use
"SELECT a FROM test WHERE b 1 LIMIT ?" because MySQLs prepared statement
syntax is not aware of named parameters. Second, you'll have to do a lot
more steps. Let's look at the pseudo C-API calls again:
stmt = mysql_stmt_init()
mysql_stmt_prepare("SELECT a FROM test WHERE b = 1 LIMIT ?")
mysql_stmt_bind_param(1, 2)
mysql_stmt_execute()
Who will this look on the line?
PHP acquires a statement handler
PHP asks MySQL to prepare the statement --> MySQL checks the SQL
PHP receives OK <-- MySQL tells PHP SQL is OK
PHP binds variables and sends variables --> MySQL runs query
PHP retrieves results <-- MySQL sends results
As you can see, you already got two round trips from the client to the
server versus one above. But there are more differences: buffered versus
unbuffered results. Native prepared statement results are unbuffered by
default. Depending on your code that can make a significant difference.
Now, can you tell that ext/mysqli is less good, less adapted for
Zend_Db? There ain't no clear answer to this. If you do not really need
prepared statements, why do you use it: what is there in "SELECT a FROM
test WHERE b = 1 LIMIT 2" that forces you to use prepared statements?
From a server perspective prepared statements are more complex to
handle. You need internal prepare structures, you need to keep record of
the handles, you get the hassle with people not freeing handles timely
and so on.
Note that I am not saying you must not use prepared statements. Nor am I
saying Zend_Db is broken by design or something.
So, in the above example PDO ended up with a stellar result: 50% less
round trips, if not more due to buffered vs. unbuffered result sets.
Unfortunately, you can find many bug reports on PDO and LIMIT. How come?
The PDO emulation does not know that MySQL wants you to provide a number
after LIMIT. PDO may go for a safe default and do "SELECT a FROM test
WHERE b 1 LIMIT :myvalue" -> "SELECT a FROM test WHERE b 1 LIMIT '2'" -
eeek, no string allowed here. Of course, the workaround is
straightforward and easy: you tell PDO its a number not a string.
Continue searching bug reports and you will learn that there are more
such issues. Am I saying PDO is broken, well, yes, I don't like the
emulation. I hate it. In particular I hate the support for stone-age
MySQL versions in PDO_MYSQL --- calm don't that's my private view, it is
nothing you, as a PHP application developer need to take care of. One
last comment on this: Brian and Lukas have written excellent blog
postings why we neither want PS emulations like PDO nor native prepared
statements in todays web applications. Thing is, no database vendor I am
aware of has a good offering for you.
What is the moral of the story? There is no simple black and white, good
and bad, less or better adopted to Zend_Db. It is really important to
know what happens behing the scene to pick the correct tool and API call.
Yes, I believe the blog author that he has observed the figures he had
posted. But, yes, I believe also that different, cleverer PHP coding may
align figures in a way that performance differences between ext/mysql,
ext/mysqli and PDO_MYSQL become irrelevant.
When benchmarking, keep an eye on:
persistent vs. non-persistent connections
(persistent with and without change_user)
buffered vs. unbuffered result sets
native prepared statement vs. non prepared statements
Ulf
--
Ulf Wendel, MySQL
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
