Marti Raudsepp | 1 Nov 02:29 2010

Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On Sun, Oct 31, 2010 at 21:59, Greg Smith <greg <at> 2ndquadrant.com> wrote:
> open_datasync support was just added to Linux itself very recently.

Oh I didn't realize it was a new feature. Indeed O_DSYNC support was
added in 2.6.33

It seems like bad behavior on PostgreSQL's part to default to new,
untested features.

I have updated the tuning wiki page with my understanding of the problem:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers

Regards,
Marti

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Mark Kirkwood | 1 Nov 06:03 2010
Picon

Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On 01/11/10 08:59, Greg Smith wrote:
Marti Raudsepp wrote:
Unless fdatasync is unsafe, I'd very much want to see it as the
default for 9.1 on Linux (I don't know about other platforms).  I
can't see any reasons why each write would need to be sync-ed if I
don't commit that often. Increasing wal_buffers probably has the same
effect wrt data safety.
 

Writes only are sync'd out when you do a commit, or the database does a checkpoint.

This issue is a performance difference introduced by a recent change to Linux.  open_datasync support was just added to Linux itself very recently.  It may be more safe than fdatasync on your platform.  As new code it may have bugs so that it doesn't really work at all under heavy load.  No one has really run those tests yet.  See http://wiki.postgresql.org/wiki/Reliable_Writes for some background, and welcome to the fun of being an early adopter.  The warnings in the tuning guide are there for a reason--you're in untested territory now.  I haven't finished validating whether I consider 2.6.32 safe for production use or not yet, and 2.6.36 is a solid year away from being on my list for even considering it as a production database kernel.  You should proceed presuming that all writes are unreliable until proven otherwise.


Greg,

Your reply is possibly a bit confusingly worded - Marti was suggesting that fdatasync be the default - so he wouldn't be a new adopter, since this call has been implemented in the kernel for ages. I guess you were wanting to stress that *open_datasync* is the new kid, so watch out to see if he bites...

Cheers

Mark
Divakar Singh | 1 Nov 13:49 2010
Picon

Insert performance with composite index

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


Divakar Singh | 1 Nov 13:56 2010
Picon

Re: Insert performance with composite index

Hi Marti,
Thanks for your tips. i will try those.
I am on Solaris Sparc 5.10
 
Best Regards,
Divakar


From: Marti Raudsepp <marti <at> juffo.org>
To: Divakar Singh <dpsmails <at> yahoo.com>
Cc: pgsql-performance <at> 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 <at> 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_buf fers
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

Regards,
Marti

Marti Raudsepp | 1 Nov 13:53 2010

Re: Insert performance with composite index

On Mon, Nov 1, 2010 at 14:49, Divakar Singh <dpsmails <at> 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

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Marti Raudsepp | 1 Nov 14:04 2010

Re: Insert performance with composite index

On Mon, Nov 1, 2010 at 14:56, Divakar Singh <dpsmails <at> 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

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Cédric Villemain | 1 Nov 14:57 2010
Picon

Re: Insert performance with composite index

2010/11/1 Divakar Singh <dpsmails <at> 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

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Andres Freund | 1 Nov 15:03 2010
Picon

Re: Insert performance with composite index

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

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Andres Freund | 1 Nov 15:14 2010
Picon

Re: 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?

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Divakar Singh | 1 Nov 15:16 2010
Picon

Re: Insert performance with composite index

I am using 9.0.1
 
Best Regards,
Divakar


From: Andres Freund <andres <at> anarazel.de>
To: Divakar Singh <dpsmails <at> yahoo.com>
Cc: pgsql-performance <at> 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?


Gmane