pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert performance with composite index
16+ messages / 5 participants
[nested] [flat]
* Insert performance with composite index
@ 2010-11-01 12:49 Divakar Singh <dpsmails@yahoo.com>
0 siblings, 3 replies; 16+ messages in thread
From: Divakar Singh @ 2010-11-01 12:49 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am trying to tune my libpq program for insert performance.
When I tried inserting 1M rows into a table with a Primary Key, it took almost
62 seconds.
After adding a composite index of 2 columns, the performance degrades to 125
seconds.
I am using COPY to insert all data in 1 transaction.
the table definition is
CREATE TABLE ABC
(
event integer,
innodeid character varying(80),
innodename character varying(80),
sourceid character varying(300),
intime timestamp(3) without time zone,
outnodeid character varying(80),
outnodename character varying(80),
destinationid character varying(300),
outtime timestamp(3) without time zone,
bytes integer,
cdrs integer,
tableindex integer NOT NULL,
noofsubfilesinfile integer,
recordsequenceintegerlist character varying(1000),
CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
)
the index definition is
CREATE INDEX "PK_AT2"
ON ABC
USING btree
(event, tableindex)
TABLESPACE sample;
Any tip to increase the insert performance in this case?
It would also be helpful if someone can send comprehensive libpq programming
guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me.
Best Regards,
Divakar
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 12:53 Marti Raudsepp <marti@juffo.org>
parent: Divakar Singh <dpsmails@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Marti Raudsepp @ 2010-11-01 12:53 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
On Mon, Nov 1, 2010 at 14:49, Divakar Singh <dpsmails@yahoo.com> wrote:
> I am trying to tune my libpq program for insert performance.
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to 125
> seconds.
This sounds a lot like the bottleneck I was hitting. What Linux kernel
version are you running?
If it's 2.6.33 or later, see:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php
Regards,
Marti
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 12:56 Divakar Singh <dpsmails@yahoo.com>
parent: Marti Raudsepp <marti@juffo.org>
0 siblings, 1 reply; 16+ messages in thread
From: Divakar Singh @ 2010-11-01 12:56 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: pgsql-performance
Hi Marti,
Thanks for your tips. i will try those.
I am on Solaris Sparc 5.10
Best Regards,
Divakar
________________________________
From: Marti Raudsepp <marti@juffo.org>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 6:23:17 PM
Subject: Re: [PERFORM] Insert performance with composite index
On Mon, Nov 1, 2010 at 14:49, Divakar Singh <dpsmails@yahoo.com> wrote:
> I am trying to tune my libpq program for insert performance.
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to 125
> seconds.
This sounds a lot like the bottleneck I was hitting. What Linux kernel
version are you running?
If it's 2.6.33 or later, see:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php
Regards,
Marti
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 13:04 Marti Raudsepp <marti@juffo.org>
parent: Divakar Singh <dpsmails@yahoo.com>
0 siblings, 0 replies; 16+ messages in thread
From: Marti Raudsepp @ 2010-11-01 13:04 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
On Mon, Nov 1, 2010 at 14:56, Divakar Singh <dpsmails@yahoo.com> wrote:
> Thanks for your tips. i will try those.
> I am on Solaris Sparc 5.10
Sorry, I assumed you were running Linux. But still it could be the
same problem as I had.
Be careful changing your wal_sync_method, as it has the potential to
corrupt your database. I have no experience with Solaris.
For what it's worth, Jignesh Shah recommends using
wal_sync_method=fsync on Solaris:
http://blogs.sun.com/jkshah/entry/postgresql_on_solaris_better_use
http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and
Regards,
Marti
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 13:57 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: Divakar Singh <dpsmails@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Cédric Villemain @ 2010-11-01 13:57 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
2010/11/1 Divakar Singh <dpsmails@yahoo.com>:
> Hi,
> I am trying to tune my libpq program for insert performance.
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to 125
> seconds.
> I am using COPY to insert all data in 1 transaction.
>
> the table definition is
>
> CREATE TABLE ABC
> (
> event integer,
> innodeid character varying(80),
> innodename character varying(80),
> sourceid character varying(300),
> intime timestamp(3) without time zone,
> outnodeid character varying(80),
> outnodename character varying(80),
> destinationid character varying(300),
> outtime timestamp(3) without time zone,
> bytes integer,
> cdrs integer,
> tableindex integer NOT NULL,
> noofsubfilesinfile integer,
> recordsequenceintegerlist character varying(1000),
> CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
> )
>
> the index definition is
>
>
> CREATE INDEX "PK_AT2"
> ON ABC
> USING btree
> (event, tableindex)
> TABLESPACE sample;
Indexing twice the same column is useless. (perhaps move your PK to
the tablespace 'sample' is good too ?)
>
> Any tip to increase the insert performance in this case?
If you create or truncate table then copy to it, you should create
index after the copy order.
>
> It would also be helpful if someone can send comprehensive libpq programming
> guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like
> me.
>
>
> Best Regards,
> Divakar
>
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:03 Andres Freund <andres@anarazel.de>
parent: Divakar Singh <dpsmails@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Andres Freund @ 2010-11-01 14:03 UTC (permalink / raw)
To: pgsql-performance; +Cc: Divakar Singh <dpsmails@yahoo.com>
Hi,
On Monday 01 November 2010 13:49:14 Divakar Singh wrote:
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to
> 125 seconds.
> I am using COPY to insert all data in 1 transaction.
Without seeing your config its hard to suggest anything here. Did you do basic
tuning of your pg installation?
wal_buffers, shared_buffers, checkpoint_segments, maintenance_work_mem are
likely most relevant for that specific case.
Andres
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:14 Andres Freund <andres@anarazel.de>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 16+ messages in thread
From: Andres Freund @ 2010-11-01 14:14 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
On Monday 01 November 2010 15:08:10 Divakar Singh wrote:
> here are my parameters:
Which pg version is that?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:16 Divakar Singh <dpsmails@yahoo.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 16+ messages in thread
From: Divakar Singh @ 2010-11-01 14:16 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance
I am using 9.0.1
Best Regards,
Divakar
________________________________
From: Andres Freund <andres@anarazel.de>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 7:44:31 PM
Subject: Re: [PERFORM] Insert performance with composite index
On Monday 01 November 2010 15:08:10 Divakar Singh wrote:
> here are my parameters:
Which pg version is that?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:20 Andres Freund <andres@anarazel.de>
parent: Divakar Singh <dpsmails@yahoo.com>
0 siblings, 1 reply; 16+ messages in thread
From: Andres Freund @ 2010-11-01 14:20 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
On Monday 01 November 2010 15:16:49 Divakar Singh wrote:
> I am using 9.0.1
Either thats not true or you cargo culted loads of your config from a
significantly older pg version.
Things like:
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
bgwriter_lru_percent = 0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 0 # 0-1000 buffers max written/round
make me very suspicious.
As I said, I would check the variables I referenced in my first post...
Andres
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:28 Divakar Singh <dpsmails@yahoo.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 16+ messages in thread
From: Divakar Singh @ 2010-11-01 14:28 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance
Do you mean these parameters have been removed starting 9.X?
As I see on
http://www.network-theory.co.uk/docs/postgresql/vol3/BackgroundWriter.html
,these parameters were added starting from 8.0 right?
Best Regards,
Divakar
________________________________
From: Andres Freund <andres@anarazel.de>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 7:50:59 PM
Subject: Re: [PERFORM] Insert performance with composite index
On Monday 01 November 2010 15:16:49 Divakar Singh wrote:
> I am using 9.0.1
Either thats not true or you cargo culted loads of your config from a
significantly older pg version.
Things like:
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
bgwriter_lru_percent = 0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 0 # 0-1000 buffers max written/round
make me very suspicious.
As I said, I would check the variables I referenced in my first post...
Andres
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-01 14:34 Andres Freund <andres@anarazel.de>
parent: Divakar Singh <dpsmails@yahoo.com>
0 siblings, 0 replies; 16+ messages in thread
From: Andres Freund @ 2010-11-01 14:34 UTC (permalink / raw)
To: Divakar Singh <dpsmails@yahoo.com>; +Cc: pgsql-performance
On Monday 01 November 2010 15:28:19 Divakar Singh wrote:
> Do you mean these parameters have been removed starting 9.X?
> As I see on
> http://www.network-theory.co.uk/docs/postgresql/vol3/BackgroundWriter.html
> ,these parameters were added starting from 8.0 right?
No, I mean setting to 0 is a bit of a strange value in many situations.
And you have comments like:
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
Which reference config options which do not exist anymore. And you have
shared_buffers = 81920
Which indicates that you started from 8.1/8.2 or so...
Andres
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-02 09:45 hubert depesz lubaczewski <depesz@depesz.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: hubert depesz lubaczewski @ 2010-11-02 09:45 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: Divakar Singh <dpsmails@yahoo.com>; pgsql-performance
On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
> > CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
> > )
> > the index definition is
> > CREATE INDEX "PK_AT2"
> > ON ABC
> > USING btree
> > (event, tableindex)
> > TABLESPACE sample;
>
> Indexing twice the same column is useless. (perhaps move your PK to
> the tablespace 'sample' is good too ?)
why do you say that?
these are not the same indexes and they serve different purposes.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-02 11:04 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 16+ messages in thread
From: Cédric Villemain @ 2010-11-02 11:04 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Divakar Singh <dpsmails@yahoo.com>; pgsql-performance
2010/11/2 hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
>> > CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
>> > )
>> > the index definition is
>> > CREATE INDEX "PK_AT2"
>> > ON ABC
>> > USING btree
>> > (event, tableindex)
>> > TABLESPACE sample;
>>
>> Indexing twice the same column is useless. (perhaps move your PK to
>> the tablespace 'sample' is good too ?)
>
> why do you say that?
> these are not the same indexes and they serve different purposes.
Given that tableindex is the PK column, I really like to now the usage
pattern for having it indexed twice.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-02 11:53 hubert depesz lubaczewski <depesz@depesz.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: hubert depesz lubaczewski @ 2010-11-02 11:53 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: Divakar Singh <dpsmails@yahoo.com>; pgsql-performance
On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote:
> 2010/11/2 hubert depesz lubaczewski <depesz@depesz.com>:
> > On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
> >> > CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
> >> > )
> >> > the index definition is
> >> > CREATE INDEX "PK_AT2"
> >> > ON ABC
> >> > USING btree
> >> > (event, tableindex)
> >> > TABLESPACE sample;
> >>
> >> Indexing twice the same column is useless. (perhaps move your PK to
> >> the tablespace 'sample' is good too ?)
> >
> > why do you say that?
> > these are not the same indexes and they serve different purposes.
>
> Given that tableindex is the PK column, I really like to now the usage
> pattern for having it indexed twice.
select * from table where event = 123 order by tableindex desc limit 50;
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-02 12:51 Divakar Singh <dpsmails@yahoo.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Divakar Singh @ 2010-11-02 12:51 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; depesz@depesz.com; +Cc: pgsql-performance
May be a query that is filtering based on these 2 columns?
Best Regards,
Divakar
________________________________
From: Cédric Villemain <cedric.villemain.debian@gmail.com>
To: depesz@depesz.com
Cc: Divakar Singh <dpsmails@yahoo.com>; pgsql-performance@postgresql.org
Sent: Tue, November 2, 2010 4:34:42 PM
Subject: Re: [PERFORM] Insert performance with composite index
2010/11/2 hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
>> > CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
>> > )
>> > the index definition is
>> > CREATE INDEX "PK_AT2"
>> > ON ABC
>> > USING btree
>> > (event, tableindex)
>> > TABLESPACE sample;
>>
>> Indexing twice the same column is useless. (perhaps move your PK to
>> the tablespace 'sample' is good too ?)
>
> why do you say that?
> these are not the same indexes and they serve different purposes.
Given that tableindex is the PK column, I really like to now the usage
pattern for having it indexed twice.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Insert performance with composite index
@ 2010-11-02 20:32 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 16+ messages in thread
From: Cédric Villemain @ 2010-11-02 20:32 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Divakar Singh <dpsmails@yahoo.com>; pgsql-performance
2010/11/2 hubert depesz lubaczewski <depesz@depesz.com>:
> On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote:
>> 2010/11/2 hubert depesz lubaczewski <depesz@depesz.com>:
>> > On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
>> >> > CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
>> >> > )
>> >> > the index definition is
>> >> > CREATE INDEX "PK_AT2"
>> >> > ON ABC
>> >> > USING btree
>> >> > (event, tableindex)
>> >> > TABLESPACE sample;
>> >>
>> >> Indexing twice the same column is useless. (perhaps move your PK to
>> >> the tablespace 'sample' is good too ?)
>> >
>> > why do you say that?
>> > these are not the same indexes and they serve different purposes.
>>
>> Given that tableindex is the PK column, I really like to now the usage
>> pattern for having it indexed twice.
>
> select * from table where event = 123 order by tableindex desc limit 50;
Correct. Thanks Hubert.
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-11-02 20:32 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-01 12:49 Insert performance with composite index Divakar Singh <dpsmails@yahoo.com>
2010-11-01 12:53 ` Marti Raudsepp <marti@juffo.org>
2010-11-01 12:56 ` Divakar Singh <dpsmails@yahoo.com>
2010-11-01 13:04 ` Marti Raudsepp <marti@juffo.org>
2010-11-01 13:57 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2010-11-02 09:45 ` hubert depesz lubaczewski <depesz@depesz.com>
2010-11-02 11:04 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2010-11-02 11:53 ` hubert depesz lubaczewski <depesz@depesz.com>
2010-11-02 20:32 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2010-11-02 12:51 ` Divakar Singh <dpsmails@yahoo.com>
2010-11-01 14:03 ` Andres Freund <andres@anarazel.de>
2010-11-01 14:14 ` Andres Freund <andres@anarazel.de>
2010-11-01 14:16 ` Divakar Singh <dpsmails@yahoo.com>
2010-11-01 14:20 ` Andres Freund <andres@anarazel.de>
2010-11-01 14:28 ` Divakar Singh <dpsmails@yahoo.com>
2010-11-01 14:34 ` Andres Freund <andres@anarazel.de>
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