Thread: 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@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
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@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of > Daniel J. Luke > Sent: Wednesday, May 24, 2006 2:45 PM > To: pgsql-performance@postgresql.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@geeklair.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@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
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@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
Daniel J. Luke wrote: > 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. > are the batches single insert's, or within a big transaction? I.E., does the inserts look like: INSERT INSERT INSERT or BEGIN INSERT INSERT INSERT COMMIT If the former, the latter is a big win. Also, what release(s) are you running? LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: Larry.Rosenman@pervasive.com Web: www.pervasive.com
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@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
On May 24, 2006, at 4:12 PM, Larry Rosenman wrote: > are the batches single insert's, or within a big transaction? > If the former, the latter is a big win. One big transaction every 5 minutes using 'COPY FROM' (instead of inserts). > Also, what release(s) are you running? 8.1.x (I think we're upgrading from 8.1.3 to 8.1.4 today). -- Daniel J. Luke +========================================================+ | *---------------- dluke@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
Daniel J. Luke wrote: > On May 24, 2006, at 4:12 PM, Larry Rosenman wrote: >> are the batches single insert's, or within a big transaction? >> If the former, the latter is a big win. > > One big transaction every 5 minutes using 'COPY FROM' (instead of > inserts). > >> Also, what release(s) are you running? > > 8.1.x (I think we're upgrading from 8.1.3 to 8.1.4 today). > Had to ask :) Also, is pg_xlog on the same or different spindles from the rest of the PG Data directory? LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: Larry.Rosenman@pervasive.com Web: www.pervasive.com
On May 24, 2006, at 4:24 PM, Larry Rosenman wrote: > Also, is pg_xlog on the same or different spindles from the rest of > the > PG Data directory? It's sitting on the same disk array (but I'm doing 1 transaction every 5 minutes, and I'm not near the array's sustained write capacity, so I don't think that's currently limiting performance). -- Daniel J. Luke +========================================================+ | *---------------- dluke@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
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@pervasive.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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Steinar H. Gunderson Sent: Wednesday, May 24, 2006 4:04 PM To: Daniel J. Luke Cc: pgsql-performance@postgresql.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
Hi, Daniel, Daniel J. Luke wrote: > One big transaction every 5 minutes using 'COPY FROM' (instead of > inserts). Are you using "COPY table FROM '/path/to/file'", having the file sitting on the server, or "COPY table FROM STDIN" or psql "/copy", having the file sitting on the client? From our tests, having the file on the server can speed up the things by factor 2 or 3 in some cases. Also, using BINARY copy may give great benefits due to lower parsing overhead. As you say you're I/O bound, spreading tables, indices, wal and input file to different spindles won't help you much. HTH Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
On May 29, 2006, at 7:11 AM, Markus Schaber wrote: >> One big transaction every 5 minutes using 'COPY FROM' (instead of >> inserts). > > Are you using "COPY table FROM '/path/to/file'", having the file > sitting > on the server, or "COPY table FROM STDIN" or psql "/copy", having the > file sitting on the client? COPY table FROM STDIN using psql on the server I should have gprof numbers on a similarly set up test machine soon ... -- Daniel J. Luke +========================================================+ | *---------------- dluke@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+
Attachment
On May 30, 2006, at 3:59 PM, Daniel J. Luke wrote: > I should have gprof numbers on a similarly set up test machine > soon ... gprof output is available at http://geeklair.net/~dluke/ postgres_profiles/ (generated from CVS HEAD as of today). Any ideas are welcome. Thanks! -- Daniel J. Luke +========================================================+ | *---------------- dluke@geeklair.net ----------------* | | *-------------- http://www.geeklair.net -------------* | +========================================================+ | Opinions expressed are mine and do not necessarily | | reflect the opinions of my employer. | +========================================================+