Verifying insert speed

2 posts / 0 new
Last post
naifwonder
naifwonder's picture
Offline
Last seen: 2 years 3 weeks ago
Joined: Jul 16 2012
Junior Boarder

Posts: 1

Naif Ahmed
Verifying insert speed

First of all, I just wanted to say thanks for a great product. InfiniDB has been a lifesaver. I use the cipimport tool for bulk loads, but there are certain situations in which I need to insert several thousand rows and using the insert statement is just far more programmatically rational. However, when I try to do this, the insert speed seems pretty slow. It is inserting around 30 rows per second (each row is pretty small.. around 5 columns per row, each of type varchar(10)). Did I configure and/or install something incorrectly, or is the expected speed using the insert statement? My computer has 16 gb ram with a SSD with 520 mb/s write speed, and using innodb I can insert several thousand rows per second using the insert statement.

bdempsey
bdempsey's picture
Offline
Last seen: 1 month 2 hours ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Verifying insert speed

You are seeing about what I would expect for line-at-time inserts. InfiniDB, like all column stores, takes a big hit for inserts: we have to open a file, position the pointer and write data to each column, and each column is on a different spot on the disk. A row database only has to deal with one file (and maybe some indexes).

You can gain some incremental speed by turning off autocommit, but your application will have to remember to commit the inserted rows when it is done.

You could also use load data infile, but, if you can get the rows into a file for LDI, you might as well use cpimport.