List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 26 2005 2:40pm
Subject:Re: performance problem on INSERT into MyISAM table
View as plain text  
I must assume you have all the proper indexes setup and your 
configuration variables are fairly optimal.

First, I would run just the select part with an explain in front of it 
to see what MySQL is trying to do. I've had MySQL run a query for an 
inordinate amount of time on a fairly small data set because of a typo 
and no index in use for a join.

Second, I would check the size of the data file to see if it is 
growing. At least you'll be able to see if something is going on.

Third, I would try just the select part with a limit of say 10 or 20. 
This will show you how long MySQL is taking to do the search and joins, 
eliminating data transfer time.

You need to find where it's bottlenecking. It may very well be that you 
are I/O bound (iostat). MySQL is reading from the tables, certainly 
using temporary tables with a data set of that size and trying to load 
data into a new table. That's a lot of reads and writes going on at 
once, your disk(s) may be getting hammered, especially if you are not 
using RAID.

You could also try disabling indexes on table 3 (if you have any) and 
then enabling after the import is done. That will speed up the import 
process.

Lastly, instead of doing and insert select, just select to a file (INTO 
OUTFILE). Then import the data using load data infile. A two step 
processing, but something that will allow you to control all the I/O 
that's going on.

On Apr 26, 2005, at 9:22 AM, Ed Sweeney wrote:

> I have been trying to run an fairly large INSERT into an empty table 
> joining two other tables now for several weeks and have not been able 
> to get the query to run to completion even when sub-seting the data 
> into smaller ranges.
>
>   
>
> I have tried this at MySQL releases 4.1.8a and 4.1.10a with no 
> noticable improvement.
>
> The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20.
>
> The server is dedicated to MySQL. The my.cnf file is attached.
>
>  There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 
> processors).
>
> SHOW STATUS shows very good buffer hit ratio. Current stats are 
> attached. Server was re-booted last Friday. It has been running the 
> INSERT query since Saturday mid-morning and has yet to complete.
>
>  
>
> Table 1             
> 230 million rows total      compund PK index – 4 
> columns  range 1 should select 35 million rows. Explain plan shows it 
> to be using the PK
>
> Table 2             
> 598 million rows            
> compound PK index – 
> 4 columns and one secondary index. Range 1 should select about 130 
> million rows
>
>  
>
> Table 3             
> Empty table unindexed. Two keys from Table 1 and 
> 17  columns from table 2 populate this table
>
>  
>
> The general form of the query is:
>
>  
>
> INSERT INTO Table 3 (col1, …. Col18)
>
> SELECT
>
>             Col1,,.col18
>
> FROM
>
>             Table 1 a
>
> INNER JOIN Table 2 b ON (PK columns and range selection)
>
> WHERE
>
>             a.col5 = b.col5
>
> AND
>
>             ….
>
> AND
>
>             …
>
> AND
>
>             a.col18 = b.col18
>
>  
>
>  
>
> Any suggestions are welcome.
>
>  
>
> Ed Sweeney
>
>  
> <show_status.txt>--
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
performance problem on INSERT into MyISAM tableEd Sweeney26 Apr
  • Re: performance problem on INSERT into MyISAM tableBrent Baisley26 Apr