is it normal for mysql to take 14+ seconds to do 10k inserts?

**spasmo** · 02-18-2007, 12:47 AM

You have to think spindles. What is your disk IO while doing this?

**Myst** · 02-18-2007, 12:56 AM

about 400KB while its inserting
the cpu mysql uses is around 30% during the inserts..

**spasmo** · 02-18-2007, 01:00 AM

I'm a bit stymied on this one. It usually points to disk IO.

Anyone else with ideas out there? I must be missing something obvious.

**Myst** · 02-18-2007, 01:02 AM

is it normally much faster?

**WiredGuy** · 02-18-2007, 01:06 AM

I'm guessing by the code you're doing this in perl. Perl has always been very slow for me in doing basic sql operations such as adds/deletions. It's extremely fast to use mysql directly to import the data from a file if this is possible in your application.
WG

**Myst** · 02-18-2007, 01:12 AM

wow.. it takes <2 seconds on a linux server

im using windows, and i am desperately trying to figure out why mysql is so 700% slower using the same code..

**darksoul** · 02-18-2007, 01:18 AM

Originally posted by WiredGuy

I'm guessing by the code you're doing this in perl. Perl has always been very slow for me in doing basic sql operations such as adds/deletions. It's extremely fast to use mysql directly to import the data from a file if this is possible in your application.
WG

thats php

**WiredGuy** · 02-18-2007, 01:21 AM

Originally posted by darksoul

thats php

Perl too. Maybe its Windows that really makes it all slow

WG

**darksoul** · 02-18-2007, 01:21 AM

the problem is mysql commits after each insert so you're better running a combined insert:

Code:

$dbcnx = mysql_connect('localhost','xxxxx', 'xxxxx'); 
mysql_select_db("testtable", $dbcnx); 
$cmd = "insert into users (muser) values ";
for ($i = 10000; $i <= 20000; $i++) { 
$cmd .= "($i),"
}
$cmd .= "(20001)"
mysql_query($cmd);

should be blazing fast.

**darksoul** · 02-18-2007, 01:22 AM

Originally posted by WiredGuy

Perl too. Maybe its Windows that really makes it all slow

WG

I meant the code he posted is php

**Myst** · 02-18-2007, 01:25 AM

figured it out =)
looks like it was the table structure..
INNODB is 7-8x faster than InnoDB (at least on windows anyway)

**WiredGuy** · 02-18-2007, 01:31 AM

Nice sig darksoul, lol.
WG

**2012** · 02-18-2007, 01:33 AM

windowz ?

edit ...

**tical** · 02-18-2007, 01:37 AM

try this query instead, should be WAY faster... only hits MySQL once with a large insert

(note: that last substr just removes the trailing "," from the query so its valid)

Code:

	$dbcnx = mysql_connect('localhost','xxxxx', 'xxxxx'); 
	mysql_select_db("testtable", $dbcnx); 
	$query = "";
	for ($i = 10000; $i <= 20000; $i++) {
		$query = $query . "('" . $i . "'),";
	}
	$query = "insert into users (muser) values " . substr($query, 0, strlen($query) - 1);
	mysql_query($query);

i think thats right, but you get the idea... you can do multiple inserts in one query w/ MySQL so handle the processing beforehand and eliminate all the redundant connections, etc.

**tical** · 02-18-2007, 01:38 AM

Originally posted by darksoul

the problem is mysql commits after each insert so you're better running a combined insert:

Code:

$dbcnx = mysql_connect('localhost','xxxxx', 'xxxxx'); 
mysql_select_db("testtable", $dbcnx); 
$cmd = "insert into users (muser) values ";
for ($i = 10000; $i <= 20000; $i++) { 
$cmd .= "($i),"
}
$cmd .= "(20001)"
mysql_query($cmd);

should be blazing fast.

blah crap i didnt even see this haha... ah well

**rotterdammer** · 02-18-2007, 01:39 AM

Bump for you lol

**Myst** · 02-18-2007, 03:47 AM

the thing is i will be doing many many single inserts, so i set it to do one at a time on purpose

the problem was the table structure

INNODB is 7-8x faster than InnoDB (at least on windows anyway)

is it normal for mysql to take 14+ seconds to do 10k inserts?

is it normal for mysql to take 14+ seconds to do 10k inserts?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment