Subject: Powerfailure caused a reduction in INSERT performance of version 12 database.

From: Frank Komsic <[email-masked]>
Date: Mon, 19 Aug 2024 14:33:04 +0000

HI,

Not sure if this is the place to write to.

We have a database running on version 12.2 of postgresql.  We have experienced an extended power failure and our backup systems did not work as intended resulting in a sudden shutdown of the database server (Windows 2012).  After restarting the system it had performed some automated recovery and the database was functional.  Unfortunately, the database speed had slowed down significantly, particularly in the INSERT commands in an UploadData application we have on our production machines that uploads 139 columns of data.

The data is inserted into partitioned tables and there is a trigger function updating additional tables.  I have noticed that this additional table although small in records (2500 records with 18 columns) it take about 4-6 seconds to visualize the table using PGADMIN 8.10.  The data in this table is being updated by multiple UlpadData applications.  So the last 10 records the date columns are constantly being updated.

Visualizing the data before the latest 15 records it appears withing 1 second or less.  The latest 15 records can take 2-4 seconds to visualize.  Then visualizing the entire table take 5-7 seconds.  This is too long if this occurs for every record that is inserted.  If every record is 2 seconds of data, the application can never keep up with the updates.

Performed a full vacuum on this table with a little improvement.

Is there anything I can do to see what is going on?

Thank you

Frank Komsic
Shoei Canada Coproration
514-949-6319

---

From: Steve Midgley <[email-masked]>
Date: Mon, 19 Aug 2024 07:49:24 -0700

On Mon, Aug 19, 2024, 7:34 AM Frank Komsic <[email-masked]> wrote:

> We have a database running on version 12.2 of postgresql.  We have
> experienced an extended power failure and our backup systems did not work
> as intended resulting in a sudden shutdown of the database server (Windows
> 2012).  After restarting the system it had performed some automated
> recovery and the database was functional.  Unfortunately, the database
> speed had slowed down significantly, particularly in the INSERT commands in
> an UploadData application we have on our production machines that uploads
> 139 columns of data.
>
>
>
> The data is inserted into partitioned tables and there is a trigger
> function updating additional tables.  I have noticed that this additional
> table although small in records (2500 records with 18 columns) it take
> about 4-6 seconds to visualize the table using PGADMIN 8.10.  The data in
> this table is being updated by multiple UlpadData applications.  So the
> last 10 records the date columns are constantly being updated.
>
>
>
> Visualizing the data before the latest 15 records it appears withing 1
> second or less.  The latest 15 records can take 2-4 seconds to visualize.
> Then visualizing the entire table take 5-7 seconds.  This is too long if
> this occurs for every record that is inserted.  If every record is 2
> seconds of data, the application can never keep up with the updates.
>
>
>
> 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'd also check if you lost any indexes you need during the bad day?

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?

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

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.

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

I hope this helps,
Steve

---

From: Frank Komsic <[email-masked]>
Date: Mon, 19 Aug 2024 16:07:23 +0000

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.

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

From: Steve Midgley <[email-masked]>
Date: Monday, August 19, 2024 at 10:49 AM
To: Frank Komsic <[email-masked]>
Cc: pgsql-sql <[email-masked]>
Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database.

On Mon, Aug 19, 2024, 7:34 AM Frank Komsic <[email-masked]<mailto:[email-masked]>> wrote:
HI,

Thank you

Frank Komsic
Shoei Canada Coproration
514-949-6319

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.

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?

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

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.

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

I hope this helps,
Steve

---

From: Frank Komsic <[email-masked]>
Date: Mon, 19 Aug 2024 16:27:01 +0000

HI Steve,

Here is an output of EXPLAIN ANALYZE

"QUERY PLAN"
"Limit  (cost=0.40..6131.45 rows=100 width=217) (actual time=0.047..2425.721 rows=100 loops=1)"
"  ->  Index Scan Backward using noprod_pkey on noprod  (cost=0.40..123970.26 rows=2022 width=217) (actual time=0.045..2425.681 rows=100 loops=1)"
"Planning Time: 0.103 ms"
"Execution Time: 2425.779 ms"

The execution time is an issue.  Here it says 2425 ms but I can say it is more like 4500ms actually.  Also I do not understand the width=217 if there are only 19 columns?

Thanks

Frank Komsic

From: Frank Komsic <[email-masked]>
Date: Monday, August 19, 2024 at 12:09 PM
To: Steve Midgley <[email-masked]>
Cc: pgsql-sql <[email-masked]>
Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database.
Hi Steve,

Steve wrote:

How do I check that?

I hope this helps,
Steve

Thank you

Frank Komsic
Shoei Canada Coproration
514-949-6319

I hope this helps,
Steve

---

From: Frank Komsic <[email-masked]>
Date: Mon, 19 Aug 2024 16:31:44 +0000

Hi Steve,

Additional info with a limit of records to display.

"QUERY PLAN"
"Index Scan Backward using noprod_pkey on prod.noprod  (cost=0.41..124667.02 rows=2148 width=216) (actual time=0.048..2618.970 rows=2259 loops=1)"
"  Output: id, line, noprod, ds, de, dst, det, quota, lab, wsr, nb, comment, metal, noprod_type, product, running, rate_min, rate_max"
"Planning Time: 0.117 ms"
"Execution Time: 2619.433 ms"

Why in the explain there is 2148 rows while there is 2259 rows in the analyze part?

Thanks
Frank

Date: Monday, August 19, 2024 at 12:27 PM

"QUERY PLAN"

Thanks

Frank Komsic

Steve wrote:

How do I check that?

I hope this helps,
Steve

Thank you

Frank Komsic
Shoei Canada Coproration
514-949-6319

I hope this helps,
Steve

---

From: Steve Midgley <[email-masked]>
Date: Mon, 19 Aug 2024 09:37:12 -0700

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

> 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.
>
>
>
> 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?
>
>
>
> 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.
>
>
>

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

---

From: Frank Komsic <[email-masked]>
Date: Mon, 19 Aug 2024 17:16:05 +0000

Thank you Steve,

Will try a few of the suggestions and see if and what works.

Best regards

Frank Komsic

Date: Monday, August 19, 2024 at 12:37 PM

On Mon, Aug 19, 2024 at 9:08 AM Frank Komsic <[email-masked]<mailto:[email-masked]>> wrote:
Hi Steve,

Steve wrote:

How do I check that?

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

---

From: Frank Komsic <[email-masked]>
Date: Tue, 3 Sep 2024 22:14:40 +0000

Hi,

I finally figured out my problem with slow query.  It was related to a replication.  I had a total of three replication slots on the main server.  One of the three was functional and the other two were not running (actually one was test converted to Pgsql 16 from the base 12.2, the other was just off).

After dropping one of the replication slots and started the other replication vm and it updated to the stream, the database started to function normally.

Have any of you experienced this slowdown of the queries due to replications that are way behind?

Thanks

Frank Komsic

Date: Monday, August 19, 2024 at 1:16 PM

Best regards

Frank Komsic

Steve wrote:

How do I check that?

I hope this helps,
Steve

Steve