is it normal for mysql to take 14+ seconds to do 10k inserts? - GoFuckYourself.com

Myst · 02-18-2007, 02:42 AM

is it normal for mysql to take 14+ seconds to do something simple like (pseudecode) for x = 10000 to 20000; mysql_query(insert into users (muser) values ($x)) ???

muser is varchar, length 5, non primary (it is the only column in the table)

I am on a AMD Athlon 64 3500+ PC with 1gb ram.. this is all being done locally

the exact code im using is below

Code:

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

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

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

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

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

spasmo · 02-18-2007, 03: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, 03:02 AM

is it normally much faster?

WiredGuy · 02-18-2007, 03: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, 03: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, 03:18 AM

Quote:

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, 03:21 AM

Quote:

Originally Posted by darksoul

thats php

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

WG

darksoul · 02-18-2007, 03: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, 03:22 AM

Quote:

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, 03: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, 03:31 AM

Nice sig darksoul, lol.
WG

2012 · 02-18-2007, 03:33 AM

edit ...

tical · 02-18-2007, 03: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, 03:38 AM

Quote:

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, 03:39 AM

Bump for you lol

Myst · 02-18-2007, 05: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)

02-18-2007, 02:42 AM	#1
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	is it normal for mysql to take 14+ seconds to do 10k inserts? is it normal for mysql to take 14+ seconds to do something simple like (pseudecode) for x = 10000 to 20000; mysql_query(insert into users (muser) values ($x)) ??? muser is varchar, length 5, non primary (it is the only column in the table) I am on a AMD Athlon 64 3500+ PC with 1gb ram.. this is all being done locally the exact code im using is below Code: $dbcnx = mysql_connect('localhost','xxxxx', 'xxxxx'); mysql_select_db("testtable", $dbcnx); for ($i = 10000; $i <= 20000; $i++) { mysql_query("insert into users (muser) values ('$i')"); } __________________ ICQ: 298-523-037

02-18-2007, 02:47 AM	#2
spasmo Confirmed User Join Date: Dec 2005 Location: Couch Posts: 2,678	You have to think spindles. What is your disk IO while doing this? __________________ Surfers: Go here for hot babes.

02-18-2007, 02:56 AM	#3
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	about 400KB while its inserting the cpu mysql uses is around 30% during the inserts.. __________________ ICQ: 298-523-037

02-18-2007, 03:00 AM	#4
spasmo Confirmed User Join Date: Dec 2005 Location: Couch Posts: 2,678	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. __________________ Surfers: Go here for hot babes.

02-18-2007, 03:02 AM	#5
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	is it normally much faster? __________________ ICQ: 298-523-037

02-18-2007, 03:06 AM	#6
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,504	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 __________________ I play with Google.

02-18-2007, 03:12 AM	#7
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	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.. __________________ ICQ: 298-523-037

02-18-2007, 03:21 AM	#10
darksoul Confirmed User Join Date: Apr 2002 Location: /root/ Posts: 4,997	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. __________________ 1337 5y54\|)m1n: 157717888 BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN Cambooth

02-18-2007, 03:25 AM	#12
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	figured it out =) looks like it was the table structure.. INNODB is 7-8x faster than InnoDB (at least on windows anyway) __________________ ICQ: 298-523-037 Last edited by Myst; 02-18-2007 at 03:27 AM..

02-18-2007, 03:31 AM	#13
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,504	Nice sig darksoul, lol. WG __________________ I play with Google.

02-18-2007, 03:33 AM	#14
2012 So Fucking What Industry Role: Join Date: Jul 2006 Posts: 17,189	windowz ? edit ... __________________ best host: Webair \| best sponsor: Kink \| best coder: 688218966 \| Go Fuck Yourself Last edited by 2012; 02-18-2007 at 03:33 AM.. Reason: i like big tits

02-18-2007, 03:37 AM	#15
tical Confirmed User Join Date: Feb 2002 Location: Las Vegas Posts: 6,504	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. __________________ 112.020.756

02-18-2007, 03:39 AM	#17
rotterdammer Confirmed User Join Date: Feb 2006 Posts: 1,523	Bump for you lol __________________ My Best Sponsors: 1 2 3 4 5 6 7 8 9 10 Im Making Over $1000 / Month With OrgyCash! Get Inside Now! - Big List Of Domains For Sale!

02-18-2007, 05:47 AM	#18
Myst Confirmed User Join Date: Feb 2004 Location: Alberta, Canada Posts: 4,708	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) __________________ ICQ: 298-523-037