Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idCvL-0007Ga-U3 for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Dec 2019 12:41:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1idCvJ-0000Rs-RN for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Dec 2019 12:41:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idCvJ-0000Rl-Bi for pgsql-bugs@lists.postgresql.org; Fri, 06 Dec 2019 12:41:29 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1idCvF-000631-SJ for pgsql-bugs@lists.postgresql.org; Fri, 06 Dec 2019 12:41:28 +0000 Received: by mail-wm1-x342.google.com with SMTP id c20so5730943wmb.0 for ; Fri, 06 Dec 2019 04:41:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=pSDqUlRImyB17apwgrwZvtphAaM7uobEH8VlhxQFTvQ=; b=PvqqXMOR8SRYailzEszD5z0MLIKH5Oo0FPp8Dk+nW8Gu9PYaQ4c8LouqmmjMjXOotA k/khZlFqV3WgbuDOQ1Y9W+ZSFv3fFus6NCdkcL3Iwjuhbc7a9pkhLJzlhNCuecznbyxO ArXb6XaRfgHm3yL3uomz1sTT7Kvc4xNK4+hAgSCaaq/D6DfLrPEnc3o9HYJwQZdOC1B3 vAs82PSeEDHeRIBLJ5AEDmBQDOWGjwKvH/T7wQn/DPidD/VHx79SYN38HmyMohCIRY9c bMFgrvSVhzF3DBBagNrNyzMt56GCGSoq7W5Ql9cOK6t/dXUbp8WXPkJJ2HY7i3OYCxNR n78g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=pSDqUlRImyB17apwgrwZvtphAaM7uobEH8VlhxQFTvQ=; b=OzGaOiVWPNA9h2XR4f+Ow/Kz+HGsnH0S7bEFKCVcJuY1iwjtzAthVf39g7dkpJaGd8 Irc2gyjVkfuweLn8F8GIgK0bY3eo+7of0LDpHVBb9IW1TV5hAF6ifADBscIk37rZ4JA3 SX0BMbxbkvgjmq2/cK6yPwD1vGt0LCCfxwtxeBR5pWFG/7KhaiVq0yAfdwCjU2Zo92pR 9Ox0PIQ5550AQaYgbrzyFdKA5NW6u7BJgTz3INRWvj9lTy//Elp/CK5viupXBU5pn41/ XiVCy9Ll4VmzWs1OMWmqxJuXHPV3VJVOC+tmnqNDQXYxjnEODxxUcpy1glWZSeNJDBoN aICg== X-Gm-Message-State: APjAAAWKZjja3uWGBSS/+YqzkFHDSWFqRMyAtp6hl56TmDN2V7832N39 qM6PSu/aedy+XdeTOgiZnE8XBg== X-Google-Smtp-Source: APXvYqxlHqgmMpK2jQycG3R9ovcnYFHYVGUzXI87tHw8G4yVncr79LuTUPJiiEOkuvN3vrCUbOvbag== X-Received: by 2002:a1c:9dd1:: with SMTP id g200mr10716572wme.90.1575636083828; Fri, 06 Dec 2019 04:41:23 -0800 (PST) Received: from localhost (static-84-42-175-93.net.upcbroadband.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id k4sm3450543wmk.26.2019.12.06.04.41.22 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 06 Dec 2019 04:41:22 -0800 (PST) Date: Fri, 6 Dec 2019 13:41:19 +0100 From: Tomas Vondra To: Roman Cervenak Cc: pgsql-bugs@lists.postgresql.org Subject: Re: Memory leak (possibly connected to postgis) leading to server crash Message-ID: <20191206124119.qe2xaffcbkcbmve3@development> References: <20191206110154.v7yshqsn32zyxx4q@development> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Fri, Dec 06, 2019 at 12:46:44PM +0100, Roman Cervenak wrote: >Yes, it was killed by oom killer: > >[2037990.376427] >oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/system-postgresql.slice,task=postgres,pid=52059,uid=111 >[2037990.376433] Out of memory: Kill process 52059 (postgres) score 294 or >sacrifice child >[2037990.384186] Killed process 52059 (postgres) total-vm:17508832kB, >anon-rss:4309296kB, file-rss:108kB, shmem-rss:12641580kB >[2037990.516504] oom_reaper: reaped process 52059 (postgres), now >anon-rss:0kB, file-rss:0kB, shmem-rss:12641580kB > >(full dmesg.log attached, if it is interesting; there are more postgres >backends visible, but they were inactive at the time) > OK, so it allocated extra ~4.3GB or so (plus shared buffers). >I can try the gdb dump next time I will see it. But I cannot imagine giving >you reproducible case - it is 500 GB proprietary database, and without it, >queries would be hardly useful, I presume? I can try to make "sample" with >generated data and find out if I can reproduce the issue with my queries >that way, but that will be quite time consuming. > Yeah, I understand the data is proprietary. But we need to identify where the issue is, somehow. And being able to reproduce it is likely the best way to do that. One way to do that is to either generate synthetic data with similar structure/features (geometries of similar size etc.), and hope that it triggers the issue too. Or distill a subset of data triggering the issue and anonymize it. Or something like that. Yes, it's going to be quite time consuming, but I don't have better ideas. Maybe running it under valgrind would help - you'd have to rebuild PostgreSQL with valgrind support and run the workload on it. The queries would be much slower (possibly by an order of magnitude or so), so you'd have to run it on a different machine, but that's machine time, not time wasted by a human. Anyway, let's start with the easy stuff - try running it again and get the memory context stats. Maybe it's a simple leak in PostgreSQL, in which case it should be easier to investigate it. If that turns out to be untrue, you can try this more complicated stuff (valgrind, ...). >Would it help to dump the memory of the backend process and deliver the >dump (by some private channel) to somebody to identify who is consuming all >that memory? (that is the usual drill in windows) > I don't think that'd be very helpful - if this really is memory leak in one of the libraries, I have no idea how to spot that in the memory dump. Also, if you claim the data is sensitive/proprietary, you should not really be sharing the dumps. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services