From: Mathieu Bruneau (mathieu.bruneauargontechnologies.ca)
Date: Tue Jul 17 2007 - 22:09:10 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Ofer Inbar a écrit :
> mos <mos99fastmail.fm> 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]