Re: Reindex taking forever, and 99% CPU

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(dot)klaver(at)aklaver(dot)com