InfiniDB insert speed with insert statement

Question

I have recently started using InfinDB, and so far it is working well. I use the cipimport tool for bulk loads and it inserts millions of rows in seconds. However, there are certain situations in which I need to insert several thousand rows and using the insert statement is just far more programmatically rational due to nature of how the data is generated. 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 MyISAM or Innodb I can insert several thousand rows per second using the insert statement.

score 2 · Answer 1 · answered Jul 31 '12 at 06:42

2

INSERT INTO VALUES(),(),... is much faster than seprate INSERT queries.

answered Jul 31 '12 at 06:42

Omesh

27,801
6
42
51

score 2 · Answer 2 · answered May 14 '14 at 21:18

I am an architect for InfiniDB

Are you doing INSERT INTO or are you doing a bunch of individual INSERT commands? You said you are doing thousands of rows, so are you able to do that with INSERT INTO?

cpimport has been optimized for fast loading and why we recommend using it over INSERT INTO and LOAD DATA. There are several reasons why this is so, due to the nature of the databases and the architecture underneath. With that said, we realize people want to be able to use INSERT INTO and LOAD DATA as they are used to using them and want faster performance similar to what they get with cpimport. That is in work right now and will be in the 4.6 release of InfiniDB which should be coming out end of June / beginning of July. These operations will be optimized to operate in a high speed mode where when there no outstanding commits.

score 0 · Answer 3 · answered Dec 13 '13 at 00:57

0

You don't use a column oriented database for OLTP operations. They are designed for batch inserts. You may automate your inserts in a cpimport job.

answered Dec 13 '13 at 00:57

user3090170

1
1

score 0 · Answer 4 · answered May 14 '14 at 13:11

InfiniDB is a column oriented database, where as MyISAM and Innodb are row-oriented. Basicly what this means is that data is saved on the harddisk grouped by columns and rows respectively. So if you want to insert a new row in a column oriented database, you will have to insert values for each column, each on different places on the harddisk. However, getting all the data from 1 column (calling AVG() for example) is much faster than with row oriented dbms.

I recommend reading the wikipedia article on column oriented databases to get the idea: http://en.wikipedia.org/wiki/Column-oriented_DBMS

InfiniDB insert speed with insert statement

4 Answers4