Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eTHbH-0005Vr-Gb for pgsql-performance@arkaria.postgresql.org; Mon, 25 Dec 2017 01:30:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eTHbG-0000FR-Ex for pgsql-performance@arkaria.postgresql.org; Mon, 25 Dec 2017 01:30:42 +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_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eTHbG-0000FH-7r for pgsql-performance@lists.postgresql.org; Mon, 25 Dec 2017 01:30:42 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eTHb8-0004YR-Hu for pgsql-performance@postgresql.org; Mon, 25 Dec 2017 01:30:41 +0000 Received: from [192.168.1.101] ([68.98.128.17]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Lkhla-1f3iHB0qNY-00aWaN; Mon, 25 Dec 2017 02:30:29 +0100 Message-ID: <5A4054B3.8010708@sqlexec.com> Date: Sun, 24 Dec 2017 20:30:27 -0500 From: MichaelDBA User-Agent: Postbox 5.0.22 (Windows/20171208) MIME-Version: 1.0 To: Jean Baro CC: pgsql-performance@postgresql.org Subject: Re: Batch insert heavily affecting query performance. References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------070604060906000704000804" X-Provags-ID: V03:K0:nOJzVjbpR09HZHk6Wv07ZeKDGEpCK3mcEal7IOhR0yMZAZfrUqf OLZemBe6MxXfg8EkqnmiJWSnRfHjB9PbCPr8HSLF/msTeQBHCL/s93BPqerAJjFYacetq78 4WsX5ZGkpXVttwnEPEahKYWLYecENG909JTORNFa16xbBEZhbojGP5Ht5yFZ6NrIaywtnzG RDdnr+Y3o2wnWgiR8JF1w== X-UI-Out-Filterresults: notjunk:1;V01:K0:NcLhWbZVIJI=:Vao66O3pq22Z5CmbDvhGke 08KkDDySKZIwntqcPzFvyIbyvVBEh0Z9tr4MdVuWiS0wS6TtMJht56YOiJgpIBe/IBo0O45cU 1P7JiHu6x8pvMQhtpH+OTLAPaMX64qZfEnt4GuDu81MTrSg8XRbfqRKrcCONFW5fTFLyF4SHB +1/xN097Cueqv4dhgUOvZNumauT+zhW5gYFfjNVcQ1zIpLCRxuS4JkyFnInsm99MiE7ubHpqf y7Uy84I+A4HLG5uqgLQbjEVCXmoeb8AmuF0aqcq+q9WQdPKNLP6o4265CyD0d/WvZVHbbO05i H94KE4DINbfW01h++PtXmzfsptX4QKtGzfn/KZMJyDHhwhqRZvT5qMvNA4tRyCBInJWJSk6L1 kfjRCN38d3L+QjuY1952z4fzqc6S2JrBSvC8zGmrdQIqh0Nx4+6fmUdwTBEqZl04xewYPfra1 9ukZ+sn2l1NKTcPTooF3TlcwhJCv/asPKte3ogX/102vcSkuqEDh9x+Czumwtk7sMiY+iYCiZ Sf5I5VKYkuszGws35tusM50NsW6nZZxVqQXHLJxZVLcK1loGHUJ7a1NcP8Z0I7GOPFQ+d82Uf R05hKkXl/gSkQnyd3w5nKABFAjpoQyZlLtsothXfsGWi5YLv1MdXK9R63g+tCi4WZZL8B9myI D5hu/FW05XLHZ8OCbHlKuEngH23MLMyI48EqYh0RbRfBSz4p/TF6F/YFbtkgAVs9P5nfKXfcQ GhRwAjlYNGBqIVhobSs28IJFbMe046UeUjC3V1jFyH5LygiCs1IJjwnA3kM= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------070604060906000704000804 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Yes it would/does make a difference! When you do it with one connection you should see a big performance gain. Delayed, granted, extend locks (locktype=extend) can happen due to many concurrent connections trying to insert into the same table at the same time. Each insert request results in an extend lock (8k extension), which blocks other writers. What normally happens is the these extend locks happen so fast that you hardly ever see them in the pg_locks table, except in the case where many concurrent connections are trying to do a lot of inserts into the same table. The following query will show if this is the case: select * from pg_locks where granted = false and locktype = 'extend'; > Jean Baro > Sunday, December 24, 2017 7:09 PM > Multiple connections, but we are going to test it with only one. Would > it make any difference? > > Thanks > > > > MichaelDBA@sqlexec.com > Sunday, December 24, 2017 6:52 PM > Are the inserts being done through one connection or multiple > connections concurrently? > > Sent from my iPhone > > > > Jean Baro > Sunday, December 24, 2017 2:51 PM > Hi there, > > We are testing a new application to try to find performance issues. > > AWS RDS m4.large 500GB storage (SSD) > > One table only, called Messages: > > Uuid > Country (ISO) > Role (Text) > User id (Text) > GroupId (integer) > Channel (text) > Title (Text) > Payload (JSON, up to 20kb) > Starts_in (UTC) > Expires_in (UTC) > Seen (boolean) > Deleted (boolean) > LastUpdate (UTC) > Created_by (UTC) > Created_in (UTC) > > Indexes: > > UUID (PK) > UserID + Country (main index) > LastUpdate > GroupID > > > We inserted 160MM rows, around 2KB each. No partitioning. > > Insert started at around 3.000 inserts per second, but (as expected) > started to slow down as the number of rows increased. In the end we > got around 500 inserts per second. > > Queries by Userd_ID + Country took less than 2 seconds, but while the > batch insert was running the queries took over 20 seconds!!! > > We had 20 Lambda getting messages from SQS and bulk inserting them > into Postgresql. > > The insert performance is important, but we would slow it down if > needed in order to ensure a more flat query performance. (Below 2 > seconds). Each query (userId + country) returns around 100 diferent > messages, which are filtered and order by the synchronous Lambda > function. So we don't do any special filtering, sorting, ordering or > full text search in Postgres. In some ways we use it more like a > glorified file system. :) > > We are going to limit the number of lambda workers to 1 or 2, and then > run some queries concurrently to see if the query performance is not > affect too much. We aim to get at least 50 queries per second > (returning 100 messages each) under 2 seconds, even when there is > millions of messages on SQS being inserted into PG. > > We haven't done any performance tuning in the DB. > > With all that said, the question is: > > What can be done to ensure good query performance (UserID+ country) > even when the bulk insert is running (low priority). > > We are limited to use AWS RDS at the moment. > > Cheers > > --------------070604060906000704000804 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Yes it would/does make a difference!  When you do it with one connection you should see a big performance gain.  Delayed, granted, extend locks (locktype=extend) can happen due to many concurrent connections trying to insert into the same table at the same time. Each insert request results in an extend lock (8k extension), which blocks other writers. What normally happens is the these extend locks happen so fast that you hardly ever see them in the pg_locks table, except in the case where many concurrent connections are trying to do a lot of inserts into the same table. The following query will show if this is the case:

select * from pg_locks where granted = false and locktype = 'extend';

Sunday, December 24, 2017 7:09 PM
Multiple connections, but we are going to test it with only one. Would it make any difference?

Thanks 



Sunday, December 24, 2017 6:52 PM
Are the inserts being done through one connection or multiple connections concurrently?

Sent from my iPhone



Sunday, December 24, 2017 2:51 PM
Hi there,

We are testing a new application to try to find performance issues.

AWS RDS m4.large 500GB storage (SSD)

One table only, called Messages:

Uuid
Country  (ISO)
Role (Text)
User id  (Text)
GroupId (integer)
Channel (text)
Title (Text)
Payload (JSON, up to 20kb)
Starts_in (UTC)
Expires_in (UTC)
Seen (boolean)
Deleted (boolean)
LastUpdate (UTC)
Created_by (UTC)
Created_in (UTC)

Indexes:

UUID (PK)
UserID + Country (main index)
LastUpdate 
GroupID 


We inserted 160MM rows, around 2KB each. No partitioning.

Insert started at around  3.000 inserts per second, but (as expected) started to slow down as the number of rows increased.  In the end we got around 500 inserts per second.

Queries by Userd_ID + Country took less than 2 seconds, but while the batch insert was running the queries took over 20 seconds!!!

We had 20 Lambda getting messages from SQS and bulk inserting them into Postgresql. 

The insert performance is important, but we would slow it down if needed in order to ensure a more flat query performance. (Below 2 seconds). Each query (userId + country) returns around 100 diferent messages, which are filtered and order by the synchronous Lambda function. So we don't do any special filtering, sorting, ordering or full text search in Postgres. In some ways we use it more like a glorified file system. :)

We are going to limit the number of lambda workers to 1 or 2, and then run some queries concurrently to see if the query performance is not affect too much. We aim to get at least 50 queries per second (returning 100 messages each) under 2 seconds, even when there is millions of messages on SQS being inserted into PG.

We haven't done any performance tuning in the DB. 

With all that said, the question is:

What can be done to ensure good query performance (UserID+ country) even when the bulk insert is running (low priority).

We are limited to use AWS RDS at the moment.

Cheers



--------------070604060906000704000804--