MySQL Bugs: #76990: MySQL 5.7 tablespace info doesn't seem to clearly show how to view tablespaces

Bug #76990	MySQL 5.7 tablespace info doesn't seem to clearly show how to view tablespaces
Submitted:	9 May 2015 10:25	Modified:	24 Jun 2015 7:19
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	documentation, I_S.tablespaces, show create tablespace, Tablespace

Description:
While looking at 5.7 notes here http://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html I notice the new tablespace options.

There’s documentation on CREATE TABLESPACE …; ALTER TABLESPACE ….; and DROP TABLESPACE

but I’m surprised not to see a mention of a command like SHOW TABLESPACES (which would mimic perhaps SHOW TABLES) or simply the "recommended way" to query for tablespaces.

I’ve just created a tablespace yet :

root@myserver [information_schema]> select * from tablespaces;
Empty set (0.00 sec)

root@myserver [information_schema]> create tablespace ts1 add datafile 'tablespace_1.ibd' engine=innodb;
ERROR 1813 (HY000): InnoDB: A tablespace named `ts1` already exists.
root@myserver [information_schema]> create tablespace ts2 add datafile 'tablespace_2.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)

root@myserver [information_schema]> select * from tablespaces;
Empty set (0.00 sec)

seems to show nothing.  So any new docs on tablespaces should tell you the commands to create, modify and remove them plus how to see them.

It looks like the info is inside the Innodb_sys_tablespaces

root@myserver [information_schema]> select * from innodb_sys_tablespaces where name like 'ts%';
+-------+------+------+-------------+------------+-----------+---------------+------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+------+------+-------------+------------+-----------+---------------+------------+
| 71257 | ts1  | 2048 | Any         | Any        |     16384 |             0 | General    |
| 71258 | ts2  | 2048 | Any         | Any        |     16384 |             0 | General    |
+-------+------+------+-------------+------------+-----------+---------------+------------+
2 rows in set (0.00 sec)

How to repeat:
see above

Suggested fix:
* Clarify the correct way to see explicitly defined tablespaces
* clarify if tablespaces only work for InnoDB
* Why is the information_schema.tablespaces empty ?
* If the tablespace option is not just for InnoDB then I'd expect a generic way to show tablespace info.

I tried to add a table to a new tablespace and that seems somewhat broken:

on the master the table looks like:

-rw-rw---- 1 mysql mysql 8.7K Jun 21  2014 mytable.frm
-rw-rw---- 1 mysql mysql 128K May  9 08:06 mytable.ibd

so a tiny table yet this took a long time to run:

root@myserver [db]> alter table mytable tablespace = ts1;
Query OK, 0 rows affected (2 min 31.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@myserver [db]> select count(*) from mytable;
+----------+
| count(*) |
+----------+
|      186 |
+----------+
1 row in set (0.00 sec)

So this looks somewhat problematic. Is there very special locking going on? Note: this server was "busy" but not especially so.

A 5.6 server doing a table rebuild takes a tiny amount of time to run:

root@otherserver [db]> optimize table mytable;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| db.mytable | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db.mytable | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

root@otherserver [db]> 

[disclaimer: I've munged host, db and tablenames for obvious reasons but the basics of what is shown here is real.

Thoughts:
* I like the idea of tablespaces, so +1
* The performance of tables inside a tablespace and moving to or from a tablespace should be comparable to rebuilding a table.

I notice also that the way to change back is to do:

root@myserver [db]> alter table mytable tablespace = innodb_file_per_table;
Query OK, 0 rows affected (51.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

So also quite a long time, but shorter than the previous command. However, it seems doing this changes the standard or previous way a table definition is shown as I now see:

root@myserver [db]> show create table mytable\G
*************************** 1. row ***************************
       Table: Currency
Create Table: CREATE TABLE `mytable` (
...
) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The db/mytable.ibd file is there so the change was completed, yet the show CREATE TABLE now shows a different output from the previous definition (currently on the master).

This seems somewhat confusing and perhaps should be documented more clearly.

Adjust as version I saw this on was 5.7.7

Also, Bug #76182

This could be a (partly) duplicate of Bug #74358

Performance problem should be duplicate of bug #74472

Closing as duplicate of problems covered in bugs 74358 , 74472 and 77093