Ofer Inbar a écrit : > 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 >
The binlog are creating most of your constant write most probably. If you have no slave attached, you're not reading them at all... -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
