Re: Powerfailure caused a reduction in INSERT performance of version 12 database. - Mailing list pgsql-sql
| From | Steve Midgley |
|---|---|
| Subject | Re: Powerfailure caused a reduction in INSERT performance of version 12 database. |
| Date | |
| Msg-id | CAJexoSKmWCGLYxc-3kQQu=Vh719=kZLHBCwbS4EBYuc8TUA-jA@mail.gmail.com Whole thread Raw |
| In response to | Re: Powerfailure caused a reduction in INSERT performance of version 12 database. (Frank Komsic <komsicf@shoeicanada.com>) |
| Responses |
Re: Powerfailure caused a reduction in INSERT performance of version 12 database.
|
| List | pgsql-sql |
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