Re: Mysql Innodb performance slow
In the last episode (Oct 25), Jeff Mathis said: 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. Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Mysql Innodb performance slow
sql,query At 15:50 -0600 10/25/02, Jeff Mathis wrote: Paul DuBois wrote: (2) drop all indexes on your table(s). rebuild them after loading (this alone can give orders of magnitude improvement) For ISAM or MyISAM, that works. It has no effect for InnoDB. Do you actually observe a reproducable difference with this strategy for InnoDB? Yes I do. I have tables with defined primary key/foreign key constraints, which I cannot drop during loading. But dropping the unique indexes I have defined on multiple columns does speed up loading times substantially. You say I am not supposed to see this behavior? Can you explain why? Sure. I get this from: http://www.innodb.com/ibman.html#Altering_InnoDB_tables Which says: InnoDB does not have a special optimization for separate index creation. Therefore it does not pay to export and import the table and create indexes afterwards. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table, that is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM I've been experimenting with putting datafiles on different disks, and am now wondering if what I'm seeing is related to disk i/o as opposed to insert optimization. jeff - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
Mark, Note that mysql does not support bind variables. If you think you'reusing them, whatever API you are using is filling them in beforesending the statement to mysql. Bind variables do solve quotingproblems, though, so if you use them, know why you're using them :) Rumour has it, though, that DBD::MySQL (or whichever module it is) performs better with the bound variable versions than the non bound variable versions. DSL -- The Linux C Programming Lists: * http://lists.linux.org.au/listinfo/linuxcprogramming/ The Linux C++ Programming Lists: * http://lists.linux.org.au/listinfo/tuxcpprogramming/ - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
At 15:46 -0600 10/25/02, Jeff Mathis wrote: forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. Right, it's the DBD::mysql driver that's emulating true parameter binding. But it's still faster, as you observe, because you avoid the prepare() call for all except the first insert statement. Parameter binding by MySQL itself should probably appear sometime in the 4.1.x series. - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
David Lloyd wrote: Mark, Note that mysql does not support bind variables. If you think you'reusing them, whatever API you are using is filling them in beforesending the statement to mysql. Bind variables do solve quotingproblems, though, so if you use them, know why you're using them :) Rumour has it, though, that DBD::MySQL (or whichever module it is) performs better with the bound variable versions than the non bound variable versions. DSL -- The Linux C Programming Lists: * http://lists.linux.org.au/listinfo/linuxcprogramming/ The Linux C++ Programming Lists: * http://lists.linux.org.au/listinfo/tuxcpprogramming/ I don't dis-agree with this, it was Dan that brought up the point you are responding to :) -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql Innodb performance slow
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;infraxs.com] Sent: Friday, October 25, 2002 3:11 AM To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
Jeroen Geusebroek wrote: [snip] 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 When you don't disable autocommit, InnoDB is going to create a transaction for _each_ statement, thus you won't be able to expect a lot of speed :) -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. I'm now using JDBC, and havne't explored this yet. I'm coming from the Oracle camp, and have only been using mysql for less than a month. so far, i find it very snappy. I do miss the ability to have stored procedures, triggers and views however. jeff Dan Nelson wrote: In the last episode (Oct 25), Jeff Mathis said: 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. Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Innodb performance slow
Jeff Mathis wrote: forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. I'm now using JDBC, and havne't explored this yet. I'm coming from the Oracle camp, and have only been using mysql for less than a month. so far, i find it very snappy. I do miss the ability to have stored procedures, triggers and views however. jeff Dan Nelson wrote: [snip] Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] Dan speaks the truth. However, using bind variables also proves that you're forward-looking as MySQL-4.1 will have prepared statements with 'real' bound parameters, so your code will run that much faster then. The quoting problem is a valid point, and in fact I always prescribe it as dynamic SQL is the root of many an exploit in database applications, especially web-based ones. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
