Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 480F01337B71 for ; Mon, 8 Nov 2010 11:02:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 65966-10 for ; Mon, 8 Nov 2010 15:02:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id B60541337B55 for ; Mon, 8 Nov 2010 11:02:03 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id 794DA348023; Mon, 8 Nov 2010 07:02:01 -0800 (PST) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id lsKGoFTxqvq9; Mon, 8 Nov 2010 07:01:58 -0800 (PST) Received: from perhan.alvh.no-ip.org (unknown [190.95.28.71]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 07AE9348022; Mon, 8 Nov 2010 07:01:58 -0800 (PST) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 204516E9BC; Mon, 8 Nov 2010 12:01:51 -0300 (CLST) Cc: Nick , Kevin Grittner , pgsql-admin Subject: Re: autovacuum launcher process eating up 17G+ of ram? From: Alvaro Herrera To: Tom Lane In-reply-to: <4232.1288036630@sss.pgh.pa.us> References: <4CC071670200002500036C8A@gw.wicourts.gov> <4CC18EF60200002500036CF3@gw.wicourts.gov> <4232.1288036630@sss.pgh.pa.us> Date: Mon, 08 Nov 2010 12:01:51 -0300 Message-Id: <1289227352-sup-6579@alvh.no-ip.org> User-Agent: Sup/git Content-Transfer-Encoding: 8bit MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-1289228511-98887-3958-4072-1-=" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/60 X-Sequence-Number: 36026 --=-1289228511-98887-3958-4072-1-= Content-Type: text/plain; charset=UTF-8 Content-Disposition: inline Excerpts from Tom Lane's message of lun oct 25 16:57:10 -0300 2010: > It looks to me like the problem is that get_database_list() > intentionally pushes its result into a long-lived context, and then the > result is never cleaned up. It doesn't help any that it does a lot of > leaky things like heap_beginscan with CurrentMemoryContext totally > unnecessarily pointing at the long-lived context. > > I'm not sure what the intent was here, but it's pretty broken as-is. > Alvaro, what did you have in mind? Should this be using a less > long-lived context, or do we need code to free an avw_dbase list? Sorry for not noticing this earlier. Seems my mail filters are broken :-( Thanks Tom for the ping via private email. Yes, get_database_list is clearly broken as is. I don't remember why it's coded like this; this memcxt shuffling was introducing in the 9.0 rewrite to read pg_database directly instead of the flat file. The right way for it to work seems to be to allocate the output list in the caller's context; both current callers are in a throwaway context and so the allocated list will go away automatically when they're done with the list. Patch for this is attached (applies cleanly to 9.0 and HEAD; 8.4 doesn't need to be patched, but I'll run the test case now just to be sure.) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support --=-1289228511-98887-3958-4072-1-= Content-Disposition: attachment; filename="0001-Fix-permanent-memory-leak-in-autovacuum-launcher.patch" Content-Type: application/octet-stream; name="0001-Fix-permanent-memory-leak-in-autovacuum-launcher.patch" Content-Transfer-Encoding: base64 RnJvbSBjNWM2MTcwYzJlOWQ0NTU1ZjBmY2IxNWFmYTZkN2U0NzU5NTU3MmQw IE1vbiBTZXAgMTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBBbHZhcm8gSGVycmVy YSA8YWx2aGVycmVAYWx2aC5uby1pcC5vcmc+CkRhdGU6IE1vbiwgOCBOb3Yg MjAxMCAxMToyODowOSAtMDMwMApTdWJqZWN0OiBbUEFUQ0hdIEZpeCBwZXJt YW5lbnQgbWVtb3J5IGxlYWsgaW4gYXV0b3ZhY3V1bSBsYXVuY2hlcgoKZ2V0 X2RhdGFiYXNlX2xpc3Qgd2FzIHVzZWxlc3NseSBhbGxvY2F0aW5nIGl0cyBv dXRwdXQgZGF0YSwgYWxvbmcgc29tZQpjcmVhdGVkIGFsb25nIHRoZSB3YXks IGluIGEgcGVybWFuZW50IG1lbW9yeSBjb250ZXh0LiAgVGhpcyBkaWRuJ3QK bWF0dGVyIHdoZW4gYXV0b3ZhY3V1bSB3YXMgYSBzaW5nbGUsIHNob3J0LWxp dmVkIHByb2Nlc3MsIGJ1dCBub3cgdGhhdAp0aGUgbGF1bmNoZXIgaXMgcGVy bWFuZW50LCBpdCBzaG93cyB1cCBhcyBhIHBlcm1hbmVudCBsZWFrLgoKVG8g Zml4LCBtYWtlIGdldF9kYXRhYmFzZSBsaXN0IGFsbG9jYXRlIGl0cyBvdXRw dXQgZGF0YSBpbiB0aGUgY2FsbGVyJ3MKY29udGV4dCwgd2hpY2ggaXMgaW4g Y2hhcmdlIG9mIGZyZWVpbmcgaXQgd2hlbiBhcHByb3ByaWF0ZTsgYW5kIHRo ZQptZW1vcnkgbGVha2VkIGJ5IGhlYXBfYmVnaW5zY2FuIGV0IGFsIGlzIGFs bG9jYXRlZCBpbiBhIHRocm93YXdheQp0cmFuc2FjdGlvbiBjb250ZXh0Lgot LS0KIHNyYy9iYWNrZW5kL3Bvc3RtYXN0ZXIvYXV0b3ZhY3V1bS5jIHwgICAy MCArKysrKysrKysrKysrKysrKy0tLQogMSBmaWxlcyBjaGFuZ2VkLCAxNyBp bnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3Ny Yy9iYWNrZW5kL3Bvc3RtYXN0ZXIvYXV0b3ZhY3V1bS5jIGIvc3JjL2JhY2tl bmQvcG9zdG1hc3Rlci9hdXRvdmFjdXVtLmMKaW5kZXggZTcxNzY0NC4uMjJj YWZkNSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRv dmFjdXVtLmMKKysrIGIvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRvdmFj dXVtLmMKQEAgLTE3NTQsNiArMTc1NCw5IEBAIGF1dG92YWNfYmFsYW5jZV9j b3N0KHZvaWQpCiAgKiBnZXRfZGF0YWJhc2VfbGlzdAogICoJCVJldHVybiBh IGxpc3Qgb2YgYWxsIGRhdGFiYXNlcyBmb3VuZCBpbiBwZ19kYXRhYmFzZS4K ICAqCisgKiBUaGUgbGlzdCBhbmQgYXNzb2NpYXRlZCBkYXRhIGlzIGFsbG9j YXRlZCBpbiB0aGUgY2FsbGVyJ3MgbWVtb3J5IGNvbnRleHQsCisgKiB3aGlj aCBpcyBpbiBjaGFyZ2Ugb2YgZW5zdXJpbmcgdGhhdCBpdCdzIHByb3Blcmx5 IGNsZWFuZWQgdXAgYWZ0ZXJ3YXJkcy4KKyAqCiAgKiBOb3RlOiB0aGlzIGlz IHRoZSBvbmx5IGZ1bmN0aW9uIGluIHdoaWNoIHRoZSBhdXRvdmFjdXVtIGxh dW5jaGVyIHVzZXMgYQogICogdHJhbnNhY3Rpb24uICBBbHRob3VnaCB3ZSBh cmVuJ3QgYXR0YWNoZWQgdG8gYW55IHBhcnRpY3VsYXIgZGF0YWJhc2UgYW5k CiAgKiB0aGVyZWZvcmUgY2FuJ3QgYWNjZXNzIG1vc3QgY2F0YWxvZ3MsIHdl IGRvIGhhdmUgZW5vdWdoIGluZnJhc3RydWN0dXJlCkBAIC0xNzY2LDYgKzE3 NjksMTAgQEAgZ2V0X2RhdGFiYXNlX2xpc3Qodm9pZCkKIAlSZWxhdGlvbgly ZWw7CiAJSGVhcFNjYW5EZXNjIHNjYW47CiAJSGVhcFR1cGxlCXR1cDsKKwlN ZW1vcnlDb250ZXh0CWN4dDsKKworCS8qIFRoaXMgaXMgdGhlIGNvbnRleHQg dGhhdCB3ZSB3aWxsIGFsbG9jYXRlIG91ciBvdXRwdXQgZGF0YSBpbiAqLwor CWN4dCA9IEN1cnJlbnRNZW1vcnlDb250ZXh0OwogCiAJLyoKIAkgKiBTdGFy dCBhIHRyYW5zYWN0aW9uIHNvIHdlIGNhbiBhY2Nlc3MgcGdfZGF0YWJhc2Us IGFuZCBnZXQgYSBzbmFwc2hvdC4KQEAgLTE3NzcsOSArMTc4NCw2IEBAIGdl dF9kYXRhYmFzZV9saXN0KHZvaWQpCiAJU3RhcnRUcmFuc2FjdGlvbkNvbW1h bmQoKTsKIAkodm9pZCkgR2V0VHJhbnNhY3Rpb25TbmFwc2hvdCgpOwogCi0J LyogQWxsb2NhdGUgb3VyIHJlc3VsdHMgaW4gQXV0b3ZhY01lbUN4dCwgbm90 IHRyYW5zYWN0aW9uIGNvbnRleHQgKi8KLQlNZW1vcnlDb250ZXh0U3dpdGNo VG8oQXV0b3ZhY01lbUN4dCk7Ci0KIAlyZWwgPSBoZWFwX29wZW4oRGF0YWJh c2VSZWxhdGlvbklkLCBBY2Nlc3NTaGFyZUxvY2spOwogCXNjYW4gPSBoZWFw X2JlZ2luc2NhbihyZWwsIFNuYXBzaG90Tm93LCAwLCBOVUxMKTsKIApAQCAt MTc4Nyw2ICsxNzkxLDE1IEBAIGdldF9kYXRhYmFzZV9saXN0KHZvaWQpCiAJ ewogCQlGb3JtX3BnX2RhdGFiYXNlIHBnZGF0YWJhc2UgPSAoRm9ybV9wZ19k YXRhYmFzZSkgR0VUU1RSVUNUKHR1cCk7CiAJCWF2d19kYmFzZSAgKmF2ZGI7 CisJCU1lbW9yeUNvbnRleHQJb2xkY3h0OworCisJCS8qCisJCSAqIEFsbG9j YXRlIG91ciByZXN1bHRzIGluIHRoZSBjYWxsZXIncyBjb250ZXh0LCBub3Qg dGhlIHRyYW5zYWN0aW9uJ3MuCisJCSAqIFdlIGRvIHRoaXMgaW5zaWRlIHRo ZSBsb29wLCBhbmQgcmVzdG9yZSB0aGUgb3JpZ2luYWwgY29udGV4dCBhdCB0 aGUKKwkJICogZW5kLCBzbyB0aGF0IGxlYWt5IHRoaW5ncyBsaWtlIGhlYXBf Z2V0bmV4dCgpIGFyZSBub3QgY2FsbGVkIGluIGEKKwkJICogcG90ZW50aWFs bHkgbG9uZy1saXZlZCBjb250ZXh0LgorCQkgKi8KKwkJb2xkY3h0ID0gTWVt b3J5Q29udGV4dFN3aXRjaFRvKGN4dCk7CiAKIAkJYXZkYiA9IChhdndfZGJh c2UgKikgcGFsbG9jKHNpemVvZihhdndfZGJhc2UpKTsKIApAQCAtMTc5Nyw2 ICsxODEwLDcgQEAgZ2V0X2RhdGFiYXNlX2xpc3Qodm9pZCkKIAkJYXZkYi0+ YWR3X2VudHJ5ID0gTlVMTDsKIAogCQlkYmxpc3QgPSBsYXBwZW5kKGRibGlz dCwgYXZkYik7CisJCU1lbW9yeUNvbnRleHRTd2l0Y2hUbyhvbGRjeHQpOwog CX0KIAogCWhlYXBfZW5kc2NhbihzY2FuKTsKLS0gCjEuNy4xCgo= --=-1289228511-98887-3958-4072-1-=--