Thread: Memory Usage
I am having a small issue with PostgreSQL 7.0.3 on FreeBSD 4.2 Stable. When I perform an update on a table with roughly 2 million rows, the postgres process starts eating up memory until it eventually uses up all of the memory and exits without finishing. I have also seen the same thing happen in complex SELECT statements that I have run. The update statement is as follows: UPDATE pages SET createdtime = NOW(); Is there a reason why this would take up all of the memory?? Also the select statement that caused the issue was a SELECT and GROUP BY that was in the form of: SELECT COUNT(*) AS hitcount, date_part('yy', createdtime) AS Year, date_part('mm', createdtime) AS Month, date_part('dd', createdtime) AS Day FROM Log group by Year, Month, Day I found a better way to do this afterwards, but why wouldn't the database utilize temporary tables to complete this query without using all the RAM? ++++++++++++++++++++++++ +Nathan Barnett +Sr. Consultant +Centuries Consulting, Inc. ++++++++++++++++++++++++
Nathan Barnett <nbarnett@centuries.com> writes: > UPDATE pages SET createdtime = NOW(); > Is there a reason why this would take up all of the memory?? The now() function invocation leaks memory ... only a dozen or so bytes per invocation, but that adds up over millions of rows :-(. In 7.0.* the memory isn't recovered until end of statement. 7.1 fixes this by recovering temporary memory after each tuple. You'll see the same behavior for functions and operators on any pass-by-reference datatype, not just timestamp. regards, tom lane
Tom Lane wrote: : Nathan Barnett <nbarnett@centuries.com> writes: : > UPDATE pages SET createdtime = NOW(); : : > Is there a reason why this would take up all of the memory?? : : The now() function invocation leaks memory ... only a dozen or so bytes : per invocation, but that adds up over millions of rows :-(. In 7.0.* : the memory isn't recovered until end of statement. 7.1 fixes this by : recovering temporary memory after each tuple. As I can see this is not that simple :-( On UPDATE -- maybe, but not on SELECT. When SELECT is executing Postgres (7.0.3) allocate how many memory as need for store full result set of query. On select * from some_big_table; this can be in some times more than "physical memory + swap" exist. :-( In general case I can't disable executing of this (and similar) queries for users. Question: Can I say postmaster (or postgres backend) don't use more than some number of memory (in per-backend basis or for all running backends totally -- no difference) and when this limit will be exceed -- switch to using temporary files or simple rollback transaction and close connection if using temporary files is impossible? (Yes, I mean what bring down one postrgres process is more cheap that bring down or hang up all machine.) Any ideas/workarounds? -- Andrew W. Nosenko (awn@bcs.zp.ua)
Tom Lane wrote: : Andrew Nosenko <awn@bcs.zp.ua> writes: : > When SELECT is executing Postgres (7.0.3) allocate how many memory as : > need for store full result set of query. : : The *client* does that (if it uses libpq). Not the server. A user can : only crash his own client if he retrieves an oversize result set. : Thanks for explanation. -- Andrew W. Nosenko (awn@bcs.zp.ua)
Andrew Nosenko <awn@bcs.zp.ua> writes: > When SELECT is executing Postgres (7.0.3) allocate how many memory as > need for store full result set of query. The *client* does that (if it uses libpq). Not the server. A user can only crash his own client if he retrieves an oversize result set. regards, tom lane