[MonetDB-users] Insert slowdown - users-list - monetdb.org

newer
[MonetDB-users] mapilib on windows

[MonetDB-users] Insert slowdown

older
[MonetDB-users] Segmentation fault...

Guillaume Theoret

7 Oct 2009 7 Oct '09

1:23 p.m.

Hi, I have a question about insert performance. At first, with an empty table, it took a little over 1 second (around 1.5) to insert 1000 rows. Now, with 5.2MM rows, it's taking ~65s. Is this to be expected? Thanks, Guillaume

Reply

Sign in to reply online Use email software

Show replies by date

Martin Kersten

7 Oct 7 Oct

2:15 p.m.

Guillaume Theoret wrote:

Hi, I have a question about insert performance.

At first, with an empty table, it took a little over 1 second (around 1.5) to insert 1000 rows. Now, with 5.2MM rows, it's taking ~65s.

Is this to be expected?

Yes, the table space grows over time, which calls for a re-alloc operation. This may involve writing/reading. The fastest way to insert 5.2 M records is to use the SQL query COPY 5200000 RECORDS INTO tt FROM....

Thanks, Guillaume

------------------------------------------------------------------------------ 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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Guillaume Theoret

2:33 p.m.

For now a batch load would do but when I actually put this to use I'm going to be inserting (or updating if the key is already present) every 15 minutes by cron job which is why the growing insert time is a little worrisome. I can see how maybe some months down the road the inserts could become so slow that they can't even complete before the next cron job starts overlapping. One of the tables I planned to use MonetDB with already has 23.3MM rows. It would be preferable to rewrite the cron job so that it wrote all the data into a csv file and bulk loaded it when done? On Wed, Oct 7, 2009 at 10:15 AM, Martin Kersten wrote:

Guillaume Theoret wrote:

...
Hi, I have a question about insert performance.

At first, with an empty table, it took a little over 1 second (around 1.5) to insert 1000 rows. Now, with 5.2MM rows, it's taking ~65s.

Is this to be expected?

Yes, the table space grows over time, which calls for a re-alloc operation. This may involve writing/reading. The fastest way to insert 5.2 M records is to use the SQL query COPY 5200000 RECORDS INTO tt FROM....

...
Thanks, Guillaume

------------------------------------------------------------------------------ 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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Martin Kersten

3:22 p.m.

Guillaume Theoret wrote:

For now a batch load would do but when I actually put this to use I'm going to be inserting (or updating if the key is already present) every 15 minutes by cron job which is why the growing insert time is a You should just be careful with setting transaction boundaries to avoid most of the overhead, e.g. autocommit, but this is standard db application writing.

little worrisome. I can see how maybe some months down the road the inserts could become so slow that they can't even complete before the next cron job starts overlapping. One of the tables I planned to use MonetDB with already has 23.3MM rows. It would be preferable to

Wrt to growth, it increase with about 20% over the old one each time, which means you won't see it happen that often. the number is not the issue, it is the way you interact with a database, and the number of columns, all this in the context of your overall system resources.

rewrite the cron job so that it wrote all the data into a csv file and bulk loaded it when done? bulk loading is fast. And you may consider to dump batches into their own table, e.g. by week/month and use a union view to glue the cheaply together.

regards, Martin

On Wed, Oct 7, 2009 at 10:15 AM, Martin Kersten wrote:

...
Guillaume Theoret wrote:

...
Hi, I have a question about insert performance.

At first, with an empty table, it took a little over 1 second (around 1.5) to insert 1000 rows. Now, with 5.2MM rows, it's taking ~65s.

Is this to be expected?

Yes, the table space grows over time, which calls for a re-alloc operation. This may involve writing/reading. The fastest way to insert 5.2 M records is to use the SQL query COPY 5200000 RECORDS INTO tt FROM....

...
Thanks, Guillaume

------------------------------------------------------------------------------ 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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-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 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Guillaume Theoret

10:02 p.m.

Please disregard everything I said about insert slowdown. Turns out I had a bug in my script. Instead of incrementing the offset I was incrementing the limit size. So obviously the batches were taking longer and longer since they were getting bigger and bigger. I'm now at 3.5MM rows inserted and the avg insert time hasn't budged. Sorry about that.

Reply

Sign in to reply online Use email software

5521

Age (days ago)

5521

Last active (days ago)

Download

4 comments

2 participants

tags

participants (2)

Guillaume Theoret
Martin Kersten