Subject: Re: Some performance degradation in REL_16 vs REL_15

From: Andres Freund <[email-masked]>
Date: Thu, 12 Oct 2023 19:05:22 -0700

Hi,

On 2023-10-12 11:00:22 +0300, Anton A. Melnikov wrote:
> Found that simple test pgbench -c20 -T20 -j8 gives approximately
> for REL_15_STABLE at 5143f76:  336+-1 TPS
> and
> for REL_16_STABLE at 4ac7635f: 324+-1 TPS
> 
> The performance drop is approximately 3,5%  while the corrected standard deviation is only 0.3%.
> See the raw_data.txt attached.

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?

Greetings,

Andres Freund

---

From: "Anton A. Melnikov" <[email-masked]>
Date: Mon, 16 Oct 2023 11:04:25 +0300

> of row level contention. What scale did you use?

I use default scale of 1.
And run the command sequence:
$pgbench -i bench
$sleep 1
$pgbench -c20 -T10 -j8
in a loop to get similar initial conditions for every "pgbench -c20 -T10 -j8" run.

Thanks for your interest!

With the best wishes,

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

---

From: 邱宇航 <[email-masked]>
Date: Wed, 18 Oct 2023 11:45:55 +0800

I wrote a script and test on branch REL_[10-16]_STABLE, and do see performance drop in REL_13_STABLE, which is about 1~2%.

scale	round	10	11	12	13	14	15	16
1	1	7922.2	8018.3	8102.8	7838.3	7829.2	7870.0	7846.1
	2	7922.4	7923.5	8090.3	7887.7	7912.4	7815.2	7865.6
	3	7937.6	7964.9	8012.8	7918.5	7879.4	7786.4	7981.1
	4	8000.4	7959.5	8141.1	7886.3	7840.9	7863.5	8022.4
	5	7921.8	7945.5	8005.2	7993.7	7957.0	7803.8	7899.8
	6	7893.8	7895.1	8017.2	7879.8	7880.9	7911.4	7909.2
	7	7879.3	7853.5	8071.7	7956.2	7876.7	7863.3	7986.3
	8	7980.5	7964.1	8119.2	8015.2	7877.6	7784.9	7923.6
	9	8083.9	7946.4	7960.3	7913.9	7924.6	7867.7	7928.6
	10	7971.2	7991.8	7999.5	7812.4	7824.3	7831.0	7953.4
	AVG	7951.3	7946.3	8052.0	7910.2	7880.3	7839.7	7931.6
	MED	7930.0	7952.9	8044.5	7900.8	7878.5	7847.1	7926.1
10	1	41221.5	41394.8	40926.8	40566.6	41661.3	40511.9	40961.8
	2	40974.0	40697.9	40842.4	40269.2	41127.7	40795.5	40814.9
	3	41453.5	41426.4	41066.2	40890.9	41018.6	40897.3	40891.7
	4	41691.9	40294.9	41189.8	40873.8	41539.7	40943.2	40643.8
	5	40843.4	40855.5	41243.8	40351.3	40863.2	40839.6	40795.5
	6	40969.3	40897.9	41380.8	40734.7	41269.3	41301.0	41061.0
	7	40981.1	41119.5	41158.0	40834.6	40967.1	40790.6	41061.6
	8	41006.4	41205.9	40740.3	40978.7	40742.4	40951.6	41242.1
	9	41089.9	41129.7	40648.3	40622.1	40782.0	40460.5	40877.9
	10	41280.3	41462.7	41316.4	40728.0	40983.9	40747.0	40964.6
	AVG	41151.1	41048.5	41051.3	40685.0	41095.5	40823.8	40931.5
	MED	41048.2	41124.6	41112.1	40731.3	41001.3	40817.6	40926.7
100	1	43429.0	43190.2	44099.3	43941.5	43883.3	44215.0	44604.9
	2	43281.7	43795.2	44963.6	44331.5	43559.7	43571.5	43403.9
	3	43749.0	43614.1	44616.7	43759.5	43617.8	43530.3	43362.4
	4	43362.0	43197.3	44296.7	43692.4	42020.5	43607.3	43081.8
	5	43373.4	43288.0	44240.9	43795.0	43630.6	43576.7	43512.0
	6	43637.0	43385.2	45130.1	43792.5	43635.4	43905.2	43371.2
	7	43621.2	43474.2	43735.0	43592.2	43889.7	43947.7	43369.8
	8	43351.0	43937.5	44285.6	43877.2	43771.1	43879.1	43680.4
	9	43481.3	43700.5	44119.9	43786.9	43440.8	44083.1	43563.2
	10	43238.7	43559.5	44310.8	43406.0	44306.6	43376.3	43242.7
	AVG	43452.4	43514.2	44379.9	43797.5	43575.6	43769.2	43519.2
	MED	43401.2	43516.8	44291.2	43789.7	43633.0	43743.2	43387.5

The script looks like:
    initdb data >/dev/null 2>&1 #initdb on every round
    pg_ctl -D data -l logfile start >/dev/null 2>&1 #start without changing any setting
    pgbench -i postgres $scale >/dev/null 2>&1
    sleep 1 >/dev/null 2>&1
    pgbench -c20 -T10 -j8

And here is the pg_config output:
...
CONFIGURE =  '--enable-debug' '--prefix=/home/postgres/base' '--enable-depend' 'PKG_CONFIG_PATH=/usr/local/lib64/pkgconfig::/usr/lib/pkgconfig'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/home/postgres/base/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm 
VERSION = PostgreSQL 16.0

—-
Yuhang Qiu

---

From: Tom Lane <[email-masked]>
Date: Wed, 18 Oct 2023 00:14:16 -0400

> I wrote a script and test on branch REL_[10-16]_STABLE, and do see performance drop in REL_13_STABLE, which is about 1~2%.

I'm really skeptical that we should pay much attention to these numbers.
You've made several of the mistakes that we typically tell people not to
make when using pgbench:

* scale <= number of sessions means you're measuring a lot of
row-update contention

* once you crank up the scale enough to avoid that problem, running
with the default shared_buffers seems like a pretty poor choice

* 10-second runtime is probably an order of magnitude too small
to get useful, reliable numbers

On top of all that, discrepancies on the order of a percent or two
commonly arise from hard-to-control-for effects like the cache
alignment of hot spots in different parts of the code.  That means
that you can see changes of that size from nothing more than
day-to-day changes in completely unrelated parts of the code.

I'd get excited about say a 10% performance drop, because that's
probably more than noise; but I'm not convinced that any of the
differences you show here are more than noise.

			regards, tom lane