Re: 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(at)archidevsys(dot)co(dot)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
>>
>>
>
>
>