Re: Some performance degradation in REL_16 vs REL_15

Hi, Andres!

Thanks for your patience and advice.

On 18.10.2023 07:10, Andres Freund wrote:
>> On 13.10.2023 05:05, Andres Freund wrote:
>>> Could you provide a bit more details about how you ran the benchmark?  The
>>> reason I am asking is that ~330 TPS is pretty slow for -c20.  Even on spinning
>>> rust and using the default settings, I get considerably higher results.
>>>
>>> Oh - I do get results closer to yours if I use pgbench scale 1, causing a lot
>>> of row level contention. What scale did you use?

>> I use default scale of 1.
> 
> That means you're largely going to be bottlenecked due to row level
> contention. For read/write pgbench you normally want to use a scale that's
> bigger than the client count, best by at least 2x.

I performed differential measurements with -s21 to obtain scale > number
in accordance with:
> On 18.10.2023 07:14, Tom Lane wrote:
>> * scale <= number of sessions means you're measuring a lot of
>> row-update contention
>> 
And plan to do the same for -s40.

> Have you built postgres with assertions enabled or such?
I ran the debug configuration with --enable-cassert and -O0.
And plan to do the same without asserts and -O2 soon.

> What is the server configuration for both versions?
In all measurements i used default postgresql.conf files. Please see the detailed reproduction below.

> 
>> And run the command sequence:
>> $pgbench -i bench
>> $sleep 1
>> $pgbench -c20 -T10 -j8
> 
> I assume you also specify the database name here, given you specified it for
> pgbench -i?

Sorry, there were a two mistakes in the command sequence that I copied in the previous letter
compared to the real one. I did not completely copy it from the sighting, not from the real script.
In fact i used pgbench -c20 -T20 -j8 bench. -T10 is really unstable and was used only for
preliminary measurements to make them more faster. Please see my first letter in this thread.
   
Here are the detailed description of the last measurements that were performed on my pc.
With a scale of -s21, the difference between REL_16 and REL_15 is rather small, but it has
a cumulative effect from version to version and reaches a maximum between
REL_10_STABLE and REL_16_STABLE.
The measurement procedure was as follows;
- rebuild from sources and reinstall server with
./configure --enable-debug --enable-cassert --with-perl \
    --with-icu --enable-depend --enable-tap-tests
- init the new empty db with initdb -k -D $INSTDIR/data
See postgresql10.conf and postgresql16.conf attached.
- run the series of 100 measurements with the scripts like that for REL_16:
date && rm -f result.txt && for ((i=0; i<100; i++)); do pgbench -U postgres -i -s21 bench> /dev/null 2>&1;
psql -U postgres -d bench -c "checkpoint"; RES=$(pgbench -U postgres -c20 -T20 -j8 bench 2>&1 | awk '/tps[[:space:]]/ {
print$3 }');
 
echo $RES >> result.txt; echo Measurement N$i done. TPS=$RES; done; cat result.txt
or
date && rm -f result.txt && for ((i=0; i<100; i++)); do pgbench -U postgres -i -s21 bench> /dev/null 2>&1;
psql -U postgres -d bench -c "checkpoint"; RES=$(pgbench -U postgres -c20 -T20 -j8 bench 2>&1 | awk '/excluding/ {
print$3 }');
 
echo $RES >> result.txt; echo Measurement N$i done. TPS=$RES; done; cat result.txt
for REL_10 respectively.

For REL_16_STABLE at 7cc2f59dd the average TPS was: 2020+-70,
for REL_10_STABLE at c18c12c98 - 2260+-70

The percentage difference was approximately 11%.
Please see the 16vs10.png picture with the graphical representation of the data obtained.
Also there are the raw data in the raw_data_s21.txt.

In some days i hope to perform additional measurements that were mentioned above in this letter.
It would be interesting to establish the reason for this difference. And  i would be very grateful
if you could advise me what other settings can be tweaked.

With the best wishes!

-- 
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company