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

GoFuckYourself.com - Adult Webmaster Forum (https://gfy.com/index.php)

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - is it normal for mysql to take 14+ seconds to do 10k inserts? (https://gfy.com/showthread.php?t=707371)

Myst	02-18-2007 02:42 AM

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')"); 

}

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..

http://img61.imageshack.us/img61/6595/savelw3.jpg

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 (Post 11936183)

thats php :)

WiredGuy

02-18-2007 03:21 AM

Quote:

Originally Posted by darksoul (Post 11936214)

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 (Post 11936222)

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

windowz ?

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 (Post 11936228)

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)

All times are GMT -7. The time now is 10:02 PM.