Comments on: Internal Temporary Tables in MySQL 5.7

By: dogofob

dogofob — Tue, 17 Dec 2019 16:54:05 +0000

Regarding the temp file path, one can avoid confusing relative paths by setting innodb_data_home_dir to an empty string.
https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html

By: Marko Mäkelä

Marko Mäkelä — Wed, 06 Dec 2017 15:35:07 +0000

While the InnoDB in MariaDB 10.2 is based on MySQL 5.7, the code for dealing with internal temporary tables was never enabled, and it was intentionally removed by me before the 10.2 GA release.
I believe that MyISAM (or in the case of MariaDB, Aria) are a better match for the query executor than InnoDB. InnoDB does not support hash indexes, and records are not deleted directly, but instead delete-marked.

By: Federico

Federico — Mon, 04 Dec 2017 22:06:43 +0000

In my current company we have 4 separate replication chains with different workloads and configurations. In all cases, using MyISAM with mmap for temporary tables is faster and sensibly reduces io_wait. I would advice users to test both options with their particular case.
Also, in our particular case, unexpected growth of ibtmp forced us to restart MySQL.

By: Federico

Federico — Mon, 04 Dec 2017 21:54:53 +0000

In reply to Phillip Orleans. MariaDB uses Aria for temporary tables, and this behaviour cannot be changed.

By: Abbas Ahmed

Abbas Ahmed — Mon, 04 Dec 2017 21:14:41 +0000

Thanks for highlighting something that’s easy to overlook, appreciated !

By: rautamiekka

rautamiekka — Mon, 04 Dec 2017 20:28:03 +0000

In reply to Manjot Singh. Double facepalm.

By: Manjot Singh

Manjot Singh — Mon, 04 Dec 2017 19:55:42 +0000

In reply to rautamiekka. As Alex said, its because its a *relative* path only.

By: Phillip Orleans

Phillip Orleans — Mon, 04 Dec 2017 19:53:18 +0000

How do you do this in MariaDB? The solution does not work with MariaDB

By: rautamiekka

rautamiekka — Mon, 04 Dec 2017 17:58:10 +0000

What’s the point in §../../../tmp/ibtmp1§ when you can do §/tmp/ibtmp1§ ? If you put too many §../§ you get access denied or something else for illegal path, for instance.