WL#11613: InnoDB: Reclaim disk space occupied by temporary tables online

When the default internal_tmp_disk_storage_engine=InnoDB, 
all user and optimizer internal temporary tables use the global temporary
tablespace (ibtmp1). Even after these tables are dropped, 
the disk space occupied is never released back to OS.

The objective of this worklog is to reclaim disk space occupied by
the temporary tables online.

1. FR-1: Reclaim the disk space occupied by optimizer internal and user
         temporary tables.

2. FR-2: Initial disk footprint for session temporary tablespaces pool
         should be within acceptable limits (~12M-20M?)

3. FR-3: Provide an option to store session temporary tablespaces
         (innodb_temp_tablespaces_dir)

4. FR-4: Provide interface to query the current usage of session temporary 
         tablespaces

5. NFR-1: Performance shouldn't be degraded or if there is drop, it should be
          within acceptable limits (< 5%?)

6. NFR-2: Should support huge number of concurrent connections/sessions using
          optimizer internal temporary tables.

Background:
-----------

Who uses temporary tablespace?
------------------------------

1. Since 5.7, the default internal_tmp_disk_storage_engine=InnoDB, so
   optimizer created temporary tables (aka instrinisic tables) are stored in
   temporary tablespace (ibtmp1). These tables are not undo-logged and are
   dropped at end of query.

2. Temporary tables created by user (CREATE TEMPORARY TABLE..). These tables
   need rollback support, so there is undo from DMLs on temporary table.
   So user temp table data and their undo pages are in temporary tablespace.
   These tables should be explicity dropped by user or dropped automatically
   at end of session.


What causes the growth of the global temporary tablespace?
----------------------------------------------------------

1. Temp tablespace is a like a general tablespace. After a table in a general
   tablespace is dropped, the tablespace never shrinks.

2. Long running queries which create internal tables and insert huge amounts of
   data. The tables are dropped at end of query but the disk space is not
   released back to OS.

3. User created temporary tables which are huge and never dropped by user.

4. Undo from user tables also resides in ibtmp1 and undo pages can make the
   tablespace grow.

Design:
=======

On startup, a pool of temporary tablespaces will be created in
innodb_temp_tablespaces_dir. These tablespaces are similar to general
tablespaces. These will be called "Session temporary tablespaces".
The shared tablespace ibtmp1 will be referred as "Global temporary tablespace".

Each session, on the first request to create a user temporary table or optimizer
internal table, will allocate a tablespace from the reserved temp tablespaces
pool. The pool can expand its capacity on demand. The pool will not shrink and
any file in the pool should be of its initial size (5 pages).

Once a temporary tablespace is attached to a session, all temporary tables from
this session would use it.

A session can have at max two temporary tablespaces. One tablespace for user
created temporary tables and another for optimizer created temporary tables.

The reason for having two separate tablespaces is that, in the future,
it is possible to reclaim the disk space occupied by optimizer tables quickly by
reclaiming even before the session disconnect.

On session disconnect, these two tablespaces will be truncated and released back
to the pool. So the disk space occupied by the temporary tables of a session, is
reclaimed.

Note that the global temporary tablespace (ibtmp1) will be still used for
rollback segments from changes to user temporary tables.

Information Schema View
=======================
An Information Schema view will be provided to see the session_id/thread_id &
its tablespaces size.

It displays the information of all .IBT files in the pool. ACTIVE state means
the tablespace is used by Session. INACTIVE state means the tablespace is
in pool and not belonging to any session.


mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
+------------+------------+----------------------------+----------+----------+
| SESSION_ID | SPACE      | PATH                       | SIZE     | STATE    |
+------------+------------+----------------------------+----------+----------+
|          7 | 4294566162 | ./#innodb_temp/temp_10.ibt |   933888 | ACTIVE   |
|          7 | 4294566161 | ./#innodb_temp/temp_9.ibt  | 10485760 | ACTIVE   |
|          8 | 4294566160 | ./#innodb_temp/temp_8.ibt  |   933888 | ACTIVE   |
|          8 | 4294566159 | ./#innodb_temp/temp_7.ibt  |  9437184 | ACTIVE   |
|          0 | 4294566153 | ./#innodb_temp/temp_1.ibt  |    81920 | INACTIVE |
|          0 | 4294566154 | ./#innodb_temp/temp_2.ibt  |    81920 | INACTIVE |
|          0 | 4294566155 | ./#innodb_temp/temp_3.ibt  |    81920 | INACTIVE |
|          0 | 4294566156 | ./#innodb_temp/temp_4.ibt  |    81920 | INACTIVE |
|          0 | 4294566157 | ./#innodb_temp/temp_5.ibt  |    81920 | INACTIVE |
|          0 | 4294566158 | ./#innodb_temp/temp_6.ibt  |    81920 | INACTIVE |
+------------+------------+----------------------------+----------+----------+
10 rows in set (0.01 sec)


Session Temporary tablespaces
============================
These temporary tablespaces will have the extension ".ibt". If a directory is
provided using "innodb_temp_tablespaces_dir", the session temporary tablespaces
will be created under the specified directory. This variable is not dynamic.  It
can only be set at startup.

If innodb_temp_tablespaces_dir is not provided explicitly, a directory named
"#innodb_temp" will be created under datadir. If this directory
already exists, InnoDB will use that directory. Under this directory, *.ibt
files will be created. If relative path is used for innodb_temp_tablespaces_dir,
it is relative to datadir.

Session temporary tablespaces are not registered in DD nor will they be noticed
or used at startup when looking for InnoDB tablespaces and undo tablespaces.

Note: Like the global temporary tablespace (ibtmp1), session temporary
tablespaces are always re-created on startup.

A higher range of 400K space_ids are reserved for session temporary tablespaces.
400K is chosen because max_connections is 100K and we will reserve 4 space_ids
per session.  With this work, only 2 will be needed. We reserve more for some
future purpose. So the total number of space_ids needed is 100K * 4 = 400K.

This is 0.09% out of the available space_ids (4Billion). These space_ids do not
survive reboot. This fixed limit does not affect future use of these
space_ids since session temporary tablespaces are not redo logged and are always
re-created on startup.

Impact on Replication:
======================
Temporary tables are replicated only in STATEMENT mode and in ROW mode, only a
"DROP TEMPORARY TABLE X IF EXISTS" is replicated. So there is no impact with
binlog ROW format.

With STATEMENT mode, on slave, all the temporary tables are created in one IBT
file. It is only truncated at shutdown.

TABLESPACE= identifier for temporary tables
===========================================
This will be disallowed when innodb_strict_mode=ON. We want users to use session
temporary tablespaces and get disk space reclaimed.

However, with innodb_strict_mode=OFF, we will allow the syntax, throw warning
and use only "session temporary tablespace".