[HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
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. Both from that transaction and random others. In additional observation of interest to us was that while both VIRT and RES was growing VIRT was always roughly double of RES. Which seems to indicate that whatever allocations were done not all of the memory allocated was actually touched (server is a Centos6 box). So I have two questions: - 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 - Any recommendations on the use of overcommit? We had it disabled on the assumption that with overcommit the OOM killer might kill a random process and that it is better to instead have a process that is actually allocating fail (and on the additional assumption that any memory allocated by postgres would actually be touched). This is not a huge production issue for us as we can fix the program to no longer issue huge numbers of selects. But we would like to understand. Thank you very much, Bene PS: The machine has huge amounts of memory and during normal operations it looks like this: -bash-4.1$ cat /proc/meminfo MemTotal: 49413544 kB MemFree: 1547604 kB Buffers:5808 kB Cached: 43777988 kB SwapCached:0 kB Active: 18794732 kB Inactive: 27309980 kB Active(anon): 13786796 kB Inactive(anon): 1411296 kB Active(file):5007936 kB Inactive(file): 25898684 kB Unevictable: 71928 kB Mlocked: 55576 kB SwapTotal: 2047992 kB SwapFree:2047992 kB Dirty: 12100 kB Writeback: 79684 kB AnonPages: 2393372 kB Mapped: 12887392 kB Shmem: 12871676 kB Slab:1050468 kB SReclaimable: 190068 kB SUnreclaim: 860400 kB KernelStack:4832 kB PageTables: 450584 kB NFS_Unstable: 23312 kB Bounce:0 kB WritebackTmp: 0 kB CommitLimit:26754764 kB Committed_AS: 17394312 kB VmallocTotal: 34359738367 kB VmallocUsed: 120472 kB VmallocChunk: 34333956900 kB HardwareCorrupted: 0 kB AnonHugePages: 1599488 kB HugePages_Total: 0 HugePages_Free:0 HugePages_Rsvd:0 HugePages_Surp:0 Hugepagesize: 2048 kB DirectMap4k:5604 kB DirectMap2M: 2078720 kB DirectMap1G:48234496 kB -- 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
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
First of all thanks to everyone who has replied so far. On Fri, Jul 20, 2012 at 10:04 AM, Andres Freund and...@2ndquadrant.com 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
On Fri, Jul 20, 2012 at 9:10 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com 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
On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann bgrundm...@janestreet.com 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 10:56 AM, Benedikt Grundmann bgrundm...@janestreet.com wrote: On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann bgrundm...@janestreet.com 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
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
