Bug #26300 | Description of Created_tmp_tables / Created_tmp_disk_tables status variables | ||
---|---|---|---|
Submitted: | 12 Feb 2007 23:18 | Modified: | 15 Oct 2009 14:29 |
Reporter: | Roland Bouman | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.1 | OS: | Any |
Assigned to: | MC Brown | CPU Architecture: | Any |
Tags: | Created_tmp_disk_tables, Created_tmp_tables |
[12 Feb 2007 23:18]
Roland Bouman
[12 Feb 2007 23:22]
Mark Leith
If you are going to be making changes here I would also like to see some reference (*anywhere* would do) noting that temporary tables are created on disk straight away if the temporary tables would include TEXT/BLOB columns - in this case increasing tmp_table_size would have no affect if you did have a large number of Created_tmp_disk_tables. I have never seen any reference to this anywhere within the manual.
[13 Feb 2007 8:16]
Sveta Smirnova
Thank you for the report. Verified as described.
[15 Oct 2009 14:29]
MC Brown
I've made sure the advice on temporary table sizes and the stats is now consistent across the different variables. I've also add the following paragraph: You can compare the number of disk tables created to the total number of tables created by comparing the values of the <literal role="statvar">Created_tmp_tables</literal> and <literal role="statvar">Created_tmp_disk_tables</literal> variables. Which should highlight how to compare the values appropriately. On the issue of temporary tables being stored on disk when there is a BLOB or TEXT column, we have a reference to this in http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html However, I have made a minor clarification in this sentence: Some conditions prevent the use of a MEMORY temporary table, in which case the server uses a MyISAM table instead: To read: Some conditions prevent the use of a MEMORY temporary table, in which case the server uses a MyISAM (on disk) table instead: (addition of 'on disk') to highlight that the table will be created using a MyISAM *disk-based* table.
[7 Dec 2012 10:50]
anh nx
I have a problem with created_tmp_tables and created_tmp_disk_tables: myconfig: tmp_table_size: 18M max_heap_table_size:16M I have a query: ---------------------------------------- SELECT A.Created_tmp_tables, B.Created_tmp_disk_tables FROM (SELECT G.`variable_value` as Created_tmp_tables FROM `information_schema`.GLOBAL_Status G WHERE G.variable_name = "Created_tmp_tables" ) A, (SELECT G.`variable_value` as Created_tmp_disk_tables FROM `information_schema`.GLOBAL_Status G WHERE G.variable_name = "Created_tmp_disk_tables" ) B; ---------------------------------- execute query, result is : created_tmp_tables: 124 created_tmp_disk_tables:13 ---------------------------- execute query again, result is: created_tmp_tables: 128 created_tmp_disk_tables:13 ---------------------------------------- Why does created_tmp_tables increases 128? ---------------------------------------- when I execute any query,why created_tem_tables always increase? -------------------------------------- I have a query : SELECT G.`variable_value` as Created_tmp_disk_tables FROM `information_schema`.GLOBAL_Status G WHERE G.variable_name = "Created_tmp_disk_tables" ; ---------- result: created_tmp_disk_tables:14 when I execute query again, created_tmp_disk_tables increases become 1 --> Why created_tem_disk_tables increases?