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
