Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rw5N4-00GfTJ-LG for pgsql-general@arkaria.postgresql.org; Sun, 14 Apr 2024 19:18:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rw5N2-007oWo-HC for pgsql-general@arkaria.postgresql.org; Sun, 14 Apr 2024 19:18:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rw5N1-007oWf-RN for pgsql-general@lists.postgresql.org; Sun, 14 Apr 2024 19:18:32 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rw5Mw-002qdY-Iy for pgsql-general@lists.postgresql.org; Sun, 14 Apr 2024 19:18:30 +0000 Received: by mail-ej1-x633.google.com with SMTP id a640c23a62f3a-a526d381d2fso11891766b.0 for ; Sun, 14 Apr 2024 12:18:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713122304; x=1713727104; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OvfNbU/Hrm2DmJDb0toETLi+r2uhIl7vjVCfKfOTIhU=; b=eg8y6BhP5wFvq2u7SgBw3f/DZM1+MmP69KEvpV0q9bUNKwFzaA4v5tFdoUBgmqxMU8 WBR1+1etVQ+aUI6npU7p77k65owakTm95l2lDTLsK/dEOa/nnPg+j1x4x14QVSJCTDPr xyVo2gwM/+hGzU/Ez2qaUPg4KL0CbH6LDJ5jI2J7tfVsTWlgqXSiIEYASXQOheHwQWy0 VAY7mDOgmTf7Y73g74HhO7UIF7hbunkDusYOkcxC84X6IA5O0uykMlR8elNOr/y5pGFX qMCHIdcAqPQrF0+F4WN1nEhLJxJMNIoK8Kw4s5y0W2nkWjxJeD3w2uZntelE3lKe1316 NYrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713122304; x=1713727104; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=OvfNbU/Hrm2DmJDb0toETLi+r2uhIl7vjVCfKfOTIhU=; b=D4AX3IWhQxfgXEzH83uxFkd38O/hGxh/XtJkmv57KbhXTJJy7NyBNfICNU6G9gm+Dw x1Yx17zMu5socOj/qyho1Q+s4T2tVeUQjU3wdst6L1mExlHEJ04NrIdcAEzTNa5K3tVD oboE9xENOPK6mNNOOa36Ee3A/YZtAAPxDDomzKQDSIvxe6CvRUnXScII6S7zL8RDQxqa fHbF/N3vT1OxqQe5f7gzq432Yu6RuVLiyBjwQ9b6L73HLoCZ6UMOfyjs1+XbGfCoTCtK lj39nWW3HXHUhFKTEt/zEWKvS3biHbExgSN3VpyVM728A1WTZ5YZCu4cB2JA0nDOJYU8 AvSg== X-Gm-Message-State: AOJu0YzK2j/lQu1zz/sCLKlY/AcZEWbcOGOtbI09RZS0LiZOJ/to2v26 0EdCeJIB2BNmJxCSJJ1MPVo07rA7eAaNsXn0Dq9ZjYrOc++n6BnxPJ4w8em7ezustjimBrx1u0E hFMgsSrzXYCfV6t0JI/NbGtWmOMGB9Zps X-Google-Smtp-Source: AGHT+IE69P0PL5BJJhk/WuceQPPDE1CrEw2QChr/TfNX2UK85xhTl62jkiTmr3aw/3Yxvo2/32Ay0gxlKGNgBl2uDfc= X-Received: by 2002:a17:907:2ce6:b0:a52:a25:2077 with SMTP id hz6-20020a1709072ce600b00a520a252077mr10176542ejc.14.1713122304007; Sun, 14 Apr 2024 12:18:24 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Vijaykumar Jain Date: Mon, 15 Apr 2024 00:48:13 +0530 Message-ID: Subject: Re: constant crashing To: jack Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000006324a70616135eba" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006324a70616135eba Content-Type: text/plain; charset="UTF-8" On Sun, 14 Apr 2024 at 21:50, jack wrote: > The full error reads: > server closed the connection expectantly > This probably means the server terminated abnormally > before or while processing the request. > error: connection to server was lost > > PostgreSQL 16.2 > > I also believe it is a resource issue which can be rectified with a > setting, but which setting? > If you were updating 100 million records what settings would you adjust? > > Here are the updates I am performing on the 100 million records: > UPDATE table SET category_modified = UPPER(category); > UPDATE table SET category_modified = > REGEXP_REPLACE(REPLACE(REPLACE(category_modified, '''','-'), '`', '-'), > '\s{2,}', ' ', 'g') WHERE AND LENGTH(category_modified)>1 AND > POSITION('--' IN category_modified)>0; > UPDATE table SET category_modified = REPLACE(category_modified,' ','-'); > UPDATE table SET category_modified = CASE WHEN category_modified IS NOT > NULL THEN regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE NULL END; > UPDATE table SET category_modified = regexp_replace(category_modified, > '-{2,}', '-', 'g'); > UPDATE table SET category_modified = SUBSTRING(category_modified FROM 1 > FOR LENGTH(category_modified) - 1) WHERE LENGTH(category_modified)>1 AND > category_modified LIKE '%-'; > > independent of best practices, i just want to check if there is a leak. I created a sample table with text data and ran updates like yours and I could not see mem growth, but I have a small vm and ofc your category_modified field might be more complex than simple text fields for 30-40 chars. can you grab the pid of your psql backend and (if you have pidstat installed) monitor resource usage for that pid postgres@pg:~/udemy/16$ psql psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1)) Type "help" for help. postgres=# select pg_backend_pid(); pg_backend_pid ---------------- 1214 (1 row) # pidstat 2 100 -rud -h -p 1214 (get all stats for that pid) that might help to figure out if there is a leak or the server has other things competing for memory and your updates were picked by the killer. Linux 5.15.0-101-generic (pg) 04/15/24 _x86_64_ (1 CPU) # Time UID PID %usr %system %guest %wait %CPU CPU minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s iodelay Command 00:40:25 113 1214 0.00 0.00 0.00 0.00 0.00 0 0.00 0.00 354112 220940 24.18 0.00 0.00 0.00 0 postgres # Time UID PID %usr %system %guest %wait %CPU CPU minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s iodelay Command 00:40:27 113 1214 0.00 0.00 0.00 0.00 0.00 0 0.00 0.00 354112 220940 24.18 0.00 0.00 0.00 0 postgres .... ofc, if there is a genuine leak , then there might be more digging needed Finding memory leaks in Postgres C code (enterprisedb.com) just kill the process requesting more mem than available Memory context: how PostgreSQL allocates memory - CYBERTEC (cybertec-postgresql.com) -- Thanks, Vijay LinkedIn - Vijaykumar Jain --0000000000006324a70616135eba Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sun, 14 Apr 2024 at 21:5= 0, jack <jack4pg@a7q.com> wrot= e:
The full error reads:
serve= r closed the connection=C2=A0expect= antly
This probably means the server terminated abnormally
before or wh= ile processing the request.
error: connection to server was lost

