On-disk size of db increased after restore

2010/8/31 Devrim GÜNDÜZ <devrim@gunduz.org>:

I tried to restore one of our db backups to 3 different machines today.

After restore, all machines reported larger on-disk size, and also
psql's \l+ confirmed that.

Here is the live machine:
On-disk size: 84 GB
Size reported by psql: 79 GB

Backup machine 1:

On-disk size: 162 GB
Size reported by psql: 177 GB

Backup machine 2:
On-disk size: 179 GB
Size reported by psql: 177 GB

They're about 2x as big. Any chance you've restored to different dbs
and have two copies? Or double the data in one db?

On Tue, 2010-08-31 at 18:08 -0600, Scott Marlowe wrote:

ny chance you've restored to different dbs
and have two copies? Or double the data in one db?

Nope. This is a single database, and I restored only once.. # of rows in
tables match to the ones in prod...
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

2010/9/1 Devrim GÜNDÜZ <devrim@gunduz.org>:

On Tue, 2010-08-31 at 18:08 -0600, Scott Marlowe wrote:

ny chance you've restored to different dbs
and have two copies?  Or double the data in one db?

Nope. This is a single database, and I restored only once.. # of rows in
tables match to the ones in prod...

Have you run this on each server?

SELECT datname, pg_database_size(datname)
FROM pg_catalog.pg_database
ORDER BY 2 DESC

And if a single database size differs, run this against the database:

SELECT tablename, pg_table_size(schemaname || '.' || tablename)
FROM pg_catalog.pg_tables
ORDER BY 2 DESC

Should at least narrow down where the space is being used.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 31/08/10 22:17, Devrim GÜNDÜZ wrote:

I have seen the opposite of this tons of times before, but I haven't
seen an increase after restore before. Does anyone know what may cause
this? Where should I look at?

Could you have changed the fillfactor on some big tables/indexes in the
live database after populating them?

Is the locale the same on each machine/db?

--
Richard Huxton
Archonet Ltd

Hi,

On Wed, 2010-09-01 at 21:13 +0100, Richard Huxton wrote:

Could you have changed the fillfactor on some big tables/indexes in
the live database after populating them?

Nope. Even a pg_dump -h prod|psql backup_node resulted with the same
issue

Is the locale the same on each machine/db?

These are generic RPM installations, and locales are the same...

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

On 01/09/10 21:32, Devrim GÜNDÜZ wrote:

On Wed, 2010-09-01 at 21:13 +0100, Richard Huxton wrote:

Could you have changed the fillfactor on some big tables/indexes in
the live database after populating them?

Nope. Even a pg_dump -h prod|psql backup_node resulted with the same
issue

Is the locale the same on each machine/db?

These are generic RPM installations, and locales are the same...

OK - so not fillfactor and not some unicode-related padding. I can't see
how a 32 vs 64-bit architecture change could produce anything like a
doubling of database size.

Is it that each file is doubled in size, or are some much larger while
others are about the same? If the indexes are to blame it's presumably
something to do with the order of row access during index creation.

--
Richard Huxton
Archonet Ltd

Excerpts from Richard Huxton's message of mié sep 01 16:39:55 -0400 2010:

OK - so not fillfactor and not some unicode-related padding. I can't see
how a 32 vs 64-bit architecture change could produce anything like a
doubling of database size.

Depending on table schemas, why not? e.g. consider a table with a
single bool column. It will waste 7 bytes on 8-byte MAXALIGN machine
but only 3 on a 4-byte MAXALIGN machine. Of course, this is a corner
case. Devrim didn't specify the platform on each server AFAICS.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Excerpts from Richard Huxton's message of mié sep 01 16:39:55 -0400 2010:

OK - so not fillfactor and not some unicode-related padding. I can't see
how a 32 vs 64-bit architecture change could produce anything like a
doubling of database size.

Depending on table schemas, why not? e.g. consider a table with a
single bool column. It will waste 7 bytes on 8-byte MAXALIGN machine
but only 3 on a 4-byte MAXALIGN machine.

Yeah, but after you account for row header overhead, the worst-case
percentage bloat still should be a lot less than 2X.

It would help if Devrim could break down the bloat to the level of
individual tables/indexes.

regards, tom lane

On Wed, 2010-09-01 at 16:50 -0400, Alvaro Herrera wrote:

Devrim didn't specify the platform on each server AFAICS.

Both are Red Hat /CentOS 5.5, x86_64, running with identical software
versions...

I first inclined to blame LVM+storage, however I could duplicate this
issue on local disks, too. This happened recently -- restoring data on
the same machine about 3 weeks ago did not have this issue. I need to
figure out what may happened since then...

Alvaro, this may be a stupid question but: I enabled custom autovac
settings for some tables. These changes are included in the dump. May
this affect on-disk size?

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:

Alvaro, this may be a stupid question but: I enabled custom autovac
settings for some tables. These changes are included in the dump. May
this affect on-disk size?

Doesn't seem likely that that would matter to the state immediately
after restoring; autovac should only affect things after you've done
some deletes/updates in the tables. But are you sure there aren't
some fillfactor tweaks in there too?

regards, tom lane

On Wed, 2010-09-01 at 17:32 -0400, Tom Lane wrote:

But are you sure there aren't some fillfactor tweaks in there too?

I'm sure. fillfactor related changes are on the radar, but I did not
commit them yet...
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

