Good morning,
First the stats: I'm using PostgreSQL 8.0.1 (I know I should upgrade, cannot due to vendor app. restrictions...), RedHat 9 on a SUN V40Z with 8GB of memory. I'm using the "out-of-the-box" settings in postgresql.conf. I've been testing various changes but cannot increase anything to improve performance till I get this memory leak and/or cache issue resolved.
Scenario: Last night the backup of my largest DB failed (4.4GB in size with 44Million+ tuples) with a memory alloc error. I'll attach it at the end of this email. Once we rebooted the box and freed memory all was well, the backup completed fine but as the backup ran and I did a few minor queries all of a sudden 3+GB of memory was used up! I then performed my nightly vacuumdb with analyze and just about the remaining 4GB of memory was gone! This was the only application running in the machine at the time.
Questions:
1. I thought using such "smallish" setting as provided would cause postgres to go to swap instead of eating up all the memory?
2. If PostgreSQL is the culprit (which I hope it is not) does postgres release any memory it assumes during processing when that processing is complete? Such as the backup and vacuumdb I mentioned?
3. Does anyone know of a way to determine if it actually is postgres hogging this memory? Using TOP I only see my postgres processes using 1% or 2% of memory. It would be nice to have a tool that showed exactly what is eating up that 7+GB?
4. IS this due to my low setting in postgresql.conf?
Any and all help is welcomed. For you PostgreSQL purists out there of whom I am fast becoming, your help is needed as my company is considering dumping postgresql in favor of Oracle.....I would much rather figure out the issue then switch DBs. Here is the error received from the failed backup and the second was noted in my pg_log file:
pg_dump: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: SQL command to dump the contents of table "msgstate" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.msgstate (id, connectormsgid, parentid, orderidfk, clordid, orgclordid, msg, rawmsg, msgtype, "action", sendstate, statechain, fromdest, todest, inserted, op_id, released, reason, outgoing, symbol, qty, price, stopprice, side, data1, data2, data3, data4, data5) TO stdout;
2006-05-04 18:04:58 EDT USER=postgres DB=FIX1 [12427] PORT = [local] ERROR: invalid memory alloc request size 18446744073709551613
Thank you,
Tim McElroy
