Re: App very unresponsive while performing simple update - Mailing list pgsql-performance

"Jim C. Nasby" <jnasby@pervasive.com> writes:

> On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
> > Brendan Duddridge <brendan@clickspace.com> writes:
> >
> > > We do have foreign keys on other tables that reference the product  table.
> > > Also, there will be updates going on at the same time as this  update. When
> > > anyone clicks on a product details link, we issue an  update statement to
> > > increment the click_count on the product. e.g.  update product set click_count
> > > = click_count + 1;
> >
> > You should realize this will produce a lot of garbage records and mean you'll
> > have to be running vacuum very frequently. You might consider instead of
> > updating the main table inserting into a separate clickstream table. That
> > trades off not getting instantaneous live totals with isolating the
> > maintenance headache in a single place. That table will grow large but you can
> > prune it at your leisure without impacting query performance on your main
> > tables.
>
> Actually, you can still get instant results, you just have to hit two
> tables to do it.

But that defeats the purpose of moving this traffic out to the clickstream
table. The whole point is to avoid generating garbage records in your main
table that you're doing a lot of real-time queries against.

I would probably keep the clickstream table, then once a day or perhaps more
often perform an aggregate query against it to generate a summary table (and
then vacuum full or cluster it since it's half garbage). Then join from the
main product table to the summary table to sort by popularity.

If you need results that are more up-to-date than 24 hours and/or can't stand
the downtime of the daily vacuum full on the summary table it becomes a lot
harder.

> > More likely you were blocking on some lock. Until that other query holding
> > that lock tries to commit Postgres won't actually detect a deadlock, it'll
> > just sit waiting until the lock becomes available.
>
> Wow, are you sure that's how it works? I would think it would be able to
> detect deadlocks as soon as both processes are waiting on each other's
> locks.

I didn't mean to describe the general situation, just what I suspected was
happening in this case. The user had a large batch update that was performing
poorly. I suspect it may have been performing poorly because it was spending
time waiting to acquire an exclusive lock. There would be no deadlock yet,
just very slow updates.

However the other client updating the other table has deferred foreign key
constraints back to the table the big update is acquiring all these exclusive
locks. Locks for deferred constraints aren't taken until they're checked. So
the actual deadlock doesn't occur until the commit occurs.

In any case Tom said I was misunderstanding the deadlock message he posted.
The kind of situation I'm talking about would look something like this:

stark=> begin;
        
BEGIN
        
                                      stark=> begin;
        
                                      BEGIN
        
stark=> update t1 set a = 0;
        
UPDATE 1
        
stark=> update t1 set a = 1;
        
UPDATE 1
        

        
                                      stark=> update t2 set b = 0;
        
                                      UPDATE 1
        
                                      stark=> update t2 set b = 2;
        
                                      UPDATE 1
        
stark=> commit;
        
                                      stark=> commit;
        
                                      ERROR:  deadlock detected
        
                                      DETAIL:  Process 16531 waits for ShareLock on transaction 245131; blocked by
process16566 
                                      Process 16566 waits for ShareLock on transaction 245132; blocked by process
16531.        
                                      CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."t1" x WHERE "a" = $1 FOR
SHAREOF x" 
                                      stark=> >
        
COMMIT
stark=> \d t1
      Table "public.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
 b      | integer |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "fk" FOREIGN KEY (b) REFERENCES t2(b) DEFERRABLE INITIALLY DEFERRED

stark=> \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer | not null
Indexes:
    "t2_pkey" PRIMARY KEY, btree (b)
Foreign-key constraints:
    "fk" FOREIGN KEY (a) REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED