Message-ID: <526BCEBA.3010102@fuzzy.cz>
Date: Sat, 26 Oct 2013 16:16:26 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Hot Standby performance issue
References: <52658780.1040002@fuzzy.cz>
 <1382390332002-5775332.post@n5.nabble.com> <5265A28E.4060601@fuzzy.cz>
 <1382396377294-5775349.post@n5.nabble.com> <5265B562.3040407@fuzzy.cz>
 <1382400045239-5775361.post@n5.nabble.com> <5266BD42.2030303@fuzzy.cz>
 <1382478627757-5775529.post@n5.nabble.com> <52670702.3030201@fuzzy.cz>
 <1382491400068-5775550.post@n5.nabble.com>
 <1382736171070-5775972.post@n5.nabble.com>
In-Reply-To: <1382736171070-5775972.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 25.10.2013 23:22, ramistuni wrote:
> Today morning I found that the performance issue on standby database
> was fixed by itself. On further investigation I found that one of the
> biggest used in this query had autovacuum  kicked in yesterday on
> primary. The last time it had autovaccum ran was on Sep 30th.
> 
> I am suspecting that this should have been fixed the issue. The table
> has update and delete operations. Only thing I did not understand why
> postgres did not pick this table for autovacuum all these days, in
> spite of this table is one of the busiest table from DML perspective.
> I was monitoring the primary database activity all these days and
> always could see autovacuum was running on another tables.

Hi,

I don't see how this would explain the issues you've reported, i.e.
query running fast on primary and very slow on standby. That suggests
the problem is somehow connected to the replication conflict resolution.
However I don't see a reason why a query should take so much longer
before failing due to a conflict.

To find out why the autovacuum didn't trigger on the largest/busiest
table, you should probably check your logs for autovacuum failures
and/or cancels.

Then we'll need to know the basic features of the table (most
importantly how many rows are there), and autovacuum thresholds. It's
possible that the table is simply way bigger than the other tables, and
thus it takes more time to accumulate enough "dead rows" to trigger
autovacuum. Or maybe most of the cleanup tasks was handled by HOT, i.e.
not requiring a vacuum at all.

I think you need to check these fields in pg_stat_all_tables

  SELECT n_live_tup, n_dead_tup,
         n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
    FROM pg_stat_all_tables
   WHERE relname = '... tablename ...'


regards
Tomas


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