Kevin, Forgot to mention that I think you're not seeing any temporary files because they're hidden. From http://www.mysql.com/doc/en/Temporary_files.html
"MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located." But then it also says: "For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'." So I dunno. :-) But I'm thinking they ARE hidden... if there's any files at all. It may be just an in memory temp HEAP table if it's smaller than tmp_table_size and no TEXT/BLOB columns are used in the query (e.g. possible length > 255). I don't think there's much tuning that can be done to the server. The query and/or tables may need to be changed, if possible, to examine/return less rows. Also, what's the EXPLAIN SELECT output look like for the queries (the original and modified)? Just change the names of the tables/columns if you can't let them be seen. :-) Hint: use \G instead of ; at the end of the EXPLAIN query to make the output more legible. Matt ----- Original Message ----- From: "Kevin Fries" Sent: Friday, September 05, 2003 11:09 AM Subject: RE: Query hung up in "Copying to tmp table" > Matt, > > Thanks for your response. It's going to take a bit for me to get the > actual SQL available. I don't yet have permission to show that off. In > the mean time, I can tell you that there are 10 table in the query, with > 4 being references to the same table. I've been able to make an > improvement that greatly improves the speed... by over-joining some > records. That is, I'm joining 3 tables such that A joins to B, B joins > to C, and C joins to A, basically. That was enough to let mySQL sort > out a better plan. > > Interestingly though, the result of the query (and it appears to be > proper) is 0 rows. I believe it's the intermediate row set that may be > huge. But I've never seen mysql spit out a temporary file while > processing. > > Again, I'd really like someone's reference to a tuning guide, if there's > a superior one available. > > Thanks again, > Kevin > > > -----Original Message----- > > From: Matt W > > Sent: Thursday, September 04, 2003 1:37 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Query hung up in "Copying to tmp table" > > > > > > Hi Kevin, > > > > I imagine the query is examining/returning so many rows that > > it's going to take a long time to create the needed temporary > > table (how long have you it go?). To start with, show us the > > EXPLAIN output for the problem SELECT, along with the SELECT. > > Also the size of the involved tables (rows and MB). > > > > > > Matt > > > > > > ----- Original Message ----- > > From: "Kevin Fries" > > Sent: Wednesday, September 03, 2003 6:51 PM > > Subject: Query hung up in "Copying to tmp table" > > > > > > > I'm having a problem where a complex SELECT query begins eating up > > lots > > > of CPU and never returns. In "show processlist", it > > > reports "Copying to tmp table". Other queries seem to get locked > > > waiting for this query, as well. > > > > > > This is on version 3.23.36 of mySQL, and I'm considering > > upgrading to > > > the latest 3.23.57, to see if that improves the > > performance. I can't > > > find any matching references in the Changes web pages that indicate > > > this, though. Can anyone verify this? > > > > > > Alternately, I'm trying to find ways to possibly improve the step of > > > "copying to tmp table". According to my "show variables" > > output, the > > > "tmpdir" is /tmp, and /tmp has plenty of room (85 megs or so). But > > > while the query is running, I don't even see a corresponding file > > there. > > > The CPU is spiking with top reporting mysql as eating 85% > > CPU. So I > > > then assume that the 'problem' is happening before the file is > > created? > > > Does that sound right? > > > > > > If someone has a better reference for tracking down the source of a > > > problem like this, other than > > > http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very > > > appreciative. > > > > > > thanks, > > > Kevin Fries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
