Re: Do you know the reason for increased max latency due to xlog scaling?

On 2014-02-18 19:12:32 +0200, Heikki Linnakangas wrote:
> You're missing MauMau's point. In essence, he's comparing two systems with
> the same number of clients, issuing queries as fast as they can, and one can
> do 2000 TPS while the other one can do 10000 TPS. You would expect the
> lower-throughput system to have a *higher* average latency.
> Each query takes
> longer, that's why the throughput is lower. If you look at the avg_latency
> columns in the graphs (http://hlinnaka.iki.fi/xloginsert-scaling/padding/),
> that's exactly what you see.
>
> But what MauMau is pointing out is that the *max* latency is much higher in
> the system that can do 10000 TPS. So some queries are taking much longer,
> even though in average the latency is lower. In an ideal, totally fair
> system, each query would take the same amount of time to execute, and after
> it's saturated, increasing the number of clients just makes that constant
> latency higher.

Consider me being enthusiastically unenthusiastic about that fact. The
change in throughput still makes this pretty uninteresting. There's so
many things that are influenced by a factor 5 increase in throughput,
that a change in max latency is really not saying much.
There's also the point that with 5 times the throughput it's getting
more likely to sleep while holding critical locks and such.

> Yeah, I'm pretty sure that's because of the extra checkpoints. If you look
> at the individual test graphs, there are clear spikes in latency, but the
> latency is otherwise small. With a higher TPS, you reach checkpoint_segments
> quicker; I should've eliminated that effect in the tests I ran...

I don't think that'd be a good idea. The number of full page writes so
greatly influences the WAL charactersistics, that changing checkpoint
segments would make the tests much harder to compare.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services