MySQL Bugs: #70700: Multiple table cache instances and impact of cursors should be explained better

Bug #70700	Multiple table cache instances and impact of cursors should be explained better
Submitted:	23 Oct 2013 12:18	Modified:	9 Jan 2015 8:59
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.6	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	table cache, table_open_cache_instances

Description:
As noted by my Facebook friend Federico Razzoli, this page: 

http://dev.mysql.com/doc/refman/5.6/en/table-cache.html 

should probably mention: 

- table_open_cache_instances (as partitioned table cache is a new feature of MySQL 5.6)
- cursors in stored routines (I wonder if the same considerations as HANDLER statement apply?)

How to repeat:
Check http://dev.mysql.com/doc/refman/5.6/en/table-cache.html

Suggested fix:
Add notes to cover the topics mentioned above.

Hello Valeriy,

Thank you for the feature request!

Thanks,
Umesh

Valeriy, can you clarify: what is the relevance of cursors to the topic on this page?

This is easy. Page explains, for example:

"If you are opening a table with the HANDLER tbl_name OPEN statement, a dedicated table object is allocated for the thread."

But it does not say anything about allocating table object when, in stored routine, cursor is opened for some SELECT from the table. Look:

mysql> create table tc(id int);
Query OK, 0 rows affected (3.42 sec)

mysql> insert into tc values(1),(2);
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delimiter //
mysql> create procedure pc()
    -> begin
    -> declare i int;
    -> declare done int default false;
    -> declare cur cursor for select id from tc;
    -> declare continue handler for not found set done=true;
    -> open cur;
    -> read_loop: loop
    -> fetch cur into i;
    -> if done then
    -> leave read_loop;
    -> end if;
    -> select i;
    -> end loop;
    -> close cur;
    -> end;
    -> //
Query OK, 0 rows affected (0.56 sec)

mysql> delimiter ;
mysql> show create table tc\G
*************************** 1. row ************************
       Table: tc
Create Table: CREATE TABLE `tc` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'Open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 0     |
| Open_tables            | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> call pc;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> show global status like 'Open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 1     |
| Open_tables            | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

So, Open_tables increased after the call to procedure that uses cursor to fetch rows from the (InnoDB in this case) table one by one. Manual page does not say anything about impact of cursors in stored routines on the content of table cache.