Vacuum running out of memory

From: Jonathan Foy <thefoy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Vacuum running out of memory
Date: 2009-12-08 15:51:14
Message-ID: 4b46b5f00912080751s672758feh45ee4b3809f08095@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello

My vacuums have suddenly started to fail, seemingly at random. I am
confused.

I'm running 8.1.3, with close to a dozen servers, up to 150 databases each.
I have 8GB of RAM. Vacuums have started to fail on all servers (though only
the occasional vacuum) with the following error:

VACUUM,ERROR: out of memory
VACUUM,DETAIL: Failed on request of size 268435452

I have some terrible tables that I inherited for which I recently created
tons of indexes in order to make them useful. I had a post a couple of
weeks ago detailing my problem with trying to get a function working to
simplify the data...I fell back on indexes where the column values were not
null/empty. Since they are almost always null/empty, I was able to
dramatically speed up access without eating up much disk space, but I did
throw an extra 200 indexes into each database. Shortly after I started
getting occasional vacuum failures with the above error.

I'm not sure if it's a coincidence or not, but my maintenance_work_mem is
set to 262144 KB, which matches the failed request size above.

I initially assumed that with 200*150 additional relations, I was messing up
my max_fsm_relations setting, which is 60,000. However, as a test a ran a
verbose vacuum analyze on a small table to get the statistics at the end,
from which I got the following:

INFO: free space map contains 2239943 pages in 28445 relations
DETAIL: A total of 2623552 page slots are in use (including overhead).
2623552 page slots are required to track all free space.
Current limits are: 8000000 page slots, 60000 relations, using 50650 KB.

which seems to indicate I'm well within my limits.

(for curiosity's sake, which relations count towards that limit? From what
I can tell it's only tables and indexes...functions, views, triggers, etc
shouldn't contribute, should they?)

Am I interpreting this wrong? Anyone have any insight as to what is going
wrong? I can provide more information if needed...

Thanks,

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2009-12-08 15:52:22 Re: error occured in dbt2 against with postgresql
Previous Message Lennin Caro 2009-12-08 15:51:13 Re: Optimizing Bitmap Heap Scan.