Skip site navigation (1) Skip section navigation (2)

Re: Linux/PostgreSQL scalability issue - problem with 8cores

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Jakub Ouhrabka <kuba(at)comgate(dot)cz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8cores
Date: 2008-01-25 23:27:09
Message-ID: 1201303629.4257.554.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 2008-01-07 at 19:54 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Perhaps it would make sense to try to take the "fast path" in
> > SIDelExpiredDataEntries with only a shared lock rather than exclusive.
> 
> I think the real problem here is that sinval catchup processing is well
> designed to create contention :-(.  

Thinking some more about handling TRUNCATEs...

> Some ideas for improving matters:
> 
> 1. Try to avoid having all the backends hit the queue at once.  Instead
> of SIGUSR1'ing everybody at the same time, maybe hit only the process
> with the oldest message pointer, and have him hit the next oldest after
> he's done reading the queue.
> 
> 2. Try to take more than one message off the queue per SInvalLock cycle.
> (There is a tuning tradeoff here, since it would mean holding the lock
> for longer at a time.)
> 
> 3. Try to avoid having every backend run SIDelExpiredDataEntries every
> time through ReceiveSharedInvalidMessages.  It's not critical to delete
> entries until the queue starts getting full --- maybe we could rejigger
> the logic so it only happens once when somebody notices the queue is
> getting full, or so that only the guy(s) who had nextMsgNum == minMsgNum
> do it, or something like that?

(2) is unnecessary if we can reduce the number of Exclusive lockers so
that repeated access to the backend's messages is not contended.

(1) would do this, but seems like it would be complex. We can reduce the
possibility of multiple re-signals though.

(3) seems like the easiest route, as long as we get a reasonable
algorithm for reducing the access rate to a reasonable level. 

I'm posting a patch for discussion to -patches now that will do this. It
seems straightforward enough to include in 8.3, but that may rise a few
eyebrows, but read the patch first.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-01-26 02:06:58
Subject: Re: How do I bulk insert to a table without affecting read performance on that table?
Previous:From: growseDate: 2008-01-25 23:27:05
Subject: How do I bulk insert to a table without affecting read performance on that table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group