"Dave McGuire" <mcgu...@neurotica.com> wrote on 6.7.2010 20:31:24:
>On 7/6/10 1:27 PM, Rodolfo Gonzalez wrote: >>>>> The thing that gets me is the Query time being 264 seconds that when >>>>> watching status sits in status 'Copying to tmp table'. I know InnoDB >>>> >>>> You got it. When MySQL goes "copying to tmp table"... that's bad. Which >>>> version are you using? Which fs for your tmp partition? >>> >>> Using MySQL 5.1.37 >>> Filesystem is ext3, noatime option at mount. >>> >>> I'm confident the /tmp partition is not used by mysql/innodb. Copying >>> to tmp table is a memory thing in SQL when joining several tables so >>> it can compile results and return them. It's this process that seems >>> super slow. >> >> Hmmm, I've seen MySQL 5.0 creating tmp tables on disk... > > Seconded...I saw it this morning in /var/tmp with an innodb table.. > >From my memory... 1. Mysql is creating tmp table on disk as soon as there is join and blob in list of returned columns or you are searching in blob column. 2. Also It's gonna move to disk tmp tables when it thinks it's going to run out of memory for specific join. We had a case as described in 1 a while ago. What I found annoying is that you are unable to find which query created which temp table on disk so you could kill it. -- No system, however painstakingly designed, can withstand the destructive force of an idiot who's found the power switch. - Z lepimi pozdravi / Kind regards, Miha Vrhovnik ------------------------------------------------------------------------------ This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone? Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user Please visit http://www.ijs.si/software/amavisd/ regularly For administrativa requests please send email to rainer at openantivirus dot org
