Re: possible memory leak in VACUUM ANALYZE - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: possible memory leak in VACUUM ANALYZE |
| Date | |
| Msg-id | CAFj8pRAU0QtKxn96sXZ5er6Tw9vqkG3kDVDbsfS7CBCa4DHCEA@mail.gmail.com Whole thread Raw |
| In response to | Re: possible memory leak in VACUUM ANALYZE (Justin Pryzby <pryzby@telsasoft.com>) |
| List | pgsql-hackers |
so 11. 2. 2023 v 7:53 odesílatel Justin Pryzby <pryzby@telsasoft.com> napsal:
On Sat, Feb 11, 2023 at 07:06:45AM +0100, Pavel Stehule wrote:
> pá 10. 2. 2023 v 23:01 odesílatel Justin Pryzby <pryzby@telsasoft.com> napsal:
> > On Fri, Feb 10, 2023 at 09:23:11PM +0100, Pavel Stehule wrote:
> > > pá 10. 2. 2023 v 21:18 odesílatel Andres Freund <andres@anarazel.de> napsal:
> > > > On 2023-02-10 21:09:06 +0100, Pavel Stehule wrote:
> > > > > Just a small note - I executed VACUUM ANALYZE on one customer's database,
> > > > > and I had to cancel it after a few hours, because it had more than 20GB RAM
> > > > > (almost all physical RAM).
> > > >
> > > > Just to make sure: You're certain this was an actual memory leak, not just
> > > > vacuum ending up having referenced all of shared_buffers? Unless you use huge
> > > > pages, RSS increases over time, as a process touched more and more pages in
> > > > shared memory. Of course that couldn't explain rising above
> > > > shared_buffers + overhead.
> > > >
> > > > > The memory leak is probably not too big. This database is a little bit
> > > > > unusual. This one database has more than 1 800 000 tables. and the same
> > > > > number of indexes.
> > > >
> > > > If you have 1.8 million tables in a single database, what you saw might just
> > > > have been the size of the relation and catalog caches.
> > >
> > > can be
> >
> > Well, how big was shared_buffers on that instance ?
>
> 20GB RAM
> 20GB swap
> 2GB shared buffers
Thanks; so that can't explain using more than 2GB + a bit of overhead.
Can you reproduce the problem and figure out which relation was being
processed, or if the memory use is growing across relations?
pg_stat_progress_analyze/vacuum would be one thing to check.
Does VACUUM alone trigger the issue ? What about ANALYZE ?
I executed VACUUM and ANALYZE separately, and memory grew up in both cases with similar speed.
almost all tables has less than 120 pages, and less than thousand tuples
and almost all tables has twenty fields + one geometry field + gist index
the size of pg_attribute has 6GB and pg_class has about 2GB
Unfortunately, that is customer's production server, and I have not full access there so I cannot to do deeper investigation
Was parallel vacuum happening (are there more than one index per table) ?
probably not - almost all tables are small
Do you have any extended stats objects or non-default stats targets ?
What server version is it? What OS? Extensions? Non-btree indexes?
PostgreSQL 14 on linux with installed PostGIS
BTW I'm interested about this because I have an VM instance running v15
which has been killed more than a couple times in the last 6 months, and
I haven't been able to diagnose why. But autovacuum/analyze could
explain it. On this one particular instance, we don't have many
relations, though...
--
Justin
pgsql-hackers by date: