"miha vrhovnik @ domenca" <miha.vrhov...@domenca.si> wrote on 7.7.2010 7:21:52:
>"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. > Ha found it: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html -- 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