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....@gmail.com> 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 <anan...@gmail.com> 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....@gmail.com> 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 <mdyk...@gmail.com> > 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....@gmail.com> 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 <mdyk...@gmail.com> > >> > 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....@gmail.com> 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 > >> > >> - mdyk...@gmail.com > >> > >> > >> > >> May the Source be with you. > >> > > > >> > > > >> > > > >> > > -- > >> > > Distributed Computing stats > >> > > http://stats.free-dc.org > >> > > > >> > > >> > > >> > > >> > -- > >> > - michael dykman > >> > - mdyk...@gmail.com > >> > > >> > May the Source be with you. > >> > > >> > >> > >> > >> -- > >> Distributed Computing stats > >> http://stats.free-dc.org > >> > > > > > > > -- > Distributed Computing stats > http://stats.free-dc.org >
