Обсуждение: On-disk size of db increased after restore
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 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? 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
Вложения
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
Вложения
http://www.tomtop.com/home-garden/werkzeuge/digital-scales.html Digital Scales for any application. Wholesale digital scale pricing available. American http://www.tomtop.com/20g40kg-digital-hanging-luggage-fishing-weight-scale_p11432.html Weight Scales has what you need. -- View this message in context: http://postgresql.1045698.n5.nabble.com/On-disk-size-of-db-increased-after-restore-tp2798698p2803475.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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> For the records, this patch fixed my issue. Just a quick note for the archives/regular users: The client machine that runs pg_dump also needs this patch. 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