On Thu, 2005-12-08 at 10:26 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > The imbalance across partitions would be a major issue because of the
> > difficulty of selecting a well-distributed partitioning key. If you use
> > the LOCKTAG, then operations on the heaviest hit tables would go to the
> > same partitions continually for LockRelation requests. The frequency of
> > access per table usually drops off dramatically in rank order: look at
> > TPC-B (pgbench) and TPC-C; my own experience would be that you seldom
> > have as many even as 16 heavy hit tables. My guess would be that doing
> > all of that would do little more than reduce contention to ~50%, and
> > that this would show quickly diminishing returns for N > 4. Also, the
> > more sharply defined your application profile, the worse this effect
> > will be.
>
> That's a fair point, and reinforces my instinct that having a large
> number of partitions would be a losing game. But you are mistaken to
> think that the number of hot-spot tables is the only limit on the number
> of usable partitions. It's the number of their indexes that matters most.
> (The pgbench test is if anything probably understating the problem,
> because it has only a single index on each table.)
True. So either 16 partitions, or maybe 8, does sound about right then.
> In any case, even a
> factor of 2 or so reduction in direct conflicts should have a useful
> impact on the number of semop waits, because it's a nonlinear effect...
Nonlinear effects work both ways. Factor of 2 is great, but not enough
to prevent this discussion becoming an annual event ;-)
(Thinks: Oh, it already is...)
Best Regards, Simon Riggs