Getting even more insert performance (250m+rows/day)

I have a system that currently inserts ~ 250 million rows per day (I
have about 10k more raw data than that, but I'm at the limit of my
ability to get useful insert performance out of postgres).

Things I've already done that have made a big difference:
- modified postgresql.conf shared_buffers value
- converted to COPY from individual insert statements
- changed BLCKSZ to 32768

I currently get ~35k/sec inserts on a table with one index (~70k/sec
inserts if I don't have any indexes).

The indexed field is basically a time_t (seconds since the epoch),
autovacuum is running (or postgres would stop choosing to use the
index). The other fields have relatively lower cardinality.

Each days worth of data gets inserted into its own table so that I
can expire the data without too much effort (since drop table is much
faster than running a delete and then vacuum).

I would really like to be able to have 1 (or 2) more indexes on the
table since it takes a while for a sequential scan of 250million rows
to complete, but CPU time goes way up.

In fact, it looks like I'm not currently IO bound, but CPU-bound. I
think some sort of lazy-index generation (especially if it could be
parallelized to use the other processors/cores that currently sit
mostly idle) would be a solution. Is anyone working on something like
this? Any other ideas? Where should I look if I want to start to
think about creating a new index that would work this way (or am I
just crazy)?

Thanks for any insight!

--
Daniel J. Luke
+========================================================+
| *---------------- dluke(at)geeklair(dot)net ----------------* |
| *-------------- http://www.geeklair.net -------------* |
+========================================================+
| Opinions expressed are mine and do not necessarily |
| reflect the opinions of my employer. |
+========================================================+

If you can live with possible database corruption, you could try turning
Fsync off. For example if you could just reinsert the data on the off
chance a hardware failure corrupts the database, you might get a decent
improvement.

Also have you tried creating the index after you have inserted all your
data? (Or maybe copy already disables the indexes while inserting?)

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Daniel J. Luke
> Sent: Wednesday, May 24, 2006 2:45 PM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: [PERFORM] Getting even more insert performance
> (250m+rows/day)
>
>
> I have a system that currently inserts ~ 250 million rows per day (I
> have about 10k more raw data than that, but I'm at the limit of my
> ability to get useful insert performance out of postgres).
>
> Things I've already done that have made a big difference:
> - modified postgresql.conf shared_buffers value
> - converted to COPY from individual insert statements
> - changed BLCKSZ to 32768
>
> I currently get ~35k/sec inserts on a table with one index (~70k/sec
> inserts if I don't have any indexes).
>
> The indexed field is basically a time_t (seconds since the epoch),
> autovacuum is running (or postgres would stop choosing to use the
> index). The other fields have relatively lower cardinality.
>
> Each days worth of data gets inserted into its own table so that I
> can expire the data without too much effort (since drop table
> is much
> faster than running a delete and then vacuum).
>
> I would really like to be able to have 1 (or 2) more indexes on the
> table since it takes a while for a sequential scan of
> 250million rows
> to complete, but CPU time goes way up.
>
> In fact, it looks like I'm not currently IO bound, but CPU-bound. I
> think some sort of lazy-index generation (especially if it could be
> parallelized to use the other processors/cores that currently sit
> mostly idle) would be a solution. Is anyone working on
> something like
> this? Any other ideas? Where should I look if I want to start to
> think about creating a new index that would work this way (or am I
> just crazy)?
>
> Thanks for any insight!
>
> --
> Daniel J. Luke
> +========================================================+
> | *---------------- dluke(at)geeklair(dot)net ----------------* |
> | *-------------- http://www.geeklair.net -------------* |
> +========================================================+
> | Opinions expressed are mine and do not necessarily |
> | reflect the opinions of my employer. |
> +========================================================+
>
>
>

On Wed, May 24, 2006 at 03:45:17PM -0400, Daniel J. Luke wrote:
> Things I've already done that have made a big difference:
> - modified postgresql.conf shared_buffers value
> - converted to COPY from individual insert statements
> - changed BLCKSZ to 32768

Have you tried fiddling with the checkpointing settings? Check your logs --
if you get a warning about checkpoints being too close together, that should
give you quite some boost.

Apart from that, you should have quite a bit to go on -- somebody on this
list reported 2 billion rows/day earlier, but it might have been on beefier
hardware, of course. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
> If you can live with possible database corruption, you could try
> turning
> Fsync off. For example if you could just reinsert the data on the off
> chance a hardware failure corrupts the database, you might get a
> decent
> improvement.

I tried, but I didn't see much of an improvement (and it's not really
acceptable for this application).

> Also have you tried creating the index after you have inserted all
> your
> data? (Or maybe copy already disables the indexes while inserting?)

The data gets inserted in batches every 5 minutes and I potentially
have people querying it constantly, so I can't remove and re-create
the index.

--
Daniel J. Luke
+========================================================+
| *---------------- dluke(at)geeklair(dot)net ----------------* |
| *-------------- http://www.geeklair.net -------------* |
+========================================================+
| Opinions expressed are mine and do not necessarily |
| reflect the opinions of my employer. |
+========================================================+

On May 24, 2006, at 4:03 PM, Steinar H. Gunderson wrote:
> Have you tried fiddling with the checkpointing settings? Check your
> logs --
> if you get a warning about checkpoints being too close together,
> that should
> give you quite some boost.

no warnings in the log (I did change the checkpoint settings when I
set up the database, but didn't notice an appreciable difference in
insert performance).

> Apart from that, you should have quite a bit to go on -- somebody
> on this
> list reported 2 billion rows/day earlier, but it might have been on
> beefier
> hardware, of course. :-)

