At 12:29 PM 7/17/2007, you wrote:
MySQL 5.0.27 running on Redhat EL4.
In /etc/my.cnf I have:
tmp_table_size=64M
mysql> SHOW GLOBAL VARIABLES LIKE "%tmp%";
+-------------------+------------------+
| Variable_name | Value |
+-------------------+------------------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /data/mysql/tmp/ |
| tmp_table_size | 67108864 |
| tmpdir | /data/mysql/tmp/ |
+-------------------+------------------+
... and yet, I frequently see tmp tables on disk much smaller than 64M:
#ls -alF /data/mysql/tmp/
total 1552
drwxr-xr-x 2 mysql mysql 4096 Jul 17 14:16 ./
drwxrwxr-x 4 mysql mysql 4096 Jul 17 13:12 ../
-rw-rw---- 1 mysql mysql 1572864 Jul 17 14:16 #sql_3b9e_0.MYD
-rw-rw---- 1 mysql mysql 1024 Jul 17 14:16 #sql_3b9e_0.MYI
#
Although I never see more than one tmp table in that directory at a
time (they go so quickly, usually I don't see any), I have a program
that tracks the rate of change of some variables from mysqladmin
extended, and my MySQL servers are consistently created 3-10 or more
disk tmp tables per second. When I do see a table in tmpdir, though,
it's almost always smaller than 5M (I've been running a job to check
every few seconds, and the largest one I've seen so far was ~12M).
Why are so many small tmp tables being created on disk, not memory?
How can I tell MySQL to use memory for these?
-- Cos
Cos,
I'd guess these temporary files are the result of Select statements
with an Order By clause that requires a FileSort. You can do a Show
ProcessList and this should tell you if the active queries are using
"FileSort". As far as getting it to sort in memory? Your guess is as good
as mine. Even though I have an index that matches the sort order, MySQL
insists on sorting it using FileSort. I suppose you could set up a Ram disk
and have that as your first MySQL temp directory.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
