Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPCC0-0000JD-RF for pgsql-bugs@arkaria.postgresql.org; Thu, 16 Apr 2020 21:37:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jPCBz-0007Ho-F4 for pgsql-bugs@arkaria.postgresql.org; Thu, 16 Apr 2020 21:37:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPCBz-0007Hh-4N for pgsql-bugs@lists.postgresql.org; Thu, 16 Apr 2020 21:37:03 +0000 Received: from mail-wr1-x443.google.com ([2a00:1450:4864:20::443]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jPCBw-0001hW-HL for pgsql-bugs@lists.postgresql.org; Thu, 16 Apr 2020 21:37:02 +0000 Received: by mail-wr1-x443.google.com with SMTP id b11so348646wrs.6 for ; Thu, 16 Apr 2020 14:37:00 -0700 (PDT) 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=ErFT0UhKNncWL6yciBkyycNp6CpuGL1IE8VGdb9xnBA=; b=YqOAanjeGamjVK5B8qyEnA8VsPKWlSyYMuKHXREHdtiYJ0SqyaWranvQ0U1eUky2hR KjCd6b3NZYvuU3YBjMn/ZoA3l851DU5jbUP7gOIBllQhqGGqhNyGCdtAngjOzBc2wqBr eG5v11Z55SiBuEkgk5l+6Y94MJvBlKbOU5w8j09+LGkV4PQF1oIsAFnytg13H5J/zGjm ZrND6jHNjz7z5tFOuSgyu5ff/SpHL7r59oHMOlnii/E29828d+M9a5xuZFRnUqZDhd0L H8tdvmxoqS1+gQydaU64WJF8XzzNvc5G9rJT3JtBDGFzqYaeax+S34+hrxZkNioUrC7a N6kg== 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=ErFT0UhKNncWL6yciBkyycNp6CpuGL1IE8VGdb9xnBA=; b=oSk9tnduR5g23TmlRZ7gAI3InLajZjbZX5xEEwN6XTqGylOqwGBmEKTsgrNtp7TZF4 okxQIcnUXrlQfeDI5yakfOlamEkt19wJgUk26W83Zu7LiUcZ+525kmW+h8y174UIAcCz ke3q46KzYNwl+ng0gBy00nHos96LZEFPY7JmKlxmxbF+/g6u0BLfipBHTB4QhS0wq+Db RsNFQAAUYwEFKXcgr8qDaLV/h6521j9Ju7t5nqH7MhgU9DfPBNsE2JmkDba4O2NAzxp4 r6Kdhm2lH4Yd/+9BzYOEpvgAtdzswa0Zjo0kbXoFRDadByfHB3bruqM+Eiju03aPqLc8 XwJw== X-Gm-Message-State: AGi0PuarQGaxrF2/mD1xEjhUD6CXNaR2FL/jUde7OHBfhJFEJgeAAofB X9/dplQYBRxhZb3NNXC4jZexHg== X-Google-Smtp-Source: APiQypLuCPokT8FKOYvTFlo9MVTNPjyCSQBhalKpdcDIYFHyV69UrataAw0+f4VZAZovLkg5nMTYrg== X-Received: by 2002:a5d:54ce:: with SMTP id x14mr260022wrv.103.1587073018907; Thu, 16 Apr 2020 14:36:58 -0700 (PDT) Received: from localhost (ip-86-49-253-141.net.upcbroadband.cz. [86.49.253.141]) by smtp.gmail.com with ESMTPSA id p6sm21640553wrt.3.2020.04.16.14.36.58 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 16 Apr 2020 14:36:58 -0700 (PDT) Date: Thu, 16 Apr 2020 23:36:56 +0200 From: Tomas Vondra To: Eduardo Barreto Alexandre Cc: luis.roberto@siscobra.com.br, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16363: Memory increases for each table accessed until connection is closed Message-ID: <20200416213656.arunx2wvbod5hirk@development> References: <16363-a66916ab04716e58@postgresql.org> <15093.1586915311@sss.pgh.pa.us> <1296821153.19168439.1587048196810.JavaMail.zimbra@siscobra.com.br> 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 I think you need to fix your mail system, because clearly it's broken in some strange way. If you look at [1] you'll see the messages indeed got to the mailing list so either you're not receiving messages from the list or it's getting stashed in a spam folder or something. [1] https://www.postgresql.org/message-id/16363-a66916ab04716e58%40postgresql.org On Thu, Apr 16, 2020 at 06:23:33PM -0300, Eduardo Barreto Alexandre wrote: >Unfortunately, same result :( > >On Thu, Apr 16, 2020 at 11:43 AM wrote: > >> Sending the reply again since it seems that it didn't go through the >> mailing list >> >> >> Hello Tom, first of all, thanks for the response! >> Sorry to not have added a lot of the needed information, I will try to >> rectify that in this message. >> So, first is what I'm trying to do, in my backend, I store financial data >> (candlesticks and trades) in Postgres + TimescaleDB, the way I do it is >> separating each exchange in one database and each market and timeframe in >> one table, for example, giving that I have `ExchangeA` with 1 market >> EUR/USD and 4 timeframes, I would get these 5 tables: >> candles_eur_usd_one_minute >> candles_eur_usd_five_minutes >> candles_eur_usd_third_minutes >> candles_eur_usd_one_hour >> trades_eur_usd >> Normally in my backend, I have, per database (exchange) ~750 markets, and >> ~16 timeframes, so 750 * 16 = 12000 tables. >> I noticed that Postgres would continuously increase my memory usage per >> connection until I got an OOM error, which, in my backend terms, means >> inserting candles and trades, ~2000 rows per minute. For a system with >> 16GB, it would take like 1 to 3 hours and then I would get the OOM. For my >> other system which has 64GB, it would take like 2 days but it would come a >> time when Postgres would use everything and crash too. >> For versions, I was using Postgres 11 with latest TimescaleDB, since I was >> not sure if this was a TimescaleDB issue, I first created a minimal example >> that triggered the issue faster (the one I talked in the first e-mail that >> uses 700 tables) and then tested without TimescaleDB in Postgres 11 and 12. >> I got the same result in both, so removing TimescaleDB out of the equation. >> For the operational system, I'm using ubuntu, but I do test it in my >> Gentoo machine with the same results, and I also tested it using Postgres >> in docker. >> For postgresql.conf, I was using first one tunned by the TimescaleDB tuner >> software, but then changed to a default one to see if it would make a >> change. >> My backend is written in Elixir, so I'm using Ecto library to connect with >> Postgres, Ecto will create a pool of connections that will be alive as long >> as my backend is alive too. To make it easier for me to test the issue, I >> limited the number of connections to 1, so I can keep an eye on that >> connection process memory usage, etc. >> >> > Yes, Postgres caches some information about every table your session >> > has accessed, and no you don't have a lot of control over that, and >> > no it isn't a bug. >> >> > Having said that, the amount of memory consumed this way is in the >> > vicinity of a few tens of KB per table, according to some simple >> > experiments I just did. I couldn't get a process touching 700 simple >> > tables to eat more than ~70MB of space. So either you are talking about >> > tables with enormously complex schemas, or there's something else going >> > on. (Please note that the SO post you are referencing describes an >> > attempt to use circa 100000 tables/views in one session, not 700.) >> >> Here are the details of one of the tables I'm using to do my test (all of >> then are equal, just the name is different): >> test_dev=# \d+ trades_eur_usd >> Table "public.trades_eur_usd" >> Column | Type | Collation | Nullable | Default >> | Storage | Stats target | Description >> >> -----------+-----------------------------+-----------+----------+---------+---------+--------------+------------- >> id | integer | | not null | >> | plain | | >> timestamp | timestamp without time zone | | not null | >> | plain | | >> amount | numeric | | not null | >> | main | | >> price | numeric | | not null | >> | main | | >> Indexes: >> "trades_eur_usd_id_desc_index" btree (id DESC) >> "trades_eur_usd_timestamp_desc_index" btree ("timestamp" DESC) >> Access method: heap >> One thing that I forgot to explain better is what I'm doing to touch those >> tables. Basically I created a little Elixir backend with Ecto which will >> keep 1 connection alive for the pool and start a bulk insertion of rows to >> one of the 700 tables, so, basically, the steps are: >> 1) Choose one of the tables randomly; >> 2) Insert in one query 13000 rows to that table; >> 3) Go back to step 1 and repeat. >> This will trigger the issue instantly and will make the memory usage of >> that connection grow very fast as shown in the last e-mail. >> One very important thing to note is that if I change the above algorithm >> to do the same thing, but always choose the same table in step 1 (so I will >> do the bulk insertion always in 1 table instead of one of the 700), I will >> not trigger the issue, memory usage will not go up at all. it only triggers >> it if I'm doing the insertion in different tables each time. >> >> > The memory consumption curve you showed looks suspiciously like you have >> > circa 4GB shared buffers allocated and the reported number is mostly >> about >> > how many of those buffers the process has touched so far. This is a >> > common misunderstanding; "top" and similar tools tend to be really bad >> > about accounting for shared memory usage in any useful way. In any case, >> > the process you've shown us stopped growing its space consumption >> > some time ago, so I wonder where the OOM complaint actually came from. >> >> > The short answer is that you're probably barking up the wrong tree, >> > but there's not enough information here to provide any useful guesses >> > about which is the right tree. >> >> I guess you are right, maybe the issue is with some shared buffers cache >> or something, I tried lowering to the minimum value each possible option in >> postgresql.conf that I found but still got the same OOM result. >> Thanks a lot for the help, and feel free to ask for more information! >> >> Can you try to run it without a connection pool? just a simple connection >> to the backend. >> > > >-- >Eduardo Barreto Alexandre > >http://lattes.cnpq.br/0983072410155605 -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services