List:General Discussion« Previous MessageNext Message »
From:Jeff Mathis Date:October 25 2002 8:12pm
Subject:Re: Mysql Innodb performance slow
View as plain text  
I'll agree to this somewhat:

(1) always bind your variables. whatever code you are using to do your
inserts, the fewer prepared statements you can make the better.

for example:
insert into TableName (col1, col2, col2, col4) values (?,?,?,?)

then, once you have a prepared statment, do a loop and insert.

(2) drop all indexes on your table(s). rebuild them after loading (this
alone can give orders of magnitude improvement)

(3) commit every 10,000 records or so.


I can load several million rows into our InnoDB tables in a few minutes.

good luck

jeff

Richard Clarke wrote:
> 
> Jeroen,
> 
> Two things are likely to make this umpteen times faster.
> 
> a) Commit the insert transaction every.. say 1000 records?
> b) use mysql's extended insert statement,
>         insert into mytable values
> (row1_field1,row1_field2),(row2_field1,row2_field2),(?,?),(?,?) etc etc
> 
> Ric.
> 
> ----- Original Message -----
> From: "Jeroen Geusebroek" <j.geusebroek@stripped>
> To: <mysql@stripped>
> Sent: Friday, October 25, 2002 12:11 PM
> 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-ric=lorcom.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> ---------------------------------------------------------------------
> 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-thread123206@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-jmathis=predict.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Jeff Mathis, Ph.D.			505-995-1434
The Prediction Company			jmathis@stripped
525 Camino de los Marquez, Ste 6	http://www.predict.com
Santa Fe, NM 87505
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