Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXU4G-0007kr-UB for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 12:24:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXU4E-0005l3-TO for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 12:24:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXU4C-0005ku-2E for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 12:24:20 +0000 Received: from mail-oi0-x22b.google.com ([2607:f8b0:4003:c06::22b]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YXU41-0004lY-Vr for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 12:24:18 +0000 Received: by oigv203 with SMTP id v203so1411376oig.3 for ; Mon, 16 Mar 2015 05:24:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zetainteractive.com; s=google; h=reply-to:from:references:in-reply-to:mime-version:thread-index:date :message-id:subject:to:cc:content-type; bh=O5Hv8lmocfcrYTpWezS55YXfwsOCjCJPIoBfgpDj4E8=; b=QyhoUxS2keOsqA4eWeN/ijq41ayGyy1ycz9aEDQGyV7k339hKNeHEw0mQtPMe1bklT 48H0TxQfoQ/tr3V8P4KdhjucQ/gvn7YYpajaKqgyC/tzK/zEOgan4zyqaSdju2oxvk4Z 8UogA8R5jWfnZtsBXwDsZoSqiSMnarGtN/k0Q= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:reply-to:from:references:in-reply-to :mime-version:thread-index:date:message-id:subject:to:cc :content-type; bh=O5Hv8lmocfcrYTpWezS55YXfwsOCjCJPIoBfgpDj4E8=; b=NF6RMYO6b3h2JYQcuCSwQgsdXzikaGT8zSHbN2C16IGoaMbptfhf6Ok5zU/MJNywb4 rjupYaCryHVigCwTZ/zbx9zfXTysRmFSAburzidDwtdtsPPT8n3ZozM7/P72u91AdOLG 0YwC+fWxN/dJMoxY1oTpInqFQMGh+ebijsTqOwIfhXBGiu9xGjFpLTn73ilC1VAwxqsM AwVst53SZascF4P8haTh+4r9+uWYwhJH+9m0PBdp3BcMWpB/m/qFFkR1rR5JWU46wlTE tGMkogyCYN+v4sVjtkPXwJMC5c3sKfxqgeEvJXDWr8hCZXvTmu+MtzOSbEOjQ7Thhcu7 J2gQ== X-Gm-Message-State: ALoCoQnrvevT/6uj1cYqIL8gZ6y24R0/YPoszKtSwZznGlvIbGj5z/xGHzyP2p/QIQ3+NRP+yNXd X-Received: by 10.60.124.83 with SMTP id mg19mr4636655oeb.42.1426508647038; Mon, 16 Mar 2015 05:24:07 -0700 (PDT) Reply-To: From: Vivekanand Joshi References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com> <160b8780d89add7f23c4eb9c3debb99d@mail.gmail.com> <55034A48.4040409@2ndquadrant.com> <849e75d9463938372dd4c516f5c5d89c@mail.gmail.com> <55037CEA.5050906@2ndquadrant.com> <5504A2CE.30007@archidevsys.co.nz> <5504A8E7.7080008@archidevsys.co.nz> In-Reply-To: <5504A8E7.7080008@archidevsys.co.nz> MIME-Version: 1.0 X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQGXPdRf3VxuG24kJ8WwflsQcHnAgQJFdAGLAd7f+TAB3+I8LgHxOMQ4AZdEQFACZq94KAEoF2rjAunz58UBLhBnRQIZ3EmNAvFlU6oBnOQkrQJeOYEqnL98+mA= Date: Mon, 16 Mar 2015 17:54:05 +0530 Message-ID: <698495ca8f09d9b320b81b381d5e7540@mail.gmail.com> Subject: Re: Performance issues To: Gavin Flower , Varadharajan Mukundan Cc: Tomas Vondra , Scott Marlowe , pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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 > 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