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 1sg5Ns-00FD3n-5z for pgsql-sql@arkaria.postgresql.org; Mon, 19 Aug 2024 16:37:32 +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 1sg5Nq-000PY5-9T for pgsql-sql@arkaria.postgresql.org; Mon, 19 Aug 2024 16:37:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sg5Np-000PXq-UK for pgsql-sql@lists.postgresql.org; Mon, 19 Aug 2024 16:37:30 +0000 Received: from mail-ej1-f50.google.com ([209.85.218.50]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sg5Nm-000TYQ-9D for pgsql-sql@lists.postgresql.org; Mon, 19 Aug 2024 16:37:29 +0000 Received: by mail-ej1-f50.google.com with SMTP id a640c23a62f3a-a7aada2358fso869900966b.0 for ; Mon, 19 Aug 2024 09:37:27 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724085446; x=1724690246; 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=IoMaqraKoufj2WRBB7FPbi7pw6tG9pHCnwAeH8MNyZU=; b=I6+wBje/MwyVJqCk8as9P7cifAZLq8FZnzVwlogJNDEpNKRaYMFng7Clsvlfms6ib8 4V4qQbw67oZofb7ur/Wypw1smpJbGAuv3XLoq3oyXlyG4TQBdRs6DcBdHgjJ4N2ICoNC Mw2h3iRyzlzn70sWrHp3zQf47noAp1JE8Wrryx8hwNFHQQkh74EXV4gMud0siVvuC/H4 oC/hIsAXYbMiJdf+LEgA0MTu3bJurSOBJ+ilR13v11sot1oADshb20YuPyOaR+xDDnFy oqadhKfc7eVazYjCcYxJkgoaviKQMRhiHUkA1HI7cEiC0dztuA2sZs2OPAO1UXHePc8U ufYw== X-Gm-Message-State: AOJu0YzHt73f78+vOiVg2PK9HXbBZHcXFdLQO+ThHmOqB8GviP75ncNU eTiIwkbohN9sn0KmESP3aV6aHkovmx8bOAnOPuodROJXVztiKk+5qWUf2zSU X-Google-Smtp-Source: AGHT+IFWW3Asyy+0oTvl4noy3Z3KpU1jJDx3spWrdKNXf5kWp+u/+OXTmM6jZ3/w1YrrekPnKSnWTQ== X-Received: by 2002:a17:907:980c:b0:a7d:8912:6697 with SMTP id a640c23a62f3a-a8643f3c2a2mr16894266b.3.1724085445699; Mon, 19 Aug 2024 09:37:25 -0700 (PDT) Received: from mail-wr1-f46.google.com (mail-wr1-f46.google.com. [209.85.221.46]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a83838cf9c3sm651669166b.70.2024.08.19.09.37.25 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 19 Aug 2024 09:37:25 -0700 (PDT) Received: by mail-wr1-f46.google.com with SMTP id ffacd0b85a97d-37186c2278bso2575288f8f.1 for ; Mon, 19 Aug 2024 09:37:25 -0700 (PDT) X-Received: by 2002:a5d:65c5:0:b0:367:9049:da2e with SMTP id ffacd0b85a97d-371c4a9fe7fmr138589f8f.8.1724085445366; Mon, 19 Aug 2024 09:37:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Mon, 19 Aug 2024 09:37:12 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database. To: Frank Komsic Cc: pgsql-sql Content-Type: multipart/alternative; boundary="00000000000088a72d06200bec09" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000088a72d06200bec09 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Aug 19, 2024 at 9:08=E2=80=AFAM Frank Komsic wrote: > Hi Steve, > > > > Thank you for your suggestions. > > > > > > Steve wrote: > > I'm far from an expert in this area but running explain it explain analyz= e > seems like a useful thing to share with the group. Then I wonder if runni= ng > vacuum analyze would be useful? Maybe the planner is doing something weir= d. > > > > I have done a VACUUM ANALYZE and a VACUUM FULL on the questionable table > with marginal improvement but still it seems to be slower than previously= . > I tried EXPLAIN ANALYZE and it does show it is slow for the number of > records. REINDEXED the index with little success as well. > > > > I'd also check if you lost any indexes you need during the bad day? > > > > How do I check that? > > > > Also being sure your system performance stats are correct - are you using > all the cores and ram that you expect? Is the disk temporary space and sw= ap > performing normally? > > > > Need to see and verify that=E2=80=A6 do not have historical data on the > performance. Is there a way to get historicals or does it require a thir= d > party software? > > > > Are other tables unaffected, somewhat affected or in the same situation a= s > this table. > > > > It seems other tables are fine as they do not have triggers on them. The > data table of millions of records seems to visualize 100 k records faster > than the table in question. > > > > Currently I stopped all updates and the table visualizes in a little over > 2 seconds. Previously while the updates were running it took 4 to 7 > seconds to visualize. > > > > I'd recommend, if your environment can tolerate debugging, with > reuploading this table's data into an identical table and see if the > problem exists there too. > > > > I am not an expert in PostgreSQL. We have lost our programmer and do not > have afall back plan for now. I have been educating myself on the > administration of postgresql, just this problem seems a bit unusual from > the training I had. > > > > How can I reupload the data into an identical table? > > > > Also if you dump the entire database can you reload in a new server and > replicate there? > > > > Yes the ultimate way to verify. I gather it would be a pg_basebackup and > then restore function? > > > > I hope this helps, > > Steve > > > I am not a postgres admin expert, but if I had this issue, I'd use pg_dump and pg_restore. I'm not familiar with pg_basebackup, but maybe it is more robust. Basically I'd run pg_dump (iirc "-F c" will dump in custom/native format), install the SAME version of postgres onto a machine with similar hardware, create a new database with the same name, then run pg_restore to get the data back into that. Regarding your explain analyze: again I'm no expert, but I really don't understand how a table with 2500 rows and 18 cols can take 2.5s to enumerate.. So that remains a very mysterious thing for me. But pulling the data to a new server/database and verifying that the same problem occurs there seems wise. But regarding the "width" - I believe that's a measure of all the columns concatenated together, returned in bytes, but I could be wrong. The fact that there is a trigger on this table is suggestive that there is maybe a locking issue that is interfering with the search. If you can copy the data to a new location, you can disable the trigger to verify if that's a big part of the problem.. Steve --00000000000088a72d06200bec09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Mon, Aug 19, 2024 at 9:08=E2=80=AF= AM Frank Komsic <komsicf@shoe= icanada.com> wrote:

Hi Steve,

=C2=A0<= /span>

Thank you for your su= ggestions.

=C2=A0<= /span>

=C2=A0<= /span>

Steve wrote:

I'm far from an expert in this area but running = explain it explain analyze seems like a useful thing to share with the grou= p. Then I wonder if running vacuum analyze would be useful? Maybe the plann= er is doing something weird.=C2=A0

=C2=A0

I have done a VACUUM ANALY= ZE and a VACUUM FULL on the questionable table with marginal improvement bu= t still it seems to be slower than previously.=C2=A0 I tried EXPLAIN ANALYZ= E and it does show it is slow for the number of records.=C2=A0 REINDEXED the index with little success as well.<= u>

=C2=A0

I'd also check if you lost any indexes you need = during the bad day?=C2=A0

=C2=A0

How do I check that?

=C2=A0

Also being sure your system performance stats are co= rrect - are you using all the cores and ram that you expect? Is the disk te= mporary space and swap performing normally?=C2=A0

=C2=A0

Need to see and verify tha= t=E2=80=A6 do not have historical data on the performance.=C2=A0 Is there a= way to get historicals or does it require a third party software?

=C2=A0

Are other tables unaffected, somewhat affected or in= the same situation as this table.=C2=A0

=C2=A0

It seems other tables are = fine as they do not have triggers on them.=C2=A0 The data table of millions= of records seems to visualize 100 k records faster than the table in quest= ion.

=C2=A0

Currently I stopped all up= dates and the table visualizes in a little over 2 seconds.=C2=A0 Previously= while the updates were running it took 4 to 7 seconds to visualize.=

=C2=A0

I'd recommend, if your environment can tolerate = debugging, with reuploading this table's data into an identical table a= nd see if the problem exists there too.=C2=A0

=C2=A0

I am not an expert in Post= greSQL.=C2=A0 We have lost our programmer and do not have afall back plan f= or now.=C2=A0 I have been educating myself on the administration of postgre= sql, just this problem seems a bit unusual from the training I had.

=C2=A0

How can I reupload the dat= a into an identical table?

=C2=A0

Also if you dump the entire database can you reload = in a new server and replicate there?=C2=A0

=C2=A0

Yes the ultimate way to ve= rify.=C2=A0 I gather it would be a pg_basebackup and then restore function?=

=C2=A0

I hope this helps,=C2=A0

Steve=C2=A0

=C2=A0<= /span>


I am not a postgres admi= n expert, but if I had this issue, I'd use pg_dump and pg_restore. I= 9;m not familiar with pg_basebackup, but maybe it is more robust. Basically= I'd run pg_dump (iirc "-F c" will dump in custom/native form= at), install the SAME version of postgres onto a machine with similar hardw= are, create a new database with the same name, then run pg_restore to get t= he data back into that.

Regarding your explain ana= lyze: again I'm no expert, but I really don't understand how a tabl= e with 2500 rows and 18 cols can take 2.5s to enumerate.. So that remains a= very mysterious thing for me. But pulling the data to a new server/databas= e and verifying that the same problem occurs there seems wise.

But regarding the "width" - I believe that's a measure = of all the columns concatenated together, returned in bytes, but I could be= wrong.

The fact that there is a trigger on th= is table is suggestive that there is maybe a locking issue that is interfer= ing with the search. If you can copy the data to a new location, you can di= sable the trigger to verify if that's a big part of the problem..

Steve
--00000000000088a72d06200bec09--