Probably :) I'll keep searching the list archives and see if I find
anything else (I did some searching and didn't find anything that I
hadn't already tried).

Thanks!

--
Daniel J. Luke
+========================================================+
| *---------------- dluke(at)geeklair(dot)net ----------------* |
| *-------------- http://www.geeklair.net -------------* |
+========================================================+
| Opinions expressed are mine and do not necessarily |
| reflect the opinions of my employer. |
+========================================================+

On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
> no warnings in the log (I did change the checkpoint settings when I
> set up the database, but didn't notice an appreciable difference in
> insert performance).

How about wal_buffers? Upping it might not help all that much if only one
thread is writing, but you might give it a try...

/* Steinar */
--
Homepage: http://www.sesse.net/

> The data gets inserted in batches every 5 minutes and I potentially
> have people querying it constantly, so I can't remove and re-create
> the index.

How live does your data need to be? One possibility would be to use a
separate table for each batch instead of a separate table per day,
create the indexes after the import and only after the indexes have been
created make the table available for user queries.

You'd be trading latency for throughput in that case.

Also, you mentioned that you're CPU-bound, but that you have multiple
CPU's. In that case, performing N concurrent imports (where N is the
number of processor cores available) might be a win over a single-
threaded import.

-- Mark Lewis

On May 24, 2006, at 4:13 PM, Steinar H. Gunderson wrote:
> On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
>> no warnings in the log (I did change the checkpoint settings when I
>> set up the database, but didn't notice an appreciable difference in
>> insert performance).
>
> How about wal_buffers? Upping it might not help all that much if
> only one
> thread is writing, but you might give it a try...

I tried, but I didn't notice a difference.

I should probably emphasize that I appear to be CPU bound (and I can
double my # of rows inserted per second by removing the index on the
table, or half it by adding another index).

I really should run gprof just to verify.

--
Daniel J. Luke
+========================================================+
| *---------------- dluke(at)geeklair(dot)net ----------------* |
| *-------------- http://www.geeklair.net -------------* |
+========================================================+
| Opinions expressed are mine and do not necessarily |
| reflect the opinions of my employer. |
+========================================================+

On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
> On May 24, 2006, at 4:03 PM, Steinar H. Gunderson wrote:
> >Have you tried fiddling with the checkpointing settings? Check your
> >logs --
> >if you get a warning about checkpoints being too close together,
> >that should
> >give you quite some boost.
>
> no warnings in the log (I did change the checkpoint settings when I
> set up the database, but didn't notice an appreciable difference in
> insert performance).

Keep in mind that the default warning time of 30 seconds is pretty
conservative; you'd want to bump that up to 300 seconds or so, probably.

As for the suggestion of multiple insert runs at a time, I suspect that
would just result in a lot of contention for some mutex/semaphore in the
index.

Your best bet really is to run gprof and post those results. It's also
possible that this is fixed be a recent patch to HEAD that reduces the
amount of traffic on the index metapage, something gprof would probably
confirm.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

We were able to achieve 2B (small) rows per day sustained with
very little latency. It is beefy hardware, but things that did
help include WAL on its own I/O channel, XFS, binary copy,
and tuning bgwriter and checkpoint settings for the application
and hardware. Things that didn't help much were shared_buffers
and wal_buffers. But our application is single-writer, and a
small number of readers.

Although there is tons of great advice in this and other forums,
I think you just have to do a lot of experimentation with careful
measurement to find what's right for your application/environment.
i.e., YMMV.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Steinar H.
Gunderson
Sent: Wednesday, May 24, 2006 4:04 PM
To: Daniel J. Luke
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Getting even more insert performance
(250m+rows/day)

On Wed, May 24, 2006 at 03:45:17PM -0400, Daniel J. Luke wrote:
> Things I've already done that have made a big difference:
> - modified postgresql.conf shared_buffers value
> - converted to COPY from individual insert statements
> - changed BLCKSZ to 32768

Have you tried fiddling with the checkpointing settings? Check your logs --
if you get a warning about checkpoints being too close together, that should
give you quite some boost.

Apart from that, you should have quite a bit to go on -- somebody on this
list reported 2 billion rows/day earlier, but it might have been on beefier
hardware, of course. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend