List:General Discussion« Previous MessageNext Message »
From:Jeroen Geusebroek Date:October 25 2002 7:47pm
Subject:RE: Mysql Innodb performance slow
View as plain text  
Hi,

>What method are you using for inserting the data?

>Individual INSERTs can be very slow:
>	INSERT INTO foo (x, y, z) VALUES (1, 2, 3);
>	INSERT INTO foo (x, y, z) VALUES (4, 5, 6);
>	...

>Batch INSERTs can be massively faster:
>	INSERT INTO foo (x, y, z) VALUES (1, 2, 3), (4, 5, 6), ...;

You where right, what i did use indivudual inserts. Now I have it
segmented in 1000 rows in one query which massively speeds up the whole
inserting progress. 160K records now takes about 20 seconds instead of
the 100 records ;) I'm really impressed by this massive speed.

Begin;
INSERT INTO foo VALUES (1,2,3,4,5) x 1000
Commit;

That is what I use now.

One question though, does innodb use transactions standard? So without
using
the begin; and commit; statement, does it still use some kind of
transaction?

Because still I don't understand why the isam table takes about 100
seconds for inserting 160K rows, while innodb takes 700 seconds. (using
individual inserts)

Thanks,

Jeroen

> -----Original Message-----
> From: Jeroen Geusebroek [mailto:j.geusebroek@stripped] 
> Sent: Friday, October 25, 2002 3:11 AM
> To: mysql@stripped
> Subject: Mysql Innodb performance slow
> 
> 
> Hi There,
> 
> We have currently an Interbase Database with millions and millions of
> rows which I would like 
> to migrate to MySQL if possible to increase the speed.
> 
> Transaction support is necessary, so I am using innoDB.
> 
> When inserting 160K rows in the database (in an innoDB table) it takes
> about 700! seconds
> while the amount of same rows when inserted in a myisam table 
> take about
> 100 seconds.
> 
> Now probably this can be fine tuned (I hope), and would like 
> to ask for
> some suggestions.
> 
> Is anybody using innodb with this amount of rows?  I'm curious of what
> the performance is.
> 
> Is there something I should keep in mind when migrating?
> 
> Kind regards,
> 
> Jeroen Geusebroek
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread123205@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-jfrisby=yourfreedvds.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 

Thread
Mysql Innodb performance slowJeroen Geusebroek25 Oct
  • Re: Mysql Innodb performance slowRichard Clarke25 Oct
  • Re: Mysql Innodb performance slowJeff Mathis26 Oct
    • Re: Mysql Innodb performance slowDan Nelson28 Oct
  • Re: Mysql Innodb performance slowJeff Mathis26 Oct
    • Re: Mysql Innodb performance slowPaul DuBois27 Oct
  • Re: Mysql Innodb performance slowMark Matthews26 Oct
    • Re: Mysql Innodb performance slowDavid Lloyd26 Oct
  • Re: Mysql Innodb performance slowMark Matthews27 Oct
RE: Mysql Innodb performance slowJeroen Geusebroek26 Oct
  • Re: Mysql Innodb performance slowMark Matthews26 Oct