Re: Slow queries on 9.3.1 despite use of index - Mailing list pgsql-performance

From Michael van Rooyen
Subject Re: Slow queries on 9.3.1 despite use of index
Date
Msg-id 535EC680.6010102@loot.co.za
Whole thread Raw
In response to Re: Slow queries on 9.3.1 despite use of index  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Slow queries on 9.3.1 despite use of index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
On 2014/04/28 07:50 PM, Tom Lane wrote:
> Michael van Rooyen <michael@loot.co.za> writes:
>> I'm trying to get to the bottom of a performance issue on a server
>> running PostgreSQL 9.3.1 on Centos 5.
> Hm ... it seems pretty suspicious that all of these examples take just
> about exactly 1 second longer than you might expect.  I'm wondering
> if there is something sitting on an exclusive table lock somewhere,
> and releasing it after 1 second.
I do have log_min_duration_statement = 1000, which may cause this.
> In particular, this looks quite a bit like the old behavior of autovacuum
> when it was trying to truncate empty pages off the end of a relation ---
> it would hold off other accesses to the table until deadlock_timeout
> elapsed, whereupon it'd get kicked off the exclusive lock (and have to
> retry the truncation next time).  Are you *sure* this server is running
> 9.3.1, and not something pre-9.3?
Definitely 9.3.1.  The strange thing is I have other servers with
similar configurations and load and with the same database, where
performance is great, so it's hard for me to know what's different
here.  Maybe I'm expecting too much from these SATA drives, or it's time
to add lots of RAM...


pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Checkpoints and slow queries
Next
From: Tom Lane
Date:
Subject: Re: Slow queries on 9.3.1 despite use of index