mos <[EMAIL PROTECTED]> wrote: > >Why are so many small tmp tables being created on disk, not memory? > >How can I tell MySQL to use memory for these?
> 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. I thought of putting tmpdir on a tmpfs mount, worried that there might occasionally be a need for a very large tmp file that would exceed the limit (or, if I don't set a limit, use up all memory and force lots of swapping). When you say "first MySQL temp directory" are you implying I can have more than one? I don't see anything in the documentation that suggests that... BTW, here's another oddity I noticed - here's typical output from "iostat 60": | avg-cpu: %user %nice %sys %iowait %idle | 7.35 0.00 3.59 0.94 88.12 | | Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn | sda 29.20 8.00 342.08 480 20528 | sda1 0.00 0.00 0.00 0 0 | sda2 0.00 0.00 0.00 0 0 | sda3 0.00 0.00 0.00 0 0 | sda4 0.00 0.00 0.00 0 0 | sda5 43.74 8.00 342.08 480 20528 | sdb 2.43 0.00 173.70 0 10424 | sdb1 21.71 0.00 173.70 0 10424 I've been running this for a few hours and it consistently shows lots of writes but no reads at all on sdb1, the partition where I have my binary logs and tmpdir. Is MySQL writing lots of tmp files and not reading them? Or, how else can I interpret this? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
