Does mysql cache strip out /* comments */ first?

Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.

We have a dedicated "NOC" screen that shows the "mytop" status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)

We sometimes see stuck queries and are always hesitant to "kill" them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.

Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.

I prefix ALL SQL (since it goes through my "sql_query()" function) with

/* ${SCRIPTNAME} */

Now all sql in the mytop shows up as:

/* foo.php */ SELECT * FROM foo WHERE id = 1;
/* bar.php */ UPDATE bar SET a = b WHERE id = 2;
Etc...

What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.

My concern is, my gut tells me that the built in mysql cache system is
"dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).

http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html

Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful "feature" (or bug-fix as
the case may be).




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Does mysql cache strip out /* comments */ first?

--0050450158882b89d704954f053e
Content-Type: text/plain; charset=ISO-8859-1

Given that even spacing is important, it's a safe bet that it takes comments
into consideration, too.

Easily tested, though: grab one of the heaviest queries you have from your
slowlog, and execute with identical and different comments.


On Thu, Nov 18, 2010 at 8:31 AM, Daevid Vincent wrote:

>
> Like most developers, I have a wrapper that all of my SQL queries go
> through in PHP.
>
> We have a dedicated "NOC" screen that shows the "mytop" status of each
> DEV/TEST/PROD master/slave pair.
> http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
> remote servers)
>
> We sometimes see stuck queries and are always hesitant to "kill" them off
> because we never know WHO is executing that SQL. Is it a customer? Is it a
> developer? Is it the boss? Is it rogue from some script gone awry? Mytop
> doesn't give the full query due to screen real-estate amongst other
> reasons. The downside is they bog down the server until they eventually
> time-out or complete.
>
> Anyways, today I implemented a simple, transparent and effective step
> towards this puzzle.
>
> I prefix ALL SQL (since it goes through my "sql_query()" function) with
>
> /* ${SCRIPTNAME} */
>
> Now all sql in the mytop shows up as:
>
> /* foo.php */ SELECT * FROM foo WHERE id = 1;
> /* bar.php */ UPDATE bar SET a = b WHERE id = 2;
> Etc...
>
> What I'd REALLY like to do is add more information in there. Perhaps add
> the FUNCTION/METHOD and the logged-in web USER that is actually executing
> that SQL, etc.
>
> My concern is, my gut tells me that the built in mysql cache system is
> "dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to
> strip out comments from the SQL statement string BEFORE storing it as the
> cache hash key (yet I have no facts either way to back it up and hence the
> reason for this email).
>
> http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
> http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
>
> Can anyone please tell me I'm wrong and that it is smarter than I give it
> credit for, as I think this would be a very useful "feature" (or bug-fix as
> the case may be).
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450158882b89d704954f053e--

RE: Does mysql cache strip out /* comments */ first?

I think you will probably find that the code you write isn't what MySQL exe=
cutes or stores in the cache.

________________________________________
From: vegivamp@gmail.com [vegivamp@gmail.com] On Behalf Of ext Johan De Mee=
rsman [vegivamp@tuxera.be]
Sent: 18 November 2010 07:48
To: Daevid Vincent
Cc: mysql
Subject: Re: Does mysql cache strip out /* comments */ first?

Given that even spacing is important, it's a safe bet that it takes comment=
s
into consideration, too.

Easily tested, though: grab one of the heaviest queries you have from your
slowlog, and execute with identical and different comments.


On Thu, Nov 18, 2010 at 8:31 AM, Daevid Vincent wrote:

