Vacuum running out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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,

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux