Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q14xG-0007QN-NF for pgsql-hackers@arkaria.postgresql.org; Mon, 22 May 2023 12:48:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q14xF-0006PY-NB for pgsql-hackers@arkaria.postgresql.org; Mon, 22 May 2023 12:48:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q14xF-0006PP-BD for pgsql-hackers@lists.postgresql.org; Mon, 22 May 2023 12:48:01 +0000 Received: from mail-qt1-x835.google.com ([2607:f8b0:4864:20::835]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q14x9-001OKH-Jr for pgsql-hackers@lists.postgresql.org; Mon, 22 May 2023 12:48:00 +0000 Received: by mail-qt1-x835.google.com with SMTP id d75a77b69052e-3f6a3a76665so7574871cf.1 for ; Mon, 22 May 2023 05:47:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=crunchydata.com; s=google; t=1684759674; x=1687351674; h=user-agent:mime-version:date:content-transfer-encoding:references :in-reply-to:cc:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=hHlu5K86VPeiouVvo3xj/1FdGiYEHwc0iVD3Fht9G0Y=; b=j0TviCsUE9wZ0NZUfJJ7tZ1OHeEVNC/0aYeqXEbBHZHhzxvBy5ic3bnbAFsKKQcDzd fkZvnR5NBeA2ivhpzw3jtW61ksmnZ/CXPt3+5YeGh+Q+KPQhDcUKO9I6zbK/WTeaJuaG Xb0M+a+FRWUEAdHrpXcRQUJ0G3vBsQvFzztRild55YNdjwrjSzTFL21iZy/l2AGFbKR5 U21G8SR6v0cbq8XGxDKKN7/F+7PjywqlYd7X4kILUH0AREAduWzUQKtnegDbLnEFSKTe bRXQwn8JmHEO8zR0AKtVwb0UnEbvxCE3XjGX8uOGGi1ZbJhbnXv7qkaNDFtBFIKU1daI b6kw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1684759674; x=1687351674; h=user-agent:mime-version:date:content-transfer-encoding:references :in-reply-to:cc:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=hHlu5K86VPeiouVvo3xj/1FdGiYEHwc0iVD3Fht9G0Y=; b=KnSgYEmyvHT6MMO6gkSuh9GI5MMq7siRMIoCafMJ+uY2LmgJ0LPD98NBzVpUz4xYeP M+ZuEfagFLK9wLu238UVLKd9xThAnLH1ozMx/DXQdeoohtFK0ylDJNew7+irbEJGpAci 9YYiAD95c7DjUKTNli/NCw2v//aCWR91Rdk3wBgWWSlyWG/Z4dOC5BkpKBZiUUOknu1B ZyC23UqCkxL6zmhH9/rKgiOPlY2cO7XlK4rZ0RLYfr/3K7h6w1XDgswYov4MaRsT7+N4 Qc4npsIceGeTGfZ7gziabIcNisu3ko0auf/2GO0dVKCxgwSRXqeyCuN2+rFdjRajk1M3 V/pw== X-Gm-Message-State: AC+VfDwjbqutHuyZWf9b57wgumOcAK8HdFRMWNusO23VUHvRpgFzFzX6 9Rc3loTW0UEG0Yu97MNmtM9nCA== X-Google-Smtp-Source: ACHHUZ7tbvymP+Zjfnxb11suIYJCNfveJlRacJMUtdqeSNQTdKI1XsSzy/KWDQsBRCJKdODpTUVtkA== X-Received: by 2002:a05:622a:190:b0:3f4:ecb4:85d with SMTP id s16-20020a05622a019000b003f4ecb4085dmr22006164qtw.11.1684759674471; Mon, 22 May 2023 05:47:54 -0700 (PDT) Received: from raker.lan (cpe-24-163-71-136.nc.res.rr.com. [24.163.71.136]) by smtp.gmail.com with ESMTPSA id h6-20020ac87766000000b003ef5dc13bbbsm2001549qtu.85.2023.05.22.05.47.53 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 22 May 2023 05:47:54 -0700 (PDT) Message-ID: <7912c911af51d5cf28c611190bf3d463b9209343.camel@crunchydata.com> Subject: Re: Add the ability to limit the amount of memory that can be allocated to backends. From: reid.thompson@crunchydata.com To: Arne Roland , Andres Freund Cc: vignesh C , "pgsql-hackers@lists.postgresql.org" , Justin Pryzby , Ibrar Ahmed , "stephen.frost" In-Reply-To: <94184680049d6bda0c85f1759af24d8127cfe895.camel@crunchydata.com> References: <20221206183244.howaxkwg4tm4bnkp@awork3.anarazel.de> <4816d81103c9360c51d0bc73f1dfb9992f10c9de.camel@crunchydata.com> <711c72e49919508d584e14f36a01119ef75d9ba5.camel@crunchydata.com> <9ab2e5e54b93651e9593342fc48d29fdd469bc99.camel@crunchydata.com> <20230123203128.wh2jjh4runhnalho@awork3.anarazel.de> <9b5226bf88ed5f09c7ae589a11c8fd147854eb98.camel@crunchydata.com> <20230214002627.7btgrc34forjkm2i@awork3.anarazel.de> <1a136d92e53eea52a4cde2163918ea91cf75e0b6.camel@crunchydata.com> <20230303002907.jktl5rddkvlazprp@alap3.anarazel.de> <457dd07b21810463d8dd282e48b99cd90c24a440.camel@crunchydata.com> <334ccda9d8790f2b80dc3fee787704b0621152da.camel@crunchydata.com> <94184680049d6bda0c85f1759af24d8127cfe895.camel@crunchydata.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Date: Mon, 22 May 2023 08:42:51 -0400 MIME-Version: 1.0 User-Agent: Evolution 3.45.1 Compiled: 2022-06-07 10:22:05 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-05-17 at 23:07 -0400, reid.thompson@crunchydata.com wrote: > Thanks for the feedback. >=20 > I'm plannning to look at this.=20 >=20 > 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? >=20 > Thanks, > Reid >=20 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 Turb= o 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,=C2=A0 dev-max-memory, and dev-max-memory-unset configured with ../../configure --silent --prefix=3D/home/rthompso/src/git/postgres/install= /${dir} --with-openssl --with-tcl --with-tclconfig=3D/usr/lib/tcl8.6 --with= -perl --with-libxml --with-libxslt --with-python --with-gssapi --with-syste= md --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 =3D 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=3Doff -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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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-activit= y-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