I have been trying to compute query cache utilization in mysql 5 but cannot because the com_select status variable is always 1 when I start a new mysql session. This probably holds for all the com_* variables and maybe others, but I've only been working with com_select. They're supposed to be cumulative and reset only when you explicitly reset status or bounce the server.
Here's an example, showing the tail end of a test mysql session showing the value of com_select when I exited, and the value a few seconds later when I began a new mysql session. This was on my own test server, no one else was on to reset status. It repeats every time I try it: ### BEGIN SESSION mysql> show status like 'com_select'; -------------- show status like 'com_select' -------------- +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 4 | +---------------+-------+ 1 row in set (0.01 sec) mysql> quit Bye > mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 94774 Server version: 5.0.45-community-log MySQL Community Edition (GPL) Reading history-file /home/jlyons/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show status like 'com_select'; -------------- show status like 'com_select' -------------- +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+ 1 row in set (0.01 sec) mysql> ### END SESSION Note how com_select is 4 in the first session, then is reset to 1. I tried this on Linux RHEL, Linux RH 5 community (as shown here) and a Windows mysql 5 platform. Heres' the script I ran: drop table if exists t; create table t (x serial); # put some data in insert into t values (null); insert into t values (null); insert into t values (null); insert into t values (null); # create some selects select * from t where x = 1; select * from t where x = 2; select * from t where x = 3; select * from t where x = 4; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; show status like 'qcache_hits'; show status like 'com_select'; When I ran the same script on a Windows mysql 4 version, the value of com_select persisted over the login, which is what it should. Is this a bug in mysql 5? Is something set incorrectly in my config file that would cause this (I can't find anything)? Thanks for any help. -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
