Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
Hi, On Friday, July 20, 2012 11:56:09 AM Benedikt Grundmann wrote: > I also noticed just know that all TopMemoryContext's after the first one > look significantly different. They contain large PortalMemory sections. > Are those related to cursors? Yes. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
On Fri, Jul 20, 2012 at 10:56 AM, Benedikt Grundmann wrote: > On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann > wrote: >> > Actually I believe this must be it. I just went back and checked the library > and it does not CLOSE the cursors. This is normally not a problem as most > transactions we have run one or two queries only... I'll patch the library > to CLOSE the cursor when all the data has been delivered and test if the > error does not happen then. > Just to confirm that indeed fixed it. Sorry for the noise. Bene -- 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] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann wrote: > > DECLARE sqmlcursor51587 CURSOR FOR select > entry_time,source,bad_fields,isin,sedol,cusip,bloomberg,reuters,exchange_code,currency,description,bbg_instrument_type,instrument_type,specifics,definer,primary_exchange,is_primary_security,is_primary_listing,tags,bloomberg_id,status > from vw_instruments_v7 where jane_symbol = E'FOO BAR' and true and > effective_until = (select max(effective_until) from > vw_instruments_v7)" > > Sorry I imagine that the fact that this generates a cursor every time > is important > but it had honestly escaped my attention, because the library we use to query > the database uses CURSORs basically for every select, so that it can process > the data in batches (in this particular case that is conceptually unnecessary > as > the query will only return one row, but the library does not know that). > Actually I believe this must be it. I just went back and checked the library and it does not CLOSE the cursors. This is normally not a problem as most transactions we have run one or two queries only... I'll patch the library to CLOSE the cursor when all the data has been delivered and test if the error does not happen then. I also noticed just know that all TopMemoryContext's after the first one look significantly different. They contain large PortalMemory sections. Are those related to cursors? TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8380416 total in 10 blocks; 3345088 free (34 chunks); 5035328 used PortalMemory: 16769024 total in 11 blocks; 2737280 free (15 chunks); 14031744 used PortalHeapMemory: 56320 total in 9 blocks; 4320 free (0 chunks); 52000 used ExecutorState: 57344 total in 3 blocks; 15248 free (3 chunks); 42096 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalHeapMemory: 56320 total in 9 blocks; 4320 free (0 chunks); 52000 used ExecutorState: 57344 total in 3 blocks; 15248 free (3 chunks); 42096 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ... Thanks everyone, Bene -- 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] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
On Fri, Jul 20, 2012 at 9:10 AM, Heikki Linnakangas wrote: > On 20.07.2012 10:19, Benedikt Grundmann wrote: >> >> We yesterday encountered a program that in a degenerate case issued in >> a single transaction a huge number of selects (in a single transaction >> but each select in a separate call to PGExec) (huge = ~ 400,000). >> >> That transaction would continue to eat memory up until a point where >> calls to malloc (in aset.c) would fail and log for example: >> >> ,"out of memory","Failed on request of size 11." >> >> ... >> >> >> - Is that expected expected behaviour? The transaction was >> in READ_COMMITED mode, and my best guess is that this implies >> that some snapshot is taken before each subselect and all >> of them are only freed once the transaction is finished > > > In more complicated scenarios, with e.g subtransactions, it's normal that > there's some growth in memory usage like that. But with simple SELECTs, I > don't think there should be. > > Can you write a simple self-contained test script that reproduces this? That > would make it easier to see where the memory is going. > Assuming that it isn't obvious now that I realized that we generate a cursor every time, I will give it a shot otherwise. > PS, you should upgrade, the latest minor version in 8.4.x series is 8.4.12. > If possible, upgrading to a more recent major version would be a good idea > too. I don't know if it will help with this problem, but it might.. > We are in fact automatically doing an upgrade in testing to 9.1 every day at the moment. We plan to pull the trigger in production in a few weeks. Thanks, Bene -- 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] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
First of all thanks to everyone who has replied so far. On Fri, Jul 20, 2012 at 10:04 AM, Andres Freund wrote: > > Hi, > > On Friday, July 20, 2012 09:19:31 AM Benedikt Grundmann wrote: > > We yesterday encountered a program that in a degenerate case > > issued in a single transaction a huge number of selects (in a > > single transaction but each select in a separate call to PGExec) > > (huge = ~ 400,000). > > That transaction would continue to eat memory up until a point > > where calls to malloc (in aset.c) would fail and log for example: > > ,"out of memory","Failed on request of size 11." > Could you show us the different statements youre running in that transaction? They all look like this: DECLARE sqmlcursor51587 CURSOR FOR select entry_time,source,bad_fields,isin,sedol,cusip,bloomberg,reuters,exchange_code,currency,description,bbg_instrument_type,instrument_type,specifics,definer,primary_exchange,is_primary_security,is_primary_listing,tags,bloomberg_id,status from vw_instruments_v7 where jane_symbol = E'FOO BAR' and true and effective_until = (select max(effective_until) from vw_instruments_v7)" Sorry I imagine that the fact that this generates a cursor every time is important but it had honestly escaped my attention, because the library we use to query the database uses CURSORs basically for every select, so that it can process the data in batches (in this particular case that is conceptually unnecessary as the query will only return one row, but the library does not know that). > Are you using any user defined functions, deferred foreign keys, or anything > like that? No there are several versions of the above view and while the one mentioned above contains calls to regexp_replace I can reproduce the same behaviour with a different version of the view that just renames columns of the underlying table. > After youve got that "out of memory" message, the log should show > a list of memory contexts with the amount of memory allocated in > each. Could you include that in a mail? We are using csv logging, which through me off for a moment because I couldn't find it in there. But indeed in the .log file I see memory contexts but I don't know how to correlate them. I assume they only get written when out of memory happens, so I have included below the very first one. TopMemoryContext: 268528136 total in 31 blocks; 37640 free (160 chunks); 268490496 used TopTransactionContext: 24576 total in 2 blocks; 14872 free (4 chunks); 9704 used Local Buffer Lookup Table: 2088960 total in 8 blocks; 234944 free (25 chunks); 1854016 used Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used PL/PgSQL function context: 24576 total in 2 blocks; 14384 free (14 chunks); 10192 used CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used Rendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PLpgSQL function cache: 24520 total in 2 blocks; 3744 free (0 chunks); 20776 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used MessageContext: 131072 total in 5 blocks; 54792 free (5 chunks); 76280 used smgr relation table: 24576 total in 2 blocks; 5696 free (4 chunks); 18880 used TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 8192 total in 1 blocks; 7888 free (1 chunks); 304 used PortalHeapMemory: 1024 total in 1 blocks; 848 free (0 chunks); 176 used ExecutorState: 65600 total in 4 blocks; 33792 free (18 chunks); 31808 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 24576 total in 2 blocks; 11792 free (3 chunks); 12784 used CacheMemoryContext: 1342128 total in 21 blocks; 290016 free (11 chunks); 1052112 used idx_raw_cfd_bear_commodity_position_records_position_date: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used CachedPlan: 3072 total in 2 blocks; 2008 free (2 chunks); 1064 used CachedPlanSource: 3072 total in 2 blocks; 1656 free (0 chunks); 1416 used SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used CachedPlan: 3072 total in 2 blocks; 1984 free (1 chunks); 1088 used CachedPlanSource: 3072 total in 2 blocks; 1696 free (0 chunks); 1376 used SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used CachedPlanSource: 3072 total in 2 blocks; 1584 free (0 chunks); 1488 used SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used CachedPlan: 1024 total in 1 blocks; 304 free (0 chunks); 720 used CachedPlanSource: 3072 total in 2 blocks; 1960 free (3 chunks); 1112 used SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used CachedPlan: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
Hi, On Friday, July 20, 2012 09:19:31 AM Benedikt Grundmann wrote: > We yesterday encountered a program that in a degenerate case issued in > a single transaction a huge number of selects (in a single transaction > but each select in a separate call to PGExec) (huge = ~ 400,000). > > That transaction would continue to eat memory up until a point where > calls to malloc (in aset.c) would fail and log for example: > > ,"out of memory","Failed on request of size 11." Could you show us the different statements youre running in that transaction? Are you using any user defined functions, deferred foreign keys, or anything like that? After youve got that "out of memory" message, the log should show a list of memory contexts with the amount of memory allocated in each. Could you include that in a mail? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
On 20.07.2012 10:19, Benedikt Grundmann wrote: We yesterday encountered a program that in a degenerate case issued in a single transaction a huge number of selects (in a single transaction but each select in a separate call to PGExec) (huge = ~ 400,000). That transaction would continue to eat memory up until a point where calls to malloc (in aset.c) would fail and log for example: ,"out of memory","Failed on request of size 11." ... - Is that expected expected behaviour? The transaction was in READ_COMMITED mode, and my best guess is that this implies that some snapshot is taken before each subselect and all of them are only freed once the transaction is finished In more complicated scenarios, with e.g subtransactions, it's normal that there's some growth in memory usage like that. But with simple SELECTs, I don't think there should be. Can you write a simple self-contained test script that reproduces this? That would make it easier to see where the memory is going. PS, you should upgrade, the latest minor version in 8.4.x series is 8.4.12. If possible, upgrading to a more recent major version would be a good idea too. I don't know if it will help with this problem, but it might.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
