Re: [HACKERS] Performance bug in DO blocks
Robert Haas robertmh...@gmail.com writes: On Tue, Jan 18, 2011 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: I just noticed that if you execute the same DO command over and over within a session, it gets slower and slower. And if you keep it up you'll notice the backend's RAM consumption bloating too. The cause appears to be that we leak the cached plans created for any SQL statements or expressions within the DO command --- the next iteration won't reuse those, but rather create its own set. Probably ought to look into releasing those when the DO block is over. Should we try to do something about this? I don't really understand what's going on here. I thought maybe the problem was that the inline handler was getting called with TopMemoryContext active, but it's not. No, the problem is that (a) the internal function representation produced by plpgsql_compile_inline is never recycled, and (b) the cached plans held by plancache.c are never released. When dealing with an ordinary function it's reasonable to hang onto those things on the expectation that the function will be called again in the session. But for an inline code block it's leakage, plain and simple. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance bug in DO blocks
On Tue, Jan 18, 2011 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: I just noticed that if you execute the same DO command over and over within a session, it gets slower and slower. And if you keep it up you'll notice the backend's RAM consumption bloating too. The cause appears to be that we leak the cached plans created for any SQL statements or expressions within the DO command --- the next iteration won't reuse those, but rather create its own set. Probably ought to look into releasing those when the DO block is over. Should we try to do something about this? I don't really understand what's going on here. I thought maybe the problem was that the inline handler was getting called with TopMemoryContext active, but it's not. It's getting called with the PortalHeapMemory context active, so anything that is allocated without first specifying the context ought to get cleaned up at end-of-statement. So somewhere we're deliberately copying something into a longer-lived memory context where we shouldn't be. Your comment about cached plans got me looking at CreateCachedPlan(), which does this; it's called from SPI_saveplan(), which is called from exec_prepare_plan(). But that's immediately followed by SPI_freeplan(), so I'm all tapped out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Performance bug in DO blocks
I just noticed that if you execute the same DO command over and over within a session, it gets slower and slower. And if you keep it up you'll notice the backend's RAM consumption bloating too. The cause appears to be that we leak the cached plans created for any SQL statements or expressions within the DO command --- the next iteration won't reuse those, but rather create its own set. Probably ought to look into releasing those when the DO block is over. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
