public inbox for [email protected]  

help / color / mirror / Atom feed

From: Steve Midgley <[email protected]>
To: Frank Komsic <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database.
Date: Mon, 19 Aug 2024 09:37:12 -0700
Message-ID: <CAJexoSKmWCGLYxc-3kQQu=Vh719=kZLHBCwbS4EBYuc8TUA-jA@mail.gmail.com> (raw)
In-Reply-To: <BL3PR05MB9236AA8B09A6458B1513B18EB38C2@BL3PR05MB9236.namprd05.prod.outlook.com>
References: <BL3PR05MB9236F587AD7551AD63B2C65AB3812@BL3PR05MB9236.namprd05.prod.outlook.com>
	<CAJexoSJ2ajT8nF3E+t630qqzDZxoPURTWH1d5uXTvqSxMuVKHw@mail.gmail.com>
	<BL3PR05MB9236AA8B09A6458B1513B18EB38C2@BL3PR05MB9236.namprd05.prod.outlook.com>

On Mon, Aug 19, 2024 at 9:08 AM Frank Komsic <[email protected]>
wrote:

> Hi Steve,
>
>
>
> Thank you for your suggestions.
>
>
>
>
>
> 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 group. Then I wonder if running
> vacuum analyze would be useful? Maybe the planner is doing something weird.
>
>
>
> 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 swap
> performing normally?
>
>
>
> Need to see and verify that… do not have historical data on the
> performance.  Is there a way to get historicals or does it require a third
> party software?
>
>
>
> Are other tables unaffected, somewhat affected or in the same situation as
> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database.
  In-Reply-To: <CAJexoSKmWCGLYxc-3kQQu=Vh719=kZLHBCwbS4EBYuc8TUA-jA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox