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

[16 Jul 2007 14:40] Robin Schumacher
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)
[18 Oct 2007 22:39] Kevin Lewis
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.
[21 Oct 2007 21:22] Christopher Powers
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.
[30 Nov 2007 17:13] Hakan Küçükyılmaz
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
[30 Nov 2007 20:43] Bugs System
Pushed into 6.0.4-alpha
[3 Dec 2007 14:15] MC Brown
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.