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 1idBNC-0003WR-DH for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Dec 2019 11:02:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1idBNB-0004SK-3r for pgsql-bugs@arkaria.postgresql.org; Fri, 06 Dec 2019 11:02:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idBNA-0004SD-Ry for pgsql-bugs@lists.postgresql.org; Fri, 06 Dec 2019 11:02:08 +0000 Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idBN3-00020y-15 for pgsql-bugs@lists.postgresql.org; Fri, 06 Dec 2019 11:02:08 +0000 Received: by mail-wm1-x343.google.com with SMTP id t14so6893863wmi.5 for ; Fri, 06 Dec 2019 03:02:00 -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=M8Q6lM2Mb6niWI7JP0tZ9MMdwnmKHBS0IvAHgtYr78k=; b=nVkbotB0hwImNUtgH7B/PWsEDnZxNT39Gur2jO+RST8fFXpSwsRgiNRIrwDgih+3Q5 JG10pTHN5N3JqXMMPmD9brLBBxefPPsiKgC+XwjLD1nhDn9kFF+jIr8iNygayXI5xDzk VURpq0r2L6OUyo6rsbIlzDmQ3QzwyXWk3mWSNJ7szdqHPWL40RnZG9epiZHeI/nl4IKd VwQ7oa+T0SKTTtxhijgivNUvMSDvw9SEUqOjhuPv64w3m67pCXZKLykoms3xTGp8zy/c 0iJIzMjjC26SBE1ztW+XiVj0JpOnjcvSN3Ao7lLpLvuHkgN+siXFqR0k6viyked81kct CHIA== 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=M8Q6lM2Mb6niWI7JP0tZ9MMdwnmKHBS0IvAHgtYr78k=; b=ZAMBz5XOjKhqbs1vnFAMBt1Mftf6Mq/RHPXNmjId/WS4dG4iyuH7PsJAXn7Itxq7Di nmiTTU9Ut0hUoHtsiBCXcbprujB/VnA5fvthiEU2ptThpX/chIWcK7HTIObIzdAnvHGr c/OYx3jN/zCPX3//GQu/brMkiJaspczo+gkOtqH+MWVHQNd8+zOecarXgZ4esFkh1HdB VhM0JlEk/c6RuafB/kNoLmJ4rE2kSBtDnRyux5EbCwfNFR0YiXAGz37dLu/2stvrlBxe MFzdbj8Zl5x8C8DyUIekY36ubIR8BOo63gkr+5EPi3/q96/y1rorBys1k4JkYvT4LKeg TVJQ== X-Gm-Message-State: APjAAAXb+CU9ysdtlfBk9iYyBx160MiiIN5RL8FWpUjkGhQNcW1ZRcGf 6mCo3UUeVb6p5WVxkx/vtfStQiN3AeM= X-Google-Smtp-Source: APXvYqyMFNUfh+VCRHRV8U9Vd2IDpD0HA1YGWtvODjbjvM1GuwEQHafjLhqIMcEl6aMd2VDzIeXmCg== X-Received: by 2002:a1c:5448:: with SMTP id p8mr10184777wmi.70.1575630119391; Fri, 06 Dec 2019 03:01:59 -0800 (PST) Received: from localhost (static-84-42-175-93.net.upcbroadband.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id f2sm2865807wmh.46.2019.12.06.03.01.56 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 06 Dec 2019 03:01:57 -0800 (PST) Date: Fri, 6 Dec 2019 12:01:54 +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: <20191206110154.v7yshqsn32zyxx4q@development> References: 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 10:22:42AM +0100, Roman Cervenak wrote: >Hello, >this may be a serious issue and I am not very experienced with reporting >this kind of stuff, I will try my best. I believe there may be a memory >leak somewhere in postgres/postgis, which can eat up all the available >memory, leading to postgres crash (signal 9 kill of backend, postmaster >terminating others, recovery). > >My setup and workload: >Postgres 12.1 (Ubuntu 12.1-1.pgdg18.04+1) installed via apt on Ubuntu >server 18.04 LTS, in VM in Azure cloud. >PostGIS version: 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 >VM has 8cores, 56 GB RAM, 7 TB RAID from managed disks (azure network >storage) > >shared_buffers = 12GB >work_mem = 256MB >maintenance_work_mem = 2GB > >I was running 8 clients (on different VMs, over network) using the >database, batch processing geographic stuff. Each worker is running one >query at a time, so pretty much 8 parallel queries 100% of the time. >Queries themselves are fairly short (5-60 seconds), SELECTing rows by >geometry index (GIST) and doing stuff like ST_SimplifyPreserveTopology, >ST_Buffer, ST_Union on them, essentially using all 8 cores to 100%. There >is a lot of things to process, so this was going on for maybe 12 hours, >when I noticed (in "htop") that memory usage is unusually high, somewhere >about 50 GB. It was suspicious, because it is way higher than it should be >with 12 GB shared buffers and 256MB work_mem with 8 clients, and it >continued to grow slowly (I could see numbers in RES column in htop slowly >rise for all backends). Until eventually: > >2019-12-06 00:04:24 UTC [21487-8] LOG: server process (PID 52059) was >terminated by signal 9: Killed >2019-12-06 00:04:24 UTC [21487-10] LOG: terminating any other active >server processes >2019-12-06 00:04:24 UTC [91091-1] WARNING: terminating connection because >of crash of another server process >2019-12-06 00:04:24 UTC [91091-2] DETAIL: The postmaster has commanded >this server process to roll back the current transaction and exit, because >another server process exited abnormally and possibly corrupted shared >memory. >... >FATAL: the database system is in recovery mode >... > >I realize, this is probably not enough for you. But I will be running this >workload again soon, so if this repeats, what should I do to help you >diagnose this? The kill was initiated by oom killer, I suppose? It might be interesting to see the message written to dmesg by it, it usually includes info about how much memory was used by the backend etc. The other thing that might be useful is dump of memory contexts - you'll have to wait until the memory usage gets excessive (i.e. shortly before the oom killer would kick in), attach gdb to a backend and call MemoryContextStats on TopMemoryContext. So, something like this: $ gdb -p $PID (gdb) p MemoryContextStats(TopMemoryContext) (gdb) q which writes a bunch of info about memory contexts into the server log. But, I'm not sure this will actually help. Based on what you wrote, the memory stays allocated across queries. So either it's allocated in one of the long-lived contexts (which queries usually don't do), or it's allocated directly through malloc() and not through our memory context infrastructure (hence it'll be invisible in the context stats). I'm not particularly familiar with PostGIS, but AFAIK it's using various libraries, and those are naturally using malloc/free directly. So maybe it's not freeing the memory in some cases. What would really help is having some sort of reproducer, and/or running the queries with valgrind, which can detect memory leaks. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services