>
> Like most developers, I have a wrapper that all of my SQL queries go
> through in PHP.
>
> We have a dedicated "NOC" screen that shows the "mytop" status of each
> DEV/TEST/PROD master/slave pair.
> http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
> remote servers)
>
> We sometimes see stuck queries and are always hesitant to "kill" them off
> because we never know WHO is executing that SQL. Is it a customer? Is it =
a
> developer? Is it the boss? Is it rogue from some script gone awry? Mytop
> doesn't give the full query due to screen real-estate amongst other
> reasons. The downside is they bog down the server until they eventually
> time-out or complete.
>
> Anyways, today I implemented a simple, transparent and effective step
> towards this puzzle.
>
> I prefix ALL SQL (since it goes through my "sql_query()" function) with
>
> /* ${SCRIPTNAME} */
>
> Now all sql in the mytop shows up as:
>
> /* foo.php */ SELECT * FROM foo WHERE id =3D 1;
> /* bar.php */ UPDATE bar SET a =3D b WHERE id =3D 2;
> Etc...
>
> What I'd REALLY like to do is add more information in there. Perhaps add
> the FUNCTION/METHOD and the logged-in web USER that is actually executing
> that SQL, etc.
>
> My concern is, my gut tells me that the built in mysql cache system is
> "dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to
> strip out comments from the SQL statement string BEFORE storing it as the
> cache hash key (yet I have no facts either way to back it up and hence th=
e
> reason for this email).
>
> http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
> http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
>
> Can anyone please tell me I'm wrong and that it is smarter than I give it
> credit for, as I think this would be a very useful "feature" (or bug-fix =
as
> the case may be).
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Does mysql cache strip out /* comments */ first?

--00032557364a93220104955085ca
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Nov 18, 2010 at 9:00 AM, wrote:

> I think you will probably find that the code you write isn't what MySQL
> executes or stores in the cache.
>

it is indeed not quite what it executes, but as I understand it the QC index
is *exactly* the string you send (well, hashed presumably), including
spaces, capitalisation and whatnot.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00032557364a93220104955085ca--

Re: Does mysql cache strip out /* comments */ first?

Daevid,


> My concern is, my gut tells me that the built in mysql cache system is
> "dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to
> strip out comments from the SQL statement string BEFORE storing it as the
> cache hash key (yet I have no facts either way to back it up and hence the
> reason for this email).
>
> http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
> http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
>
> Can anyone please tell me I'm wrong and that it is smarter than I give it
> credit for, as I think this would be a very useful "feature" (or bug-fix as
> the case may be).

Your suspicions are correct, the query cache does not strip comments
before storing the statement.

This can however be done in the Percona build.

http://www.percona.com/docs/wiki/percona-server:features:que ry_cache_enhance#query_cache_strip_comments
http://www.percona.com/docs/wiki/percona-server:features:imp lementation_details:details_query_cache_with_comments

Cheers,

Ewen

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Does mysql cache strip out /* comments */ first?

Ewen thank you! You've opened my eyes to something I didn't even know about
and made my "special purpose" tingle.

Have you used Percona personally? What are your opinions/thoughts? If you
haven't used it, I'd be curious why not or what turned you "away" from it?

> -----Original Message-----
> From: Ewen Fortune [mailto:ewen.fortune@gmail.com]
> Sent: Thursday, November 18, 2010 4:56 AM
> To: Daevid Vincent
> Cc: mysql
> Subject: Re: Does mysql cache strip out /* comments */ first?
>
> Daevid,
>
>
> > My concern is, my gut tells me that the built in mysql
> cache system is
> > "dumb". And by that I mean, I suspect that mySQL isn't
> "smart" enough to
> > strip out comments from the SQL statement string BEFORE
> storing it as the
> > cache hash key (yet I have no facts either way to back it
> up and hence the
> > reason for this email).
> >
> > http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
> > http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
> >
> > Can anyone please tell me I'm wrong and that it is smarter
> than I give it
> > credit for, as I think this would be a very useful
> "feature" (or bug-fix as
> > the case may be).
>
> Your suspicions are correct, the query cache does not strip comments
> before storing the statement.
>
> This can however be done in the Percona build.
>
> http://www.percona.com/docs/wiki/percona-server:features:que ry
> _cache_enhance#query_cache_strip_comments
> http://www.percona.com/docs/wiki/percona-server:features:imp le
mentation_details:details_query_cache_with_comments
>
> Cheers,
>
> Ewen
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Does mysql cache strip out /* comments */ first?

Daevid,

