Details
Minor
Description
MySQL opening .frm even when table is in table definition cache
See http://www.mysqlperformanceblog.com/2011/11/21/mysql-opening-frm-even-when-table-is-in-table-definition-cache/ for details.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The original post indicates that it happens for various DDL operations, but also any general "open table".
Of course it's easily checked if you have a trace or debug mysqld going, which I currently don't.
Will just doing a select on a table twice should do the trick? If we have a method to trigger the situation (if it still exists), then someone testing something else with a debug or trace could check this on the side some time soon - or I can do it.
Just doing select on a table will not open the .frm twice (just verified it).
In other words, it does not happen for a general open table.
It can easily be checked by examining 'opened_files' in show status.
This is mainly a problem with VIEW and DROP and RENAME TABLE.
At least the DROP TABLE issue should be worth fixing.
Stewart Smith pasted something into #maria when Monty, he and I were discussing this topic 2012-12-18.
I think the bit I was talking about is op open_table() in sql_base.cc, namely this bit
/*
Note that we can't be 100% sure that it is a view since it's
possible that we either simply have not found unused TABLE
instance in THD::open_tables list or were unable to open table
during prelocking process (in this case in theory we still
should hold shared metadata lock on it).
*/
if (dd_frm_type(thd, path, ¬_used) == FRMTYPE_VIEW)
...
It looks like it was fixed in 10.0.2, my suspicion is – as a part of table discovery implementation.
See strace fragments related to t1.frm and t2.frm for 10.0.1 and 10.0.2 for the following test case:
create table t1 (i int); insert into t1 values (1); rename table t1 to t2; insert into t2 values (2); drop table t2;
write(8, "\t\t 2 Query\tcreate table t1 (i"..., 38) = 38
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDWR|O_CREAT|O_TRUNC, 0660) = 51
...
open("./test/t1.frm", O_RDONLY) = 51
...
write(8, "\t\t 2 Query\tinsert into t1 val"..., 40) = 40
...
open("./test/t1.frm", O_RDONLY) = 51
...
write(8, "\t\t 2 Query\trename table t1 to"..., 36) = 36
...
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDONLY) = 51
...
rename("./test/t1.frm", "./test/t2.frm") = 0
...
write(8, "\t\t 2 Query\tinsert into t2 val"..., 40) = 40
...
open("./test/t2.frm", O_RDONLY) = 57
...
write(8, "\t\t 2 Query\tdrop table t2\n", 28) = 28
...
access("./test/t2.frm", F_OK) = 0
...
open("./test/t2.frm", O_RDONLY) = 57
...
unlink("./test/t2.frm") = 0
...
write(8, "\t\t 2 Query\tcreate table t1 (i"..., 38) = 38
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDWR|O_CREAT|O_TRUNC, 0660) = 51
...
write(8, "\t\t 2 Query\tinsert into t1 val"..., 40) = 40
...
open("./test/t1.frm", O_RDONLY) = 51
...
write(8, "\t\t 2 Query\trename table t1 to"..., 36) = 36
...
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
...
rename("./test/t1.frm", "./test/t2.frm") = 0
...
write(8, "\t\t 2 Query\tinsert into t2 val"..., 40) = 40
...
open("./test/t2.frm", O_RDONLY) = 57
...
write(8, "\t\t 2 Query\tdrop table t2\n", 28) = 28
...
unlink("./test/t2.frm") = 0
So, I'm closing it as fixed in 10.0 (set 10.0.6 in Fix version because it's the lowest available on the list, but in fact it's 10.0.2).
If anyone disagrees, please comment and/or re-open.
According to Monty, this only happens for LOCK TABLES on a view and SHOW TABLES