Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUFCz-0001iE-WD for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:09:38 +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 1eUFCz-0002Ry-19 for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:09:37 +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 1eUFCy-0002Ro-IA for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2017 17:09:36 +0000 Received: from mail-wr0-x231.google.com ([2a00:1450:400c:c0c::231]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eUFCu-0006jd-8y for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 17:09:35 +0000 Received: by mail-wr0-x231.google.com with SMTP id o15so5031474wrf.12 for ; Wed, 27 Dec 2017 09:09:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ongres.com; s=gsuite; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=l1cu7qASb1do3d/gfwWztpGSccyNiKojUXpT+2jnNOQ=; b=xevA17NJhfhbAw6sYe7HNaR2dRSxgdaFPBB4ffffEGlajoJuDEws4pDW3IjOUW4e0F 6Tplg2kwYsz2m0XLlAoZ6J1m0dS7UZRcS4SMpbcIw75Gd87AM/TK9319dDl6i6XkMUDq mWCZJOlc4tpBq1rNA49OkVUYbNvLxQgfalC5c= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=l1cu7qASb1do3d/gfwWztpGSccyNiKojUXpT+2jnNOQ=; b=bGbCdMn02lwZQJBBQxkraHgfvykRZhKMOJ1+5QZOezoufNDpeulriWD5K66AWp0LAF z2bkHxZcqx8mrMo3lV4snjVpap4jWNFGEB28u4htMIjEOODnqBO7ocv+snWGiPemPDC3 9jKNCrhTe3gXMqcoWj9G2LwlD/Uu8kbbyjceHP1Zdd9fFq9vlrKAS/gZz1DaZqx+Mqr2 42nzOEEmob2KMjEXIibgIDq2f/G4QYC2ubn2iSFxTUxN2+IfMqLnqon4k9z+msmo3+Tu 73Q/OPhpAxqg6YXmEiRpCgSJ2J5lk9gPlGpyEQ+/TWMXbj/Qy4in84isiMaUtaG7qlkO /VwQ== X-Gm-Message-State: AKGB3mJ7DsBsbZtK2IJXTLoEfEbfJrJgJEc68ofl6gS3PauYX5B/Ny8s 1nOkMIGuZB/UPWdf2JFcfPvF8RniizI= X-Google-Smtp-Source: ACJfBotMq1fiCbCQT+vi1GAlIYrbvPMr1DSG6wS4StZBI4EqvYLBYxI9p6ctDBoMykgf/S87wwP8Ag== X-Received: by 10.223.160.77 with SMTP id l13mr25042093wrl.56.1514394569604; Wed, 27 Dec 2017 09:09:29 -0800 (PST) Received: from [192.168.0.158] (din-21-243-231-77.ipcom.comunitel.net. [77.231.243.21]) by smtp.gmail.com with ESMTPSA id 30sm13099047wrl.22.2017.12.27.09.09.28 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 27 Dec 2017 09:09:28 -0800 (PST) Subject: Re: Batch insert heavily affecting query performance. To: Jean Baro , Jeremy Finzel Cc: Danylo Hlynskyi , pgsql-performance@postgresql.org References: From: Alvaro Hernandez Message-ID: <89226d19-6f3c-b091-761a-e11031c156d1@ongres.com> Date: Wed, 27 Dec 2017 18:09:28 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------D5B08F40EFD6185EAB3C3DDB" Content-Language: es-ES List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------D5B08F40EFD6185EAB3C3DDB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 27/12/17 18:02, Jean Baro wrote: > Sorry guys, > > The performance problem is not caused by PG. > > 'Index Scan using idx_user_country on public.old_card  > (cost=0.57..1854.66 rows=460 width=922) (actual time=3.442..76.606 > rows=200 loops=1)' > '  Output: id, user_id, user_country, user_channel, 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 = '1234'::text) AND > (old_card.user_country = 'BR'::bpchar))' > '  Buffers: shared hit=11 read=138 written=35' > '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!!     gp2 disks are of *variable* performance. Once you exhaust the I/O credits, you are capped to a baseline IOPS that are proportional to the size. I guess you would experience low performance in this scenario since your disk is not big. And actually performance numbers with gp2 disks are unreliable as you don't know in which credit status you are.     Benchmark with provisioned iops to get a right picture of the desired performance.     Cheers,     Álvaro -- Alvaro Hernandez ----------- OnGres > > Now we are investigating other bottlenecks, is it the creation 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 > performance  between PG and Lambda? > > I am sorry for wasting your time guys, it helped us to find the > problem though, even if it wasn't a PG problem. > > BTW, what a performance! I am impressed. > > Thanks PG community! > > 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. > > I am glad we might end up with a much better performance > (currently each query 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 > (cost=0.57..1854.66 rows=460 width=922)' > '  Index Cond: (((user_id)::text = '4684'::text) AND > (user_country = '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 should take 500ms.  Your instinct is correct there. > > --------------D5B08F40EFD6185EAB3C3DDB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 27/12/17 18:02, Jean Baro wrote:
Sorry guys,

The performance problem is not caused by PG. 

'Index Scan using idx_user_country on public.old_card  (cost=0.57..1854.66 rows=460 width=922) (actual time=3.442..76.606 rows=200 loops=1)'
'  Output: id, user_id, user_country, user_channel, 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 = '1234'::text) AND (old_card.user_country = 'BR'::bpchar))'
'  Buffers: shared hit=11 read=138 written=35'
'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!!


    gp2 disks are of *variable* performance. Once you exhaust the I/O credits, you are capped to a baseline IOPS that are proportional to the size. I guess you would experience low performance in this scenario since your disk is not big. And actually performance numbers with gp2 disks are unreliable as you don't know in which credit status you are.

    Benchmark with provisioned iops to get a right picture of the desired performance.


    Cheers,

    Álvaro


-- 

Alvaro Hernandez


-----------
OnGres





Now we are investigating other bottlenecks, is it the creation 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 performance  between PG and Lambda?

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

BTW, what a performance! I am impressed. 

Thanks PG community! 

Em 27 de dez de 2017 14:34, "Jean Baro" <jfbaro@gmail.com> escreveu:
Thanks Jeremy,

We will provide a more complete EXPLAIN as other people have suggested. 

I am glad we might end up with a much better performance (currently each query takes around 2 seconds!).

Cheers


Em 27 de dez de 2017 14:02, "Jeremy Finzel" <finzelj@gmail.com> escreveu:


The EXPLAIN

'Index Scan using idx_user_country on card  (cost=0.57..1854.66 rows=460 width=922)'
'  Index Cond: (((user_id)::text = '4684'::text) AND (user_country = '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 should take 500ms.  Your instinct is correct there.


--------------D5B08F40EFD6185EAB3C3DDB--