Re: gaussian distribution pgbench - Mailing list pgsql-hackers

And I find new useful point of this feature. Under following results are
'--gaussian=20' case and '--gaussian=2' case, and postgresql setting is same.

> [mitsu-ko@pg-rex31 pgbench]$ ./pgbench -c8 -j4 --gaussian=20 -T30 -P 5
> starting vacuum...end.
> progress: 5.0 s, 4285.8 tps, lat 1.860 ms stddev 0.425
> progress: 10.0 s, 4249.2 tps, lat 1.879 ms stddev 0.372
> progress: 15.0 s, 4230.3 tps, lat 1.888 ms stddev 0.430
> progress: 20.0 s, 4247.3 tps, lat 1.880 ms stddev 0.400
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 25.0 s, 4269.0 tps, lat 1.870 ms stddev 0.427
> progress: 30.0 s, 4318.1 tps, lat 1.849 ms stddev 0.415
> transaction type: Gaussian distribution TPC-B (sort of)
> scaling factor: 10
> standard deviation threshold: 20.00000
> access probability of top 20%, 10% and 5% records: 0.99994 0.95450 0.68269
> query mode: simple
> number of clients: 8
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 128008
> latency average: 1.871 ms
> latency stddev: 0.412 ms
> tps = 4266.266374 (including connections establishing)
> tps = 4267.312022 (excluding connections establishing)


> [mitsu-ko@pg-rex31 pgbench]$ ./pgbench -c8 -j4 --gaussian=2 -T30 -P 5
> starting vacuum...end.
> LOG:  checkpoints are occurring too frequently (13 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (1 second apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 5.0 s, 3927.9 tps, lat 2.030 ms stddev 0.691
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (1 second apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 10.0 s, 4045.8 tps, lat 1.974 ms stddev 0.835
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (1 second apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 15.0 s, 4042.5 tps, lat 1.976 ms stddev 0.613
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 20.0 s, 4103.9 tps, lat 1.946 ms stddev 0.540
> LOG:  checkpoints are occurring too frequently (1 second apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 25.0 s, 4003.1 tps, lat 1.995 ms stddev 0.526
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (1 second apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (2 seconds apart)
> HINT:  Consider increasing the configuration parameter "checkpoint_segments".
> progress: 30.0 s, 4025.5 tps, lat 1.984 ms stddev 0.568
> transaction type: Gaussian distribution TPC-B (sort of)
> scaling factor: 10
> standard deviation threshold: 2.00000
> access probability of top 20%, 10% and 5% records: 0.32566 0.16608 0.08345
> query mode: simple
> number of clients: 8
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 120752
> latency average: 1.984 ms
> latency stddev: 0.638 ms
> tps = 4024.823433 (including connections establishing)
> tps = 4025.777787 (excluding connections establishing)

In '--gaussian=2' benchmark, checkpoint is frequently happen than '--gaussian=20'
benchmark. Because former update large range of records
so that fullpage write WALs are bigger than later. Former distribution updates
large range of records, so that fullpage-write WALs are bigger than later
distribution. Such benchmark was not able to come out by the past pgbench at all.

I think that this feature will be also useful for survey new buffer-replace
algorithm and checkpoint strategy, so on. If we remove this option, it is really
dissapointed..

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center