Performance issues while running select sql query
Hi,
I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance type
with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing
performance issues. The sql query response takes around *127713.413 ms *time
*.* Is there a way to find out the bottleneck?
The select sql query are as below :-
# SELECT serorgid,appname,sum(message_count) AS mtrc0,date_trunc('day',
client_received_start_timestamp at time zone '+5:30:0')::timestamp without
time zone AS time_unit FROM analytics."test.prod.fact" WHERE
client_received_start_timestamp >= '2018-3-28 18:30:0' AND
client_received_start_timestamp < '2018-4-11 18:30:0' AND ((apiproxy in
('test-service' ) ) and (exchangeinstance != '(not set)' ) and (devemail
!= '[email protected]' ) and (devemail != '[email protected]' ) and
(devemail != '[email protected]' ) and (devemail != '[email protected]' ) and
(apistatus = 'Success' ) and (apiaction not in
('LRN','finder','ManuallySelect' ) ) and (appname not in ('Mobile Connect
Developer Portal (Int(', 'MinskHBM', 'LondonHBM', 'SeoulHBM', 'MumbaiHBM',
'NVirginiaHBM','SPauloHBM', 'Mobile Connect HeartBeat Monitor',
'PDMAOpenSDKTest1', 'PDMAOpenSDKTest2', 'PDMASDKTest', 'APIHealth',
'A1qaDemoApp','test', 'dublin o2o test tool', 'Test from John do not
provision' ) ) and (serorgid = 'aircel' )) GROUP BY
serorgid,appname,time_unit ORDER BY time_unit DESC LIMIT 14400 OFFSET 0;
*Time: 127713.413 ms*
Any help will be highly appreciable. I look forward to hearing from you.
Best Regards,
Kaushal
Re: Performance issues while running select sql query
On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote: > Hi, > > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance type > with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing > performance issues. The sql query response takes around *127713.413 ms *time > *.* Is there a way to find out the bottleneck? Send the output of "explain(analyze,buffers)" for the query? Justin
Re: Performance issues while running select sql query
On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby
wrote:
> On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote:
> > Hi,
> >
> > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance
> type
> > with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing
> > performance issues. The sql query response takes around *127713.413 ms
> *time
> > *.* Is there a way to find out the bottleneck?
>
> Send the output of "explain(analyze,buffers)" for the query?
>
> Justin
>
Hi Justin,
Do i need to run the below sql query? Please comment.
explain(analyze,buffers) SELECT serorgid,appname,sum(message_count) AS
> mtrc0,date_trunc('day', client_received_start_timestamp at time zone
> '+5:30:0')::timestamp without time zone AS time_unit FROM
> analytics."test.prod.fact" WHERE client_received_start_timestamp >=
> '2018-3-28 18:30:0' AND client_received_start_timestamp < '2018-4-11
> 18:30:0' AND ((apiproxy in ('test-service' ) ) and (exchangeinstance !=
> '(not set)' ) and (devemail != '[email protected]' ) and (devemail != '
> [email protected]' ) and (devemail != '[email protected]' ) and (devemail
> != '[email protected]' ) and (apistatus = 'Success' ) and (apiaction not
> in ('LRN','finder','ManuallySelect' ) ) and (appname not in ('Mobile
> Connect Developer Portal (Int(', 'MinskHBM', 'LondonHBM', 'SeoulHBM',
> 'MumbaiHBM', 'NVirginiaHBM','SPauloHBM', 'Mobile Connect HeartBeat
> Monitor', 'PDMAOpenSDKTest1', 'PDMAOpenSDKTest2', 'PDMASDKTest',
> 'APIHealth', 'A1qaDemoApp','test', 'dublin o2o test tool', 'Test from John
> do not provision' ) ) and (serorgid = 'aircel' )) GROUP BY
> serorgid,appname,time_unit ORDER BY time_unit DESC LIMIT 14400 OFFSET 0;
I look forward to hearing from you.
Best Regards,
Re: Performance issues while running select sql query
On Sun, Apr 29, 2018 at 10:33 AM, Kaushal Shriyan
wrote:
>
>
> On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby
> wrote:
>
>> On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote:
>> > Hi,
>> >
>> > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance
>> type
>> > with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing
>> > performance issues. The sql query response takes around *127713.413 ms
>> *time
>> > *.* Is there a way to find out the bottleneck?
>>
>> Send the output of "explain(analyze,buffers)" for the query?
>>
>> Justin
>>
>
> Hi Justin,
>
> Do i need to run the below sql query? Please comment.
>
> explain(analyze,buffers) SELECT serorgid,appname,sum(message_count) AS
>> mtrc0,date_trunc('day', client_received_start_timestamp at time zone
>> '+5:30:0')::timestamp without time zone AS time_unit FROM
>> analytics."test.prod.fact" WHERE client_received_start_timestamp >=
>> '2018-3-28 18:30:0' AND client_received_start_timestamp < '2018-4-11
>> 18:30:0' AND ((apiproxy in ('test-service' ) ) and (exchangeinstance !=
>> '(not set)' ) and (devemail != '[email protected]' ) and (devemail != '
>> [email protected]' ) and (devemail != '[email protected]' ) and (devemail
>> != '[email protected]' ) and (apistatus = 'Success' ) and (apiaction not
>> in ('LRN','finder','ManuallySelect' ) ) and (appname not in ('Mobile
>> Connect Developer Portal (Int(', 'MinskHBM', 'LondonHBM', 'SeoulHBM',
>> 'MumbaiHBM', 'NVirginiaHBM','SPauloHBM', 'Mobile Connect HeartBeat
>> Monitor', 'PDMAOpenSDKTest1', 'PDMAOpenSDKTest2', 'PDMASDKTest',
>> 'APIHealth', 'A1qaDemoApp','test', 'dublin o2o test tool', 'Test from John
>> do not provision' ) ) and (serorgid = 'aircel' )) GROUP BY
>> serorgid,appname,time_unit ORDER BY time_unit DESC LIMIT 14400 OFFSET 0;
>
>
> I look forward to hearing from you.
>
> Best Regards,
>
>
Hi Justin,
Please find the below details and let me know if you need any additional
information.
> QUERY PLAN
>
>
>
>
>
>
>
>
>
>
> ---
> Limit (cost=2568002.26..2568038.26 rows=14400 width=35) (actual
> time=127357.296..127357.543 rows=231 loops=1)
>Buffers: shared hit=28019 read=1954681
>-> Sort (cost=2568002.26..2568389.38 rows=154849 width=35) (actual
> time=127357.294..127357.383 rows=231 loops=1)
> Sort Key: ((date_trunc('day'::text, timezone('+5:30:0'::text,
> "test.prod.fact".client_received_start_timestamp)))::timestamp without time
> zone)
> Sort Method: quicksort Memory: 45kB
> Buffers: shared hit=28019 read=1954681
> -> HashAggregate (cost=2553822.90..2556532.76 rows=154849
> width=35) (actual time=127356.707..127357.103 rows=231 loops=1)
>Group Key: (date_trunc('day'::text,
> timezone('+5:30:0'::text,
> "test.prod.fact".client_received_start_timestamp)))::timestamp without time
> zone, "test.prod.fact".serorgid, "excha
> nge-p.prod.fact".appname
>Buffers: shared hit=28016 read=1954681
>-> Result (cost=0.43..2551252.21 rows=257069 width=35)
> (actual time=2.399..126960.471 rows=311015 loops=1)
> Buffers: shared hit=28016 read=1954681
> -> Append (cost=0.43..2549324.20 rows=257069
> width=35) (actual time=2.294..126163.689 rows=311015 loops=1)
>Buffers: shared hit=28016 read=1954681
>-> Index Scan using
> "exchange-pprodfactclrecsts" on "test.prod.fact" (cost=0.43..6644.45
> rows=64 width=33) (actual time=2.292..3.887 rows=2 loops=1)
> Index Cond:
> ((client_received_start_timestamp >= '2018-03-28 18:30:00'::timestamp
> without time zone) AND (client_received_start_timestamp < '2018-04-11
> 18:30:00'::timestam
> p without time zone))
> Filter: ((exchangeinstance <> '(not
> set)'::text) AND (devemail <> '[email protected]'::text) AND (devemail <> '
> [email protected]'::text) AND (devemail <> '[email protected]'::
> text) AND (devemail <>
Performance issues while running select sql query
On Saturday, April 28, 2018, Kaushal Shriyan wrote: > Hi, > > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance > type with 500 GB volume of volume type io1 with 25000 IOPS and I am > seeing performance issues. The sql query response takes around *127713.413 > ms *time*.* Is there a way to find out the bottleneck? > I would suggest reading the following and providing some additional details, in particular your table and/or view definitions. Specifically I'd be looking for indexes on "serorgid", your apparent partitioning setup, and your use of indexes in general. https://wiki.postgresql.org/wiki/Slow_Query_Questions You may also wish to attach the explain output as a text file. David J.
