SEO

Gang,

Hoping you all can help me with a rather bizarre issue that I've run
across. I don't really need a solution, I think I have one, but I'd
really like to run it by everyone in case I'm headed in the wrong
direction.

I'm running a small Slony (v1.1.5)/postgresql 8.0.4 cluster (on
RedHat) that contains one master database, and two slaves. The db1
(the master) has been up for about 1.5 years, db2 (slave 1) for about
9 months, and db3 (second slave) for about two months. I do a VACUUM
ANALYZE every morning on all three databases. However, the vacuum on
db1 takes approxiamately 4.5 hours, and on the slaves it takes about
1/2 hour. As far as I can tell, my FSM settings are correct. This
is concerning because the vacuum on db1 is starting to run into
production hours. The master receives all inserts, updates and
deletes (as well as a fair number of selects). The slaves are select-
only.

In my investigation of this anomaly, I noticed that the data/ dir on
db1 (the master) is around 60 Gigs. The data directory on the slaves
is around 25Gb. After about 3 months of head scratching, someone on
the irc channel suggested that it may be due to index bloat.
Although, doing some research, it would seem that those problems were
resolved in 7.4(ish), and it wouldn't account for one database being
2.5x bigger. Another unknown is Slony overhead (both in size and
vacuum times).

The ONLY thing I can think of is that I DROPped a large number of
tables from db1 a few months ago (they weren't getting replicated).
This is on the order of 1700+ fairly largeish (50,000+ row) tables.
I do not remember doing a vacuum full after dropping them, so perhaps
that's my problem. I'm planning on doing some maintenance this
weekend, during which I will take the whole system down, then on db1,
run a VACUUM FULL ANALYZE on the whole database, then a REINDEX on my
very large tables. I may drop and recreate the indexes on my big
tables, as I hear that may be faster than a REINDEX. I will probably
run a VACUUM FULL ANALYZE on the slaves as well.

Thoughts? Suggestions? Anyone think this will actually help my
problem of size and vacuum times? Do I need to take Slony down while
I do this? Will the VACUUM FULL table locking interfere with Slony?

Thanks for any light you all can shed on these issues...

/kurt


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kurt Overberg <kurt@hotdogrecords.com> writes:
> In my investigation of this anomaly, I noticed that the data/ dir on
> db1 (the master) is around 60 Gigs. The data directory on the slaves
> is around 25Gb. After about 3 months of head scratching, someone on
> the irc channel suggested that it may be due to index bloat.

This is not something you need to guess about. Compare the table and
index sizes, one by one, between the master and slaves. Do a VACUUM
VERBOSE on the one(s) that are radically bigger on the master, and look
at what it has to say.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Kurt Overberg wrote:
>
> In my investigation of this anomaly, I noticed that the data/ dir on db1
> (the master) is around 60 Gigs. The data directory on the slaves is
> around 25Gb. After about 3 months of head scratching, someone on the
> irc channel suggested that it may be due to index bloat. Although,
> doing some research, it would seem that those problems were resolved in
> 7.4(ish), and it wouldn't account for one database being 2.5x bigger.
> Another unknown is Slony overhead (both in size and vacuum times).

Check the oid2name/dbsize utilities in the contrib RPM for 8.0.x
http://www.postgresql.org/docs/8.0/s...diskusage.html
Shouldn't be too hard to find out where the disk space is going.

Oh and 8.0.13 is the latest release of 8.0 series, so you'll want to use
your maintenance window to upgrade too. Lots of good bugfixes there.

> The ONLY thing I can think of is that I DROPped a large number of tables
> from db1 a few months ago (they weren't getting replicated). This is on
> the order of 1700+ fairly largeish (50,000+ row) tables. I do not
> remember doing a vacuum full after dropping them, so perhaps that's my
> problem. I'm planning on doing some maintenance this weekend, during
> which I will take the whole system down, then on db1, run a VACUUM FULL
> ANALYZE on the whole database, then a REINDEX on my very large tables.
> I may drop and recreate the indexes on my big tables, as I hear that may
> be faster than a REINDEX. I will probably run a VACUUM FULL ANALYZE on
> the slaves as well.

You'll probably find CLUSTER to be quicker than VACUUM FULL, although
you need enough disk-space free for temporary copies of the
table/indexes concerned.

Dropping and recreating indexes should prove much faster than VACUUMING
with them. Shouldn't matter for CLUSTER afaict.

> Thoughts? Suggestions? Anyone think this will actually help my problem
> of size and vacuum times? Do I need to take Slony down while I do
> this? Will the VACUUM FULL table locking interfere with Slony?

Well, I'd take the opportunity to uninstall/reinstall slony just to
check my scripts/procedures are working.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

kurt@hotdogrecords.com (Kurt Overberg) writes:
> In my investigation of this anomaly, I noticed that the data/ dir on
> db1 (the master) is around 60 Gigs. The data directory on the slaves
> is around 25Gb. After about 3 months of head scratching, someone on
> the irc channel suggested that it may be due to index bloat.
> Although, doing some research, it would seem that those problems were
> resolved in 7.4(ish), and it wouldn't account for one database being
> 2.5x bigger. Another unknown is Slony overhead (both in size and
> vacuum times).

There are three tables in Slony-I that would be of interest; on the
master, do a VACUUM VERBOSE on:

- [clustername].sl_log_1
- [clustername].sl_log_2
- [clustername].sl_seqlog

If one or another is really bloated, that could be the cause of *some*
problems. Though that shouldn't account for 35GB of space :-).

> The ONLY thing I can think of is that I DROPped a large number of
> tables from db1 a few months ago (they weren't getting replicated).
> This is on the order of 1700+ fairly largeish (50,000+ row) tables.
> I do not remember doing a vacuum full after dropping them, so perhaps
> that's my problem. I'm planning on doing some maintenance this
> weekend, during which I will take the whole system down, then on db1,
> run a VACUUM FULL ANALYZE on the whole database, then a REINDEX on my
> very large tables. I may drop and recreate the indexes on my big
> tables, as I hear that may be faster than a REINDEX. I will probably
> run a VACUUM FULL ANALYZE on the slaves as well.

When tables are dropped, so are the data files. So even if they were
bloated, they should have simply disappeared. So I don't think that's
the problem.

> Thoughts? Suggestions? Anyone think this will actually help my
> problem of size and vacuum times? Do I need to take Slony down while
> I do this? Will the VACUUM FULL table locking interfere with Slony?

I'd be inclined to head to the filesystem level, and try to see what
tables are bloated *there*.

You should be able to search for bloated tables via the command:

$ find $PGDATA/base -name "[0-9]+\.[0-9]+"

That would be likely to give you a listing of filenames that look
something like:

12341.1
12341.2
12341.3
12341.4
12341.5
12341.6
231441.1
231441.2
231441.3

which indicates all table (or index) data files that had to be
extended past 1GB.

In the above, the relation with OID 12341 would be >6GB in size,
because it has been extended to have 6 additional files (in addition
to the "bare" filename, 12341).

You can then go into a psql session, and run the query:
select * from pg_class where oid = 12341;
and thereby figure out what table is involved.

I'll bet that if you do this on the "origin" node, you'll find that
there is some small number of tables that have *way* more 1GB
partitions than there are on the subscriber nodes.

Those are the tables that will need attention.

You could probably accomplish the reorganization more quickly via the
"CLUSTER" statement; that will reorganize the table according based on
the ordering of one specified index, and then regenerate all the other
indices. It's not MVCC-safe, so if you have reports running
concurrently, this could confuse them, but if you take the apps down,
as you surely should, it won't be a problem.

You don't forcibly have to take Slony-I down during this, but the
locks taken out on tables by CLUSTER/VACUUM FULL will block slons from
doing any work until those transactions complete.

I wouldn't think you need to do VACUUM FULL or CLUSTER against the
subscribers if they haven't actually bloated (and based on what you
have said, there is no indication that they have).
--
output = reverse("ofni.secnanifxunil" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/li...ributions.html
The quickest way to a man's heart is through his chest, with an axe.

Chris Browne <cbbrowne@acm.org> writes:
> kurt@hotdogrecords.com (Kurt Overberg) writes:
>> In my investigation of this anomaly, I noticed that the data/ dir on
>> db1 (the master) is around 60 Gigs. The data directory on the slaves
>> is around 25Gb. After about 3 months of head scratching, someone on
>> the irc channel suggested that it may be due to index bloat.

> I'd be inclined to head to the filesystem level, and try to see what
> tables are bloated *there*.

At least as a first cut, it should be sufficient to look at
pg_class.relpages, which'd be far easier to correlate with table names
;-). The relpages entry should be accurate as of the most recent VACUUM
on each table, which ought to be close enough unless I missed something
about the problem situation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Richard,

Thanks for the feedback! I found oid2name and have been mucking
about with it, but haven't really found anything that stands out
yet. Most of the tables/indexes I'm comparing across machines seem
to take up a similar amount of disk space. I think I'm going to have
to get fancy and write some shell scripts. Regarding the slony
configuration scripts, you're assuming that I have such scripts. Our
slony install was originally installed by a contractor, and modified
since then so "getting my act together with respect to slony" is
kinda beyond the scope of what I'm trying to accomplish with this
maintenance. I really just want to figure out whats going on with
db1, and want to do so in a way that won't ruin slony since right now
it runs pretty well, and I doubt I'd be able to fix it if it
seriously broke.

Upon a cursory pass with oid2name, it seems that my sl_log_1_idx1
index is out of hand:


-bash-3.00$ oid2name -d mydb -f 955960160
From database "mydb":
Filenode Table Name
--------------------------
955960160 sl_log_1_idx1

-bash-3.00$ ls -al 955960160*
-rw------- 1 postgres postgres 1073741824 Jun 19 11:08 955960160
-rw------- 1 postgres postgres 1073741824 Jun 13 2006 955960160.1
-rw------- 1 postgres postgres 909844480 Jun 19 10:47 955960160.10
-rw------- 1 postgres postgres 1073741824 Jul 31 2006 955960160.2
-rw------- 1 postgres postgres 1073741824 Sep 12 2006 955960160.3
-rw------- 1 postgres postgres 1073741824 Oct 19 2006 955960160.4
-rw------- 1 postgres postgres 1073741824 Nov 27 2006 955960160.5
-rw------- 1 postgres postgres 1073741824 Feb 3 12:57 955960160.6
-rw------- 1 postgres postgres 1073741824 Mar 2 11:57 955960160.7
-rw------- 1 postgres postgres 1073741824 Mar 29 09:46 955960160.8
-rw------- 1 postgres postgres 1073741824 Mar 29 09:46 955960160.9


I know that slony runs its vacuuming in the background, but it
doesn't seem to be cleaning this stuff up. Interestingly, from my
VACUUM pgfouine output,
that index doesn't take that long at all to vacuum analyze (compared
to my other, much larger tables). Am I making the OID->filename
translation properly?

Running this:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
....gives me...

sl_log_1_idx1 |
1421785
xrefmembergroup |
1023460
answerselectinstance |
565343

....does this jibe with what I'm seeing above? I guess I'll run a
full vacuum on the slony tables too? I figured something would else
would jump out bigger than this. FWIW, the same table on db2 and db3
is very small, like zero. I guess this is looking like it is
overhead from slony? Should I take this problem over to the slony
group?

Thanks again, gang-

/kurt



On Jun 19, 2007, at 10:13 AM, Richard Huxton wrote:

> Kurt Overberg wrote:
>> In my investigation of this anomaly, I noticed that the data/ dir
>> on db1 (the master) is around 60 Gigs. The data directory on the
>> slaves is around 25Gb. After about 3 months of head scratching,
>> someone on the irc channel suggested that it may be due to index
>> bloat. Although, doing some research, it would seem that those
>> problems were resolved in 7.4(ish), and it wouldn't account for
>> one database being 2.5x bigger. Another unknown is Slony overhead
>> (both in size and vacuum times).
>
> Check the oid2name/dbsize utilities in the contrib RPM for 8.0.x
> http://www.postgresql.org/docs/8.0/s...diskusage.html
> Shouldn't be too hard to find out where the disk space is going.
>
> Oh and 8.0.13 is the latest release of 8.0 series, so you'll want
> to use your maintenance window to upgrade too. Lots of good
> bugfixes there.
>
>> The ONLY thing I can think of is that I DROPped a large number of
>> tables from db1 a few months ago (they weren't getting
>> replicated). This is on the order of 1700+ fairly largeish (50,000
>> + row) tables. I do not remember doing a vacuum full after
>> dropping them, so perhaps that's my problem. I'm planning on
>> doing some maintenance this weekend, during which I will take the
>> whole system down, then on db1, run a VACUUM FULL ANALYZE on the
>> whole database, then a REINDEX on my very large tables. I may
>> drop and recreate the indexes on my big tables, as I hear that may
>> be faster than a REINDEX. I will probably run a VACUUM FULL
>> ANALYZE on the slaves as well.
>
> You'll probably find CLUSTER to be quicker than VACUUM FULL,
> although you need enough disk-space free for temporary copies of
> the table/indexes concerned.
>
> Dropping and recreating indexes should prove much faster than
> VACUUMING with them. Shouldn't matter for CLUSTER afaict.
>
>> Thoughts? Suggestions? Anyone think this will actually help my
>> problem of size and vacuum times? Do I need to take Slony down
>> while I do this? Will the VACUUM FULL table locking interfere
>> with Slony?
>
> Well, I'd take the opportunity to uninstall/reinstall slony just to
> check my scripts/procedures are working.
>
> --
> Richard Huxton
> Archonet Ltd


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kurt Overberg wrote:
> Richard,
>
> Thanks for the feedback! I found oid2name and have been mucking about
> with it, but haven't really found anything that stands out yet. Most of
> the tables/indexes I'm comparing across machines seem to take up a
> similar amount of disk space. I think I'm going to have to get fancy
> and write some shell scripts. Regarding the slony configuration
> scripts, you're assuming that I have such scripts. Our slony install
> was originally installed by a contractor, and modified since then so
> "getting my act together with respect to slony" is kinda beyond the
> scope of what I'm trying to accomplish with this maintenance. I really
> just want to figure out whats going on with db1, and want to do so in a
> way that won't ruin slony since right now it runs pretty well, and I
> doubt I'd be able to fix it if it seriously broke.
>
> Upon a cursory pass with oid2name, it seems that my sl_log_1_idx1 index
> is out of hand:

If the sl_log_1 table is large too, it'll be worth reading throught the
FAQ to see if any of its notes apply.

http://cbbrowne.com/info/faq.html

> -bash-3.00$ oid2name -d mydb -f 955960160
> From database "mydb":
> Filenode Table Name
> --------------------------
> 955960160 sl_log_1_idx1
>
> -bash-3.00$ ls -al 955960160*
> -rw------- 1 postgres postgres 1073741824 Jun 19 11:08 955960160
> -rw------- 1 postgres postgres 1073741824 Jun 13 2006 955960160.1
> -rw------- 1 postgres postgres 909844480 Jun 19 10:47 955960160.10
> -rw------- 1 postgres postgres 1073741824 Jul 31 2006 955960160.2
> -rw------- 1 postgres postgres 1073741824 Sep 12 2006 955960160.3
> -rw------- 1 postgres postgres 1073741824 Oct 19 2006 955960160.4
> -rw------- 1 postgres postgres 1073741824 Nov 27 2006 955960160.5
> -rw------- 1 postgres postgres 1073741824 Feb 3 12:57 955960160.6
> -rw------- 1 postgres postgres 1073741824 Mar 2 11:57 955960160.7
> -rw------- 1 postgres postgres 1073741824 Mar 29 09:46 955960160.8
> -rw------- 1 postgres postgres 1073741824 Mar 29 09:46 955960160.9
>
>
> I know that slony runs its vacuuming in the background, but it doesn't
> seem to be cleaning this stuff up. Interestingly, from my VACUUM
> pgfouine output,
> that index doesn't take that long at all to vacuum analyze (compared to
> my other, much larger tables). Am I making the OID->filename
> translation properly?

