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

Re: App very unresponsive while performing simple update

From: Greg Stark <gsstark(at)mit(dot)edu>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Brendan Duddridge <brendan(at)clickspace(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: App very unresponsive while performing simple update
Date: 2006-05-31 15:24:05
Message-ID: 874pz6jjze.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:

> On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
> > Brendan Duddridge <brendan(at)clickspace(dot)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 process 16566
                                      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 SHARE OF 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


In response to

Responses

pgsql-performance by date

Next:From: Brendan DuddridgeDate: 2006-05-31 17:34:38
Subject: Re: App very unresponsive while performing simple update
Previous:From: Bruno Wolff IIIDate: 2006-05-31 15:11:41
Subject: Re: App very unresponsive while performing simple update

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