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