Contents
Can a table cause disk exhaustion in MySQL?
In some particular cases, you can have disk exhaustion and server outage. As any other InnoDB table in the database, the temporary tables have their own tablespace file. The new file is in the data directory together with the general tablespace, with the name ibtmp1.
Which is the best default for MySQL temporary tables?
The new default is the best option for the overall performance and should be used in the majority of the cases. A new configuration variable is available to set the storage engine for the temporary tables: internal_tmp_disk_storage_engine. The variable can be set to innodb (default if not set) or myisam.
Why do we need implicit temporary tables in MySQL?
MySQL needs to create implicit temporary tables for solving some kinds of queries. The queries that require a sorting stage most of the time need to rely on a temporary table. For example, when you use GROUP BY, ORDER BY or DISTINCT.
Where is the ibtmp1 file in MySQL?
The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE. The ibtmp1 makes no difference, as you cannot use OPTIMIZE.
Is there a limit to the size of MySQL?
A MySQL restart is required. Please note that the path has to be specified as relative to the data directory. In this case, the file cannot grow more than 10GB. You can easily eliminate the outages, but this is a dangerous solution. When the data file reaches the maximum size, queries fail with an error indicating that the table is full.
How to move ibtmp1 file to a larger disk?
You can also move the ibtmp1 file on a dedicated large disk, by setting the following configuration variable: A MySQL restart is required. Please note that the path has to be specified as relative to the data directory. In this case, the file cannot grow more than 10GB.
Where is the new table in MySQL stored?
The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE.