From: Jim C. Nasby <jim@nasby.net>
To: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with inconsistant query performance.
Date: Wed, 27 Sep 2006 17:28:57 -0500
Message-ID: <20060927222857.GC19827@nasby.net> (raw)
In-Reply-To: <451AF873.1010306@aptalaska.net>
References: <451AC4E2.90106@aptalaska.net>
<20060927203155.GT19827@nasby.net>
<451AEE15.6090103@aptalaska.net>
<20060927214221.GA19827@nasby.net>
<451AF873.1010306@aptalaska.net>
On Wed, Sep 27, 2006 at 02:17:23PM -0800, Matthew Schumacher wrote:
> Jim C. Nasby wrote:
> >
> > It can cause a race if another process could be performing those same
> > inserts or updates at the same time.
>
> There are inserts and updates running all of the time, but never the
> same data. I'm not sure how I can get around this since the queries are
> coming from my radius system which is not able to queue this stuff up
> because it waits for a successful query before returning an OK packet
> back to the client.
>
> >
> > I know the UPDATE case can certainly cause a race. 2 connections try to
> > update, both hit NOT FOUND, both try to insert... only one will get to
> > commit.
>
> Why is that? Doesn't the first update lock the row causing the second
> one to wait, then the second one stomps on the row allowing both to
> commit? I must be confused....
What if there's no row to update?
Process A Process B
UPDATE .. NOT FOUND
UPDATE .. NOT FOUND
INSERT
INSERT blocks
COMMIT
UNIQUE_VIOLATION
That's assuming that there's a unique index. If there isn't one, you'd
get duplicate records.
> > I think that the UNIQUE_VIOLATION case should be safe, since a second
> > inserter should block if there's another insert that's waiting to
> > commit.
>
> Are you saying that inserts inside of an EXCEPTION block, but normal
> inserts don't?
No... if there's a unique index, a second INSERT attempting to create a
duplicate record will block until the first INSERT etiher commits or
rollsback.
> > DELETEs are something else to think about for both cases.
>
> I only do one delete and that is every night when I move the data to the
> primary table and remove that days worth of data from the tmp table.
> This is done at non-peak times with a vacuum, so I think I'm good here.
Except that you might still have someone fire off that function while
the delete's running, or vice-versa. So there could be a race condition
(I haven't thought enough about what race conditions that could cause).
> > If you're certain that only one process will be performing DML on those
> > tables at any given time, then what you have is safe. But if that's the
> > case, I'm thinking you should be able to group things into chunks, which
> > should be more efficient.
>
> Yea, I wish I could, but I really need to do one at a time because of
> how radius waits for a successful query before telling the access server
> all is well. If the query fails, the access server won't get the 'OK'
> packet and will send the data to the secondary radius system where it
> gets queued.
In that case, the key is to do the absolute smallest amount of work
possible as part of that transaction. Ideally, you would only insert a
record into a queue table somewhere, and then periodically process
records out of that table in batches.
> Do you know of a way to see what is going on with the locking system
> other than "select * from pg_locks"? I can't ever seem to catch the
> system when queries start to lag.
No. Your best bet is to open two psql sessions and step through things
in different combinations (make sure and do this in transactions).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, matt.s@aptalaska.net
Subject: Re: Problems with inconsistant query performance.
In-Reply-To: <20060927222857.GC19827@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox