Re: [HACKERS] Heh, the disappearing problem! - Mailing list pgsql-hackers

>
> Nope, nope and it was working until this morning with no indicated errors.
>
> This and the text indices are the two really, really odd things I'm seeing
> in 6.3, along with *some* operations taking a lot longer than they used to.
>
> One of the things we do around here from libpq is an operation that looks
> like this:
>
>
> 1)    Select a set of records from a join on a couple of tables.
>
> 2)    Select from a different table (using the results from the first
>     select), looking specifically for certain data which we then use
>     programmatically to perform a set of computations.
>
> Now, the first select is just peachy.  It returns ~1500 or so records.
>
> The iterative loop on the second select used to run through the entire 1500
> records or so (doing a select for each) in ~20-30 seconds.  Now it takes
> roughly 2-3 minutes to do the same job.  I have checked with "explain" that
> the indices are being used for all queries - they are.
>
> I've seen this also with a few other processes that do the same kind of
> thing, and get the same results.
>
> I'm wondering if what we're seeing here is a severe degredation of locking
> performance.  That is, could this be related to the new deadlock detection
> code?  We're doing selects/updates within several tables  in these jobs, but
> all within one transaction block (begin/commit or begin/rollback).

The deadlock code only runs after a minute of waiting for a lock, and
every minute thereafter, so if you are not waiting on a lock, you don't
run that code.

Now the queue behavior has changed.  We used to give readers preference,
but now we do some queue management that is fairer to readers and
writers.

See backend/storage/lmgr/proc.c::ProcSleep for the details:

    /*
     * If the first entries in the waitQueue have a greater priority than
     * we have, we must be a reader, and they must be a writers, and we
     * must be here because the current holder is a writer or a reader but
     * we don't share shared locks if a writer is waiting. We put
     * ourselves after the writers.  This way, we have a FIFO, but keep
     * the readers together to give them decent priority, and no one
     * starves.  Because we group all readers together, a non-empty queue
     * only has a few possible configurations:
     *
     * [readers] [writers] [readers][writers] [writers][readers]
     * [writers][readers][writers]
     *
     * In a full queue, we would have a reader holding a lock, then a writer
     * gets the lock, then a bunch of readers, made up of readers who
     * could not share the first readlock because a writer was waiting,
     * and new readers arriving while the writer had the lock.
     *
     */


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)