pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBulk loading/merging
6+ messages / 4 participants
[nested] [flat]
* Bulk loading/merging
@ 2006-05-26 18:48 Worky Workerson <worky.workerson@gmail.com>
2006-05-26 19:04 ` Re: Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
2006-05-31 01:09 ` Re: Bulk loading/merging Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 6+ messages in thread
From: Worky Workerson @ 2006-05-26 18:48 UTC (permalink / raw)
To: pgsql-performance
I've set up something similar the 'recommended' way to merge data into
the DB, i.e.
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
however I did it with a trigger on insert, i.e. (not my schema :) ):
CREATE TABLE db (a INT PRIMARY KEY, b TEXT, c INTEGER, d INET);
CREATE FUNCTION merge_db() RETURNS TRIGGER AS
$$
BEGIN
UPDATE db SET b = NEW.data
WHERE a = NEW.key
AND NOT (c IS DISTINCT FROM NEW.c)
AND NOT (d IS DISTINCT FROM NEW.d);
IF found THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER merge_db_tr BEFORE INSERT ON db
FOR EACH ROW EXECUTE PROCEDURE merge_db();
Is this the best/fastest way to do this sort of thing? I only get
about 50 records/second inserts, while without the trigger (inserting
unmerged data) I can get more like 1000/second. I'm doing the whole
NOT ... IS DISTINCT stuff to handle NULL values that might be in the
columns ... I'm only considering two column keys equal if (a,c,d) are
all the same (i.e. either the same value or both NULL).
I read that there is a race condition with the above method as applied
to a normal function ... does this apply to a trigger as well?
Optimization Questions:
-Can I do better with the trigger function itself?
-I realize that I can create indexes on some of the lookup columns
('key' in the above example). This would speed up the location of the
update record but slow down the actual update insert, right? Would
this be a win? I tested an index on 10000 rows, and it beat out the
non-indexed by about 7% (3:31 with index, 3:45 without) ... is this
all the benefit that I can expect?
-Will moving pg_xlog to a different disk help all that much, if the
whole DB is currently on a 4 disk RAID10? What about moving the
indexes? I've set up my postgresql.conf according to the docs and
Josh Berkus' presentation, i.e. (16GB ram, quad Opteron moachine, not
all settings are relevant):
shared_buffers = 60000
temp_buffers = 10000
work_mem = 131072
maintenance_work_mem = 524288
effective_cache_size = 120000
random_page_cost = 2
wal_buffers = 128
checkpoint_segments = 128
checkpoint_timeout = 3000
max_fsm_pages = 2000000
max_fsm_relations = 1000000
-If I break up my dataset into smaller chunks and parallelize it,
could I get better total performance, or would I most likely be
thrashing the disk?
-If I sort the data in the COPY file by key (i.e. a,c,d) before
inserting it into the database, will this help out the DB at all?
-Its cleaner to just be able to insert everything into the database
and let the DB aggregate the records, however I could use some of our
extra hardware to do aggregation in perl and then output the already
aggregated records to the DB ... this has the advantage of being
easily parallelizable but requires a bit of extra work to get right.
Do you think that this is the best way to go?
Also, as a slight aside, without a trigger, COPY seems to process each
record very quickly (using Perl DBI, about 7000 records/second)
however there is a long pause once the last record has been delivered.
Is this just the backend queuing up the insert commands given by
perl, or is there extra processing that needs to be done at the end of
the COPY that could be taking a while (10s on 500K record COPY).
Thanks!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bulk loading/merging
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
@ 2006-05-26 19:04 ` Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Worky Workerson @ 2006-05-26 19:04 UTC (permalink / raw)
To: pgsql-performance
Another little question ... would using any sort of TEMP table help out, i.e.
loading the unaggregated data into a TEMP table, aggregating the data via a
SELECT INTO another TEMP table, and then finally INSERT ... SELECT into the
master, aggregated, triggered table? It seems like this might be a win if
A) the TEMP tables fit into memory, and B) the load data aggregates well.
Worst case (i.e. all unique data in the load) seems like it might take much
longer, however, since I'm creating 2 new TEMP tables ....
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bulk loading/merging
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
@ 2006-05-31 01:09 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-01 18:04 ` Re: Bulk loading/merging Michael Artz <mlartz@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-05-31 01:09 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Your best bet is to do this as a single, bulk operation if possible.
That way you can simply do an UPDATE ... WHERE EXISTS followed by an
INSERT ... SELECT ... WHERE NOT EXISTS.
On Fri, May 26, 2006 at 02:48:20PM -0400, Worky Workerson wrote:
> I've set up something similar the 'recommended' way to merge data into
> the DB, i.e.
>
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> however I did it with a trigger on insert, i.e. (not my schema :) ):
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bulk loading/merging
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
2006-05-31 01:09 ` Re: Bulk loading/merging Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-01 18:04 ` Michael Artz <mlartz@gmail.com>
2006-06-04 12:00 ` Re: Bulk loading/merging Ahmad Fajar <ahmadfajar@i2.co.id>
2006-06-05 15:04 ` Re: Bulk loading/merging Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 6+ messages in thread
From: Michael Artz @ 2006-06-01 18:04 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
On 5/30/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> Your best bet is to do this as a single, bulk operation if possible.
> That way you can simply do an UPDATE ... WHERE EXISTS followed by an
> INSERT ... SELECT ... WHERE NOT EXISTS.
hmm, I don't quite understand what you are saying and I think my
basic misunderstanding is how to use the UPDATE ... WHERE EXISTS to merge
data in bulk. Assuming that I bulk COPYed the data into a temporary
table, I'd need to issue an UPDATE for each row in the newly created table,
right?
For example, for a slightly different key,count schema:
CREATE TABLE kc (key integer, count integer);
and wanting to merge the following data by just updating the count for a
given key to the equivalent of OLD.count + NEW.count:
1,10
2,15
3,45
1,30
How would I go about using UPDATE ... WHERE EXISTS to update the "master" kc
table from a (temporary) table loaded with the above data?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bulk loading/merging
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
2006-05-31 01:09 ` Re: Bulk loading/merging Jim C. Nasby <jnasby@pervasive.com>
2006-06-01 18:04 ` Re: Bulk loading/merging Michael Artz <mlartz@gmail.com>
@ 2006-06-04 12:00 ` Ahmad Fajar <ahmadfajar@i2.co.id>
1 sibling, 0 replies; 6+ messages in thread
From: Ahmad Fajar @ 2006-06-04 12:00 UTC (permalink / raw)
To: 'Michael Artz' <mlartz@gmail.com>; +Cc: pgsql-performance
On 06/02/2006, Michael Artz wrote:
hmm, I don't quite understand what you are saying and I think my basic
misunderstanding is how to use the UPDATE ... WHERE EXISTS to merge data in
bulk. Assuming that I bulk COPYed the data into a temporary table, I'd need
to issue an UPDATE for each row in the newly created table, right?
For example, for a slightly different key,count schema:
CREATE TABLE kc (key integer, count integer);
and wanting to merge the following data by just updating the count for a
given key to the equivalent of OLD.count + NEW.count:
1,10
2,15
3,45
1,30
How would I go about using UPDATE ... WHERE EXISTS to update the "master" kc
table from a (temporary) table loaded with the above data?
May be, this method could help you:
CREATE TEMP TABLE clip_temp (
cids int8 NOT NULL,
clip_id int8 NOT NULL,
mentions int4 DEFAULT 0,
CONSTRAINT pk_clip_temp PRIMARY KEY (cids, clip_id))
)
insert data into this temporary table...
then do:
UPDATE clip_category SET mentions=clip_temp.mentions
FROM clip_temp
WHERE clip_category.cids=clip_temp.cids
AND clip_category.clip_id=clip_temp.clip_id
DELETE FROM clip_temp USING clip_category
WHERE clip_temp.cids=clip_category.cids
AND clip_temp.clip_id=clip_category.clip_id
INSERT INTO clip_category (cids, clip_id, mentions)
SELECT * FROM clip_temp
DROP TABLE clip_temp;
Best regards,
ahmad fajar,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bulk loading/merging
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
2006-05-31 01:09 ` Re: Bulk loading/merging Jim C. Nasby <jnasby@pervasive.com>
2006-06-01 18:04 ` Re: Bulk loading/merging Michael Artz <mlartz@gmail.com>
@ 2006-06-05 15:04 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jim C. Nasby @ 2006-06-05 15:04 UTC (permalink / raw)
To: Michael Artz <mlartz@gmail.com>; +Cc: pgsql-performance
On Thu, Jun 01, 2006 at 02:04:46PM -0400, Michael Artz wrote:
> On 5/30/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> >Your best bet is to do this as a single, bulk operation if possible.
> >That way you can simply do an UPDATE ... WHERE EXISTS followed by an
> >INSERT ... SELECT ... WHERE NOT EXISTS.
>
>
>
> hmm, I don't quite understand what you are saying and I think my
> basic misunderstanding is how to use the UPDATE ... WHERE EXISTS to merge
> data in bulk. Assuming that I bulk COPYed the data into a temporary
> table, I'd need to issue an UPDATE for each row in the newly created table,
> right?
>
> For example, for a slightly different key,count schema:
>
> CREATE TABLE kc (key integer, count integer);
>
> and wanting to merge the following data by just updating the count for a
> given key to the equivalent of OLD.count + NEW.count:
>
> 1,10
> 2,15
> 3,45
> 1,30
>
> How would I go about using UPDATE ... WHERE EXISTS to update the "master" kc
> table from a (temporary) table loaded with the above data?
CREATE TEMP TABLE moo () LIKE kc;
COPY ... moo;
BEGIN;
UPDATE kc
SET count=kc.count + moo.count
FROM moo
WHERE moo.key = kc.key
;
INSERT INTO kc(key, count)
SELECT key, count
FROM moo
WHERE NOT EXISTS (
SELECT 1
FROM kc
WHERE kc.key = moo.key
)
;
COMMIT;
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-06-05 15:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-26 18:48 Bulk loading/merging Worky Workerson <worky.workerson@gmail.com>
2006-05-26 19:04 ` Worky Workerson <worky.workerson@gmail.com>
2006-05-31 01:09 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-01 18:04 ` Michael Artz <mlartz@gmail.com>
2006-06-04 12:00 ` Ahmad Fajar <ahmadfajar@i2.co.id>
2006-06-05 15:04 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox