From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Varadharajan Mukundan <srinathsmn@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: vjoshi@zetainteractive.com, Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance issues
Date: Sun, 15 Mar 2015 10:32:23 +1300
Message-ID: <5504A8E7.7080008@archidevsys.co.nz> (raw)
In-Reply-To: <CACKkDGFjjPG4BTJgZMzHrD608XaGdNzLRthmC6hhuLa41EdNtA@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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: GavinFlower@archidevsys.co.nz, srinathsmn@gmail.com, tomas.vondra@2ndquadrant.com, scott.marlowe@gmail.com
Subject: Re: Performance issues
In-Reply-To: <5504A8E7.7080008@archidevsys.co.nz>
* 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