On Wed, 2023-05-17 at 23:07 -0400, reid.thomp...@crunchydata.com wrote: > Thanks for the feedback. > > I'm plannning to look at this. > > Is your benchmark something that I could utilize? I.E. is it a set of > scripts or a standard test from somewhere that I can duplicate? > > Thanks, > Reid > Hi Arne,
Followup to the above. I experimented on my system regarding "The simple query select * from generate_series(0, 10000000) shows roughly 18.9 % degradation on my test server." My laptop: 32GB ram 11th Gen Intel(R) Core(TM) i7-11850H 8 cores/16 threads @ 2.50GHz (Max Turbo Frequency. 4.80 GHz ; Cache. 24 MB) SSD -> Model: KXG60ZNV1T02 NVMe KIOXIA 1024GB (nvme) I updated to latest master and rebased my patch branches. I wrote a script to check out, build, install, init, and startup master, patch 1, patch 1+2, patch 1+2 as master, pg-stats-memory, dev-max-memory, and dev-max-memory-unset configured with ../../configure --silent --prefix=/home/rthompso/src/git/postgres/install/${dir} --with-openssl --with-tcl --with-tclconfig=/usr/lib/tcl8.6 --with-perl --with-libxml --with-libxslt --with-python --with-gssapi --with-systemd --with-ldap --enable-nls where $dir in master, pg-stats-memory, and dev-max-memory, dev-max-memory-unset. The only change made to the default postgresql.conf was to have the script add to the dev-max-memory instance the line "max_total_backend_memory = 2048" before startup. I did find one change in patch 2 that I pushed back into patch 1, this should only impact the pg-stats-memory instance. my .psqlrc turns timing on I created a script where I can pass two instances to be compared. It invokes psql -At -d postgres $connstr -P pager=off -c 'select * from generate_series(0, 10000000)' 100 times on each of the 2 instances and calculates the AVG time and SD for the 100 runs. It then uses the AVG from each instance to calculate the percentage difference. Depending on the instance, my results differ from master from negligible to ~5.5%. Comparing master to itself had up to a ~2% variation. See below. ------------------------ 12 runs comparing dev-max-memory 2048 VS master Shows ~3% to 5.5% variation Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1307.14 -> VER dev-max-memory 2048 1240.74 -> VER master 5.21218% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1315.99 -> VER dev-max-memory 2048 1245.64 -> VER master 5.4926% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1317.39 -> VER dev-max-memory 2048 1265.33 -> VER master 4.03141% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1313.52 -> VER dev-max-memory 2048 1256.69 -> VER master 4.42221% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1329.98 -> VER dev-max-memory 2048 1253.75 -> VER master 5.90077% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1314.47 -> VER dev-max-memory 2048 1245.6 -> VER master 5.38032% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1309.7 -> VER dev-max-memory 2048 1258.55 -> VER master 3.98326% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1322.16 -> VER dev-max-memory 2048 1248.94 -> VER master 5.69562% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1320.15 -> VER dev-max-memory 2048 1261.41 -> VER master 4.55074% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1345.22 -> VER dev-max-memory 2048 1280.96 -> VER master 4.8938% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1296.03 -> VER dev-max-memory 2048 1257.06 -> VER master 3.05277% difference -- Calculate average runtime percentage difference between VER dev-max-memory 2048 and VER master 1319.5 -> VER dev-max-memory 2048 1252.34 -> VER master 5.22272% difference ---------------------------- 12 showing dev-max-memory-unset VS master Shows ~2.5% to 5% variation Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1300.93 -> VER dev-max-memory unset 1235.12 -> VER master 5.18996% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1293.57 -> VER dev-max-memory unset 1263.93 -> VER master 2.31789% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1303.05 -> VER dev-max-memory unset 1258.11 -> VER master 3.50935% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1302.14 -> VER dev-max-memory unset 1256.51 -> VER master 3.56672% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1299.22 -> VER dev-max-memory unset 1282.74 -> VER master 1.27655% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1334.06 -> VER dev-max-memory unset 1263.77 -> VER master 5.41144% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1319.92 -> VER dev-max-memory unset 1262.35 -> VER master 4.45887% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1318.01 -> VER dev-max-memory unset 1257.16 -> VER master 4.7259% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1316.88 -> VER dev-max-memory unset 1257.63 -> VER master 4.60282% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1320.33 -> VER dev-max-memory unset 1282.12 -> VER master 2.93646% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1306.91 -> VER dev-max-memory unset 1246.12 -> VER master 4.76218% difference -- Calculate average runtime percentage difference between VER dev-max-memory unset and VER master 1320.65 -> VER dev-max-memory unset 1258.78 -> VER master 4.79718% difference ------------------------------- 12 showing pg-stat-activity-only VS master Shows ~<1% to 2.5% variation Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1252.65 -> VER pg-stat-activity-backend-memory-allocated 1245.36 -> VER master 0.583665% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1294.75 -> VER pg-stat-activity-backend-memory-allocated 1277.55 -> VER master 1.33732% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1264.11 -> VER pg-stat-activity-backend-memory-allocated 1257.57 -> VER master 0.518702% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1267.44 -> VER pg-stat-activity-backend-memory-allocated 1251.31 -> VER master 1.28079% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1270.05 -> VER pg-stat-activity-backend-memory-allocated 1250.1 -> VER master 1.58324% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1298.92 -> VER pg-stat-activity-backend-memory-allocated 1265.04 -> VER master 2.64279% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1280.99 -> VER pg-stat-activity-backend-memory-allocated 1263.51 -> VER master 1.37394% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1273.23 -> VER pg-stat-activity-backend-memory-allocated 1275.53 -> VER master -0.18048% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1261.2 -> VER pg-stat-activity-backend-memory-allocated 1263.04 -> VER master -0.145786% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1289.73 -> VER pg-stat-activity-backend-memory-allocated 1289.02 -> VER master 0.0550654% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1287.57 -> VER pg-stat-activity-backend-memory-allocated 1279.42 -> VER master 0.634985% difference -- Calculate average runtime percentage difference between VER pg-stat-activity-backend-memory-allocated and VER master 1272.01 -> VER pg-stat-activity-backend-memory-allocated 1259.22 -> VER master 1.01058% difference ---------------------------------- I also did 12 runs master VS master Shows, ~1% to 2% variation Calculate average runtime percentage difference between VER master and VER master 1239.6 -> VER master 1263.73 -> VER master -1.92783% difference -- Calculate average runtime percentage difference between VER master and VER master 1253.82 -> VER master 1252.5 -> VER master 0.105334% difference -- Calculate average runtime percentage difference between VER master and VER master 1256.05 -> VER master 1258.97 -> VER master -0.232205% difference -- Calculate average runtime percentage difference between VER master and VER master 1264.8 -> VER master 1248.94 -> VER master 1.26186% difference -- Calculate average runtime percentage difference between VER master and VER master 1265.08 -> VER master 1275.43 -> VER master -0.814797% difference -- Calculate average runtime percentage difference between VER master and VER master 1260.95 -> VER master 1288.81 -> VER master -2.1853% difference -- Calculate average runtime percentage difference between VER master and VER master 1260.46 -> VER master 1252.86 -> VER master 0.604778% difference -- Calculate average runtime percentage difference between VER master and VER master 1253.49 -> VER master 1255.25 -> VER master -0.140309% difference -- Calculate average runtime percentage difference between VER master and VER master 1277.5 -> VER master 1267.42 -> VER master 0.792166% difference -- Calculate average runtime percentage difference between VER master and VER master 1266.2 -> VER master 1283.12 -> VER master -1.32741% difference -- Calculate average runtime percentage difference between VER master and VER master 1245.78 -> VER master 1246.78 -> VER master -0.0802388% difference -- Calculate average runtime percentage difference between VER master and VER master 1255.15 -> VER master 1276.73 -> VER master -1.70466% difference