Load data buffer from memory into InfniDB (no cpimport)

4 posts / 0 new
Last post
lelsen
lelsen's picture
Offline
Last seen: 1 month 3 weeks ago
Joined: Apr 2 2014
Junior Boarder

Posts: 6

Lennart Elsen
Load data buffer from memory into InfniDB (no cpimport)

Hi all,

 

I am currently getting started with InfiniDB and geared towards making it my go-to database solution. I am using it in combination with in-house written software that produces and queries the data that ends up in the database tables.

 

The concrete use case it that, within my C program, data is collected in a buffer for a time frame of roughly 5 minutes and then flushed out from memory directly into the database. I have come to realize that this is not as straightforward in InfiniDB because for efficient bulk loading, cpimport is used. However, I would like to avoid multiple disk reads and writes as in the following scenario:

 

In-Memory buffer (my program) --> Temp file on disk --> memory (cpimport) --> Database on disk

 

Instead, it would be beneficial to do something programmatic along the lines of the following posts: 

 

http://infinidb.co/community/insert-speed

http://infinidb.co/community/running-cpimport-on-data-stored-in-memory

 

In-Memory buffer (my program) --> API (operating on memory) --> Database on disk

 

Are there any solutions other than cpimport? The following post talks about piping data to STDOUT, however, this still involves multiple on-disk operations and the use of cpimport:

 

http://infinidb.co/community/insert-speed

 

If I need to bulk insert roughly 10 000 entries every 5 minutes, would it be possible to use standard MySQL "INSERT" statements instead, while using the underlying InfiniDB engine?

 

 

 

radams
radams's picture
Offline
Last seen: 3 days 14 hours ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Load data buffer from memory into InfniDB (no cpimport)

 

Loading data using an insert statement is going to be slow. Here is an example to try. 

 

This will select from a table in the ssb schema and pipe to to a new table using cpimport. 

 
# idbmysql ssb -q --skip-column-names -e "select * from lineorder;" | /usr/local/Calpont/bin/cpimport ssb lineorder_new -s '\t' -n1
 
 
Thanks,
 
Robert
 
 
lelsen
lelsen's picture
Offline
Last seen: 1 month 3 weeks ago
Joined: Apr 2 2014
Junior Boarder

Posts: 6

Lennart Elsen
Thanks a lot for your answer

Thanks a lot for your answer Robert.

 

I tested your approach on a database with 7.3 million entries and arrived at an insert rate of roughly 120 000 rows/second, as opposed to standard MySQL inserts which go at roughly 10 000 rows/second. This is quite impressive.

 

However, using the pipe as you proposed still does not solve the problem of having multiple disk reads and writes as well as a greater memory need since cpimport is used.

 

As a rough estimate, how cumbersome do you think it would be to take the core of cpimport's code and make it operate on memory buffers instead of files?

djoshi
djoshi's picture
Offline
Last seen: 1 month 3 weeks ago
Joined: Jun 6 2012
Administrator

Posts: 28

Dipti Joshi
Lennart: In upcoming 4.6

Lennart:

 

In upcoming 4.6 release of InfiniDB  - the performance of "insert into <tablename> select * from <table_2>"  and "load data infile" will be at par with cpimport performance. Does this address your needs ? or you have an external program that puts data in memory buffer that you would like cpimport to consume ?

 

Thanks,

Dipti