MySQL Bugs: #6702: MySQL creating disk-based tmp files when the size of tables is < tmp_table

Bug #6702	MySQL creating disk-based tmp files when the size of tables is < tmp_table_size
Submitted:	18 Nov 2004 18:02	Modified:	24 Jun 2005 13:09
Reporter:	Sean Leach	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Whitebox Linux 3.0
Assigned to:		CPU Architecture:	Any

Description:
I have 128MB dedicated for the tmp_table_size variable (see below).  But when I run a ton of queries at my database, it starts creating a lot of small disk-based tmp tables.  Watching the /tmp dir, I see tmp tables being creating at like 8KB.  It is never even close to 128MB, even when adding the sizes of all of them up (it's at most 1MB)

Here is my output from the show status, see how many have gone to disk?

mysql> show status like 'Created_tmp%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 70950  |
| Created_tmp_files       | 3      |
| Created_tmp_tables      | 209942 |
+-------------------------+--------+

Here is my tmp_table_size  value.  This problem is really slowing down our system.

mysql> show variables like '%tmp%';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| max_tmp_tables | 32        |
| tmp_table_size | 134217728 |
| tmpdir         |           |
+----------------+-----------+

What do I need to do?

How to repeat:
Happens whenever multiple tmp tables would be created at the same time (like 10-15)

Suggested fix:
no idea :)

Hi,

What is the value of max_heap_table_size?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Do you have queries that need to do a "using filesort; using temporary" that include a TEXT or BLOG field in their resultset?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".