MySQL Bugs: #30436: MySQL Key_Reads variable seems to be wrong

Bug #30436	MySQL Key_Reads variable seems to be wrong
Submitted:	15 Aug 2007 13:48	Modified:	29 Mar 2008 8:28
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
I'm running the group by query on the table with no indexes which does not flush key blocks to the key file according to the show status however it shows there are some key reads:

mysql> show global status like "key%";
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 37629   |
| Key_blocks_unused      | 198873  |
| Key_blocks_used        | 60588   |
| Key_read_requests      | 3229320 |
| Key_reads              | 37629   |
| Key_write_requests     | 664049  |
| Key_writes             | 0       |
+------------------------+---------+
7 rows in set (0.00 sec)

The status was flushed before running the query.

How to repeat:
CREATE TABLE `gtest` (
  `i` int(10) unsigned NOT NULL,
  `c` char(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

select count(*) cnt,c from gtest group by c order by null limit 10;

Just populate table with some random data

Thank you for the bug report. I wasn't able to repeat with current source
server any clue how to repeat?:

mysql> show global status like "key%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6698  |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
7 rows in set (0.01 sec)

mysql> select count(*) cnt,c from gtest group by c order by null limit 10;
+---------+--------------+
| cnt     | c            |
+---------+--------------+
| 5767168 | ssssdsddsdds |
+---------+--------------+
1 row in set (14.32 sec)

mysql> show global status like "key%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6698  |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
7 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.48-debug |
+--------------+
1 row in set (0.00 sec)

mysql>

Miguel in your case I do not see any key writes at all which must be where in case on disk temporary table is used.

I suspect in your case table is small enough (contains small enough value of distinct values) so it fits in in-memory table.

Thank you for the bug report.

[miguel@skybr 5.0]$ bin/mysql -uroot  test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global status like "key%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     | 
| Key_blocks_unused      | 7248  | 
| Key_blocks_used        | 0     | 
| Key_read_requests      | 0     | 
| Key_reads              | 0     | 
| Key_write_requests     | 0     | 
| Key_writes             | 0     | 
+------------------------+-------+
7 rows in set (0.00 sec)

mysql> select count(*) cnt,c from gtest group by c order by null limit 10;
+-----+--------+
| cnt | c      |
+-----+--------+
|   1 | 11873  | 
|   1 | 23747  | 
|   1 | 35621  | 
|   1 | 47495  | 
|   1 | 59369  | 
|   1 | 71243  | 
|   1 | 83117  | 
|   1 | 94991  | 
|   1 | 106865 | 
|   1 | 118739 | 
+-----+--------+
10 rows in set (1 min 6.32 sec)

mysql> show global status like "key%";
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 0       | 
| Key_blocks_unused      | 7248    | 
| Key_blocks_used        | 7248    | 
| Key_read_requests      | 3938841 | 
| Key_reads              | 57297   | 
| Key_write_requests     | 1038022 | 
| Key_writes             | 49985   | 
+------------------------+---------+
7 rows in set (0.00 sec)

mysql> 

Application to create and populate the table:

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"192.168.0.125"
#define DB_USER			"miguel" 
#define DB_PASSW		"lookingbug"
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	NULL

#define QUERY_CREATE "CREATE TABLE IF NOT EXISTS gtest (i int unsigned NOT NULL, c char(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1"

void main( void )
{
  MYSQL mysql;
  char query[1024];
  int i;
  time_t currentTime;
  time( &currentTime );

  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected with server: %s\n",mysql_get_server_info(&mysql));

  if (mysql_query( &mysql,QUERY_CREATE) )
  {
    printf("Error (query): %s\n", mysql_error( &mysql ));
    mysql_close( &mysql );
    return;
  }
  else
	  printf("Created the table: gtest!\n");

  for( i = 1; i <= 1000000; i++ )
  {
    sprintf( query,"INSERT INTO gtest VALUES ('%lu','%lu') "  "",
                                i,( currentTime * i ) / 100000 );

	if (mysql_query( &mysql,query) )
    {
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
      return;
    }
  }
  
   printf("One million rows inserted!\n");
   mysql_close( &mysql );
}

Miguel,

You did not repeat the problem becuase in your case there are key writes so temporary table index does not fit in allocated key buffer.  As there are writes there should be also reads.

Set key buffer high enough to get no writes - in this case there should be no reads from the disk as well as everything should be in memory.

As far as I can see, we do not have sufficient information, how to repeat this.

It does not seem to work with too little data and not with too much data. We need more specific information, how to load the table and how big the keycache needs to be in this case.

In 5.1.19 we have a big update to the key cache. Can you please check if the counts are ok in this (or later) version?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".