| tmp_table_size ignored? [message #330] |
Tue, 07 November 2006 19:44  |
 vgatto
Messages: 116
Registered: November 2006 |
Senior Member |
|
|
It looks like no matter what I do, one of my queries is always going to disk to create its temp file, instead of creating one in memory, and this is killing my server's performance.
I have an 8G machine running MySQL 4.1.16, and I've set my tmp_table_size=64M and my max_heap_table_size=64M. I've verified these settings using show variables. I'm checking to see if temp files are created by watching Created_tmp_disk_tables and the temp data directory. Temp files are always created, the largest being 20M in my tests, which is way under my settings (and in test I've bumped both the of the table settings from 64M all the way to 4G with no change in behavior).
My query doesn't use BLOB or TEXT fields at all, but is grouping by a VARCHAR. I've read on the forums that VARCHAR is automatically converted to CHAR for HEAP tables, with the cost being a much larger temp table due to the fixed size conversion. Even if I restrict my query to a tiny range of data (like 100 rows between the two tables I'm joining), so that the result set is under 10 rows, it will still create a temp file on disk.
If I change the query to group by an unindexed integer id in one of these tables, this query NEVER creates a disk temp table, even if I increase the source data range to be 10x the size of my original test. Basically, it looks like grouping by varchar equals disk based temp tables. Is anyone else seeing this?
On the bright side, using tmpfs does help the performance considerably, but it's not my preferred solution (and neither is normalizing my character data by some integer id).
|
|
|
|
| Re: tmp_table_size ignored? [message #334 is a reply to message #331 ] |
Wed, 08 November 2006 04:18  |
toasty
Messages: 19
Registered: August 2006
Location: UK |
Junior Member |
|
|
Hi Folks,
Temp tables are always created on disk if they use columns types that the in-memory temp table type (HEAP/MEMORY) doesn't support. Let me see if I can find something on this...
OK, so this http://dev.mysql.com/doc/refman/4.1/en/memory-use.html page says:
"Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk."
No idea what 'large' means in this case though.
There is a better explanation of this online somewhere, can't find it just at the mo though..
The trick (apart from putting your tmp dir on a ramdisk) is to try to remove blobs etc from getting in your tmp tables as part of the query. Usually not possible though I know...
HTH,
Toasty
[Updated on: Wed, 08 November 2006 04:19]
|
|
|
|
Members
Search
Help
Register
Login
Home
