Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUFIt-00029R-HK for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:15: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 1eUFIs-00023Y-MM for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:15:42 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eUFIr-00023K-Ux for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2017 17:15:42 +0000 Received: from sonic314-13.consmr.mail.bf2.yahoo.com ([74.6.132.123]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eUFIo-0006vN-Jf for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 17:15:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1514394936; bh=rdiXL/TNJZALqRPL9t0aZThOAOCQfzMqxOwEemcSzcM=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=KGWIyvVqyKxzC5BifImPlCV7ABWokGOb8VSVsUiXloMwXSfiilwyTGYtDZpRF1hqAMGZ1iD0BsOK1mQ+PYRXPVL8mwsxli7wAwiKvs9Ubgmq5xE8npd2BfvvPnLamVv3WoQLRN8QBDcBBTVFiPE7w3TZjiA8HWGaZHlQWwqdMme8utaMRMY8Z3+n0N5AmnM4lza/NJ8T/LWtOE6Ovd+vXshSqyzTmyAYbq9krZIs116M/TURBOFFYGSy8d3QFtKe27e0OLkT3XnmCQZ+hBri7gmzqULxaty+6wZBY99khG6e/XDlATrqy7X5zOO3qD3rPcjpKmLom9KZeBS/vvhzng== X-YMail-OSG: EGh2k1IVM1laqyinWexo6aXn9o1jrAnT8PQg91zzhRB5peISqsFWMf2S1RHKPxD H5UDC_VvNmKlRnEFsnkfw3WVHA6mlByNVWo1k3cOA_3nWQfBKjj4YaegWWpYX0g1n.PmLk.l4AWP lO58YE7_9x.qVVMTHBuLpCnHwGlPZApMyXs7A0rBaBwpsDMIXDjquO9J0n4qFimJngGosRje9Pyj O3OTmqPSlFRSLnQtFn.HvuyFeJKu7UDqG9I5BUEBzdGF6ICzwb1yWBicBMqP0JiBx4HkvaOEgLZU zlcIAUv0GjdnWOZ3DwGWCp6K0WOCorNJWrYqqhMggFawAMW9_q6j.Hc1iAfgn9lUYmnzkqhWjvXI JUAbTbrNYXHV4aXhbwjVVuLFSqaItqNWbTJMhNwvPifZNm6oQDzZ4xDL0B2n06Np5CMdBoJMkIYi _6gkY8763CHN6.Bqloo.ZlAtaI5RJVd59UzUHPdggpTs5ZgaRVYyPu7aj6nF6JBZWHtBRd4jKLhn OrByk Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.bf2.yahoo.com with HTTP; Wed, 27 Dec 2017 17:15:36 +0000 Date: Wed, 27 Dec 2017 17:15:31 +0000 (UTC) From: David Miller Reply-To: David Miller To: Jean Baro , Jeremy Finzel Cc: Danylo Hlynskyi , "pgsql-performance@postgresql.org" Message-ID: <164810660.5479757.1514394931575@mail.yahoo.com> In-Reply-To: References: Subject: Re: Batch insert heavily affecting query performance. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5479756_1315910733.1514394931570" X-Mailer: WebService/1.1.11051 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36 Content-Length: 11859 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5479756_1315910733.1514394931570 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Jean, It is very likely you are running out of IOPS with that size of server. We = have several Postgres databases running at AWS. We consistently run out of = IOPS on our development servers due to the types queries and sizing of our = development databases. I would check the AWS monitoring graphs to determine= the cause. We typically see low CPU and high IOPS just prior to our degrad= ed performance. Our production environment runs provisioned IOPS to avoid t= his very issue. Regards,=C2=A0David=C2=A0 From: Jean Baro To: Jeremy Finzel =20 Cc: Danylo Hlynskyi ; pgsql-performance@postgresql.= org Sent: Wednesday, December 27, 2017 11:03 AM Subject: Re: Batch insert heavily affecting query performance. =20 Sorry guys, The performance problem is not caused by PG.=C2=A0 'Index Scan using idx_user_country on public.old_card=C2=A0 (cost=3D0.57..1= 854.66 rows=3D460 width=3D922) (actual time=3D3.442..76.606 rows=3D200 loop= s=3D1)''=C2=A0 Output: id, user_id, user_country, user_channel, user_role, = created_by_system_key, created_by_username, created_at, last_modified_at, d= ate_start, date_end, payload, tags, menu, deleted, campaign, correlation_id= ''=C2=A0 Index Cond: (((old_card.user_id)::text =3D '1234'::text) AND (old_= card.user_country =3D 'BR'::bpchar))''=C2=A0 Buffers: shared hit=3D11 read= =3D138 written=3D35''Planning time: 7.748 ms''Execution time: 76.755 ms' 77ms on an 8GB database with 167MM rows and almost 500GB in size is amazing= !! Now we are investigating other bottlenecks, is it the creation of a new con= nection to PG=C2=A0 (no connection poller at the moment, like PGBouncer), i= s it the Lambda start up time? Is it the network performance=C2=A0 between = PG and Lambda? I am sorry for wasting your time guys, it helped us to find the problem tho= ugh, even if it wasn't a PG problem.=C2=A0 BTW, what a performance! I am impressed.=C2=A0 Thanks PG community!=C2=A0 Em 27 de dez de 2017 14:34, "Jean Baro" escreveu: Thanks Jeremy, We will provide a more complete EXPLAIN as other people have suggested.=C2= =A0 I am glad we might end up with a much better performance (currently each qu= ery takes around 2 seconds!). Cheers Em 27 de dez de 2017 14:02, "Jeremy Finzel" escreveu: The EXPLAIN 'Index Scan using idx_user_country on card=C2=A0 (cost=3D0.57..1854.66 rows= =3D460 width=3D922)''=C2=A0 Index Cond: (((user_id)::text =3D '4684'::text)= AND (user_country =3D 'BR'::bpchar))' Show 3 runs of the full explain analyze plan on given condition so that we = can also see cold vs warm cache performance. There is definitely something wrong as there is no way a query like that sh= ould take 500ms.=C2=A0 Your instinct is correct there. =20 ------=_Part_5479756_1315910733.1514394931570 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Jean,<= /span>

<= span id=3D"yui_3_16_0_ym19_1_1514249799541_465447">It is very likely you ar= e running out of IOPS with that size of server. We have several Postgres da= tabases running at AWS. We consistently run out of IOPS on our development = servers due to the types queries and sizing of our development databases. I= would check the AWS monitoring graphs to determine the cause. We typically= see low CPU and high IOPS just prior to our degraded performance. Our prod= uction environment runs provisioned IOPS to avoid this very issue.
Regards,
 
David 


From: Jean Baro = <jfbaro@gmail.com>
To: Jeremy Finzel <finzelj@gmail.com>
Cc: Danylo Hlynskyi <abcz2.uprola@gmail.com>= ; pgsql-performance@postgresql.org
Subject: Re: Batch insert heavily affect= ing query performance.

<= div id=3D"yui_3_16_0_ym19_1_1514249799541_465320">
Sorry guys,

The performance problem is not c= aused by PG. 
=
'Index Scan using = idx_user_country on public.old_card  (cost=3D0.57..1854.66 rows=3D460 = width=3D922) (actual time=3D3.442..76.606 rows=3D200 loops=3D1)'
'  Output: id, user_id, user_country, user_chann= el, user_role, created_by_system_key, created_by_username, created_at, last= _modified_at, date_start, date_end, payload, tags, menu, deleted, campaign,= correlation_id'
'  Index Cond: (((old= _card.user_id)::text =3D '1234'::text) AND (old_card.user_country =3D 'BR':= :bpchar))'
'  Buffers: shared hit=3D1= 1 read=3D138 written=3D35'
'Planning time: = 7.748 ms'
'Execution time: 76.755 ms'

77ms on an 8GB database with 167MM rows and almost 500GB in size is a= mazing!!

Now we are investigating other bottlenecks, is it the cre= ation of a new connection to PG  (no connection poller at the moment, = like PGBouncer), is it the Lambda start up time? Is it the network performa= nce  between PG and Lambda?

I am sorry for wasting your tim= e guys, it helped us to find the problem though, even if it wasn't a PG pro= blem. 

BTW, what a performance! I am impressed. 

Thanks PG community! 


------=_Part_5479756_1315910733.1514394931570--