Query regarding postgres lock contention
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
Import Notes
Reply to msg id not found: 1970492718.172835.1317805507022.JavaMail.root@mailhub035.itcs.purdue.edu
In addition to the previous post,
My postgres version is 8.3.7
Show quoted text
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@purdue.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.Grittner@wicourts.gov> wrote:
Hamza Bin Sohail <hsohail@purdue.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