Re: count with high allocation

---------- Forwarded message ----------
From: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Date: 2010/4/7
Subject: Res: [GENERAL] count with high allocation
To: pgsql-general(at)postgresql(dot)org

shared_buffer is too large. It is good for server with 64GB RAM. It
can be about 1/2 RAM for dedicated server. PostgreSQL allocate shared
memory after start - before your query, and use it as memory cache.

postgres=# show shared_buffers;
 shared_buffers
----------------
 16GB
(1 row)

shared_buffers + work_mem * max_connection < 90% of dedicated memory

Regards
Pavel Stehule

postgres=# show work_mem;
 work_mem
----------
 5MB
(1 row)

________________________________
De: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Cc: pgsql-general(at)postgresql(dot)org
Enviadas: Quarta-feira, 7 de Abril de 2010 13:51:24
Assunto: Re: [GENERAL] count with high allocation

2010/4/7 paulo matadr <saddoness(at)yahoo(dot)com(dot)br>:
> with
> set enable_hashagg to off , I give the same allocation.

ok, then problem will be other.

what is result of:

show shared_buffers;
show work_mem;

Regards
Pavel Stehule
>
> ________________________________
> De: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
> Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; GENERAL <pgsql-general(at)postgresql(dot)org>
> Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23
> Assunto: Re: [GENERAL] count with high allocation
>
> please, EXPLAIN ANALYZE
>
> and try to execute
>
> set enable_hashagg to off before as second variant. It have to take less
> memory
>
> regards
> Pavel Stehule
>
> 2010/4/7 paulo matadr <saddoness(at)yahoo(dot)com(dot)br>:
>> EXPLAIN/TOP
>>
>> EXPLAIN:
>> Aggregate  (cost=3710076.58..3710076.59 rows=1 width=4)
>>   ->  Hash Join  (cost=2668820.36..3692200.16 rows=7150570 width=4)
>>         Hash Cond: (osunidade.attp_id = art.attp_id)
>>         ->  Hash Join  (cost=2668819.29..3593878.75 rows=7150570 width=8)
>>               Hash Cond: (osunidade.orse_id = os.orse_id)
>>               ->  Seq Scan on ordem_servico_unidade osunidade
>> (cost=0.00..429514.00 rows=23418900 width=8)
>>               ->  Hash  (cost=2598702.48..2598702.48 rows=4033665 width=4)
>>                     ->  Hash Left Join  (cost=1372486.83..2598702.48
>> rows=4033665 width=4)
>>                           Hash Cond: (os.cbdo_id = cobra.cbdo_id)
>>                           ->  Merge Join  (cost=0.00..880392.67
>> rows=4033665
>> width=8)
>>                                 Merge Cond: (os.rgat_id = ra.rgat_id)
>>                                 ->  Index Scan using xfk1_ordem_servico on
>> ordem_servico os  (cost=0.00..879051.89 rows=13210693 width=12)
>>                                 ->  Index Scan using
>> registro_atendimento_pkey on registro_atendimento ra
>> (cost=0.00..548171.12
>> rows=5369913 width=4)
>>                                       Filter: (rgat_id IS NOT NULL)
>>                           ->  Hash  (cost=897238.26..897238.26
>> rows=27340126
>> width=4)
>>                                 ->  Seq Scan on cobranca_documento cobra
>> (cost=0.00..897238.26 rows=27340126 width=4)
>>         ->  Hash  (cost=1.03..1.03 rows=3 width=4)
>>               ->  Seq Scan on atendimento_relacao_tipo art
>> (cost=0.00..1.03
>> rows=3 width=4)
>>
>> "TOP"
>> PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>>
>> 26809 postgres  16   0 16.5g 8.6g 8.6g S    1 27.5   1:28.84 postgres:
>> user
>> database 10.1.1.7(54033) SELECT
>> ________________________________
>> De: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
>> Cc: GENERAL <pgsql-general(at)postgresql(dot)org>
>> Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
>> Assunto: Re: [GENERAL] count with high allocation
>>
>> paulo matadr <saddoness(at)yahoo(dot)com(dot)br> writes:
>>> Monitoring "top" in database server  , i could  noticed an query with
>>> reserved  8GB on physical memory.
>>
>>> select count(field) from big_table  1 inner join big_table2...
>>
>>> There is the possibility of using another function with less memory
>>> allocation?
>>> Is there a way to limit the memory usage of the count?
>>
>> It seems quite likely that what top is telling you just reflects the
>> process touching all shared buffers, and has nothing to do with any
>> real "memory consumption".  What do you have shared_buffers set to?
>>
>>             regards, tom lane
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>> ________________________________
>> Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
>> Celebridades - Música - Esportes
>
>

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
Celebridades - Música - Esportes