| Bug #69782 | Old files not being removed from performance_schema.file_instances | ||
|---|---|---|---|
| Submitted: | 18 Jul 2013 17:42 | Modified: | 14 Jan 2014 18:27 |
| Reporter: | Don Coffin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 5.6.12 | OS: | Linux |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
| Tags: | performance_schema | ||
[18 Jul 2013 17:42]
Don Coffin
[24 Jul 2013 6:22]
MySQL Verification Team
Hello Don, Thank you for the bug report. Verified as described on MySQL 5.6.12 version. Thanks, Umesh
[24 Jul 2013 6:24]
MySQL Verification Team
//
slave1 [localhost] {root} ((none)) > select version();
+------------+
| version() |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)
slave1 [localhost] {root} ((none)) > select count(*) from performance_schema.file_instances where file_name like '/data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin%';
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
slave1 [localhost] {root} ((none)) > select * from performance_schema.file_instances where file_name like '/data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin%';
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
| FILE_NAME | EVENT_NAME | OPEN_COUNT |
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000007 | wait/io/file/sql/binlog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000006 | wait/io/file/sql/binlog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000008 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000009 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000010 | wait/io/file/sql/relaylog | 1 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.index | wait/io/file/sql/binlog_index | 1 |
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
6 rows in set (0.00 sec)
slave1 [localhost] {root} ((none)) >
//// file system snapshot - to confirm ***-relay-bin.000007/6/8 doesn't exist on disk...
[ushastry@cluster-repo ~]$ more sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.index
./mysql_sandbox19078-relay-bin.000009
./mysql_sandbox19078-relay-bin.000010
[ushastry@cluster-repo ~]$ ls -l sandboxes/rsandbox_5_6_12/node1/data/
total 110684
-rw-rw---- 1 ushastry ushastry 56 Jul 19 20:03 auto.cnf
-rw-rw---- 1 ushastry ushastry 12582912 Jul 25 09:43 ibdata1
-rw-rw---- 1 ushastry ushastry 50331648 Jul 25 09:43 ib_logfile0
-rw-rw---- 1 ushastry ushastry 50331648 Jul 19 20:03 ib_logfile1
-rw-rw---- 1 ushastry ushastry 130 Jul 25 09:43 master.info
-rw-r----- 1 ushastry ushastry 21979 Jul 25 09:43 msandbox.err
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 mysql
-rw-rw---- 1 ushastry ushastry 143 Jul 19 20:07 mysql-bin.000001
-rw-rw---- 1 ushastry ushastry 120 Jul 24 12:44 mysql-bin.000002
-rw-rw---- 1 ushastry ushastry 143 Jul 24 12:44 mysql-bin.000003
-rw-rw---- 1 ushastry ushastry 120 Jul 25 09:43 mysql-bin.000004
-rw-rw---- 1 ushastry ushastry 76 Jul 25 09:43 mysql-bin.index
-rw-rw---- 1 ushastry ushastry 5 Jul 25 09:43 mysql_sandbox19078.pid
-rw-rw---- 1 ushastry ushastry 349 Jul 25 09:43 mysql_sandbox19078-relay-bin.000009
-rw-rw---- 1 ushastry ushastry 283 Jul 25 09:43 mysql_sandbox19078-relay-bin.000010
-rw-rw---- 1 ushastry ushastry 76 Jul 25 09:43 mysql_sandbox19078-relay-bin.index
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 performance_schema
-rw-rw---- 1 ushastry ushastry 73 Jul 25 09:43 relay-log.info
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 test
[24 Jul 2013 6:24]
MySQL Verification Team
/// Creating new *relay files on slave
slave1 [localhost] {root} ((none)) > stop slave;
Query OK, 0 rows affected (0.09 sec)
slave1 [localhost] {root} ((none)) > start slave;
Query OK, 0 rows affected (0.05 sec)
slave1 [localhost] {root} ((none)) > stop slave;
Query OK, 0 rows affected (0.09 sec)
slave1 [localhost] {root} ((none)) > start slave;
Query OK, 0 rows affected (0.05 sec)
slave1 [localhost] {root} ((none)) > select count(*) from performance_schema.file_instances where file_name like '/data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin%';
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
slave1 [localhost] {root} ((none)) > select * from performance_schema.file_instances where file_name like '/data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin%';
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
| FILE_NAME | EVENT_NAME | OPEN_COUNT |
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000007 | wait/io/file/sql/binlog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000006 | wait/io/file/sql/binlog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000008 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000009 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000010 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000011 | wait/io/file/sql/relaylog | 0 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.000012 | wait/io/file/sql/relaylog | 1 |
| /data/ushastry/sandboxes/rsandbox_5_6_12/node1/data/mysql_sandbox19078-relay-bin.index | wait/io/file/sql/binlog_index | 1 |
+-----------------------------------------------------------------------------------------+-------------------------------+------------+
8 rows in set (0.00 sec)
// from file system snapshot
[ushastry@cluster-repo ~]$ ls -l sandboxes/rsandbox_5_6_12/node1/data/
total 110684
-rw-rw---- 1 ushastry ushastry 56 Jul 19 20:03 auto.cnf
-rw-rw---- 1 ushastry ushastry 12582912 Jul 25 09:43 ibdata1
-rw-rw---- 1 ushastry ushastry 50331648 Jul 25 09:43 ib_logfile0
-rw-rw---- 1 ushastry ushastry 50331648 Jul 19 20:03 ib_logfile1
-rw-rw---- 1 ushastry ushastry 130 Jul 25 09:57 master.info
-rw-r----- 1 ushastry ushastry 24295 Jul 25 09:57 msandbox.err
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 mysql
-rw-rw---- 1 ushastry ushastry 143 Jul 19 20:07 mysql-bin.000001
-rw-rw---- 1 ushastry ushastry 120 Jul 24 12:44 mysql-bin.000002
-rw-rw---- 1 ushastry ushastry 143 Jul 24 12:44 mysql-bin.000003
-rw-rw---- 1 ushastry ushastry 120 Jul 25 09:43 mysql-bin.000004
-rw-rw---- 1 ushastry ushastry 76 Jul 25 09:43 mysql-bin.index
-rw-rw---- 1 ushastry ushastry 5 Jul 25 09:43 mysql_sandbox19078.pid
-rw-rw---- 1 ushastry ushastry 349 Jul 25 09:57 mysql_sandbox19078-relay-bin.000011
-rw-rw---- 1 ushastry ushastry 283 Jul 25 09:57 mysql_sandbox19078-relay-bin.000012
-rw-rw---- 1 ushastry ushastry 76 Jul 25 09:57 mysql_sandbox19078-relay-bin.index
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 performance_schema
-rw-rw---- 1 ushastry ushastry 73 Jul 25 09:57 relay-log.info
drwx------ 2 ushastry ushastry 4096 Jul 19 20:03 test
[ushastry@cluster-repo ~]$
^^ Clearly mysql_sandbox19078-relay-bin.000007..10 relay logs doesn't exist on file system but still file_instances lists them which some
how contradicts " If a file on disk has never been opened, it will not be in file_instances. When a file is deleted from the disk, it is also removed from the file_instances table. " - Please reference http://dev.mysql.com/doc/refman/5.6/en/file-instances-table.html
Imho - either this behavior should be documented or fixed in code.
Thanks,
Umesh
[14 Jan 2014 18:27]
Paul DuBois
Noted in 5.6.16, 5.7.4 changelogs. Some files in the Performance Schema file_instances table were not being removed because the file-removal operation was not instrumented.
[3 Feb 2014 11:55]
Laurynas Biveinis
5.6$ bzr log -r 5715
------------------------------------------------------------
revno: 5715
committer: Venkata Sidagam <venkata.sidagam@oracle.com>
branch nick: 5.6
timestamp: Fri 2013-12-27 14:11:20 +0530
message:
Bug #17209750 OLD FILES NOT BEING REMOVED FROM
PERFORMANCE_SCHEMA.FILE_INSTANCES
Description: "When a file is deleted from the disk, it is
also removed from the file_instances table." This is not
true on either 5.5.23 or 5.6.11. When there are three
relay log files on disk, but the file_instances table
lists more number of relay logs. Even this case is
happening for master bin and slave bin logs.
Analysis: When we are deleting the
slave-relay-bin/master-bin/slave-bin log files we were
using the my_delete() function which is not P_S
instrumented function. Hence, the files are not removed
from the file_instances table.
Fix: As part of the fix we replaced my_delete()
function with P_S instrumented mysql_file_delete() function.
