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

On Mon, Aug 19, 2024, 7:34 AM Frank Komsic <komsicf(at)shoeicanada(dot)com> wrote:

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

>
>