List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:September 9 2010 8:22pm
Subject:Re: Trying to remove a filesort.
View as plain text  
You make an excellent point.  If there are a lot of connections to
that server, many sort buffers may be in use and can squeeze ram out
of the rest of the system.  2M is a pretty good choice.

 - md

On Thu, Sep 9, 2010 at 4:08 PM, Ananda Kumar <anandkl@stripped> wrote:
> Its not advisiable...as this size will be allocated to all the session and
> cause system running out of memory.
> It should be set at session and in my.cnf it should be around 2 MB.
>
> Please correct if  i am wrong.
>
> regards
> anandkl
>
> On Fri, Sep 10, 2010 at 1:26 AM, Phil <freedc.bok@stripped> wrote:
>
>> It's in my.cnf. There is 12Gb in the database server and I watch it fairly
>> carefully and have not gone into swap yet in the past few years.
>>
>> On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar <anandkl@stripped> wrote:
>>
>> > have u set sort_buffer_size at session level or in my.cnf.
>> > Setting high value in my.cnf, will cause mysql to run out off MEMORY and
>> > paging will happen
>> >
>> > regards
>> > anandkl
>> >
>> > On Fri, Sep 10, 2010 at 1:10 AM, Phil <freedc.bok@stripped> wrote:
>> >
>> >> Even prior to the group by it's still not likely to ever be more than
>> 200
>> >> or
>> >> so maximum.
>> >>
>> >> I have the sort_buffer_size at 256Mb so I don't believe it's that
> either
>> >> :(
>> >>
>> >> On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman
> <mdykman@stripped>
>> wrote:
>> >>
>> >> > How many rows before the GROUP BY?  Group by is, in effect a
> sorting
>> >> > process..  perhaps that contains enough data to justify going
> to disk.
>> >> >
>> >> > What is the value of the variable sort_buffer_size?
>> >> >         show variables like '%sort%';
>> >> >
>> >> >  - md
>> >> >
>> >> > On Thu, Sep 9, 2010 at 3:04 PM, Phil <freedc.bok@stripped>
> wrote:
>> >> > > On average it would be between 10 and 40, certainly no more
> than
>> 100.
>> >> > >
>> >> > >
>> >> > > On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman
> <mdykman@stripped>
>> >> > wrote:
>> >> > >>
>> >> > >> The filesort is probably necessary because of the number
> of rows in
>> >> > >> the result set to be ordered.  How many rows do you
> get out of this
>> >> > >> query?
>> >> > >>
>> >> > >>  - michael dykman
>> >> > >>
>> >> > >> On Thu, Sep 9, 2010 at 1:53 PM, Phil
> <freedc.bok@stripped> wrote:
>> >> > >> > I wonder if anyone could help with a query which I've
> been unable
>> >> to
>> >> > >> > prevent
>> >> > >> > from using a filesort. Might be something obvious
> I'm
>> overlooking!
>> >> > >> >
>> >> > >> > I have a table which tracks milestones in distributed
> computing
>> >> > projects
>> >> > >> >
>> >> > >> > Create Table: CREATE TABLE `boinc_milestone` (
>> >> > >> >  `proj` char(6) NOT NULL,
>> >> > >> >  `id` int(11) NOT NULL,
>> >> > >> >  `stat_date` date NOT NULL DEFAULT
> '0000-00-00',
>> >> > >> >  `milestone_type` char(1) NOT NULL DEFAULT '0',
>> >> > >> >  `milestone` double NOT NULL DEFAULT '0',
>> >> > >> >  `cpid` varchar(32) DEFAULT NULL,
>> >> > >> >  `team` int(11) DEFAULT NULL,
>> >> > >> >  PRIMARY KEY
> (`proj`,`id`,`stat_date`,`milestone`),
>> >> > >> >  KEY `two`
> (`proj`,`stat_date`,`id`,`milestone`),
>> >> > >> >  KEY `cpid`
> (`cpid`,`proj`,`id`,`stat_date`,`milestone`),
>> >> > >> >  KEY `team`
> (`proj`,`team`,`id`,`stat_date`,`milestone`)
>> >> > >> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>> >> > >> >
>> >> > >> > These are added to on a daily basis as users pass the
> various
>> >> credit
>> >> > >> > milestones so for instance you can end up with rows
> for
>> >> > >> > 1000,5000,10000,50000,1000000 etc on different dates
> as time goes
>> >> on.
>> >> > >> >
>> >> > >> > Now on one page for display I want to show the latest
> milestone
>> for
>> >> > each
>> >> > >> > project for a particular cpid. The query I use is as
> follows:
>> >> > >> >
>> >> > >> > select a.proj,a.id,max(stat_date),max(a.milestone)
> as
>> >> > >> > milestone,b.description
>> >> > >> >         from boinc_milestone a
> join boinc_projects b on a.proj =
>> >> > b.proj
>> >> > >> >         where cpid = '$cpid'
>> >> > >> >         group by proj
>> >> > >> >         order by stat_date desc
>> >> > >> >
>> >> > >> > The order by causes the filesort and I can't find an
> easy way
>> >> around
>> >> > it.
>> >> > >> >
>> >> > >> > mysql> explain  select a.proj,a.id
>> ,max(stat_date),max(a.milestone)
>> >> as
>> >> > >> > milestone,b.description from boinc_milestone a join
>> boinc_projects
>> >> b
>> >> > on
>> >> > >> > a.proj = b.proj where cpid = 'XXX' group by proj
> order by
>> >> stat_date\G
>> >> > >> > *************************** 1. row
> ***************************
>> >> > >> >           id: 1
>> >> > >> >  select_type: SIMPLE
>> >> > >> >        table: a
>> >> > >> >         type: ref
>> >> > >> > possible_keys: PRIMARY,two,cpid,team
>> >> > >> >          key: cpid
>> >> > >> >      key_len: 35
>> >> > >> >          ref: const
>> >> > >> >         rows: 1
>> >> > >> >        Extra: Using where; Using
> index; Using temporary; Using
>> >> > filesort
>> >> > >> > *************************** 2. row
> ***************************
>> >> > >> >           id: 1
>> >> > >> >  select_type: SIMPLE
>> >> > >> >        table: b
>> >> > >> >         type: eq_ref
>> >> > >> > possible_keys: PRIMARY
>> >> > >> >          key: PRIMARY
>> >> > >> >      key_len: 10
>> >> > >> >          ref: stats.a.proj
>> >> > >> >         rows: 1
>> >> > >> >        Extra: Using where
>> >> > >> > 2 rows in set (0.00 sec)
>> >> > >> >
>> >> > >> > I could just remove the order by altogether and
> perform the sort
>> in
>> >> > php
>> >> > >> > afterwards I guess but any other ideas?
>> >> > >> >
>> >> > >> > Thanks
>> >> > >> >
>> >> > >> > Phil
>> >> > >> >
>> >> > >> > --
>> >> > >> > Distributed Computing stats
>> >> > >> > http://stats.free-dc.org
>> >> > >> >
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >> --
>> >> > >>  - michael dykman
>> >> > >>  - mdykman@stripped
>> >> > >>
>> >> > >>  May the Source be with you.
>> >> > >
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Distributed Computing stats
>> >> > > http://stats.free-dc.org
>> >> > >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> >  - michael dykman
>> >> >  - mdykman@stripped
>> >> >
>> >> >  May the Source be with you.
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Distributed Computing stats
>> >> http://stats.free-dc.org
>> >>
>> >
>> >
>>
>>
>> --
>>  Distributed Computing stats
>> http://stats.free-dc.org
>>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
Trying to remove a filesort.Phil9 Sep
  • Re: Trying to remove a filesort.Michael Dykman9 Sep
    • Re: Trying to remove a filesort.Phil9 Sep
      • Re: Trying to remove a filesort.Michael Dykman9 Sep
        • Re: Trying to remove a filesort.Phil9 Sep
          • Re: Trying to remove a filesort.Ananda Kumar9 Sep
            • Re: Trying to remove a filesort.Phil9 Sep
              • Re: Trying to remove a filesort.Ananda Kumar9 Sep
                • Re: Trying to remove a filesort.Michael Dykman9 Sep
  • RE: Trying to remove a filesort.Travis Ard10 Sep
    • Re: Trying to remove a filesort.Phil10 Sep