MySQL ODBC Connector - performance problem on INSERT into MyISAM table

Author

performance problem on INSERT into MyISAM table

Ed Sweeney

2005-04-26, 9:23 am

------ _=_NextPart_001_01C5
4A63.06AF5C51
Content-Type: multipart/alternative;
boundary="---- _=_NextPart_002_01C5
4A63.06AF5C51"

------ _=_NextPart_002_01C5
4A63.06AF5C51
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.=20
=20
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.=20
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.
=20
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
=20
Table 3 Empty table unindexed. Two keys from Table 1 and 17
columns from table 2 populate this table
=20
The general form of the query is:
=20
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 =3D b.col5
AND
....
AND
...
AND
a.col18 =3D b.col18
=20
=20
Any suggestions are welcome.
=20
Ed Sweeney
=20

------ _=_NextPart_002_01C5
4A63.06AF5C51
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:st1=3D"urn:schemas-microsoft- com:office:smarttags
" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DProgId content=3DWord.Document>
<meta name=3DGenerator content=3D"Microsoft Word 10">
<meta name=3DOriginator content=3D"Microsoft Word 10">
<link rel=3DFile-List href=3D"cid:filelist.xml@01C54A41.78C3E180">
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft- com:office:smarttags
"
name=3D"PersonName" downloadurl=3D"http://www.microsoft.com"/>

<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple =
style=3D'tab-interval:.5in'>

<div class=3DSection1>

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. <o:p></o:p>

<o:p> </o:p>

I have tried this at MySQL releases 4.1.8a and =
4.1.10a with
no noticable improvement.<o:p></o:p>

The server is running Linux 2.4.21-4 Elsmp RedHat =
3.2.3.-20.<o:p></o:p>

The server is dedicated to MySQL. The my.cnf file is
attached. <o:p></o:p>

There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs =
(top
shows 4 processors).<o:p></o:p>

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.<o:p></o:p>

<o:p> </o:p>

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<o:p></o:p>

Table 2 =
; 598
million rows =
; compound
PK index – 4 columns and one secondary index. Range 1 should =
select about
130 million rows<o:p></o:p>

<o:p> </o:p>

Table 3 =
; Empty
table unindexed. Two keys from Table 1 and 17 columns from table 2 =
populate this
table<o:p></o:p>

<o:p> </o:p>

The general form of the query =
is:<o:p></o:p>

<o:p> </o:p>

INSERT INTO Table 3 (col1, =
….
Col18)<o:p></o:p>

SELECT<o:p></o:p>

 =
; Col1,,.col18<o:p></o:p>

FROM<o:p></o:p>

 =
; Table
1 a<o:p></o:p>

INNER JOIN Table 2 b ON (PK columns and range =
selection)<o:p></o:p>

WHERE<o:p></o:p>

 =
; a.col5
=3D b.col5<o:p></o:p>

AND<o:p></o:p>

 =
; ….<o:p></o:p>

AND<o:p></o:p>

 =
; …<o:p></o:p>

AND<o:p></o:p>

 =
; a.col18
=3D b.col18<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

Any suggestions are =
welcome.<o:p></o:p>

<o:p> </o:p>

<st1:PersonName>Ed =
Sweeney</st1:PersonName><o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>
=00
------ _=_NextPart_002_01C5
4A63.06AF5C51--

------ _=_NextPart_001_01C5
4A63.06AF5C51
Content-Type: text/plain;
name="show_status.txt"
Content-Transfer-Encoding: base64
Content-Description: show_status.txt
Content-Disposition: attachment;
filename="show_status.txt"

