Description:
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.
How to repeat:
Run this script:
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 'com_select';
quit;
Notice the value for com_select;
Once you are back at the operating system prompt, enter mysql again and enter:
show status like 'com_select';
It's reset to 1.
