Vacuum and Materialized view refresh slow

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Vacuum and Materialized view refresh slow

Vikas Sharma
Hello There,

We have postgres 9.6 with 3 slaves replicated in synchronous commit.
 we recently migrated to to another cloud provider.  Though we have the same configuration for VMs and postgres, we are experiencing systemwide slowness with writes to database.

I am more concerned about a scheduled script to vacuum and refresh two Materialized views after every 5 minutes. 
Time to refresh MV and vacuum has increased considerably and during this time a simple insert takes 10-15 seconds which normally should take milliseconds whereas with old provider that simple insert used to take not more than couple of seconds during refresh time.

I have seen the vacuum and refresh MV process showing as waiting for a {change location} sometimes for a fraction of seconds.

Could I be enlighted about what I can do to improve the performance to make write faster.

Best regards
Vikas
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum and Materialized view refresh slow

ichbinrene
Have you benchmarked the new infrastructure's IO and network performance? why did you switch providers? was it because of cost?
lup
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum and Materialized view refresh slow

lup


On 11/30/18 1:53 PM, Rene Romero Benavides wrote:
Have you benchmarked the new infrastructure's IO and network performance? why did you switch providers? was it because of cost?
And have you proven to yourself that all the indices are in place, stats are up-to-date?  You didn't by any chance migrate away from SSD?