Обсуждение: 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
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@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