DomainKey-Signature: a=rsa-sha1; s=mail; d=loot.co.za; c=nofws; q=dns;
 b=UALKfPIqIsO07FiCX2KyVAMwKhWwaHKUq1kM6cC4evLbEjld2BvSbpkcUW1NcXr8X
 Phkb+YJy4CAXxu9iZxHYLVEO8lrFQBKdwjsZ8CV9pMk0WxtpnZrtLq5j4+F3Qnm
Message-ID: <535EC680.6010102@loot.co.za>
Date: Mon, 28 Apr 2014 23:22:08 +0200
From: Michael van Rooyen <michael@loot.co.za>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow queries on 9.3.1 despite use of index
References: <535E8BFF.1010503@loot.co.za> <23166.1398707420@sss.pgh.pa.us>
In-Reply-To: <23166.1398707420@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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...


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance