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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
