Re: checkpointer continuous flushing - Mailing list pgsql-hackers

Test details

------------------

warmup=60

scale=300

max_connections=150

shared_buffers=8GB

checkpoint_timeout=2min

time=7200

synchronous_commit=on

max_wal_size=5GB

parallelism - 128 clients, 128 threads

avg over 7200: 8256.382528 ± 6218.769282 [0.000000, 76.050000, 10975.500000, 13105.950000, 21729.000000]

percent of values below 10.0: 19.5%

Sort - on

Before going to conclusion, let me try to explain above data (I am

explaining again even though Fabien has explained, to make it clear

if someone has not read his mail)

Let's try to understand with data for sorting - off option

avg over 7200: 8256.382528 ± 6218.769282

8256.382528 - average tps for 7200s pgbench run 

6218.769282 - standard deviation on per second figures

[0.000000, 84.000000, 10946.000000, 13084.000000, 20289.900000]

These 5 values can be read as minimum TPS, q1, median TPS, q3,

maximum TPS over 7200s pgbench run.  As far as I understand q1

and q3 median of subset of values which I didn't focussed much.

percent of values below 10.0: 19.5%

Now about test results, these tests are done for pgbench full speed runs

and the above results indicate that there is approximately 1.5%

improvement in avg. TPS and ~1% improvement in tps values which are

below 10 with sorting on and there is almost no improvement in median or

maximum TPS values, instead they or slightly less when sorting is

on which could be due to run-to-run variation.

I have done more tests as well by varying time and number of clients

keeping other configuration same as above, but the results are quite

similar.

The results of sorting patch for the tests done indicate that the win is not

big enough with just doing sorting during checkpoints, we should consider

flush patch along with sorting.  I would like to perform some tests with both

the patches together (sort + flush) unless somebody else thinks that sorting

patch alone is beneficial and we should test some other kind of scenarios to

see it's benefit.

>
> The reason for the tablespace balancing is that in the current postgres buffers are written more or less randomly, so it is (probably) implicitely and statistically balanced over tablespaces because of this randomness, and indeed, AFAIK, people with multi tablespace setup have not complained that postgres was using the disks sequentially.
>
> However, once the buffers are sorted per file, the order becomes deterministic and there is no more implicit balancing, which means that if someone has a pg setup with several disks it will write sequentially on these instead of in parallel.
>

What if tablespaces are not on separate disks or not enough hardware

support to make Writes parallel?  I think for such cases it might be

better to do it sequentially.