MySQL Bugs: #29823: Falcon falcon_database_io table doesn't report stats for user DB's

Bug #29823	Falcon falcon_database_io table doesn't report stats for user DB's
Submitted:	16 Jul 2007 14:40	Modified:	3 Dec 2007 14:15
Reporter:	Robin Schumacher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Windows
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	cwp

Description:
falcon_database_io object doesn't appear to report stats for user databases. 

How to repeat:
mysql> use gimf
Database changed
mysql> select count(*) from rms2;
+----------+
| count(*) |
+----------+
|   186750 |
+----------+
1 row in set (0.13 sec)

mysql> select * from information_schema.falcon_tables;
Empty set (0.00 sec)

mysql> create table t1 like rms2;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 engine=falcon;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from rms2;
Query OK, 186750 rows affected (2.94 sec)
Records: 186750  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.falcon_database_io;
+---------------+-----------+---------+----------------+--------+---------------+-------+
| DATABASE      | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+---------------+-----------+---------+----------------+--------+---------------+-------+
| falcon_master |      4096 |    1024 |             58 |     30 |          1311 |     2 |
+---------------+-----------+---------+----------------+--------+---------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   186750 |
+----------+
1 row in set (0.61 sec)

mysql> select * from information_schema.falcon_database_io;
+---------------+-----------+---------+----------------+--------+---------------+-------+
| DATABASE      | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+---------------+-----------+---------+----------------+--------+---------------+-------+
| falcon_master |      4096 |    1024 |             62 |     32 |          1315 |     2 |
+---------------+-----------+---------+----------------+--------+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from information_schema.falcon_tables;
+-------------+------------+-------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE  |
+-------------+------------+-------------+
| GIMF        | T1         | FALCON_USER |
+-------------+------------+-------------+
1 row in set (0.00 sec)

mysql> create table t2 as select * from t1;
Query OK, 186750 rows affected (11.38 sec)
Records: 186750  Duplicates: 0  Warnings: 0

mysql> alter table t2 engine=falcon;
Query OK, 186750 rows affected (6.02 sec)
Records: 186750  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.falcon_tables;
+-------------+------------+-------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE  |
+-------------+------------+-------------+
| GIMF        | T1         | FALCON_USER |
| GIMF        | T2         | FALCON_USER |
+-------------+------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.falcon_database_io;
+---------------+-----------+---------+----------------+--------+---------------+-------+
| DATABASE      | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+---------------+-----------+---------+----------------+--------+---------------+-------+
| falcon_master |      4096 |    1024 |            108 |     65 |          1708 |     2 |
+---------------+-----------+---------+----------------+--------+---------------+-------+
1 row in set (0.00 sec)

mysql> select sum(number_of_units) from t2 where broker_id > 3;
+----------------------+
| sum(number_of_units) |
+----------------------+
|             73245500 |
+----------------------+
1 row in set (0.75 sec)

mysql> select * from information_schema.falcon_database_io;
+---------------+-----------+---------+----------------+--------+---------------+-------+
| DATABASE      | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+---------------+-----------+---------+----------------+--------+---------------+-------+
| falcon_master |      4096 |    1024 |            108 |     73 |          1708 |     2 |
+---------------+-----------+---------+----------------+--------+---------------+-------+
1 row in set (0.00 sec)

Chris, It seems that this information_schema report is refering to 'databases' and reporting tablespaces.  But the numbers reflect the whole instance of Falcon,  so maybe we should just drop the DATABASE column.

Falcon does not track I/O stats for individual user databases (MySQL schemas). However, Information_Schema.FALCON_DATABASE_IO still lacked statistics for all tablespaces.

Modified StorageDatabase::getIOInfo() to collect statistics from all tablespaces, in addition to the system default tablespaces. 

Also changed the FALCON_DATABASE_IO column header from "DATABASE" to the more accurate "TABLESPACE".

Changeset 1.2669, mysql-6.0-falcon-team.

Works now

[18:12] root@test>select count(*) from t2;
+----------+
| count(*) |
+----------+
|  1835008 |
+----------+
1 row in set (10.03 sec)

6.0.4-alpha-debug
[18:13] root@test>select * from information_schema.falcon_database_io;
+------------------+-----------+---------+----------------+--------+---------------+-------+
| TABLESPACE       | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+------------------+-----------+---------+----------------+--------+---------------+-------+
| FALCON_MASTER    |      4096 |    1024 |             33 |     51 |          5090 |   111 |
| FALCON_TEMPORARY |      4096 |    1024 |              0 |      4 |             0 |     4 |
| FALCON_USER      |      4096 |    1024 |          22669 |  25979 |      28433247 | 21650 |
+------------------+-----------+---------+----------------+--------+---------------+-------+
3 rows in set (0.00 sec)

6.0.4-alpha-debug

Pushed into 6.0.4-alpha

A note has been added tot he 6.0.4 changelog: 

When accessing the statistics in INFORMATION_SCHEMA.FALCON_DATABASE_IO, the information would related only to the Falcon database, not user tablespaces. The output has been updated to report on all tablespaces, and the column title has been changed to reflect the fact that the statistics are now reported by tablespace, not database.