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.fort...@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-cache.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:query >>> _cache_enhance#query_cache_strip_comments >>> http://www.percona.com/docs/wiki/percona-server:features:imple >> 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=waynn...@gmail.com > > -- Sent from my mobile device __ Waynn Lue 626.429.6412 | waynn...@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=arch...@jab.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.fort...@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-cache.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:query >> _cache_enhance#query_cache_strip_comments >> http://www.percona.com/docs/wiki/percona-server:features:imple > 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=arch...@jab.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.fort...@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-cache.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:query > _cache_enhance#query_cache_strip_comments > http://www.percona.com/docs/wiki/percona-server:features:imple 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=arch...@jab.org
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-cache.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:query_cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:implementation_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=arch...@jab.org
Re: Does mysql cache strip out /* comments */ first?
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
RE: Does mysql cache strip out /* comments */ first?
I think you will probably find that the code you write isn't what MySQL
executes or stores in the cache.
From: vegiv...@gmail.com [vegiv...@gmail.com] On Behalf Of ext Johan De
Meersman [vegiv...@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 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-cache.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=vegiv...@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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql cache strip out /* comments */ first?
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-cache.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=vegiv...@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
