Re: Reindex taking forever, and 99% CPU

On 08/03/2014 08:55 PM, Jeff Janes wrote:

> Does RAID 1 mean you only have 2 disks in your RAID? If so, that is
> woefully inadequate to your apparent workload. The amount of RAM
> doesn't inspire confidence, either.

Phoenix, I agree that this is probably the core of the problem you're
having. a 101GB table on a system with so few disk resources and such a
small amount of memory will take an absurdly long amount of time to
process. Vacuuming such a large table will take an extremely long time,
and reindexing it will be an exercise in frustration and possibly days
of waiting.

If you can't upgrade to better equipped hardware, I strongly suggest
implementing partitioning on the table. One of the reasons we apply
partitioning to our larger tables (generally anything over 100M rows) is
due to maintenance. If we ever need to bulk modify, reindex, or do
anything substantial to a table, it's much faster when the table isn't
so immense.

Even considering our hardware vastly outclasses what you have, it still
pays to keep table architecture "lean and mean."

Take a look here:

http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email