From: Alvaro Hernandez <aht@ongres.com>
To: Jean Baro <jfbaro@gmail.com>
To: Jeremy Finzel <finzelj@gmail.com>
Cc: Danylo Hlynskyi <abcz2.uprola@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Batch insert heavily affecting query performance.
Date: Wed, 27 Dec 2017 18:09:28 +0100
Message-ID: <89226d19-6f3c-b091-761a-e11031c156d1@ongres.com> (raw)
In-Reply-To: <CA+fQeekCVjCCOaGTvTPt=a1WCocZXP8-cXj4LxW5Q3jvYZMDVg@mail.gmail.com>
References: <CA+fQeemGw8j+01vjfFKDWYQHj9CtuOH4Mbv2YdwK9i3RrSY5jg@mail.gmail.com>
<CANZg+ycLeP4eHVObqgtdSN9sQ7puWcgtMatiiMXUX35XK70qgA@mail.gmail.com>
<CANZg+yenhBaemswuRP53Rjo+K42PQybYgOcu7w=1mx3AucD4CA@mail.gmail.com>
<CA+fQeeno-4Pomi0f3cif0TVaCLyqCxDwGvGQfL4hBf91KEfoSg@mail.gmail.com>
<CA+fQeemOe1f1aetNNDtnvN3riPz7bDNdEAa8RuH4cSJMsS4XuA@mail.gmail.com>
<CAMa1XUjrFEQ3KxBCQ38FmKy783CJ3GJ7gjVzNA7ssurkhuJpiw@mail.gmail.com>
<CA+fQeem6jR4UWcXicPr_5n1Y9SEV1ErHS-tYTAGZK9+Vcv+k=g@mail.gmail.com>
<CA+fQeekCVjCCOaGTvTPt=a1WCocZXP8-cXj4LxW5Q3jvYZMDVg@mail.gmail.com>
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
> <mailto: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
> <mailto: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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aht@ongres.com, jfbaro@gmail.com, finzelj@gmail.com, abcz2.uprola@gmail.com
Subject: Re: Batch insert heavily affecting query performance.
In-Reply-To: <89226d19-6f3c-b091-761a-e11031c156d1@ongres.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox