MySQL Open and Opened Tables

Question

I have 81 tables in an innodb database (MySQL). The data in them amounts to 2GB on disk. My queries rarely join more than 3 tables together at once. My innodb_buffer_pool size is about 2.1 GB.

Running mysqltuner.pl I get the following !!

[!!] Table cache hit rate: 7% (274 open / 3K opened)

From mysqlreport I see that I indeed have 274 open, have had 3K opened and that my ceiling for open is 400.

However, doing this

show status like '%open%'

gets this result

...
Open_table_definitions      161
Open_tables                 274
Opened_files             150232
Opened_table_definitions      0
Opened_tables                 0

Two questions:

1) Shouldn't the "opened tables" say 3K and not zero in the above result from show status like '%open%'?

2) Any advice on what I need to do to remedy this !! i.e. the low table cache hit rate?

Thanks

PS. If it helps, the second !! I have in mysqltuner.pl is this:

[!!] Temporary tables created on disk: 29% (35K on disk / 119K total)

Answer 1

1) Shouldn't the "opened tables" say 3K and not zero in the above result from show status like '%open%'?

YES, result came mostly from SHOW STATUS and SHOW VARIABLES and some basic calculus operations.

2) Any advice on what I need to do to remedy this !! i.e. the low table cache hit rate?

Table cache hit is due to: 1. too few table opened 2. Total number of table in all databases <<< open table cache size.

Answer 2

1. show status like '%open%' shows the status for the current session rather then for the whole MySQL DB(SHOW STATUS Syntax). For getting global status, use show global status like '%open%' instead.

2. One problem I encountered for mysqltuner.pl is whenever it is run, it opens all the tables in the database, thus increasing the opened_tables statistics. If it is not the case, MySQL manual suggested set table_open_cache to * .