bloating index, pg_restore
Hello,
I have a database which is bloated because of vacuum full, so you find indexes bigger than the table itself.
I have dumped this database and restored it without reindixing and it was extremely slow. So, my question what is the relation between bloated database and pg_restore.
Regards
Hello,
On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
I have a database which is bloated because of vacuum full, so you find
indexes bigger than the table itself.
Table can not be bloated because of vacuum full, it removes bloat from
the table and its indexes. The fact that an index is larger then the
table it it built on does not say that something is bloated. Use the
pgstattuple extension to determine bloat
http://www.postgresql.org/docs/9.2/static/pgstattuple.html.
I have dumped this database and restored it without reindixing and it was
extremely slow. So, my question what is the relation between bloated
database and pg_restore.Regards
--
Kind regards,
Sergey Konoplev
Database and Software Consultant
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sergey Konoplev <gray.ru@gmail.com> writes:
On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
I have a database which is bloated because of vacuum full, so you find
indexes bigger than the table itself.
Table can not be bloated because of vacuum full, it removes bloat from
the table and its indexes.
Um, well, that depends a lot on which PG version the OP is running
(which he didn't say). The pre-9.0 implementation of VACUUM FULL
was notorious for creating index bloat, because it shuffled heap
entries around to compact heap space, but created an additional
index entry for each such heap-tuple motion.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks for the reply,
Well my question was not very precise, the postgresql version is 8.3 which is not supported, so I wanted to migrate to a newer version which is 9.1.
I have used pg_dump with -Fc option and I was monitoring the pg_restore activity. Normally, the dump and restore takes from 30-40 minutes; but yesterday when the indexes are bloated - I do not know how this could happen in one or two days, the database size increased from 700 MiB to 13 GiB - the pg_restore on 9.1 takes around 6 hours. Since pg_restore is using insert into (....) . How can bloated indexes affect the restore performance.
I have re-indexed one table and the size dropped to again 700 MiB. So what could be the problem here?
Thanks
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: salah jubeh <s_jubeh@yahoo.com>; pgsql <pgsql-general@postgresql.org>
Sent: Thursday, March 28, 2013 5:08 AM
Subject: Re: [GENERAL] bloating index, pg_restore
Sergey Konoplev <gray.ru@gmail.com> writes:
On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
I have a database which is bloated because of vacuum full, so you find
indexes bigger than the table itself.
Table can not be bloated because of vacuum full, it removes bloat from
the table and its indexes.
Um, well, that depends a lot on which PG version the OP is running
(which he didn't say). The pre-9.0 implementation of VACUUM FULL
was notorious for creating index bloat, because it shuffled heap
entries around to compact heap space, but created an additional
index entry for each such heap-tuple motion.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
salah jubeh <s_jubeh@yahoo.com> wrote:
Well my question was not very precise, the postgresql version is
8.3 which is not supported, so I wanted to migrate to a newer
version which is 9.1.I have used pg_dump with -Fc option and I was monitoring the
pg_restore activity. Normally, the dump and restore takes from
30-40 minutes; but yesterday when the indexes are bloated - I do
not know how this could happen in one or two days, the database
size increased from 700 MiB to 13 GiB - the pg_restore on 9.1
takes around 6 hours. Since pg_restore is using insert into
(....). How can bloated indexes affect the restore performance.I have re-indexed one table and the size dropped to again 700
MiB. So what could be the problem here?
You are still leaving way to much to the imagination here.
What version of pg_dump are you using for the dump?
Why are there enough dumps to have a "normallY' timing?
What is this "one or two day" gap you're talking about? What
happened during that time?
Are you doing multiple tests with a new database to restore to each
time, dumping and restoring multiple databases within one cluster,
or what?
Without more detail, we can only guess.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general