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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores
Date: 2008-01-14 15:22:14
Message-ID: 13894.1200324134@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Jakub Ouhrabka <kuba(at)comgate(dot)cz> writes:
> What does it mean?

Look at src/include/storage/sinval.h and src/include/utils/syscache.h.
What you seem to have here is a bunch of tuple updates in pg_class
(invalidating caches 29 and 30, which in 8.2 correspond to RELNAMENSP
and RELOID), followed by a bunch of SharedInvalRelcacheMsg and
SharedInvalSmgrMsg.

What I find interesting is that the hits are coming against
nearly-successive tuple CTIDs in pg_class, eg these are all
on pages 25 and 26 of pg_class:

> LOG:  sending inval msg 30 0 25 45 30036 4294936595
> LOG:  sending inval msg 29 0 25 45 30036 2019111801
> LOG:  sending inval msg 30 0 26 11 30036 4294936595
> LOG:  sending inval msg 29 0 26 11 30036 2019111801
> LOG:  sending inval msg 30 0 25 44 30036 4294936597
> LOG:  sending inval msg 29 0 25 44 30036 3703878920
> LOG:  sending inval msg 30 0 26 10 30036 4294936597
> LOG:  sending inval msg 29 0 26 10 30036 3703878920
> LOG:  sending inval msg 30 0 26 9 30036 4294936616
> LOG:  sending inval msg 29 0 26 9 30036 3527122063
> LOG:  sending inval msg 30 0 25 43 30036 4294936616
> LOG:  sending inval msg 29 0 25 43 30036 3527122063

The ordering is a little strange --- not sure what's producing that.

I can think of three things that might be producing this:

1. DDL operations ... but most sorts of DDL on a table would touch
more catalogs than just pg_class, so this is a bit hard to credit.

2. VACUUM.

3. Some sort of direct update of pg_class.

The fact that we have a bunch of catcache invals followed by
relcache/smgr invals says that this all happened in one transaction,
else they'd have been intermixed better.  That lets VACUUM off the
hook, because it processes each table in a separate transaction.

I am wondering if maybe your app does one of those sneaky things like
fooling with pg_class.reltriggers.  If so, the problem might be soluble
by just avoiding unnecessary updates.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: PatricDate: 2008-01-14 18:19:36
Subject: Saving result set of SELECT to table column
Previous:From: Guillaume CottenceauDate: 2008-01-14 14:34:36
Subject: Re: Seq scans on indexed columns.

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