Open_tables not correct

[Jay Janssen]

Maybe a PS issue more than PXC, but PXC is where I'm observing it. Note no wsrep is configured.

mysql> show global variables like 'table%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| table_definition_cache | 150000 |
| table_open_cache | 50000 |
+------------------------+--------+
2 rows in set (0.00 sec)

mysql> show open tables;
...
150000 rows in set (0.34 sec)

mysql> show global status like 'open%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| Open_files | 1 |
| Open_streams | 0 |
| Open_table_definitions | 150000 |
| Open_tables | 0 |
| Opened_files | 922978 |
| Opened_table_definitions | 902929 |
| Opened_tables | 919817 |
+--------------------------+--------+
7 rows in set (0.00 sec)

Note that Open_tables is 0. Interestingly, SHOW OPEN TABLES gives 150k results, but that is the table_definition_cache size, not the table_open_cache size.

[Jay Janssen (jay-janssen)]

This may not be a bug: I am only querying the I_S.columns table. Tables are clearly getting opened, but for some reason they are getting closed right away after those queries (nothing else on the system).

[Nilnandan Joshi (nilnandan-joshi)]

Couldn't able to reproduce with PS 5.6.20 and PXC 5.6.20. I'm not getting value for open_tables = 0. Can you please provide the exact version or PS/PXC and how we can reproduce it? Thanks.

root@deb-pxc56-1:~# mysql -uroot -p
Enter password:
...
Server version: 5.6.20-68.0-56-log Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126

mysql> show global variables like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 1400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 1 |
+----------------------------+-------+
3 rows in set (0.01 sec)

mysql> show open tables;
.....
70 rows in set (0.00 sec)

mysql> show global status like 'open%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 22 |
| Open_streams | 0 |
| Open_table_definitions | 70 |
| Open_tables | 63 |
| Opened_files | 150 |
| Opened_table_definitions | 70 |
| Opened_tables | 70 |
+--------------------------+-------+
7 rows in set (0.00 sec)

mysql>

[Launchpad Janitor (janitor)]

[Expired for Percona XtraDB Cluster because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1356