out of memory error when trying to vacuum/analyze

[six4withafro]

as the title states, i'm encountering this error when i try to vacuum analyze my db.

should i allocate less, or more, memory to postgresql through the conf file?

any other ideas?

[ptrack pat]

I've never seen that issue before with PostgreSQL. I'm not sure what settings to tell you to alter in the conf file. Have you tried using pg_Admin to do the v/a directly? You just connect, right-click on your database and select "Maintenance" and you can do the vacuum/analyze from there. If that still gives an error, you can drill down to the table level and do a v/a on the individual tables. It's probably the "game_players" table that is causing it because that's the largest table in the db in terms of columns and rows.

[six4withafro]

i tried to do the maintenance through pgadmin, and encountered the same error at this point:

INFO: analyzing "public.game_players"
INFO: "game_players": scanned 3000 of 128881 pages, containing 103609 live rows and 0 dead rows; 3000 rows in sample, 4451077 estimated total rows
INFO: vacuuming "public.hand_histories"

ERROR: out of memory
DETAIL: Failed on request of size 531144000.


i clustered this db in the last 2 weeks, and was able to vacuum full/analyze then, so it's not a critical issue at this point, but it is awfully curious. there are only 4.5M hands in the db (as defined in the totals at the bottom of the page in the summary tab), and i know pt2 can handle more than that. perhaps i need to increase the maintenance work mem, or effective cache? my shared buffers is set at 1156MB and my effective cache is 1536MB, but the maintenance work mem is only 512MB. still, i've done this operation on larger dbs with less assigned maintenance mem in the past.

[ptrack pat]

So it looks like it's failing on the hand_histories table. Did you try to just vacuum that table by itself? I'm not sure if that table is somehow corrupted because, like I said, I've never seen this error before in the entire time since I added the PostgreSQL option. Another thing you might try is to reindex the hand_histories table first and then try to vacuum it. To re-index, just right-click on the hand_histories table and select Maintenance. Then click the "Reindex" option. If that works, then do the vacuum option.