VmFyaWFibGVfbmFtZQkJ
VmFsdWUNCkFib3J0ZWRf
Y2xpZW50cwkJNTINCkFi
b3J0ZWRfY29ubmVj
dHMJNQ0KQmlubG9nX2Nh
Y2hlX2Rpc2tfdXNlCTAN
CkJpbmxvZ19jYWNoZV91
c2UJMA0KQnl0ZXNf
cmVjZWl2ZWQJCTMzNzE0
OA0KQnl0ZXNfc2VudAkJ
NDA2ODAwMTENCkNvbV9h
ZG1pbl9jb21tYW5k
cwkwDQpDb21fYWx0ZXJf
ZGIJCTANCkNvbV9hbHRl
cl90YWJsZQkJMA0KQ29t
X2FuYWx5emUJCTAN
CkNvbV9iYWNrdXBfdGFi
bGUJMA0KQ29tX2JlZ2lu
CQkwDQpDb21fY2hhbmdl
X2RiCQkzOA0KQ29t
X2NoYW5nZV9tYXN0ZXIJ
MA0KQ29tX2NoZWNrCQkw
DQpDb21fY2hlY2tzdW0J
CTANCkNvbV9jb21t
aXQJCTANCkNvbV9jcmVh
dGVfZGIJCTANCkNvbV9j
cmVhdGVfZnVuY3Rpb24J
MA0KQ29tX2NyZWF0
ZV9pbmRleAkwDQpDb21f
Y3JlYXRlX3RhYmxlCTEN
CkNvbV9kZWFsbG9jX3Nx
bAkJMA0KQ29tX2Rl
bGV0ZQkJMA0KQ29tX2Rl
bGV0ZV9tdWx0aQkwDQpD
b21fZG8JMApDb21fZHJv
cF9kYgkwDQpDb21f
ZHJvcF9mdW5jdGlvbgkw
DQpDb21fZHJvcF9pbmRl
eAkJMA0KQ29tX2Ryb3Bf
dGFibGUJCTANCkNv
bV9kcm9wX3VzZXIJCTAN
CkNvbV9leGVjdXRlX3Nx
bAkJMA0KQ29tX2ZsdXNo
CQkzDQpDb21fZ3Jh
bnQJCTQNCkNvbV9oYV9j
bG9zZQkJMA0KQ29tX2hh
X29wZW4JCTANCkNvbV9o
YV9yZWFkCQkwDQpD
b21faGVscAkJMA0KQ29t
X2luc2VydAkJMA0KQ29t
X2luc2VydF9zZWxlY3QJ
NA0KQ29tX2tpbGwJ
CTINCkNvbV9sb2FkCQkw
DQpDb21fbG9hZF9tYXN0
ZXJfZGF0YQkwDQpDb21f
bG9hZF9tYXN0ZXJf
dGFibGUJMA0KQ29tX2xv
Y2tfdGFibGVzCQkwDQpD
b21fb3B0aW1pemUJCTAN
CkNvbV9wcmVsb2Fk
X2tleXMJMA0KQ29tX3By
ZXBhcmVfc3FsCQkwDQpD
b21fcHVyZ2UJCTANCkNv
bV9wdXJnZV9iZWZv
cmVfZGF0ZQkwDQpDb21f
cmVuYW1lX3RhYmxlCTAN
CkNvbV9yZXBhaXIJCTAN
CkNvbV9yZXBsYWNl
CQkwDQpDb21fcmVwbGFj
ZV9zZWxlY3QJMA0KQ29t
X3Jlc2V0CQkwDQpDb21f
cmVzdG9yZV90YWJs
ZQkwDQpDb21fcmV2b2tl
CQkwDQpDb21fcmV2b2tl
X2FsbAkJMA0KQ29tX3Jv
bGxiYWNrCQkwDQpD
b21fc2F2ZXBvaW50CQkw
DQpDb21fc2VsZWN0CQk5
MA0KQ29tX3NldF9vcHRp
b24JCTE1DQpDb21f
c2hvd19iaW5sb2dfZXZl
bnRzCTANCkNvbV9zaG93
X2JpbmxvZ3MJMA0KQ29t
X3Nob3dfY2hhcnNl
dHMJMQ0KQ29tX3Nob3df
Y29sbGF0aW9ucwkzNA0K
Q29tX3Nob3dfY29sdW1u
X3R5cGVzCTANCkNv
bV9zaG93X2NyZWF0ZV9k
YgkwDQpDb21fc2hvd19j
cmVhdGVfdGFibGUJMg0K
Q29tX3Nob3dfZGF0
YWJhc2VzCTMNCkNvbV9z
aG93X2Vycm9ycwkJMA0K
Q29tX3Nob3dfZmllbGRz
CQkyNQ0KQ29tX3No
b3dfZ3JhbnRzCQkwDQpD
b21fc2hvd19pbm5vZGJf
c3RhdHVzCTANCkNvbV9z
aG93X2tleXMJCTIw
DQpDb21fc2hvd19sb2dz
CQkwDQpDb21fc2hvd19t
YXN0ZXJfc3RhdHVzCTAN
CkNvbV9zaG93X25l
d19tYXN0ZXIJMA0KQ29t
X3Nob3dfb3Blbl90YWJs
ZXMJMA0KQ29tX3Nob3df
cHJpdmlsZWdlcwkw
DQpDb21fc2hvd19wcm9j
ZXNzbGlzdAk2NjAzDQpD
b21fc2hvd19zbGF2ZV9o
b3N0cwkwDQpDb21f
c2hvd19zbGF2ZV9zdGF0
dXMJMA0KQ29tX3Nob3df
c3RhdHVzCTY2MTkNCkNv
bV9zaG93X3N0b3Jh
Z2VfZW5naW5lcwkwDQpD
b21fc2hvd190YWJsZXMJ
CTUNCkNvbV9zaG93X3Zh
cmlhYmxlcwkyOQ0K
Q29tX3Nob3dfd2Fybmlu
Z3MJMA0KQ29tX3NsYXZl
X3N0YXJ0CQkwDQpDb21f
c2xhdmVfc3RvcAkJ
MA0KQ29tX3RydW5jYXRl
CQkwDQpDb21fdW5sb2Nr
X3RhYmxlcwkwDQpDb21f
dXBkYXRlCQkwDQpD
b21fdXBkYXRlX211bHRp
CTANCkNvbm5lY3Rpb25z
CQk4OQ0KQ3JlYXRlZF90
bXBfZGlza190YWJs
ZXMJMg0KQ3JlYXRlZF90
bXBfZmlsZXMJMw0KQ3Jl
YXRlZF90bXBfdGFibGVz
CTQNCkRlbGF5ZWRf
ZXJyb3JzCQkwDQpEZWxh
eWVkX2luc2VydF90aHJl
YWRzCTANCkRlbGF5ZWRf
d3JpdGVzCQkwDQpG
bHVzaF9jb21tYW5kcwkJ
MQ0KSGFuZGxlcl9jb21t
aXQJCTANCkhhbmRsZXJf
ZGVsZXRlCQkwDQpI
YW5kbGVyX2Rpc2NvdmVy
CTANCkhhbmRsZXJfcmVh
ZF9maXJzdAkxNA0KSGFu
ZGxlcl9yZWFkX2tl
eQkzNDY2NDQ0Mg0KSGFu
ZGxlcl9yZWFkX25leHQJ
MjQ2MDQ2ODU5Nw0KSGFu
ZGxlcl9yZWFkX3By
ZXYJMA0KSGFuZGxlcl9y
ZWFkX3JuZAk0Mg0KSGFu
ZGxlcl9yZWFkX3JuZF9u
ZXh0CTE4NDcwMzA2
Mg0KSGFuZGxlcl9yb2xs
YmFjawkwDQpIYW5kbGVy
X3VwZGF0ZQkJMQ0KSGFu
ZGxlcl93cml0ZQkJ
MzQ4NjAyMzM3DQpLZXlf
YmxvY2tzX25vdF9mbHVz
aGVkCTANCktleV9ibG9j
a3NfdW51c2VkCTU5
MDE4MA0KS2V5X2Jsb2Nr
c191c2VkCQkxODExODI5
DQpLZXlfcmVhZF9yZXF1
ZXN0cwkxNjkxMTE2
NjM5DQpLZXlfcmVhZHMJ
CTE2NzM3NTA5DQpLZXlf
d3JpdGVfcmVxdWVzdHMJ
MTQ4ODgxNjIwDQpL
ZXlfd3JpdGVzCQkxOTQ5
MTc4DQpNYXhfdXNlZF9j
b25uZWN0aW9ucwk1DQpO
b3RfZmx1c2hlZF9k
ZWxheWVkX3Jvd3MJMA0K
T3Blbl9maWxlcwkJNDMN
Ck9wZW5fc3RyZWFtcwkJ
MA0KT3Blbl90YWJs
ZXMJCTIzDQpPcGVuZWRf
dGFibGVzCQk1MQ0KUWNh
Y2hlX2ZyZWVfYmxvY2tz
CTENClFjYWNoZV9m
cmVlX21lbW9yeQkyNTA5
NDIxNg0KUWNhY2hlX2hp
dHMJCTE0DQpRY2FjaGVf
aW5zZXJ0cwkJMTUN
ClFjYWNoZV9sb3dtZW1f
cHJ1bmVzCTANClFjYWNo
ZV9ub3RfY2FjaGVkCTQ3
DQpRY2FjaGVfcXVl
cmllc19pbl9jYWNoZQkx
NA0KUWNhY2hlX3RvdGFs
X2Jsb2NrcwkzMg0KUXVl
c3Rpb25zCQkxMzU2
NA0KUnBsX3N0YXR1cwkJ
TlVMTA0KU2VsZWN0X2Z1
bGxfam9pbgkwDQpTZWxl
Y3RfZnVsbF9yYW5n
ZV9qb2luCTANClNlbGVj
dF9yYW5nZQkJOA0KU2Vs
ZWN0X3JhbmdlX2NoZWNr
CTANClNlbGVjdF9z
Y2FuCQkxNA0KU2xhdmVf
b3Blbl90ZW1wX3RhYmxl
cwkwDQpTbGF2ZV9ydW5u
aW5nCQlPRkYNClNs
b3dfbGF1bmNoX3RocmVh
ZHMJMA0KU2xvd19xdWVy
aWVzCQk0DQpTb3J0X21l
cmdlX3Bhc3Nlcwkw
DQpTb3J0X3JhbmdlCQkw
DQpTb3J0X3Jvd3MJCTQy
DQpTb3J0X3NjYW4JCTEN
ClRhYmxlX2xvY2tz
X2ltbWVkaWF0ZQkxNDQN
ClRhYmxlX2xvY2tzX3dh
aXRlZAkwDQpUaHJlYWRz
X2NhY2hlZAkJMQ0K
VGhyZWFkc19jb25uZWN0
ZWQJNA0KVGhyZWFkc19j
cmVhdGVkCQk1DQpUaHJl
YWRzX3J1bm5pbmcJ
CTMNClVwdGltZQkJCTQx
OTc4Nwo=

