From: Michael Dykman Date: September 9 2010 8:22pm Subject: Re: Trying to remove a filesort. List-Archive: http://lists.mysql.com/mysql/222911 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 wrote: > Its not advisiable...as this size will be allocated to all the session an= d > 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 =A0i am wrong. > > regards > anandkl > > On Fri, Sep 10, 2010 at 1:26 AM, Phil wrote: > >> It's in my.cnf. There is 12Gb in the database server and I watch it fair= ly >> carefully and have not gone into swap yet in the past few years. >> >> On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar 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 a= nd >> > paging will happen >> > >> > regards >> > anandkl >> > >> > On Fri, Sep 10, 2010 at 1:10 AM, Phil 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 eit= her >> >> :( >> >> >> >> On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman >> wrote: >> >> >> >> > How many rows before the GROUP BY? =A0Group by is, in effect a sort= ing >> >> > process.. =A0perhaps that contains enough data to justify going to = disk. >> >> > >> >> > What is the value of the variable sort_buffer_size? >> >> > =A0 =A0 =A0 =A0 show variables like '%sort%'; >> >> > >> >> > =A0- md >> >> > >> >> > On Thu, Sep 9, 2010 at 3:04 PM, Phil 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 >> >> > wrote: >> >> > >> >> >> > >> The filesort is probably necessary because of the number of rows= in >> >> > >> the result set to be ordered. =A0How many rows do you get out of= this >> >> > >> query? >> >> > >> >> >> > >> =A0- michael dykman >> >> > >> >> >> > >> On Thu, Sep 9, 2010 at 1:53 PM, Phil wrot= e: >> >> > >> > I wonder if anyone could help with a query which I've been una= ble >> >> to >> >> > >> > prevent >> >> > >> > from using a filesort. Might be something obvious I'm >> overlooking! >> >> > >> > >> >> > >> > I have a table which tracks milestones in distributed computin= g >> >> > projects >> >> > >> > >> >> > >> > Create Table: CREATE TABLE `boinc_milestone` ( >> >> > >> > =A0`proj` char(6) NOT NULL, >> >> > >> > =A0`id` int(11) NOT NULL, >> >> > >> > =A0`stat_date` date NOT NULL DEFAULT '0000-00-00', >> >> > >> > =A0`milestone_type` char(1) NOT NULL DEFAULT '0', >> >> > >> > =A0`milestone` double NOT NULL DEFAULT '0', >> >> > >> > =A0`cpid` varchar(32) DEFAULT NULL, >> >> > >> > =A0`team` int(11) DEFAULT NULL, >> >> > >> > =A0PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), >> >> > >> > =A0KEY `two` (`proj`,`stat_date`,`id`,`milestone`), >> >> > >> > =A0KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), >> >> > >> > =A0KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) >> >> > >> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 >> >> > >> > >> >> > >> > 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 g= oes >> >> on. >> >> > >> > >> >> > >> > Now on one page for display I want to show the latest mileston= e >> 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 >> >> > >> > =A0 =A0 =A0 =A0 from boinc_milestone a join boinc_projects b o= n a.proj =3D >> >> > b.proj >> >> > >> > =A0 =A0 =A0 =A0 where cpid =3D '$cpid' >> >> > >> > =A0 =A0 =A0 =A0 group by proj >> >> > >> > =A0 =A0 =A0 =A0 order by stat_date desc >> >> > >> > >> >> > >> > The order by causes the filesort and I can't find an easy way >> >> around >> >> > it. >> >> > >> > >> >> > >> > mysql> explain =A0select 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 =3D b.proj where cpid =3D 'XXX' group by proj order by >> >> stat_date\G >> >> > >> > *************************** 1. row *************************** >> >> > >> > =A0 =A0 =A0 =A0 =A0 id: 1 >> >> > >> > =A0select_type: SIMPLE >> >> > >> > =A0 =A0 =A0 =A0table: a >> >> > >> > =A0 =A0 =A0 =A0 type: ref >> >> > >> > possible_keys: PRIMARY,two,cpid,team >> >> > >> > =A0 =A0 =A0 =A0 =A0key: cpid >> >> > >> > =A0 =A0 =A0key_len: 35 >> >> > >> > =A0 =A0 =A0 =A0 =A0ref: const >> >> > >> > =A0 =A0 =A0 =A0 rows: 1 >> >> > >> > =A0 =A0 =A0 =A0Extra: Using where; Using index; Using temporar= y; Using >> >> > filesort >> >> > >> > *************************** 2. row *************************** >> >> > >> > =A0 =A0 =A0 =A0 =A0 id: 1 >> >> > >> > =A0select_type: SIMPLE >> >> > >> > =A0 =A0 =A0 =A0table: b >> >> > >> > =A0 =A0 =A0 =A0 type: eq_ref >> >> > >> > possible_keys: PRIMARY >> >> > >> > =A0 =A0 =A0 =A0 =A0key: PRIMARY >> >> > >> > =A0 =A0 =A0key_len: 10 >> >> > >> > =A0 =A0 =A0 =A0 =A0ref: stats.a.proj >> >> > >> > =A0 =A0 =A0 =A0 rows: 1 >> >> > >> > =A0 =A0 =A0 =A0Extra: Using where >> >> > >> > 2 rows in set (0.00 sec) >> >> > >> > >> >> > >> > I could just remove the order by altogether and perform the so= rt >> in >> >> > php >> >> > >> > afterwards I guess but any other ideas? >> >> > >> > >> >> > >> > Thanks >> >> > >> > >> >> > >> > Phil >> >> > >> > >> >> > >> > -- >> >> > >> > Distributed Computing stats >> >> > >> > http://stats.free-dc.org >> >> > >> > >> >> > >> >> >> > >> >> >> > >> >> >> > >> -- >> >> > >> =A0- michael dykman >> >> > >> =A0- mdykman@stripped >> >> > >> >> >> > >> =A0May the Source be with you. >> >> > > >> >> > > >> >> > > >> >> > > -- >> >> > > Distributed Computing stats >> >> > > http://stats.free-dc.org >> >> > > >> >> > >> >> > >> >> > >> >> > -- >> >> > =A0- michael dykman >> >> > =A0- mdykman@stripped >> >> > >> >> > =A0May the Source be with you. >> >> > >> >> >> >> >> >> >> >> -- >> >> Distributed Computing stats >> >> http://stats.free-dc.org >> >> >> > >> > >> >> >> -- >> =A0Distributed Computing stats >> http://stats.free-dc.org >> > --=20 =A0- michael dykman =A0- mdykman@stripped =A0May the Source be with you.