Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:52102
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain thelounge.net designates 91.118.73.15 as permitted sender)
Message-ID: <4DBC32B1.7000806@thelounge.net>
Date: Sat, 30 Apr 2011 18:02:57 +0200
Organization: the lounge interactive design
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc14 Lightning/1.0b3pre Thunderbird/3.1.10
MIME-Version: 1.0
To: internals@lists.php.net
References: <BANLkTimtRdY_+Jj46jFEqkseuz7WD1ZPZQ@mail.gmail.com>	<4DBC2D1B.10302@lerdorf.com> <BANLkTik2QKUKcSXzL-XYimokph3XPUmpcQ@mail.gmail.com>
In-Reply-To: <BANLkTik2QKUKcSXzL-XYimokph3XPUmpcQ@mail.gmail.com>
OpenPGP: id=7F780279;
	url=http://arrakis.thelounge.net/gpg/h.reindl_thelounge.net.pub.txt
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigCB05C776FAFD547EE1A2A668"
Subject: Re: [PHP-DEV] Change Request: Make PDO default to not emulate prepared
 statements for MySQL
From: h.reindl@thelounge.net (Reindl Harald)

--------------enigCB05C776FAFD547EE1A2A668
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable


Am 30.04.2011 17:45, schrieb Ferenc Kovacs:
> On Sat, Apr 30, 2011 at 5:39 PM, Rasmus Lerdorf <rasmus@lerdorf.com> wr=
ote:
>> Do you realize why we did this in the first place? The common versions=
 of
>> MySQL in use out there are not very clever when it comes to the native=

>> prepared statement handling. First, there is no prepared statement cac=
he, so
>> there is no benefit to doing them natively, but worse, when you use a =
native
>> prepared statement you completely miss the query result cache. As a re=
sult
>> emulated prepared statements are either the same speed or faster than =
the
>> native ones. Changing this default would result in a performance hit f=
or
>> most people. It should be better documented, but that is the only prob=
lem I
>> see here.
>>
> I disable query_cache on my machines, because it can cause performance =
and
> stability issues.
> http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlqu=
ery-cache-a-second-chance/

i guess you must have some really strange things in your applications

> which can take a lot of time if you have millions of queries cache for =
given table

makes me sure you do something wrong and not know the SQL_NO:_CACHE hints=
 for disable
caching of queries from them you know that they can not benefit or with r=
eally
small results with the wehre-statemant on the primary key where the cache=
 is
not faster as the normal query and I/O does not matter because the result=
 size
_______________________

we are using mysql-query-cache on all servers with some hundret domains
since years and as long the whole generate time of a dynamic page
is done between 0.008 and 0.011 seconds there is no performance issue

disable the query cache would degrade the whole box 10 years back
and even on a dbmail-mailserver with innodb-backend the qc improve
performance dramatically

[--] Reads / Writes: 68% / 32%
[--] Total buffers: 2.4G global + 3.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 3.0G (37% of installed RAM)
[OK] Slow queries: 0% (3/27M)
[OK] Highest usage of available connections: 17% (35/200)
[OK] Key buffer size / total MyISAM indexes: 256.0M/106.8M
[OK] Key buffer hit rate: 99.9% (109M cached / 126K reads)
[OK] Query cache efficiency: 89.6% (20M cached / 23M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (924 temp sorts / 210K sorts)


--------------enigCB05C776FAFD547EE1A2A668
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk28MrEACgkQhmBjz394AnldhgCfbktMfOfUyIVGDxboHMVGWqOj
bZcAmgKSWDxwoKbB2zU0UsfLjVHRmQTw
=ltQk
-----END PGP SIGNATURE-----

--------------enigCB05C776FAFD547EE1A2A668--