Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
> The issue I'm seeing is that 8 real cores outperform 16 real
> cores, which outperform 32 real cores under high concurrency.
With every benchmark I've done of PostgreSQL, the "knee" in the
performance graph comes right around ((2 * cores) +
effective_spindle_count). With the database fully cached (as I
believe you mentioned), effective_spindle_count is zero. If you
don't use a connection pool to limit active transactions to the
number from that formula, performance drops off. The more CPUs you
have, the sharper the drop after the knee.
I think it's nearly inevitable that PostgreSQL will eventually add
some sort of admission policy or scheduler so that the user doesn't
see this effect. With an admission policy, PostgreSQL would
effectively throttle the startup of new transactions so that things
remained almost flat after the knee. A well-designed scheduler
might even be able to sneak marginal improvements past the current
knee. As things currently stand it is up to you to do this with a
carefully designed connection pool.
> 32 cores is much faster than 8 when I have relatively few clients,
> but as the number of clients is scaled up 8 cores wins outright.
Right. If you were hitting disk heavily with random access, the
sweet spot would increase by the number of spindles you were
hitting.
> I was hoping someone had seen this sort of behaviour before, and
> could offer some sort of explanation or advice.
When you have multiple resources, adding active processes increases
overall throughput until roughly the point when you can keep them
all busy. Once you hit that point, adding more processes to contend
for the resources just adds overhead and blocking. HT is so bad
because it tends to cause context switch storms, but context
switching becomes an issue even without it. The other main issue is
lock contention. Beyond a certain point, processes start to contend
for lightweight locks, so you might context switch to a process only
to find that it's still blocked and you have to switch again to try
the next process, until you finally find one which can make
progress. To acquire the lightweight lock you first need to acquire
a spinlock, so as things get busier processes start eating lots of
CPU in the spinlock loops trying to get to the point of being able
to check the LW locks to see if they're available.
You clearly got the best performance with all 32 cores and 16 to 32
processes active. I don't know why you were hitting the knee sooner
than I've seen in my benchmarks, but the principle is the same. Use
a connection pool which limits how many transactions are active,
such that you don't exceed 32 processes busy at the same time, and
make sure that it queues transaction requests beyond that so that a
new transaction can be started promptly when you are at your limit
and a transaction completes.
-Kevin