Re: Chronic performance issue with Replication Failover and FSM.

All,

I've discovered a built-in performance issue with replication failover
at one site, which I couldn't find searching the archives. I don't
really see what we can do to fix it, so I'm posting it here in case
others might have clever ideas.

1. The Free Space Map is not replicated between servers.

2. Thus, when we fail over to a replica, it starts with a blank FSM.

3. I believe replica also starts with zero counters for autovacuum.

4. On a high-UPDATE workload, this means that the replica assumes tables
have no free space until it starts to build a new FSM or autovacuum
kicks in on some of the tables, much later on.

5. If your hosting is such that you fail over a lot (such as on AWS),
then this causes cumulative table bloat which can only be cured by a
VACUUM FULL.

I can't see any way around this which wouldn't also bog down
replication. Clever ideas, anyone?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Tue, Mar 13, 2012 at 4:53 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> All,
>
> I've discovered a built-in performance issue with replication failover
> at one site, which I couldn't find searching the archives.  I don't
> really see what we can do to fix it, so I'm posting it here in case
> others might have clever ideas.
>
> 1. The Free Space Map is not replicated between servers.
>
> 2. Thus, when we fail over to a replica, it starts with a blank FSM.
>
> 3. I believe replica also starts with zero counters for autovacuum.
>
> 4. On a high-UPDATE workload, this means that the replica assumes tables
> have no free space until it starts to build a new FSM or autovacuum
> kicks in on some of the tables, much later on.
>
> 5. If your hosting is such that you fail over a lot (such as on AWS),
> then this causes cumulative table bloat which can only be cured by a
> VACUUM FULL.
>
> I can't see any way around this which wouldn't also bog down
> replication.  Clever ideas, anyone?

Would it bog it down by "much"?

(1 byte per 8kb) * 2TB = 250MB. Even if you doubled or tripled it for
pointer-overhead reasons it's pretty menial, whereas VACUUM traffic is
already pretty intense. Still, it's clearly...work.

--
fdr

On Wed, Mar 14, 2012 at 8:53 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> All,
>
> I've discovered a built-in performance issue with replication failover
> at one site, which I couldn't find searching the archives.  I don't
> really see what we can do to fix it, so I'm posting it here in case
> others might have clever ideas.
>
> 1. The Free Space Map is not replicated between servers.
>
> 2. Thus, when we fail over to a replica, it starts with a blank FSM.
>
> 3. I believe replica also starts with zero counters for autovacuum.
>
> 4. On a high-UPDATE workload, this means that the replica assumes tables
> have no free space until it starts to build a new FSM or autovacuum
> kicks in on some of the tables, much later on.

If it's really a high-UPDATE workload, wouldn't autovacuum start soon?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Tue, Mar 13, 2012 at 7:05 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:>
> If it's really a high-UPDATE workload, wouldn't autovacuum start soon?

Also, while vacuum cleanup records are applied, could not the standby
also update its free space map, without having to send the actual FSM
updates? I guess that's bogging down of another variety.

--
fdr

On 14.03.2012 01:53, Josh Berkus wrote:
> 1. The Free Space Map is not replicated between servers.
>
> 2. Thus, when we fail over to a replica, it starts with a blank FSM.

The FSM is included in the base backup, and it is updated when VACUUM
records are replayed.

It is also updated when insert/update/delete records are replayed,
athough there's some fuzziness there: records with full page images
don't update the FSM, and the FSM is only updated when the page has less
than 20% of free space left. But that would cause an error in the other
direction, with the FSM claiming that some pages have more free space
than they do in reality.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki,

> The FSM is included in the base backup, and it is updated when VACUUM
> records are replayed.

Oh? Hmmmm. In that case, the issue I'm seeing in production is
something else. Unless that was a change for 9.1?

> It is also updated when insert/update/delete records are replayed,
> athough there's some fuzziness there: records with full page images
> don't update the FSM, and the FSM is only updated when the page has less
> than 20% of free space left. But that would cause an error in the other
> direction, with the FSM claiming that some pages have more free space
> than they do in reality.

Thanks.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 20.03.2012 23:41, Josh Berkus wrote:
> Heikki,
>
>> The FSM is included in the base backup, and it is updated when VACUUM
>> records are replayed.
>
> Oh? Hmmmm. In that case, the issue I'm seeing in production is
> something else. Unless that was a change for 9.1?

No, it's been like that since 8.4, when the FSM was rewritten.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Mar 13, 2012 at 4:53 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> 4. On a high-UPDATE workload, this means that the replica assumes tables
> have no free space until it starts to build a new FSM or autovacuum
> kicks in on some of the tables, much later on.
>
> 5. If your hosting is such that you fail over a lot (such as on AWS),
> then this causes cumulative table bloat which can only be cured by a
> VACUUM FULL.

I'd like to revive this thread. Like other people, I thought this was
not a huge problem -- or least maybe not directly from the mechanism
proposed -- but sometimes it's a pretty enormous one, and I've started
to notice it. I did a bug report here
(http://archives.postgresql.org/pgsql-bugs/2012-08/msg00108.php, plots
in http://archives.postgresql.org/pgsql-performance/2012-08/msg00181.php),
but just today we promoted another system via streaming replication to
pick up the planner fix in 9.1.5 (did you know: that planner bug seems
to make GIN FTS indexes un-used in non-exotic cases, and one goes to
seqscan?), and then a 40MB GIN index bloated to two gigs on a 1.5GB
table over the course of maybe six hours.

In addition, the thread on pgsql-performance that has the plot I
linked to indicates someone having the same problem with 8.3 after a
warm-standby promotion.

So I think there are some devils at work here, and I am not even sure
if they are hard to reproduce -- yet, people use standby promotion
("unfollow") on Heroku all the time and we have not been plagued
mightily by support issues involving such incredible bloating, so
there's something about the access pattern. In my two cases, there is
a significant number of UPDATEs vs actual number of INSERTs/DELETES of
records (the ratio is probably 10000+ to 1), even though neither of
these would be close to what one could consider a large or even
medium-sized database in terms of TPS or database size. In fact, the
latter system bloated even though it comfortably fits entirely in
memory.

--
fdr

Daniel Farina <daniel(at)heroku(dot)com> writes:
> but just today we promoted another system via streaming replication to
> pick up the planner fix in 9.1.5 (did you know: that planner bug seems
> to make GIN FTS indexes un-used in non-exotic cases, and one goes to
> seqscan?), and then a 40MB GIN index bloated to two gigs on a 1.5GB
> table over the course of maybe six hours.

I think this is probably unrelated to what Josh was griping about:
even granting that the system forgot any free space that had been
available within the original 40MB, that couldn't in itself lead
to eating more than another 40MB, no? My guess is something is
broken about the oldest-xmin-horizon mechanism, such that VACUUM
is failing to recover space. Can you put together a self-contained
test case that exhibits similar growth?

regards, tom lane