Slow insert speed?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Slow insert speed?

Guillaume Theoret
I'm using the pg2sql driver and inserting into a single wide table (71
columns) in a loop.

So far I've been getting this:

guillaume@dev3:~$ php luciddb_test.php
Established connection to PG2LucidDB
Server version: PostgreSQL 8.1.4 server protocol using LucidDB
Mysql rows: 7575335
Inserting
100 done - 0.0013% in 55.77 (0.5452 avg)
200 done - 0.0026% in 111.51 (0.5574 avg)
300 done - 0.004% in 167.48 (0.5597 avg)
400 done - 0.0053% in 226.28 (0.588 avg)
500 done - 0.0066% in 284.5 (0.5822 avg)
600 done - 0.0079% in 344.88 (0.6038 avg)
700 done - 0.0092% in 406.06 (0.6118 avg)
800 done - 0.0106% in 467.57 (0.6151 avg)

At this rate it's going to take over 51 days to complete. Any idea why
it's so slow?

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Zelaine Fong
It sounds like you're trying to execute individual insert statements in a loop.  If so, that would explain the slowness.  LucidDB is not very efficient when it comes to singleton inserts, as it was designed to perform well for bulk-type inserts, e.g., loading data that's sourced from a large text file.
 
-- Zelaine


From: Guillaume Theoret <[hidden email]>
To: [hidden email]
Sent: Wednesday, October 14, 2009 12:48:09 PM
Subject: [luciddb-users] Slow insert speed?

I'm using the pg2sql driver and inserting into a single wide table (71
columns) in a loop.

So far I've been getting this:

guillaume@dev3:~$ php luciddb_test.php
Established connection to PG2LucidDB
Server version: PostgreSQL 8.1.4 server protocol using LucidDB
Mysql rows: 7575335
Inserting
100 done - 0.0013% in 55.77 (0.5452 avg)
200 done - 0.0026% in 111.51 (0.5574 avg)
300 done - 0.004% in 167.48 (0.5597 avg)
400 done - 0.0053% in 226.28 (0.588 avg)
500 done - 0.0066% in 284.5 (0.5822 avg)
600 done - 0.0079% in 344.88 (0.6038 avg)
700 done - 0.0092% in 406.06 (0.6118 avg)
800 done - 0.0106% in 467.57 (0.6151 avg)

At this rate it's going to take over 51 days to complete. Any idea why
it's so slow?

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Nicholas Goodman
In reply to this post by Guillaume Theoret
Guillaume,

Single row inserts, no matter which interface you choose to use, are  
slow with LucidDB.  Column Stores in general being optimized for  
column at a time access are inherently pretty cruddy at single row  
INSERTs.  There are ways to fake this out, and build some workarounds  
into the product at great cost/complexity but all that really does is  
simply mask the issue.

LucidDB is designed to load in bulk and to get the incoming data as  
streaming sets of records (from SQL/MED wrappers, UDXs, etc).

For instance, to read from MySQL and load into LucidDB in the most  
efficient manner you would:
a) create a foreign data server to MySQL
b) import the foreign tables (which is a metadata operation) into this  
foreign data server
c) execute a statement like:
INSERT INTO luciddbtable (col1, col2) select col1, col2 from  
importedschema.sourcetablename;

I regularly see INSERT speeds (your mileage may vary with this  
approach) from MySQL to LucidDB on the 60k/s range.

If you change the approach you'll be very happy with the load  
performance!  :)

Nick

On Oct 14, 2009, at 12:48 PM, Guillaume Theoret wrote:

> I'm using the pg2sql driver and inserting into a single wide table (71
> columns) in a loop.
>
> So far I've been getting this:
>
> guillaume@dev3:~$ php luciddb_test.php
> Established connection to PG2LucidDB
> Server version: PostgreSQL 8.1.4 server protocol using LucidDB
> Mysql rows: 7575335
> Inserting
> 100 done - 0.0013% in 55.77 (0.5452 avg)
> 200 done - 0.0026% in 111.51 (0.5574 avg)
> 300 done - 0.004% in 167.48 (0.5597 avg)
> 400 done - 0.0053% in 226.28 (0.588 avg)
> 500 done - 0.0066% in 284.5 (0.5822 avg)
> 600 done - 0.0079% in 344.88 (0.6038 avg)
> 700 done - 0.0092% in 406.06 (0.6118 avg)
> 800 done - 0.0106% in 467.57 (0.6151 avg)
>
> At this rate it's going to take over 51 days to complete. Any idea why
> it's so slow?
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart  
> your
> developing skills, take BlackBerry mobile applications to market and  
> stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Nicholas Goodman
In reply to this post by Zelaine Fong
We can do a better of job of making this present for people coming to LucidDB.  Guillaume, thanks for bring it up.