Looks OK to me

> Running this:
> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
> ...gives me...
>
> sl_log_1_idx1 | 1421785
> xrefmembergroup | 1023460
> answerselectinstance | 565343
>
> ...does this jibe with what I'm seeing above? I guess I'll run a full
> vacuum on the slony tables too? I figured something would else would
> jump out bigger than this. FWIW, the same table on db2 and db3 is very
> small, like zero. I guess this is looking like it is overhead from
> slony? Should I take this problem over to the slony group?

Well, pages are 8KB each (by default), so that'd be about 10.8GB, which
seems to match your filesizes above.

Read through the FAQ I linked to - for some reason Slony's not clearing
out transactions it's replicated to your slaves (they *are* in sync,
aren't they?). Could be a transaction preventing vacuuming, or perhaps a
partially dropped node?

Check the size of the sl_log_1 table and see if that tallies.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Chris,

I took your advice, and I had found that sl_log_1 seems to be causing
some of the problem. Here's the result of a VACUUM VERBOSE

mydb # vacuum verbose _my_cluster.sl_log_1 ;
INFO: vacuuming "_my_cluster.sl_log_1"
INFO: index "sl_log_1_idx1" now contains 309404 row versions in
1421785 pages
DETAIL: 455001 index row versions were removed.
1419592 index pages have been deleted, 1416435 are currently reusable.
CPU 16.83s/5.07u sec elapsed 339.19 sec.
^@^@^@INFO: index "sl_log_1_idx2" now contains 312864 row versions
in 507196 pages
DETAIL: 455001 index row versions were removed.
506295 index pages have been deleted, 504998 are currently reusable.
CPU 6.44s/2.27u sec elapsed 138.70 sec.
INFO: "sl_log_1": removed 455001 row versions in 7567 pages
DETAIL: CPU 0.56s/0.40u sec elapsed 6.63 sec.
INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
versions in 13764 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 51972 unused item pointers.
0 pages are entirely empty.
CPU 24.13s/7.85u sec elapsed 486.49 sec.
INFO: vacuuming "pg_toast.pg_toast_955960155"
INFO: index "pg_toast_955960155_index" now contains 9 row versions
in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_955960155": found 0 removable, 9 nonremovable row
versions in 3 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 3 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