PostgreSQL 16.2
I also believe it is a resour= ce issue which can be rectified with a setting, but which setting?
If you were = updating 100 million records what settings would you adjust?

H= ere are the updates I am performing on the 100 million records:
= UPDATE table SET category_modified =3D UPPER(category);
<= div>UPDATE table SET category_modified =3D REGEXP_REPLACE(REPLACE(REP= LACE(category_modified, '''','-'), '`', = 9;-'), '\s{2,}', ' ', 'g') WHERE =C2=A0AND LENG= TH(category_modified)>1 AND POSITION('--' IN category_modified)&= gt;0;
UPDATE table SET category_modified =3D REPLACE= (category_modified,' ','-');
UPDATE = table SET category_modified =3D CASE WHEN category_modified IS NOT NULL THE= N regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE = NULL END;
UPDATE table SET category_modified =3D reg= exp_replace(category_modified, '-{2,}', '-', 'g');<= /span>
UPDATE table SET category_modified =3D SUBSTRING(cat= egory_modified FROM 1 FOR LENGTH(category_modified) - 1) WHERE LENGTH(categ= ory_modified)>1 AND category_modified LIKE '%-';


independent of best practices, i= just want to check if there is a leak.
I created a sample table = with text data and ran updates like yours and I could not see mem growth, b= ut I have a small vm and ofc your=C2=A0 category_modified field might be mo= re complex than simple text fields for 30-40 chars.

can you grab the pid of your psql backend and (if you have pidstat instal= led) monitor resource usage for that pid

post= gres@pg:~/udemy/16$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))<= /div>
Type "help" for help.

postgres= =3D# select pg_backend_pid();=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_backend_pid
-= ---------------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01214
(1 row)

# pidstat 2 100 -rud -h = -p 1214=C2=A0
(get all stats for that pid) that might help to fig= ure out if there is a leak or the server has other things competing for mem= ory and your updates were picked by the killer.

Li= nux 5.15.0-101-generic (pg)=C2=A0 =C2=A004/15/24=C2=A0 =C2=A0 =C2=A0 =C2=A0= _x86_64_=C2=A0 =C2=A0 =C2=A0 =C2=A0 (1 CPU)

# Tim= e=C2=A0 =C2=A0 =C2=A0 =C2=A0 UID=C2=A0 =C2=A0 =C2=A0 =C2=A0PID=C2=A0 =C2=A0= %usr %system=C2=A0 %guest=C2=A0 =C2=A0%wait=C2=A0 =C2=A0 %CPU=C2=A0 =C2=A0= CPU=C2=A0 minflt/s=C2=A0 majflt/s=C2=A0 =C2=A0 =C2=A0VSZ=C2=A0 =C2=A0 =C2= =A0RSS=C2=A0 =C2=A0%MEM=C2=A0 =C2=A0kB_rd/s=C2=A0 =C2=A0kB_wr/s kB_ccwr/s i= odelay=C2=A0 Command
00:40:25=C2=A0 =C2=A0 =C2=A0 113=C2=A0 =C2= =A0 =C2=A0 1214=C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2=A0= =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A00=C2=A0 =C2=A0 =C2=A0 0.0= 0=C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 354112=C2=A0 220940=C2=A0 24.18=C2=A0 =C2= =A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 =C2= =A0 =C2=A0 =C2=A00=C2=A0 postgres

# Time=C2=A0 =C2= =A0 =C2=A0 =C2=A0 UID=C2=A0 =C2=A0 =C2=A0 =C2=A0PID=C2=A0 =C2=A0 %usr %syst= em=C2=A0 %guest=C2=A0 =C2=A0%wait=C2=A0 =C2=A0 %CPU=C2=A0 =C2=A0CPU=C2=A0 m= inflt/s=C2=A0 majflt/s=C2=A0 =C2=A0 =C2=A0VSZ=C2=A0 =C2=A0 =C2=A0RSS=C2=A0 = =C2=A0%MEM=C2=A0 =C2=A0kB_rd/s=C2=A0 =C2=A0kB_wr/s kB_ccwr/s iodelay=C2=A0 = Command
00:40:27=C2=A0 =C2=A0 =C2=A0 113=C2=A0 =C2=A0 =C2=A0 1214= =C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 0.00=C2= =A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A00=C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 = =C2=A0 0.00=C2=A0 354112=C2=A0 220940=C2=A0 24.18=C2=A0 =C2=A0 =C2=A0 0.00= =C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A0 0.00=C2=A0 =C2=A0 =C2=A0 =C2= =A00=C2=A0 postgres
....

= ofc, if there is a genuine leak , then there might be more digging needed= =C2=A0Finding memory leaks in Postgres C code (enterprisedb.com)=C2=A0


--
--0000000000006324a70616135eba--