On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent wrote:
> Ewen thank you! You've opened my eyes to something I didn't even know about
> and made my "special purpose" tingle.
>
> Have you used Percona personally? What are your opinions/thoughts? If you
> haven't used it, I'd be curious why not or what turned you "away" from it?

I work for Percona :o)
So I think its best someone else chips in.

Ewen

>
>> -----Original Message-----
>> From: Ewen Fortune [mailto:ewen.fortune@gmail.com]
>> Sent: Thursday, November 18, 2010 4:56 AM
>> To: Daevid Vincent
>> Cc: mysql
>> Subject: Re: Does mysql cache strip out /* comments */ first?
>>
>> Daevid,
>>
>>
>> > My concern is, my gut tells me that the built in mysql
>> cache system is
>> > "dumb". And by that I mean, I suspect that mySQL isn't
>> "smart" enough to
>> > strip out comments from the SQL statement string BEFORE
>> storing it as the
>> > cache hash key (yet I have no facts either way to back it
>> up and hence the
>> > reason for this email).
>> >
>> > http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
>> > http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
>> >
>> > Can anyone please tell me I'm wrong and that it is smarter
>> than I give it
>> > credit for, as I think this would be a very useful
>> "feature" (or bug-fix as
>> > the case may be).
>>
>> Your suspicions are correct, the query cache does not strip comments
>> before storing the statement.
>>
>> This can however be done in the Percona build.
>>
>> http://www.percona.com/docs/wiki/percona-server:features:que ry
>> _cache_enhance#query_cache_strip_comments
>> http://www.percona.com/docs/wiki/percona-server:features:imp le
> mentation_details:details_query_cache_with_comments
>>
>> Cheers,
>>
>> Ewen
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Does mysql cache strip out /* comments */ first?

Percona's got a great reputation in the community, and I would have no
qualms using their builds.

On 11/18/10, Ewen Fortune wrote:
> Daevid,
>
> On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent wrote:
>> Ewen thank you! You've opened my eyes to something I didn't even know
>> about
>> and made my "special purpose" tingle.
>>
>> Have you used Percona personally? What are your opinions/thoughts? If you
>> haven't used it, I'd be curious why not or what turned you "away" from it?
>
> I work for Percona :o)
> So I think its best someone else chips in.
>
> Ewen
>
>>
>>> -----Original Message-----
>>> From: Ewen Fortune [mailto:ewen.fortune@gmail.com]
>>> Sent: Thursday, November 18, 2010 4:56 AM
>>> To: Daevid Vincent
>>> Cc: mysql
>>> Subject: Re: Does mysql cache strip out /* comments */ first?
>>>
>>> Daevid,
>>>
>>>
>>> > My concern is, my gut tells me that the built in mysql
>>> cache system is
>>> > "dumb". And by that I mean, I suspect that mySQL isn't
>>> "smart" enough to
>>> > strip out comments from the SQL statement string BEFORE
>>> storing it as the
>>> > cache hash key (yet I have no facts either way to back it
>>> up and hence the
>>> > reason for this email).
>>> >
>>> > http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
>>> > http://dev.mysql.com/tech-resources/articles/mysql-query-cac he.html
>>> >
>>> > Can anyone please tell me I'm wrong and that it is smarter
>>> than I give it
>>> > credit for, as I think this would be a very useful
>>> "feature" (or bug-fix as
>>> > the case may be).
>>>
>>> Your suspicions are correct, the query cache does not strip comments
>>> before storing the statement.
>>>
>>> This can however be done in the Percona build.
>>>
>>> http://www.percona.com/docs/wiki/percona-server:features:que ry
>>> _cache_enhance#query_cache_strip_comments
>>> http://www.percona.com/docs/wiki/percona-server:features:imp le
>> mentation_details:details_query_cache_with_comments
>>>
>>> Cheers,
>>>
>>> Ewen
>>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=waynnlue@gmail.com
>
>

--
Sent from my mobile device

______________________________________________

Waynn Lue
626.429.6412 | waynnlue@gmail.com
Facebook: www.facebook.com/waynn
LinkedIn: www.linkedin.com/in/waynn
______________________________________________

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org