List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:September 9 2010 8:08pm
Subject:Re: Trying to remove a filesort.
View as plain text  
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
>

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