I have a maintenance window coming up and using pg_upgrade to upgrade from 9.2.X to 9.3.X.
As part of the window, I’d like to ‘cluster’ each table by its primary key. After doing so, I see amazing performance
improvements(probably mostly because of index bloat - but possibly due to table fragmentation)
That being said, I have a single table that is blowing my window -
at 140 million rows (28 gig in size with 75 gig worth of indexes), this bad boy is my white whale. There are 10 indexes
(notincluding the primary key). Yes - 10 is a lot - but I’ve been monitoring their use (most are single column or
partialindexes) and all are used.
That being said, I’ve been reading and experimenting in trying to get a cluster of this table (which re-indexes all
10/11indexes) to complete in a reasonable amount of time.
There are lots of settings and ranges to chose from and while my experiments continue, I was looking to get some
input. Lowest I have gotten for clustering this table is just under 6 hours.
I am familiar with pg_reorg and it’s sibling pg_repack - but they call the base postgresql reindex functions underneath
-and I have learned by using ‘verbose’ that the actual clustering of the table is quick - it’s the reindexing that is
slow(It’s doing each reindex sequentially instead of concurently)
PostgreSQL 9.3.2 on x86_64-pc-solaris2.11, compiled by gcc (GCC) 4.5.2, 64-bit
500 gig of ram
2.7gig processors (48 cores)
Shared buffers set to 120gig
Maintenance work men set to 1gig
work men set to 500 meg
Things I have read/seen/been told to tweak…
fsync (set to off)
setting wal_level to minimal (to avoid wal logging of cluster activity)
bumping up maintenance work men (but I’ve also seen/read that uber high values cause disk based sorts which ultimately
slowthings down)
Tweaking checkpoint settings (although with wal_level set to minimal - I don’t think it comes into play)
any good suggestions for lighting a fire under this process?
If worse comes to worse, I can vacuum full the table and reindex each index concurrently - but it won’t give me the
benefitof having the tuples ordered by their oft-grouped primary key.