------ _=_NextPart_001_01C5
4A63.06AF5C51
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
------ _=_NextPart_001_01C5
4A63.06AF5C51--

Brent Baisley

2005-04-26, 9:23 am

I must assume you have all the proper indexes setup and your=20
configuration variables are fairly optimal.

First, I would run just the select part with an explain in front of it=20=

to see what MySQL is trying to do. I've had MySQL run a query for an=20
inordinate amount of time on a fairly small data set because of a typo=20=

and no index in use for a join.

Second, I would check the size of the data file to see if it is=20
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.=20
This will show you how long MySQL is taking to do the search and joins,=20=

eliminating data transfer time.

You need to find where it's bottlenecking. It may very well be that you=20=

are I/O bound (iostat). MySQL is reading from the tables, certainly=20
using temporary tables with a data set of that size and trying to load=20=

data into a new table. That's a lot of reads and writes going on at=20
once, your disk(s) may be getting hammered, especially if you are not=20
using RAID.

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

Lastly, instead of doing and insert select, just select to a file (INTO=20=

OUTFILE). Then import the data using load data infile. A two step=20
processing, but something that will allow you to control all the I/O=20
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=20=

> joining two other tables now for several weeks and have not been able=20=

> to get the query to run to completion even when sub-seting the data=20
> into smaller ranges.
>
> =A0
>
> I have tried this at MySQL releases 4.1.8a and 4.1.10a with no=20
> 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=20
> processors).
>
> SHOW STATUS shows very good buffer hit ratio. Current stats are=20
> attached. Server was re-booted last Friday. It has been running the=20
> INSERT query since Saturday mid-morning and has yet to complete.
>
> =A0
>
> Table 1=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0=A0=A0
230 million rows =
total=A0=A0=A0=A0=A0
compund PK index =96 4=20
> columns=A0 range 1 should select 35 million rows. Explain plan shows =
it=20

> to be using the PK
>
> Table 2=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0=A0=A0
598 million =
rows=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0=A0=A0
compound PK index =96=20
> 4 columns and one secondary index. Range 1 should select about 130=20
> million rows
>
> =A0
>
> Table 3=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0=A0=A0
Empty table unindexed. =
Two keys from Table 1 and=20
> 17 =A0columns from table 2 populate this table
>
> =A0
>
> The general form of the query is:
>
> =A0
>
> INSERT INTO Table 3 (col1, =85. Col18)
>
> SELECT
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 Col1,,.col18
>
> FROM
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 Table 1 a
>
> INNER JOIN Table 2 b ON (PK columns and range selection)
>
> WHERE
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 a.col5 =3D b.col5
>
> AND
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 =85.
>
> AND
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 =85
>
> AND
>
> =A0=A0=A0=A0=A0=A0=A
0=A0=A0=A0=A0 a.col18 =3D b.col18
>
> =A0
>
> =A0
>
> Any suggestions are welcome.
>
> =A0
>
> Ed Sweeney
>
> =A0
> <show_status.txt>--
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =20
> http://lists.mysql.com/mysql? unsub...ov
er.com
--=20
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links