Re: Query regarding postgres lock contention - Followup

Hi there,

Just to let you know, I'm not a database expert by any means.
I have configured dbt-2 with postgres and created a database with 4000 warehouses,
150 customers etc. The database size is over 8G. I am aware that lock contention
can be checked with lockstat (and with pg_locks ? ) but I wanted to know if
someone can tell me how much contention there would be for this database
in a 16-core system vs a 4-core system. I just need a rough idea.

Any response would be very helpful

Thanks

~Hamza

In addition to the previous post,

My postgres version is 8.3.7

>Hi there,
>
>Just to let you know, I'm not a database expert by any means.
>I have configured dbt-2 with postgres and created a database with 4000 warehouses,
>150 customers etc. The database size is over 8G. I am aware that lock contention
>can be checked with lockstat (and with pg_locks ? ) but I wanted to know if
>someone can tell me how much contention there would be for this database
>in a 16-core system vs a 4-core system. I just need a rough idea.
>
>Any response would be very helpful
>
>Thanks
>
>~Hamza

Hamza Bin Sohail <hsohail(at)purdue(dot)edu> wrote:

> My postgres version is 8.3.7

Why such an old version? Why exclude the available bug fixes?

http://www.postgresql.org/support/versioning

>> I am aware that lock contention can be checked with lockstat (and
>> with pg_locks ? ) but I wanted to know if someone can tell me how
>> much contention there would be for this database in a 16-core
>> system vs a 4-core system. I just need a rough idea.

How many database connections will be used? If more than about
twice the number of cores, you should probably be going through a
transaction-based connection pool.

With 16 cores, even with a properly configured connection pool, you
will probably be on the edge of where spinlock contention starts
eating significant CPU time. With enough system RAM and proper
tuning the hit should be fairly minor, I think. It really gets bad
at 32 cores, although that is being improved for next year's 9.2
release.

-Kevin

On Wed, Oct 5, 2011 at 1:48 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Hamza Bin Sohail <hsohail(at)purdue(dot)edu> wrote:
>
>> My postgres version is 8.3.7
>
> Why such an old version?  Why exclude the available bug fixes?
>
> http://www.postgresql.org/support/versioning
>
>>> I am aware that lock contention can be checked with lockstat (and
>>> with pg_locks ? ) but I wanted to know if someone can tell me how
>>> much contention there would be for this database in a 16-core
>>> system vs a 4-core system. I just need a rough idea.
>
> How many database connections will be used?  If more than about
> twice the number of cores, you should probably be going through a
> transaction-based connection pool.
>
> With 16 cores, even with a properly configured connection pool, you
> will probably be on the edge of where spinlock contention starts
> eating significant CPU time.  With enough system RAM and proper
> tuning the hit should be fairly minor, I think.  It really gets bad
> at 32 cores, although that is being improved for next year's 9.2
> release.

I think that on write-heavy workloads (like pgbench) we bottleneck on
lightweight lock contention around 8 cores. :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company