....I then checked the disk and those pages are still there. If I do a:

select count(*) from _my_cluster.sl_log_1;
count
-------
6366
(1 row)

Would a VACUUM FULL take care of this? It seems to me that its not
clearing up the indexes properly. You are correct in that
I do see things getting much bigger on the master than on the
subscriber nodes. Could this cause my slony replication to bog down?

Also- I have a question about this comment:

>
> You don't forcibly have to take Slony-I down during this, but the
> locks taken out on tables by CLUSTER/VACUUM FULL will block slons from
> doing any work until those transactions complete.

Thats because no writing will be done to the tables, thus, no slony
triggers will get triggered, correct? I'd rather not
shut down slony if I dont have to, but will if it "is safer/better/
more badass".

For those playing along at home,

> $ find $PGDATA/base -name "[0-9]+\.[0-9]+"
>

....I had to use:

find $PGDATA/base -name "[0-9]*\.[0-9]*"

....but the pluses should have worked too. Still a much better way
than how I was doing it. Thanks again for helping me with this, its
greatly appreciated!

/kurt

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Kurt Overberg <kurt@hotdogrecords.com> writes:
> mydb # vacuum verbose _my_cluster.sl_log_1 ;
> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
> versions in 13764 pages
> DETAIL: 0 dead row versions cannot be removed yet.