I've added some notes on this at http://pub.eigenbase.org/wiki/LucidDbInsert

Can anyone else think of other good spots to also link/present this information so we can make this architectural data point known to people as they start using LucidDB?

Nick

On Oct 14, 2009, at 1:01 PM, Zelaine Fong wrote:

It sounds like you're trying to execute individual insert statements in a loop.  If so, that would explain the slowness.  LucidDB is not very efficient when it comes to singleton inserts, as it was designed to perform well for bulk-type inserts, e.g., loading data that's sourced from a large text file.
 
-- Zelaine


From: Guillaume Theoret <[hidden email]>
To: [hidden email]
Sent: Wednesday, October 14, 2009 12:48:09 PM
Subject: [luciddb-users] Slow insert speed?

I'm using the pg2sql driver and inserting into a single wide table (71
columns) in a loop.

So far I've been getting this:

guillaume@dev3:~$ php luciddb_test.php
Established connection to PG2LucidDB
Server version: PostgreSQL 8.1.4 server protocol using LucidDB
Mysql rows: 7575335
Inserting
100 done - 0.0013% in 55.77 (0.5452 avg)
200 done - 0.0026% in 111.51 (0.5574 avg)
300 done - 0.004% in 167.48 (0.5597 avg)
400 done - 0.0053% in 226.28 (0.588 avg)
500 done - 0.0066% in 284.5 (0.5822 avg)
600 done - 0.0079% in 344.88 (0.6038 avg)
700 done - 0.0092% in 406.06 (0.6118 avg)
800 done - 0.0106% in 467.57 (0.6151 avg)

At this rate it's going to take over 51 days to complete. Any idea why
it's so slow?

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay 
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay 
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Alex Mekhrishvili
In reply to this post by Guillaume Theoret
Single insert statements will be always pretty slow for column-oriented databases. 
I suggest you to look through LucidDBEtlTutorial (http://pub.eigenbase.org/wiki/LucidDbEtlTutorial) and try loading your data in a bulk.

The fastest way will be probably to dump your table as CSV file (or a set of files) and then
load it using flatfile foreign data wrapper (or foreign server using CsvJdbc driver in case if you 
have unicode data).

Alexander

On Wed, Oct 14, 2009 at 11:48 PM, Guillaume Theoret <[hidden email]> wrote:
I'm using the pg2sql driver and inserting into a single wide table (71
columns) in a loop.

So far I've been getting this:

guillaume@dev3:~$ php luciddb_test.php
Established connection to PG2LucidDB
Server version: PostgreSQL 8.1.4 server protocol using LucidDB
Mysql rows: 7575335
Inserting
100 done - 0.0013% in 55.77 (0.5452 avg)
200 done - 0.0026% in 111.51 (0.5574 avg)
300 done - 0.004% in 167.48 (0.5597 avg)
400 done - 0.0053% in 226.28 (0.588 avg)
500 done - 0.0066% in 284.5 (0.5822 avg)
600 done - 0.0079% in 344.88 (0.6038 avg)
700 done - 0.0092% in 406.06 (0.6118 avg)
800 done - 0.0106% in 467.57 (0.6151 avg)

At this rate it's going to take over 51 days to complete. Any idea why
it's so slow?

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

John Sichi
Administrator
In reply to this post by Nicholas Goodman
Nicholas Goodman wrote:
> Can anyone else think of other good spots to also link/present this
> information so we can make this architectural data point known to people
> as they start using LucidDB?

http://pub.eigenbase.org/wiki/LucidDbUserFaq

JVS

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Francisco Reyes-3
In reply to this post by Nicholas Goodman
Nicholas Goodman writes:

>
> I've added some notes on this
> at <URL:http://pub.eigenbase.org/wiki/LucidDbInsert>http://pub.eigenbase.or 
> g/wiki/LucidDbInsert
 
Wouldn't this best be suited for the FAQ?
There is already a link on the FAQ for Slow INSERT performance
http://pub.eigenbase.org/wiki/LucidDbUserFaq#Slow_INSERT_performance

There is a link there to the ETL tutorial.


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow insert speed?

Nicholas Goodman
On Oct 15, 2009, at 11:06 AM, Francisco Reyes wrote:

Wouldn't this best be suited for the FAQ?
There is already a link on the FAQ for Slow INSERT performance
http://pub.eigenbase.org/wiki/LucidDbUserFaq#Slow_INSERT_performance

Yeah, I added that section yesterday.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...