Introduction
MySQL® 6.0.5-alpha, the latest version of the 6.x branch of the Database Server, is available for download from the SUN|MySQL Web Site.
Metadata (data about the data) are very important, especially for software developers. In this article we will see what’s new in FALCON metadata handling doing some comparison with the old 6.0.4-alpha version.
New tables in the `information_schema` database
As you know, the source for metadata is the database `information_schema`. To start, let’s see which tables related with FALCON metadata are included in that database:
mysql> SELECT VERSION();
+—————————+
| VERSION() |
+—————————+
| 6.0.5-alpha-community-log |
+—————————+
1 row in set (0.01 sec)
mysql> USE `information_schema`;
Database changed
mysql> SHOW TABLES LIKE ‘FALCON%’;
+—————————————-+
| Tables_in_information_schema (FALCON%) |
+—————————————-+
| FALCON_TABLES |
| FALCON_SERIAL_LOG_INFO |
| FALCON_SYSTEM_MEMORY_DETAIL |
| FALCON_SYSTEM_MEMORY_SUMMARY |
| FALCON_VERSION |
| FALCON_TRANSACTION_SUMMARY |
| FALCON_TABLESPACES |
| FALCON_SYNCOBJECTS |
| FALCON_TRANSACTIONS |
| FALCON_RECORD_CACHE_DETAIL |
| FALCON_TABLESPACE_IO |
| FALCON_RECORD_CACHE_SUMMARY |
| FALCON_TABLESPACE_FILES |
+—————————————-+
13 rows in set (0.00 sec)
At a glance, we see that we have thirteen tables while in the old 6.0.4-alpha version the tables were only eleven:
mysql> SELECT VERSION();
+—————————+
| VERSION() |
+—————————+
| 6.0.4-alpha-community-log |
+—————————+
1 row in set (0.00 sec)
mysql> USE `information_schema`;
Database changed
mysql> SHOW TABLES LIKE ‘FALCON%’;
+—————————————-+
| Tables_in_information_schema (FALCON%) |
+—————————————-+
| FALCON_TABLES |
| FALCON_RECORD_CACHE_SUMMARY |
| FALCON_SYSTEM_MEMORY_DETAIL |
| FALCON_SERIAL_LOG_INFO |
| FALCON_VERSION |
| FALCON_TRANSACTION_SUMMARY |
| FALCON_DATABASE_IO |
| FALCON_SYNCOBJECTS |
| FALCON_TRANSACTIONS |
| FALCON_RECORD_CACHE_DETAIL |
| FALCON_SYSTEM_MEMORY_SUMMARY |
+—————————————-+
11 rows in set (0.00 sec)
The new and very useful tables are `FALCON_TABLESPACES` and `FALCON_TABLESPACE_FILES`.
If you played with version 6.0.4-alpha you probably felt the need of similar tables and, if no, you can see why they were important for me looking at bug #37030 .
Also note that the table `FALCON_DATABASE_IO` has been renamed in `FALCON_TABLESPACE_IO` so, for example, the query for calculating the Falcon Cache Hit Ratio by Tablespace becomes:
mysql> SELECT
-> `tablespace` AS `Tablespace`,
-> 100 * 1-(SUM(`physical_reads`) / SUM(IF(`logical_reads` > 1, `logical_reads`,1))) `Cache Hit Ratio`
-> FROM `information_schema`.`FALCON_TABLESPACE_IO`
-> GROUP BY `tablespace`;
+——————+—————–+
| Tablespace | Cache Hit Ratio |
+——————+—————–+
| FALCON_MASTER | 99.9660 |
| FALCON_TEMPORARY | 99.0000 |
| FALCON_USER | 99.0000 |
+——————+—————–+
3 rows in set (0.00 sec)
`FALCON_TABLESPACES` and `FALCON_TABLESPACE_FILES` in details
Let’s see the structure of the new tables:
mysql> DESCRIBE `information_schema`.`FALCON_TABLESPACES`;
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLESPACE_NAME | varchar(127) | NO | | | |
| TYPE | varchar(127) | NO | | | |
| COMMENT | varchar(127) | NO | | | |
+—————–+————–+——+—–+———+——-+
3 rows in set (0.33 sec)
mysql> DESCRIBE `information_schema`.`FALCON_TABLESPACE_FILES`;
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLESPACE_NAME | varchar(127) | NO | | | |
| TYPE | varchar(127) | NO | | | |
| FILE_ID | int(127) | NO | | 0 | |
| FILE_NAME | varchar(127) | NO | | | |
+—————–+————–+——+—–+———+——-+
4 rows in set (0.00 sec)
As you can see a column named `COMMENT` is included in `information_schema`.`FALCON_TABLESPACES`, so, unlike version 6.0.4-alpha and previous, in version 6.0.5-alpha you can now specify a comment for your FALCON tablespaces.
Let’s create two FALCON tablespaces, `test1` and `test2`, and see how those two metadata tables are populated:
mysql> CREATE TABLESPACE `test1`
-> ADD DATAFILE ‘test1.fts’
-> EXTENT_SIZE = 1M
-> INITIAL_SIZE = 10M
-> COMMENT=’test1-comment’
-> ENGINE = FALCON;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLESPACE `test2`
-> ADD DATAFILE ‘test2.fts’
-> EXTENT_SIZE = 1M
-> INITIAL_SIZE = 10M
-> COMMENT=’test2-comment’
-> ENGINE = FALCON;
Query OK, 0 rows affected (0.00 sec)
I’d make you aware of the fact that when you create a FALCON tablespace sometimes you can get the following error: “ERROR 1044 (42000): Access denied for user ‘root’@'localhost’ to database ‘information_schema’”. Frankly, at the moment I don’t know why this error occurs. I am trying to create a reproducible bug report. If you experienced that error and you know why it sometimes occurs, please let me know. By the way, if that error will occur you can try closing your client and reopening it. That fix the error for me.
If we select over the table `FALCON_TABLESPACES` we get the following output:
mysql> SELECT * FROM `information_schema`.`FALCON_TABLESPACES`;
+——————+——————+—————+
| TABLESPACE_NAME | TYPE | COMMENT |
+——————+——————+—————+
| FALCON_USER | FALCON_USER | |
| FALCON_TEMPORARY | FALCON_TEMPORARY | |
| test1 | USER_DEFINED | test1-comment |
| test2 | USER_DEFINED | test2-comment |
+——————+——————+—————+
4 rows in set (0.00 sec)
As you can see, there are two rows for what we now call ’system-tablespaces’ - i.e. the tablespaces ‘FALCON_USER’ and ‘FALCON_TEMPORARY’ - and one row for each tablespace created with the CREATE TABLESPACE syntax (in our example ‘test1′ and ‘test2′).
By looking at that table you can also see that there are three types of FALCON tablespaces. One is the type ‘USER_DEFINED’ - that is the type of the tablespaces created by you using the CREATE TABLESPACE syntax - and two are system-related: ‘FALCON_USER’ and ‘FALCON_TEMPORARY’. We will talk about those ’system’ types later.
If we select over the table `FALCON_TABLESPACE_FILES` we get the following output:
mysql> SELECT * FROM `information_schema`.`FALCON_TABLESPACE_FILES`;
+——————+——————+———+———————-+
| TABLESPACE_NAME | TYPE | FILE_ID | FILE_NAME |
+——————+——————+———+———————-+
| FALCON_USER | FALCON_USER | 1 | falcon_user.fts |
| FALCON_TEMPORARY | FALCON_TEMPORARY | 1 | falcon_temporary.fts |
| test1 | USER_DEFINED | 1 | test1.fts |
| test2 | USER_DEFINED | 1 | test2.fts |
+——————+——————+———+———————-+
4 rows in set (0.02 sec)
As you see, for each FALCON tablespace the name of the file as well as an ID is shown.
`FALCON_TABLES` in details
The table `FALCON_TABLES`, already included in MySQL® 6.0.4-alpha and previous, lists the FALCON tables created on your server:
mysql> DESCRIBE `information_schema`.`FALCON_TABLES`;
+—————+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————–+——+—–+———+——-+
| SCHEMA_NAME | varchar(127) | NO | | | |
| TABLE_NAME | varchar(127) | NO | | | |
| PARTITION | varchar(127) | NO | | | |
| TABLESPACE | varchar(127) | NO | | | |
| INTERNAL_NAME | varchar(127) | NO | | | |
+—————+————–+——+—–+———+——-+
5 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
Empty set (0.00 sec)
In my system I get an empty set because there are no FALCON tables at the moment.
Let’s now try answering to the following question: “why do we have two system-related FALCON tablespaces (’FALCON_USER’ and ‘FALCON_TEMPORARY’)?”
The answer is quite simply. When you create a FALCON table you can specify a FALCON tablespace as a table option (CREATE TABLE … TABLESPACE) but if you don’t set a tablespace the FALCON table being created will use the default system tablespaces.
Let’s see this in action.
mysql> CREATE DATABASE `fdatabase`;
Query OK, 1 row affected (0.05 sec)
mysql> USE `fdatabase`;
Database changed
mysql> CREATE TABLE `fdatabase`.`ftable_generic`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.64 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE | INTERNAL_NAME |
+————-+—————-+———–+————-+—————-+
| FDATABASE | FTABLE_GENERIC | | FALCON_USER | FTABLE_GENERIC |
+————-+—————-+———–+————-+—————-+
1 row in set (0.00 sec)
As you can see, we now have our FALCON table listed in `information_schema`.`FALCON_TABLES` and we can see that the tablespace used by the table just created is the system-related tablespace ‘FALCON_USER’.
Let’s create a temporary FALCON table (as before, without setting a tablespace) and see that the ‘FALCON_TEMPORARY’ tablespace is used:
mysql> CREATE TEMPORARY TABLE `fdatabase`.`ftable_generic_temporary`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+——————+—————-+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE | INTERNAL_NAME |
+————-+—————-+———–+——————+—————-+
| FDATABASE | FTABLE_GENERIC | | FALCON_USER | FTABLE_GENERIC |
| TEMP | | | FALCON_TEMPORARY | #SQL1FF4_13_0 |
+————-+—————-+———–+——————+—————-+
2 rows in set (0.00 sec)
If we drop the temporary table:
mysql> DROP TABLE `fdatabase`.`ftable_generic_temporary`;
Query OK, 0 rows affected (0.13 sec)
we have:
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE | INTERNAL_NAME |
+————-+—————-+———–+————-+—————-+
| FDATABASE | FTABLE_GENERIC | | FALCON_USER | FTABLE_GENERIC |
+————-+—————-+———–+————-+—————-+
1 row in set (0.00 sec)
You can now guess what’s happen if you specify a FALCON tablespace in the CREATE TABLE syntax:
mysql> CREATE TABLE `fdatabase`.`ftable1_test1`
-> TABLESPACE `test1`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE | INTERNAL_NAME |
+————-+—————-+———–+————-+—————-+
| FDATABASE | FTABLE_GENERIC | | FALCON_USER | FTABLE_GENERIC |
| FDATABASE | FTABLE1_TEST1 | | test1 | FTABLE1_TEST1 |
+————-+—————-+———–+————-+—————-+
2 rows in set (0.00 sec)
We can see above that the FALCON table `ftable1_test1` uses the tablespace ‘test1′.
Let’s create a second table using the tablespace ‘test1′:
mysql> CREATE TABLE `fdatabase`.`ftable2_test1`
-> TABLESPACE `test1`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE | INTERNAL_NAME |
+————-+—————-+———–+————-+—————-+
| FDATABASE | FTABLE_GENERIC | | FALCON_USER | FTABLE_GENERIC |
| FDATABASE | FTABLE1_TEST1 | | test1 | FTABLE1_TEST1 |
| FDATABASE | FTABLE2_TEST1 | | test1 | FTABLE2_TEST1 |
+————-+—————-+———–+————-+—————-+
Filtering FALCON Tables by TableSpaces
Now a common question is: “Which are the FALCON tables that are using a particular tablespace?”. Here’s the possible answer:
mysql> SELECT CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) FROM `information_schema`.`FALCON_TABLES` WHERE `TABLESPACE` = ‘test1′;
+——————————————————+
| CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) |
+——————————————————+
| `FDATABASE`.`FTABLE1_TEST1` |
| `FDATABASE`.`FTABLE2_TEST1` |
+——————————————————+
2 rows in set (0.00 sec)
Unfortunately, you should note that this method fails due to bug #35210.
In fact, let’s create a FALCON table with a strange name and see what happen:
mysql> CREATE TABLE `fdatabase`.`ftable_strange_name_#_!_test1`
-> TABLESPACE `test1`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`\G
*************************** 1. row ***************************
SCHEMA_NAME: FDATABASE
TABLE_NAME: FTABLE_GENERIC
PARTITION:
TABLESPACE: FALCON_USER
INTERNAL_NAME: FTABLE_GENERIC
*************************** 2. row ***************************
SCHEMA_NAME: FDATABASE
TABLE_NAME: FTABLE1_TEST1
PARTITION:
TABLESPACE: test1
INTERNAL_NAME: FTABLE1_TEST1
*************************** 3. row ***************************
SCHEMA_NAME: FDATABASE
TABLE_NAME: FTABLE2_TEST1
PARTITION:
TABLESPACE: test1
INTERNAL_NAME: FTABLE2_TEST1
*************************** 4. row ***************************
SCHEMA_NAME: FDATABASE
TABLE_NAME: FTABLE_STRANGE_NAME_@0023_@0021_TEST1
PARTITION:
TABLESPACE: test1
INTERNAL_NAME: FTABLE_STRANGE_NAME_@0023_@0021_TEST1
4 rows in set (0.00 sec)
The table `ftable_strange_name_#_!_test1` is stored in the FALCON metadata catalog with the name `FTABLE_STRANGE_NAME_@0023_@0021_TEST1` so the method above fails:
mysql> SELECT CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) FROM `information_schema`.`FALCON_TABLES` WHERE `TABLESPACE` = ‘test1′;
+——————————————————+
| CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) |
+——————————————————+
| `FDATABASE`.`FTABLE1_TEST1` |
| `FDATABASE`.`FTABLE2_TEST1` |
| `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1` |
+——————————————————+
3 rows in set (0.00 sec)
We do not get the correct list of FALCON tables: the table `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1` does not exist:
mysql> DESCRIBE `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1`;
ERROR 1146 (42S02): Table ‘fdatabase.ftable_strange_name_@0023_@0021_test1′ does n’t exist
Indeed, you should note that it is not difficult to avoid such a problem at software-level. For example you can create a function to parse, using defined rules, the name `FTABLE_STRANGE_NAME_@0023_@0021_TEST1`. With this approach you have a function that returns the real name `ftable_strange_name_#_!_test1`.
Conclusion
It’s now time to close this article. New features of the FALCON metadata catalog have been (in part) explained.
For other interesting stuff, you can refer to the articles by Robin Schumacher available on the SUN|MySQL Web Site at dev.mysql.com.
