Bug #49196 Clarify how TEMPORARY tables work (in terms of memory use) on create-table page
Submitted: 30 Nov 2009 8:20 Modified: 1 Dec 2009 16:27
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Triage: Needs Triage: D4 (Minor)

[30 Nov 2009 8:20] Roel Van de Paar
Description:
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

States:

'A temporary table that is created initially as a MEMORY table might be converted to a MyISAM table and stored on disk if it becomes too large.'

But it does not discuss non-internal explicitly-created TEMPORARY tables (see bug #49176).

Now, maybe the 'create-table.html' page could clarify how this works for TEMPORARY tables?

The structure of a TEMPORARY table (for instance #.MYD/.MYI/.frm for MyISAM) gets created on disk immediately, but the way that these files are used is somewhat different for Linux and Windows:

On Linux, it seems that any data written to a TEMPORARY table will be written to disk (for instance to a .MYD file for MyISAM) immediately.

On Windows, as the attached example shows, the data goes through 3 stages, depending on size: when small, it is retained in memory (confirmed by total disk space not shrinking), then when it grows (in memory size), it is cached by Windows (on disk, shown by the total disk space shrinking, but no increase in the data file), and lastly, when the data grows substantially, it is written to the disk by mysqld.

How to repeat:
See attached file for Windows test.

Suggested fix:
Clarify the above on the create-table.html page for CREATE TEMPORARY TABLE.
[30 Nov 2009 8:27] Roel Van de Paar
Windows test

Attachment: Bug_49196_Windows.txt (text/plain), 5.61 KiB.

[30 Nov 2009 8:47] Roel Van de Paar
Also note bug #26300
[1 Dec 2009 16:27] Paul Dubois
There's nothing here to indicate a difference between how TEMPORARY and non-TEMPORARY tables are handled.

Anyway, what you're observing isn't something we can specify. I asked Serg, his response was:

we have no control on how OS caches the data and when it writes them to disk