Postgres Pro
Downloads
Home   >   mailing lists

Re: Batch insert heavily affecting query performance. - Mailing list pgsql-performance

From David Miller
Subject Re: Batch insert heavily affecting query performance.
Date
Msg-id 164810660.5479757.1514394931575@mail.yahoo.com
Whole thread Raw
In response to Re: Batch insert heavily affecting query performance.  (Jean Baro <jfbaro@gmail.com>)
List pgsql-performance
Tree view
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 degraded performance. Our production 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
Sent: Wednesday, December 27, 2017 11:03 AM
Subject: Re: Batch insert heavily affecting query performance.

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!!

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.



pgsql-performance by date:

Previous
From: "Mike Sofen"
Date:
Subject: RE: Batch insert heavily affecting query performance.
Next
From: Robert Blayzor
Date:
Subject: Table performance with millions of rows