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
