---------- Forwarded message ---------- From: paulo matadr <saddoness@xxxxxxxxxxxx> Date: 2010/4/7 Subject: Res: count with high allocation To: pgsql-general@xxxxxxxxxxxxxx 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@xxxxxxxxx> Para: paulo matadr <saddoness@xxxxxxxxxxxx> Cc: pgsql-general@xxxxxxxxxxxxxx Enviadas: Quarta-feira, 7 de Abril de 2010 13:51:24 Assunto: Re: count with high allocation 2010/4/7 paulo matadr <saddoness@xxxxxxxxxxxx>: > 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@xxxxxxxxx> > Para: paulo matadr <saddoness@xxxxxxxxxxxx> > Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; GENERAL <pgsql-general@xxxxxxxxxxxxxx> > Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23 > Assunto: Re: 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@xxxxxxxxxxxx>: >> 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@xxxxxxxxxxxxx> >> Para: paulo matadr <saddoness@xxxxxxxxxxxx> >> Cc: GENERAL <pgsql-general@xxxxxxxxxxxxxx> >> Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27 >> Assunto: Re: count with high allocation >> >> paulo matadr <saddoness@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) >> 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@xxxxxxxxxxxxxx) 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
