# InnoDB Temporary Tablespaces

When the user creates a temporary table using the [CREATE TEMPORARY TABLE](/docs/server/server-usage/tables/create-table.md) statement and the engine is set as InnoDB, MariaDB creates a temporary tablespace file. When the table is not compressed, MariaDB writes to a shared temporary tablespace as defined by the [innodb\_temp\_data\_file\_path](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_temp_data_file_path) system variable. MariaDB does not allow the creation of `ROW_FORMAT=COMPRESSED` temporary tables. All temporary tables are uncompressed. MariaDB deletes temporary tablespaces when the server shuts down gracefully and is recreated when it starts again. It cannot be placed on a raw device.

Internal temporary tablespaces, (that is, temporary tables that cannot be kept in memory) use either Aria or MyISAM, depending on the [aria\_used\_for\_temp\_tables](/docs/server/server-usage/storage-engines/aria/aria-system-variables.md#aria_used_for_temp_tables) system variable. You can set the default storage engine for user-created temporary tables using the [default\_tmp\_storage\_engine](/docs/server/server-management/variables-and-modes/server-system-variables.md#default_tmp_storage_engine) system variable.

Prior to [MariaDB 10.2](/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102.md), temporary tablespaces existed as part of the InnoDB [system](/docs/server/server-usage/storage-engines/innodb/innodb-tablespaces/innodb-system-tablespaces.md) tablespace or were file-per-table depending on the configuration of the [innodb\_file\_per\_table](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_file_per_table) system variable.

## Syntax for the value of the innodb\_temp\_data\_file\_path variable

The options for [innodb\_temp\_data\_file\_path](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_temp_data_file_path) is one path or a set of paths, separated by ';'.

The syntax for each path is:

```
path:size[:autoextend][:max:size][:autoshrink]
```

`size` can have extensions 'G' (Gigabytes), 'M' (Megabytes) or 'K' (Kilobytes). If no extension is given, then megabytes is assumed.

* The first `size` argument is the initial size of the temporary table space.
* `autoextend` means that the file size will automatically increase if needed.
* `max` can be used to limit the total size of the temporary file if `autoextend` is used.
* `autoshrink` means that the file will shrink to original size when possible.

Only the last path can have the `autoextend` , `max` and `autoshrink` options.

## Sizing Temporary Tablespaces

In order to size temporary tablespaces, use the [innodb\_temp\_data\_file\_path](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_temp_data_file_path) system variable. This system variable can be specified as a command-line argument to [mysqld](/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options.md) or it can be specified in a relevant server [option group](/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files.md#option-groups) in an [option file](/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files.md):

```ini
[mariadb]
...
innodb_temp_data_file_path=ibtmp1:32M:autoextend
```

This system variable's syntax is the same as the [innodb\_data\_file\_path](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_data_file_path) system variable. That is, a file name, size and option. By default, it writes a 12MB autoextending file to `ibtmp1` in the data directory.

To increase the size of the temporary tablespace, you can add a path to an additional tablespace file to the value of the [innodb\_temp\_data\_file\_path](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_temp_data_file_path) system variable. Providing additional paths allows you to spread the temporary tablespace between multiple tablespace files. The last file can have the `autoextend` attribute, which ensures that you won't run out of space:

```ini
[mariadb]
...
innodb_temp_data_file_path=ibtmp1:32M;ibtmp2:32M:autoextend
```

Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

## Shrinking the Tablespace

From [MariaDB 11.3.0](/docs/release-notes/community-server/old-releases/11.3/11.3.0.md), the temporary tablespace can be shrunk by setting [innodb\_truncate\_temporary\_tablespace\_now](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_truncate_temporary_tablespace_now) to `ON`:

```sql
SET GLOBAL innodb_truncate_temporary_tablespace_now=1;
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-tablespaces/innodb-temporary-tablespaces.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.