Thread: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
From
Vikas Sharma
Date:
Hi All,
I am looking for advice in a issue where two materialized views are being refreshed concurrently and dbsize has grown to 150gb from 4gb in two days.
We use two materialized views to keep processed data for faster query results for a search function. Earlier materialized views were refreshed not concurrently and all was good on DB.
We changed mv refresh to concurrently to take advantage of simultaneous access when mv refreshed. Now the refresh takes slightly longer and but DB size has grown exponentially.
I ran full vacuum on DB and size again reduced to 4gb from 150gb.
We need to refresh mvs every 5 mins so I created a script to refresh MV concurrently and then running vacuum (full,analyze) on the mv immediately. I hoped it would solve the issue of DB size growing exponentially but it hasn't and size still growing.
Please advice how can I refresh MV concurrently and DB size doesn't grow.
Much appreciated.
Regards
Vikas
Re: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
From
Alban Hertroys
Date:
> On 25 Jun 2018, at 19:21, Vikas Sharma <shavikas@gmail.com> wrote: > > I am looking for advice in a issue where two materialized views are being refreshed concurrently and dbsize has grown to150gb from 4gb in two days. > > We use two materialized views to keep processed data for faster query results for a search function. Earlier materializedviews were refreshed not concurrently and all was good on DB. > > We changed mv refresh to concurrently to take advantage of simultaneous access when mv refreshed. Now the refresh takesslightly longer and but DB size has grown exponentially. > > I ran full vacuum on DB and size again reduced to 4gb from 150gb. You did not disable or tune down autovacuum perchance? With materialized view refreshes that often, you probably need fairly aggressive autovacuuming on that table - you can tuneautovacuum parameters per table (see Storage parameters). That probably won't put you at 4GB, more around double thatsize, but it should stay a reasonable size that way. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
From
Vikas Sharma
Date:
Hi Alban,
I haven't disabled autovacuum task, it's running fine for other objects.
I was also getting "Error: cancelling autovacuum task" on the materialized view when concurrently refreshed so decided to write a script and run vacuum (full, analyze) the MV immediately after concurrent refresh but still it's not working as intended.
On Mon, Jun 25, 2018, 20:02 Alban Hertroys <haramrae@gmail.com> wrote:
> On 25 Jun 2018, at 19:21, Vikas Sharma <shavikas@gmail.com> wrote:
>
> I am looking for advice in a issue where two materialized views are being refreshed concurrently and dbsize has grown to 150gb from 4gb in two days.
>
> We use two materialized views to keep processed data for faster query results for a search function. Earlier materialized views were refreshed not concurrently and all was good on DB.
>
> We changed mv refresh to concurrently to take advantage of simultaneous access when mv refreshed. Now the refresh takes slightly longer and but DB size has grown exponentially.
>
> I ran full vacuum on DB and size again reduced to 4gb from 150gb.
You did not disable or tune down autovacuum perchance?
With materialized view refreshes that often, you probably need fairly aggressive autovacuuming on that table - you can tune autovacuum parameters per table (see Storage parameters). That probably won't put you at 4GB, more around double that size, but it should stay a reasonable size that way.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Re: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
From
Jeff Janes
Date:
On Mon, Jun 25, 2018 at 1:21 PM, Vikas Sharma <shavikas@gmail.com> wrote:
Hi All,I am looking for advice in a issue where two materialized views are being refreshed concurrently and dbsize has grown to 150gb from 4gb in two days.We use two materialized views to keep processed data for faster query results for a search function. Earlier materialized views were refreshed not concurrently and all was good on DB.
Where is the space going? Does it show up in psql with \l+ ? Does is show up in \dm+ ? Does it only show up using OS tools in the data directory? Which subdirectory of the data directory?
Cheers,
Jeff