Re: Really slow batch insert??
Ah, forgot to mention that table is INNODB. On Mon, Dec 30, 2013 at 1:59 PM, Cabbar Duzayak cab...@gmail.com wrote: Hi, We have a basic table, which is something like : (id varchar50, productId varchar50, category varchar50) In this table, ID is the primary key and we have a unique index on (category, productId). And, there is a case where we want to do bulk inserts (3000 inserts) using: INSERT INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’, ‘y2’, ‘z3’) ….. ON DUPLICATE KEY productId = VALUES(productId), category = VALUES(category) So, when we try to insert 3000 rows using this syntax with a single statement, it takes ~ 3 seconds to execute this on an empty table. BTW, innodb_flush_log_at_trx_commit is set to 2 for us. I don’t have something factual data here, but it feels that this is too much just for 3000 rows. Isn’t this just too slow? Are we doing something wrong here? Or, can you advice as to which configuration parameters can we tweak here to make it faster? Thanks
Really slow batch insert??
Hi, We have a basic table, which is something like : (id varchar50, productId varchar50, category varchar50) In this table, ID is the primary key and we have a unique index on (category, productId). And, there is a case where we want to do bulk inserts (3000 inserts) using: INSERT INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’, ‘y2’, ‘z3’) ….. ON DUPLICATE KEY productId = VALUES(productId), category = VALUES(category) So, when we try to insert 3000 rows using this syntax with a single statement, it takes ~ 3 seconds to execute this on an empty table. BTW, innodb_flush_log_at_trx_commit is set to 2 for us. I don’t have something factual data here, but it feels that this is too much just for 3000 rows. Isn’t this just too slow? Are we doing something wrong here? Or, can you advice as to which configuration parameters can we tweak here to make it faster? Thanks
Re: Really slow batch insert??
2013/12/30 13:59 +0200, Cabbar Duzayak We have a basic table, which is something like : (id varchar50, productId varchar50, category varchar50) In this table, ID is the primary key and we have a unique index on (category, productId). And, there is a case where we want to do bulk inserts (3000 inserts) using: INSERT INTO (id, productId, category) VALUES (x1, y1, z1), (x2, y2, z3) .. ON DUPLICATE KEY productId = VALUES(productId), category = VALUES(category) So, when we try to insert 3000 rows using this syntax with a single statement, it takes ~ 3 seconds to execute this on an empty table. BTW, innodb_flush_log_at_trx_commit is set to 2 for us. I dont have something factual data here, but it feels that this is too much just for 3000 rows. I have nothing real to say about the slowness, but it looks as if in this case your operation is the same as REPLACE INTO (id, productId, category) VALUES (x1, y1, z1), (x2, y2, z3) and maybe that takes less time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
