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,
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. |