On 08/02/2014 06:20 PM, Phoenix Kiula wrote:
Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday.
One of my large tables (101 GB on disk, about 1.1 billion rows) used
to take too long to vacuum. Not sure if it's an index corruption
issue. But I tried VACUUM FULL ANALYZE as recommended in another
thread yesterday, which took 5 hours on the two times I tried, without
finishing.
Now the REINDEX TABLE has taken over 6 hours as I decided to be
patient and just let something finish. Not sure this is normal though!
How do production level DBAs do this if it takes so long?
So why the REINDEX?
If I open another SSH window to my server and try "select * from
pg_stats_activity" it just hangs there, as the REINDEX I presume is
taking up all the memory? I basically can't do anything else on this
server.
Just in case it helps, a segment of my postgresql.conf is below. Would
appreciate any tips on what I can do.
(I did a pg_dump of just this table, which also took about 2 hours,
then I renamed the original table in the database, and tried to
pg_restore just the table, but it gave me an error message about the
archive being in the wrong format !!! So REINDEX or something like it
seems to be the only idea?)
Sounds to me like you did a plain text dump and then tried to use
pg_restore to restore. One of the quirks of pg_dump/pg_restore is that
if you do a plain text dump you need to feed it to psql not pg_restore.
That being said I am not sure that increasing the size of your database
by another 101 GB on what seems to be an overloaded machine is the answer.
Thanks for any help!
Still not sure what the problem is that you are trying to solve?
There was reference to VACUUM issues, but not a lot of detail. Some more
information on what specifically you where having issues with might lead
to some clarity on where to go from here.
PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf
and TOP output during the running of the REINDEX are below..
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
