Bug #3144 possible problem with query_cache during running of TPC-C test

• Submitted: 11 Mar 2004 8:58
• Modified: 18 Mar 2004 14:10
• Reporter: Alexey Stroganov
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1
• OS: Linux (Linux)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[11 Mar 2004 8:58] Alexey Stroganov

Description:
I'm running TPC-C test with MySQL 4.1 with query cache enabled.
All tables are constantly changed so amount of queries in query cache
shall be low, however it is high and constantly growing.   I suspect
there is a bug where....

| Open_tables              | 133       |
| Open_files               | 1         |
| Open_streams             | 0         |
| Opened_tables            | 133       |
| Questions                | 4307836   |
| Qcache_queries_in_cache  | 60378     |
| Qcache_inserts           | 132095    |
| Qcache_hits              | 620510    |
| Qcache_lowmem_prunes     | 0         |
| Qcache_not_cached        | 64958     |
| Qcache_free_memory       | 72351128  |
| Qcache_free_blocks       | 1         |
| Qcache_total_blocks      | 120759    |

A while afterwards

| Questions                | 4669866   |
| Qcache_queries_in_cache  | 61701     |
| Qcache_inserts           | 135336    |
| Qcache_hits              | 676856    |
| Qcache_lowmem_prunes     | 0         |
| Qcache_not_cached        | 70649     |
| Qcache_free_memory       | 70996216  |
| Qcache_free_blocks       | 1         |
| Qcache_total_blocks      | 123404    |

Look also it is growing concurrently and as free blocks is always "1"
it looks like content is never invalidated.

| Opened_tables            | 137        |
| Questions                | 5371118    |
| Qcache_queries_in_cache  | 63891      |
| Qcache_inserts           | 138835     |
| Qcache_hits              | 784742     |
| Qcache_lowmem_prunes     | 0          |
| Qcache_not_cached        | 81342      |
| Qcache_free_memory       | 68753656   |
| Qcache_free_blocks       | 1          |
| Qcache_total_blocks      | 127784     |

So I suspect there might be some problem here.
I was using MySQL 4.1.1 , Innodb, Transactions (BEGIN/END)

How to repeat:
just run tpc-c test.

I will send TPC-C test and instruction how to run it directly to Sanja or anyone who interests.

[11 Mar 2004 9:22] Oleksandr Byelkin

Thank you for bugreport. 
 
But we need fully repeateble test suite.

[11 Mar 2004 9:46] Alexey Stroganov

It is possible to look at bug closer on quadxeon box. There test was installed and configured and one just can run/debug it.

[18 Mar 2004 14:10] Oleksandr Byelkin

that queries are really in the query cache (some tables is not changed) 
 
1 free memory block is natural whan number of  qweries grows (but sometimes it 
was not 1 for me) 
 
here is my desults after test: 
 
ysql> show status like "Qcache%"; 
+-------------------------+---------+ 
| Variable_name           | Value   | 
+-------------------------+---------+ 
| Qcache_queries_in_cache | 1558    | 
| Qcache_inserts          | 2578    | 
| Qcache_hits             | 219     | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 162     | 
| Qcache_free_memory      | 8391152 | 
| Qcache_free_blocks      | 1       | 
| Qcache_total_blocks     | 3118    | 
+-------------------------+---------+ 
8 rows in set (0.00 sec) 
 
mysql> drop database test; 
Query OK, 10 rows affected (0.08 sec) 
 
mysql> show status like "Qcache%"; 
+-------------------------+---------+ 
| Variable_name           | Value   | 
+-------------------------+---------+ 
| Qcache_queries_in_cache | 0       | 
| Qcache_inserts          | 2578    | 
| Qcache_hits             | 219     | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 162     | 
| Qcache_free_memory      | 9990384 | 
| Qcache_free_blocks      | 1       | 
| Qcache_total_blocks     | 1       | 
+-------------------------+---------+ 
8 rows in set (0.00 sec)