Re: Performance issues - Mailing list pgsql-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
>>
>>
>
>
>