Hmm. So you don't have a long-running-transactions problem (else that
DETAIL number would have been large). What you do have is a failure
to vacuum sl_log_1 on a regular basis (because there are so many
dead/removable rows). I suspect also some sort of Slony problem,
because AFAIK a properly operating Slony system shouldn't have that
many live rows in sl_log_1 either --- don't they all represent
as-yet-unpropagated events? I'm no Slony expert though. You probably
should ask about that on the Slony lists.

> ...I then checked the disk and those pages are still there.

Yes, regular VACUUM doesn't try very hard to shorten the disk file.

> Would a VACUUM FULL take care of this?

It would, but it will take an unpleasantly long time with so many live
rows to reshuffle. I'd advise first working to see if you can get the
table down to a few live rows. Then a VACUUM FULL will be a snap.
Also, you might want to do REINDEX after VACUUM FULL to compress the
indexes --- VACUUM FULL isn't good at that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

That's the thing thats kinda blowing my mind here, when I look at
that table:

db1=# select count(*) from _my_cluster.sl_log_1 ;
count
-------
6788
(1 row)

As far as my DB is concerned, there's only ~7000 rows (on average)
when I look
in there (it does fluctuate, I've seen it go as high as around 12k,
but then its
gone back down, so I know events are moving around in there).

So from what I can tell- from the disk point of view, there's ~11Gb
of data; from the
vacuum point of view there's 309318 rows. From the psql point of
view, there's only
around 7,000. Am I missing something? Unless there's something
going on under the
hood that I don't know about (more than likely), it seems like my
sl_log_1 table is munged or
somehow otherwise very screwed up. I fear that a re-shuffling or
dropping/recreating
the index will mess it up further. Maybe when I take my production
systems down for
maintenance, can I wait until sl_log_1 clears out, so then I can just
drop that
table altogether (and re-create it of course)?

Thanks!

/kurt




On Jun 19, 2007, at 5:33 PM, Tom Lane wrote:

> Kurt Overberg <kurt@hotdogrecords.com> writes:
>> mydb # vacuum verbose _my_cluster.sl_log_1 ;
>> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
>> versions in 13764 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>
> Hmm. So you don't have a long-running-transactions problem (else that
> DETAIL number would have been large). What you do have is a failure
> to vacuum sl_log_1 on a regular basis (because there are so many
> dead/removable rows). I suspect also some sort of Slony problem,
> because AFAIK a properly operating Slony system shouldn't have that
> many live rows in sl_log_1 either --- don't they all represent
> as-yet-unpropagated events? I'm no Slony expert though. You probably
> should ask about that on the Slony lists.
>
>> ...I then checked the disk and those pages are still there.
>
> Yes, regular VACUUM doesn't try very hard to shorten the disk file.
>
>> Would a VACUUM FULL take care of this?
>
> It would, but it will take an unpleasantly long time with so many live
> rows to reshuffle. I'd advise first working to see if you can get the
> table down to a few live rows. Then a VACUUM FULL will be a snap.
> Also, you might want to do REINDEX after VACUUM FULL to compress the
> indexes --- VACUUM FULL isn't good at that.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate