DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=IjxGCtNLO63m/A1EAyQPAXHkgJOx9NfTZFs8KSl6tgdsV60BXMzMCKRCV1Fg80Yhw1HXMKKTWj9yvimvfgmm0Ci1dw3JsH9hiNx3qjCtxwhpUrKakFTphok0E98mgPOYKLU10xfDfAzu7nTAwbW3p8DRcqAPEKb8k1Mr7vWoHJY=;
References: <B150080B-9E9A-456F-8B8D-110F07621165@ebureau.com>
 <1379421830.30619.YahooMailNeo@web162902.mail.bf1.yahoo.com>
 <A8C537BF-969B-41B9-8CFC-652F63E95F57@ebureau.com>
Message-ID: <1379450777.26197.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Tue, 17 Sep 2013 13:46:17 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Dumping a database that is not accepting commands?
To: Natalie Wenz <nataliewenz@ebureau.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <A8C537BF-969B-41B9-8CFC-652F63E95F57@ebureau.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Natalie Wenz <nataliewenz@ebureau.com> wrote:

> Sorry; my description of what is going on was a little unclear.
> We didn't upgrade the existing database. We moved it to different
> hardware, and just created a brand new database to accept the
> data that had been backing up in sqlite files while our original
> database was offline. I'm still dealing with the wraparound on
> the original, just on a different machine.

OK, to restate to be sure I understand, the original database is
being vacuumed in a new location, and a new 9.3 database in the
original location has absorbed the queued data?=A0 Once you complete
the vacuum, you will copy the old data back to the new database at
the old location?

> autovacuum_freeze_max_age=A0=A0=A0=A0=A0=A0 | 800000000

> Normally the autovacuum seem to keep up sufficiently. We got into
> trouble with a bad combination of not-typical long-running
> queries, a disk failure and subsequent zpool repair, and the only
> person who checks the log files regularly (me) was out of the
> office. This has been so painful and slow to recover from, I
> don't think we'll ever get into this mess again. (At least not
> quite like this. I seem to have a knack for finding *new* ways to
> break things.)

A perfect storm of events, eh?=A0 It's hard to have things always go
smoothly in the face of such events, but I see a couple things you
might want to consider.=A0 Increasing autovacuum_freeze_max_age
reduces the amount of time you have to get back on track.=A0 You
might want to take that back down to the default.=A0 There was a bug
causing wraparound prevention autovacuums to trigger too
frequently, which is now fixed in the latest minor releases, so
making that chnage might not be as painful as you expect.=A0 Make
sure you are monitoring for long-running transactions, so you don't
get burned by one that is accidental.=A0 Also, if you have a
processing cycle where there are off-peak hours on a daily or
weekly basis, you might want to run a VACUUM ANALYZE command
durning those windows, to get some of the freezing done before it
is critical.

> I will also take this opportunity to mention again that if anyone
> is considering a making a patch for 64-bit xids, you would make
> at least one small group of people very, very happy.=A0 :)

While 64-bit xids isn't likely, there is some work taking a more
creatie approach to the issue which might make you even happier in
a year or so.=A0 :-)

> Side question: some of the longer queries that were running when
> the database yakked were deletes of old data that had been
> manually migrated to a new table with a more appropriate format.
> We were running out of disk space, so we were trying to clear up
> some space by removing data we now had in two places. Does a
> delete of previously-frozen rows unfreeze them, or anything like
> that? Because in a series of maybe a dozen queries or so, we
> deleted billions of rows. Does that generate a significant amount
> of extra work for the autovacuumer?=20

The pages which had tuples deleted would need to be cleaned up by
vacuum, and rewritten.=A0 It would also remove all index entries for
all deleted rows.=A0 It might also scan backward from the end of the
table to release space to the OS.=A0 That could conceivably be enough
avoidable work to make your idea of copying out the remaining data
feasible.=A0 What percentage of the rows were deleted?=A0 Could your
copy out be to a separate set of drives?

If you know at the time a row is added what group it will be in for
deletion, it might pay to move to partitioning, so that a group of
rows could be deleted pretty much as fast as you can drop a table.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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