From: Vivekanand Joshi <vjoshi@zetainteractive.com>
To: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Varadharajan Mukundan <srinathsmn@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Mon, 16 Mar 2015 17:54:05 +0530
Message-ID: <698495ca8f09d9b320b81b381d5e7540@mail.gmail.com> (raw)
In-Reply-To: <5504A8E7.7080008@archidevsys.co.nz>
References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com>
<CACKkDGF=Qx_fUje5meLAMFWMJ-c7AUZ9V4GDtBDpr3zO3VKwpA@mail.gmail.com>
<160b8780d89add7f23c4eb9c3debb99d@mail.gmail.com>
<CACKkDGETLNUON-bLM0gCo_r-+ts3Q29aQwmargv1Usi=PgQvVg@mail.gmail.com>
<CANwLwatODrcwDgO+JXG-L=jJJQTucvUrur01E-y_tE_PT8i7kQ@mail.gmail.com>
<55034A48.4040409@2ndquadrant.com>
<CANwLwavsL-+SDRB7VKxQ_OJq-T=MVQV6T6qH5Aug9PaSWt2C0w@mail.gmail.com>
<CACKkDGHOnb2efYvLj557E5pSuzS+Wmu9dqG3Z=b_dbYhpi7muQ@mail.gmail.com>
<CAOR=d=2L86fB+HWX8h078tYsd7LVKKFPQb=jfd8YNRU1Fc7mUA@mail.gmail.com>
<849e75d9463938372dd4c516f5c5d89c@mail.gmail.com>
<55037CEA.5050906@2ndquadrant.com>
<5504A2CE.30007@archidevsys.co.nz>
<CACKkDGFjjPG4BTJgZMzHrD608XaGdNzLRthmC6hhuLa41EdNtA@mail.gmail.com>
<5504A8E7.7080008@archidevsys.co.nz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Team,
This is the EXPLAIN ANALYZE for one of the view : S_V_D_CAMPAIGN_HIERARCHY:
===========================================
Nested Loop (cost=33666.96..37971.39 rows=1 width=894) (actual
time=443.556..966558.767 rows=45360 loops=1)
Join Filter: (tp_exec.touchpoint_execution_id =
valid_executions.touchpoint_execution_id)
Rows Removed by Join Filter: 3577676116
CTE valid_executions
-> Hash Join (cost=13753.53..31711.17 rows=1 width=8) (actual
time=232.571..357.749 rows=52997 loops=1)
Hash Cond:
((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id =
s_f_touchpoint_execution_status_history.touchpoint_execution_id) AND ((max(s
_f_touchpoint_execution_status_history_1.creation_dt)) =
s_f_touchpoint_execution_status_history.creation_dt))
-> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)
(actual time=139.713..171.340 rows=76454 loops=1)
-> Seq Scan on s_f_touchpoint_execution_status_history
s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows=291104
width=16) (actual ti
me=0.006..38.582 rows=291104 loops=1)
-> Hash (cost=5493.80..5493.80 rows=135878 width=16) (actual
time=92.737..92.737 rows=136280 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 6389kB
-> Seq Scan on s_f_touchpoint_execution_status_history
(cost=0.00..5493.80 rows=135878 width=16) (actual time=0.012..55.078
rows=136280 loops=1)
Filter: (touchpoint_execution_status_type_id = ANY
('{3,4}'::integer[]))
Rows Removed by Filter: 154824
-> Nested Loop Left Join (cost=1955.80..6260.19 rows=1 width=894)
(actual time=31.608..3147.015 rows=67508 loops=1)
-> Nested Loop (cost=1955.67..6260.04 rows=1 width=776) (actual
time=31.602..2912.625 rows=67508 loops=1)
-> Nested Loop Left Join (cost=1955.54..6259.87 rows=1
width=658) (actual time=31.595..2713.696 rows=72427 loops=1)
-> Nested Loop Left Join (cost=1955.40..6259.71
rows=1 width=340) (actual time=31.589..2532.926 rows=72427 loops=1)
-> Nested Loop Left Join (cost=1955.27..6259.55
rows=1 width=222) (actual time=31.581..2354.662 rows=72427 loops=1)
-> Nested Loop (cost=1954.99..6259.24
rows=1 width=197) (actual time=31.572..2090.104 rows=72427 loops=1)
-> Nested Loop
(cost=1954.71..6258.92 rows=1 width=173) (actual time=31.562..1802.857
rows=72427 loops=1)
Join Filter:
(camp_exec.campaign_id = wave.campaign_id)
Rows Removed by Join Filter:
243
-> Nested Loop
(cost=1954.42..6254.67 rows=13 width=167) (actual time=31.551..1468.718
rows=72670 loops=1)
-> Hash Join
(cost=1954.13..6249.67 rows=13 width=108) (actual time=31.525..402.039
rows=72670 loops=1)
Hash Cond:
((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id =
tp.wave_id))
-> Hash Join
(cost=1576.83..4595.51 rows=72956 width=90) (actual time=26.254..256.328
rows=72956 loops=1)
Hash Cond:
(tp_exec.wave_execution_id = wave_exec.wave_execution_id)
-> Seq Scan
on s_d_touchpoint_execution tp_exec (cost=0.00..1559.56 rows=72956
width=42) (actual time=0.005..76.099 rows=72956 loops=1)
-> Hash
(cost=1001.37..1001.37 rows=46037 width=56) (actual time=26.178..26.178
rows=46037 loops=1)
Buckets:
8192 Batches: 1 Memory Usage: 4104kB
-> Seq
Scan on s_d_wave_execution wave_exec (cost=0.00..1001.37 rows=46037
width=56) (actual time=0.006..10.388 rows=46037 loops=1)
-> Hash
(cost=212.72..212.72 rows=10972 width=26) (actual time=5.252..5.252
rows=10972 loops=1)
Buckets: 2048
Batches: 1 Memory Usage: 645kB
-> Seq Scan
on s_d_touchpoint tp (cost=0.00..212.72 rows=10972 width=26) (actual
time=0.012..2.319 rows=10972 loops=1)
-> Index Scan using
s_d_campaign_execution_idx on s_d_campaign_execution camp_exec
(cost=0.29..0.37 rows=1 width=67) (actual time=0.013..0.013 rows=1
loops=72670)
Index Cond:
(campaign_execution_id = wave_exec.campaign_execution_id)
-> Index Scan using
s_d_wave_pkey on s_d_wave wave (cost=0.29..0.31 rows=1 width=22) (actual
time=0.003..0.003 rows=1 loops=72670)
Index Cond: (wave_id =
wave_exec.wave_id)
-> Index Scan using
s_d_campaign_pkey on s_d_campaign camp (cost=0.29..0.32 rows=1 width=40)
(actual time=0.003..0.003 rows=1 loops=72427)
Index Cond: (campaign_id =
camp_exec.campaign_id)
-> Index Scan using s_d_content_pkey on
s_d_content content (cost=0.28..0.30 rows=1 width=33) (actual
time=0.002..0.003 rows=1 loops=72427)
Index Cond: (tp_exec.content_id =
content_id)
-> Index Scan using s_d_message_type_pkey on
s_d_message_type message_type (cost=0.13..0.15 rows=1 width=120) (actual
time=0.001..0.002 rows=1 loops=72427)
Index Cond: (tp_exec.message_type_id =
message_type_id)
-> Index Scan using s_d_group_pkey on s_d_group grup
(cost=0.13..0.15 rows=1 width=320) (actual time=0.001..0.002 rows=1
loops=72427)
Index Cond: (camp_exec.group_id = group_id)
-> Index Scan using d_channel_pk on s_d_channel_type channel
(cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.002 rows=1
loops=72427)
Index Cond: (channel_type_id = tp.channel_type_id)
-> Index Scan using s_d_category_pkey on s_d_category "CATEGORY"
(cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.002 rows=1
loops=67508)
Index Cond: (camp.category_id = category_id)
-> CTE Scan on valid_executions (cost=0.00..0.02 rows=1 width=8)
(actual time=0.004..6.803 rows=52997 loops=67508)
Total runtime: 966566.574 ms
========================================================
Can you please see it an let me know where is the issue?
-----Original Message-----
From: Gavin Flower [mailto:GavinFlower@archidevsys.co.nz]
Sent: Sunday, March 15, 2015 3:02 AM
To: Varadharajan Mukundan
Cc: Tomas Vondra; vjoshi@zetainteractive.com; Scott Marlowe;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance issues
On 15/03/15 10:23, Varadharajan Mukundan wrote:
> Hi Gavin,
>
> Vivekanand is his first mail itself mentioned the below configuration
> of postgresql.conf. It looks good enough to me.
>
> Total Memory : 8 GB
>
> shared_buffers = 2GB
>
> work_mem = 64MB
>
> maintenance_work_mem = 700MB
>
> effective_cache_size = 4GB
Sorry, it didn't register when I read it!
(Probably reading too fast)
>
> On Sat, Mar 14, 2015 at 10:06 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz> wrote:
>> On 14/03/15 13:12, Tomas Vondra wrote:
>>> On 14.3.2015 00:28, Vivekanand Joshi wrote:
>>>> Hi Guys,
>>>>
>>>> So here is the full information attached as well as in the link
>>>> provided below:
>>>>
>>>> http://pgsql.privatepaste.com/41207bea45
>>>>
>>>> I can provide new information as well.
>>> Thanks.
>>>
>>> We still don't have EXPLAIN ANALYZE - how long was the query running
>>> (I assume it got killed at some point)? It's really difficult to
>>> give you any advices because we don't know where the problem is.
>>>
>>> If EXPLAIN ANALYZE really takes too long (say, it does not complete
>>> after an hour / over night), you'll have to break the query into
>>> parts and first tweak those independently.
>>>
>>> For example in the first message you mentioned that select from the
>>> S_V_D_CAMPAIGN_HIERARCHY view takes ~9 minutes, so start with that.
>>> Give us EXPLAIN ANALYZE for that query.
>>>
>>> Few more comments:
>>>
>>> (1) You're using CTEs - be aware that CTEs are not just aliases, but
>>> impact planning / optimization, and in some cases may prevent
>>> proper optimization. Try replacing them with plain views.
>>>
>>> (2) Varadharajan Mukundan already recommended you to create index on
>>> s_f_promotion_history.send_dt. Have you tried that? You may also
>>> try creating an index on all the columns needed by the query, so
>>> that "Index Only Scan" is possible.
>>>
>>> (3) There are probably additional indexes that might be useful here.
>>> What I'd try is adding indexes on all columns that are either a
>>> foreign key or used in a WHERE condition. This might be an
>>> overkill in some cases, but let's see.
>>>
>>> (4) I suspect many of the relations referenced in the views are not
>>> actually needed in the query, i.e. the join is performed but
>>> then it's just discarded because those columns are not used.
>>> Try to simplify the views as much has possible - remove all the
>>> tables that are not really necessary to run the query. If two
>>> queries need different tables, maybe defining two views is
>>> a better approach.
>>>
>>> (5) The vmstat / iostat data are pretty useless - what you provided are
>>> averages since the machine was started, but we need a few samples
>>> collected when the query is running. I.e. start the query, and
>>> then
>>> give us a few samples from these commands:
>>>
>>> iostat -x -k 1
>>> vmstat 1
>>>
>>>> Would like to see if queries of these type can actually run in
>>>> postgres server?
>>> Why not? We're running DWH applications on tens/hundreds of GBs.
>>>
>>>> If yes, what would be the minimum requirements for hardware? We
>>>> would like to migrate our whole solution on PostgreSQL as we can
>>>> spend on hardware as much as we can but working on a proprietary
>>>> appliance is becoming very difficult for us.
>>> That's difficult to say, because we really don't know where the
>>> problem is and how much the queries can be optimized.
>>>
>>>
>> I notice that no one appears to have suggested the default setting in
>> postgresql.conf - these need changing as they are initially set up
>> for small machines, and to let PostgreSQL take anywhere near full
>> advantage of a box have large amounts of RAM, you need to change some
>> of the configuration settings!
>>
>> For example 'temp_buffers' (default 8MB) and 'maintenance_work_mem'
>> (default
>> 16MB) should be drastically increased, and there are other settings
>> that need changing. The precise values depend on many factors, but
>> the initial values set by default are definitely far too small for your
>> usage.
>>
>> Am assuming that you are looking at PostgreSQL 9.4.
>>
>>
>>
>> Cheers,
>> Gavin
>>
>>
>
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: vjoshi@zetainteractive.com, GavinFlower@archidevsys.co.nz, srinathsmn@gmail.com, tomas.vondra@2ndquadrant.com, scott.marlowe@gmail.com
Subject: Re: Performance issues
In-Reply-To: <698495ca8f09d9b320b81b381d5e7540@mail.gmail.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