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 dae...@daevid.com 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?
On Thu, Nov 18, 2010 at 9:00 AM, andrew.2.mo...@nokia.com 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?
Daevid, snip 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?
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, snip 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, On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent dae...@daevid.com 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, snip 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?
Percona's got a great reputation in the community, and I would have no qualms using their builds. On 11/18/10, Ewen Fortune ewen.fort...@gmail.com wrote: Daevid, On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent dae...@daevid.com 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, snip 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
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-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=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 dae...@daevid.com 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
