Hello Amit,
>> [...]
>> Ok, I misunderstood your question. I thought you meant a dip between 1
>> client and 4 clients. I meant that when flush is turned on tps goes down by
>> 8% (743 to 681 tps) on this particular run.
>
> This 8% might matter if the dip is bigger with more clients and
> more aggressive workload. Do you know what could lead to this
> dip, because if we know what is the reason than it will be more
> predictable to know if this is the max dip that could happen or it
> could lead to bigger dip in other cases.
I do not know the cause of the dip, and whether it would increase with
more clients. I do not have a box for such tests. If someone can provided
the box, I can provide test scripts:-)
The first, although higher, measure is really very unstable, with pg
totaly unresponsive (offline, really) at time.
I think that the flush option may always have a risk of (small)
detrimental effects on tps, because there are two steady states: one with
pg only doing wal-logged transactions with great tps, and one with pg
doing the checkpoint at nought tps. If this is on the same disk, even at
best the combination means that probably each operation will amper the
other one a little bit, so the combined tps performance would/could be
lower than doing one after the other and having pg offline 50% of the
time...
Please also note that this 8% "dip" is on a 681 (with the dip) vs 198 (no
options at all) a X 3.4 improvement compared to pg current behavior.
>> Basically tps improvements mostly come from "sort", and "flush" has
>> uncertain effects on tps (throuput), but much more on latency and
>> performance stability (lower late rate, lower standard deviation).
>
> I agree that performance stability is important, but not sure if it
> is good idea to sacrifice the throuput for it.
See discussion above. I think better stability may imply slightly lower
throughput on some load. That is why there are options and DBA to choose
them:-)
> If sort + flush always gives better results, then isn't it better to
> perform these actions together under one option.
Sure, but that is not currently the case. Also what is done is very
orthogonal, so I would tend to keep these separate. If one is always
beneficial and it is wished that it should be always activated, then the
option could be removed.
>> Hmmm. My point of view is still that the logical priority is to optimize
>> for disk IO first, then look for compatible RAM optimisations later.
>
> It is not only about RAM optimisation which we can do later, but also
> about avoiding regression in existing use-cases.
Hmmm. Currently I have not seen really significant regressions. I have
seen some less good impact of some options on some loads.
--
Fabien.