On Wed, 2010-09-01 at 16:59 -0400, Tom Lane wrote:

It would help if Devrim could break down the bloat to the level of
individual tables/indexes.

While setting up this data (by anonymizing table names, etc), I saw that
almost all relations are smaller on backup server, as compared to prod.
Yeah, there is a little bloat on master, but at the end of the day,
total size is expected to be smaller on backup.

See 5 top disk space eaters (in bytes):

Prod:
idx1|1441636352 bytes
tbl3|3248930816 bytes
tbl4|9065570304 bytes
tbl5|10850549760 bytes

Backup:
idx1|1215463424 bytes
tbl3|3189325824 bytes
tbl4|8910422016 bytes
tbl5|10814955520 bytes

Almost all relations are smaller on backup.

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

Excerpts from Devrim GÜNDÜZ's message of mié sep 01 17:39:55 -0400 2010:

On Wed, 2010-09-01 at 17:32 -0400, Tom Lane wrote:

But are you sure there aren't some fillfactor tweaks in there too?

I'm sure. fillfactor related changes are on the radar, but I did not
commit them yet...

Maybe you're on one of these versions on which, if you tweaked the
autovacuum settings, the fillfactor magically got moved to some other
value.

Can you check how full the pages are? There's a contrib module for
that, I don't recall the name.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Excerpts from Devrim GÜNDÜZ's message of mié sep 01 17:39:55 -0400 2010:

On Wed, 2010-09-01 at 17:32 -0400, Tom Lane wrote:

But are you sure there aren't some fillfactor tweaks in there too?

I'm sure. fillfactor related changes are on the radar, but I did not
commit them yet...

Maybe you're on one of these versions on which, if you tweaked the
autovacuum settings, the fillfactor magically got moved to some other
value.

Oh, bingo, that could be it. IIRC that bug actually caused fillfactor
to effectively become *zero*. Devrim, have you identified yet which
tables have the bloat? Are they the ones with tweaked autovacuum
parameters?

regards, tom lane

Hi,

On Thu, 2010-09-02 at 13:22 -0400, Tom Lane wrote:

Devrim, have you identified yet which tables have the bloat? Are they
the ones with tweaked autovacuum parameters?

That's it.

On prod server, that table consumes 50 GB disk space, and on the backup
machine, it uses 148 GB. I applied custom autovac settings only to that
table.

This is 8.4.4 btw...

So, what should I do now?

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:

On Thu, 2010-09-02 at 13:22 -0400, Tom Lane wrote:

Devrim, have you identified yet which tables have the bloat? Are they
the ones with tweaked autovacuum parameters?

That's it.

On prod server, that table consumes 50 GB disk space, and on the backup
machine, it uses 148 GB. I applied custom autovac settings only to that
table.

This is 8.4.4 btw...

OK, so the bug is fixed, but you still have fillfactor = 0 on the
affected table.

So, what should I do now?

Explicitly reset the table's fillfactor to default (100), then
you'll need to CLUSTER or VACUUM FULL or something.

regards, tom lane

On Fri, 2010-09-03 at 09:41 -0400, Tom Lane wrote:

This is 8.4.4 btw...

OK, so the bug is fixed, but you still have fillfactor = 0 on the
affected table.

I'm confused. I'm still seeing a bug in here: I cannot restore a dump
effectively... Running CLUSTER or VACUUM FULL does not make any sense to
me in here.

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:

On Fri, 2010-09-03 at 09:41 -0400, Tom Lane wrote:

This is 8.4.4 btw...

OK, so the bug is fixed, but you still have fillfactor = 0 on the
affected table.

I'm confused. I'm still seeing a bug in here: I cannot restore a dump
effectively... Running CLUSTER or VACUUM FULL does not make any sense to
me in here.

Oh, wait. What you need is this patch:

2010-06-06 23:01 itagaki

* doc/src/sgml/ref/create_table.sgml,
src/backend/access/common/reloptions.c (REL8_4_STABLE): Ensure
default-only storage parameters for TOAST relations to be
initialized with proper values. Affected parameters are fillfactor,
analyze_threshold, and analyze_scale_factor.

Especially uninitialized fillfactor caused inefficient page usage
because we built a StdRdOptions struct in which fillfactor is zero
if any reloption is set for the toast table.

In addition, we disallow toast.autovacuum_analyze_threshold and
toast.autovacuum_analyze_scale_factor because we didn't actually
support them; they are always ignored.

Report by Rumko on pgsql-bugs on 12 May 2010. Analysis by Tom Lane
and Alvaro Herrera. Patch by me.

Backpatch to 8.4.

which I now realize went in *post* 8.4.4.

We're really overdue for a new set of back-branch releases ...

regards, tom lane

Hi,

On Fri, 2010-09-03 at 11:29 -0400, Tom Lane wrote:

I'm confused. I'm still seeing a bug in here: I cannot restore a
dump effectively... Running CLUSTER or VACUUM FULL does not make any
sense to me in here.

Oh, wait. What you need is this patch:

2010-06-06 23:01 itagaki

<snip>

which I now realize went in *post* 8.4.4.

Perfect. I will need to apply this patch to our prod this Sunday.

We're really overdue for a new set of back-branch releases ...

Agreed. I am working on 9.1 Alpha1 package sets now, and I'd like to see
whether multiple version installation really works or not. As a
packager, I am available for new releases after that.

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz