Обсуждение: count with high allocation

Hello

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:
> Hi all,
> 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?

probably you have unactualised statistic. What is result of EXPLAIN
ANALYZE select count(... ?

I think, so planner uses hash arrays

try to

set enable_hashagg to off;
and again SELECT ...

Regards
Pavel Stehule

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

On Wed, Apr 7, 2010 at 8:11 AM, paulo matadr <saddoness@yahoo.com.br> wrote:
> Hi all,
> Monitoring "top" in database server  , i could  noticed an query with
> reserved  8GB on physical memory.

You are likely seeing the SHR and VIRT columns saying that.  Lemme
guess, you've got 8G of shared memory allocated to pgsql?

If not, please show the lines from top that have you worried.

paulo matadr <saddoness@yahoo.com.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

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@yahoo.com.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@sss.pgh.pa.us>
> Para: paulo matadr <saddoness@yahoo.com.br>
> Cc: GENERAL <pgsql-general@postgresql.org>
> Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
> Assunto: Re: [GENERAL] count with high allocation
>
> paulo matadr <saddoness@yahoo.com.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@postgresql.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

On Wed, Apr 7, 2010 at 10:41 AM, paulo matadr <saddoness@yahoo.com.br> wrote:
> with
> set enable_hashagg to off , I give the same allocation.

So what is your setting for shared_buffers?  Cause this looks pretty
normal to me.

2010/4/7 paulo matadr <saddoness@yahoo.com.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.stehule@gmail.com>
> Para: paulo matadr <saddoness@yahoo.com.br>
> Cc: Tom Lane <tgl@sss.pgh.pa.us>; GENERAL <pgsql-general@postgresql.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@yahoo.com.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@sss.pgh.pa.us>
>> Para: paulo matadr <saddoness@yahoo.com.br>
>> Cc: GENERAL <pgsql-general@postgresql.org>
>> Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
>> Assunto: Re: [GENERAL] count with high allocation
>>
>> paulo matadr <saddoness@yahoo.com.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@postgresql.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
>
>

---------- Forwarded message ----------
From: paulo matadr <saddoness@yahoo.com.br>
Date: 2010/4/7
Subject: Res: [GENERAL] count with high allocation
To: pgsql-general@postgresql.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.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: pgsql-general@postgresql.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@yahoo.com.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.stehule@gmail.com>
> Para: paulo matadr <saddoness@yahoo.com.br>
> Cc: Tom Lane <tgl@sss.pgh.pa.us>; GENERAL <pgsql-general@postgresql.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@yahoo.com.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@sss.pgh.pa.us>
>> Para: paulo matadr <saddoness@yahoo.com.br>
>> Cc: GENERAL <pgsql-general@postgresql.org>
>> Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
>> Assunto: Re: [GENERAL] count with high allocation
>>
>> paulo matadr <saddoness@yahoo.com.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@postgresql.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@postgresql.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