On Thu, Jan 9, 2014 at 2:03 PM, Jeff Amiel <becauseimj...@yahoo.com> wrote:
> 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) > Since you seem to have a test environment where you can so such things, you can try first reindexing, and then clustering, to so which step is the important one to get the performance improvement. If it is the reindexing that is really the key, you could get that benefit outside of the maintenance window by building a new index with the same columns concurrently, then dropping the old one, doing it one index at a time. If the cluster is the key part, that is hard to move outside a maintenance window. > 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 (not including the primary > key). Yes - 10 is a lot - but I’ve been monitoring their use (most are > single column or partial indexes) 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/11 indexes) 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) > I see little point in that. You are putting your data at serious risk, and bulk index creation shouldn't be doing much fsyncing anyway. > setting wal_level to minimal (to avoid wal logging of cluster activity) > That can be a big win in some cases, if it is compatible with your backup policy. But I don't know if cluster is actually one of those cases. > bumping up maintenance work men (but I’ve also seen/read that uber high > values cause disk based sorts which ultimately slow things down) > I don't understand that advice. If it is big enough, you can *avoid* disk bases sorts by doing them in RAM, which should be faster, provided you actually have enough RAM so that you don't swap. However, there are a variety of things which limit how much memory a sort could actually use (most of which will be removed in 9.4) so you might not get in-RAM sorts no matter how much you crank up maintenance_work_mem. You can turn on trace_sort to see whether your sorts are in RAM or on disk, and how much memory they are using. > Tweaking checkpoint settings (although with wal_level set to minimal - I > don’t think it comes into play) > Measure it--how often does it cycle through checkpoints during your test CLUSTER? > > 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 benefit of having the > tuples ordered by their oft-grouped primary key. > If you vacuum full, it will rebuild the indexes for you automatically, so after that there is no point in doing a manual reindex. You could drop them manually before hand, and then build them manually afterward, but you can do that whether the central activity is a CLUSTER or a VACUUM FULL. If you want to do this in parallel, then you need to keep in mind that maintenance_work_mem needs to be small enough to fit all of the concurrent builds, and your IO system needs to accomodate all of that traffic. Cheers, Jeff
