Thread: Postgres not using array

Postgres not using array

From
André Volpato
Date:
Folks,

In a fresh Pg8.3.3 box, we created a 3-stripe RAID0 array.
The write speed is about 180MB/s, as shown by dd :

# dd if=/dev/zero of=/postgres/base/teste2 bs=1024 count=5000000
5000000+0 records in
5000000+0 records out
5120000000 bytes (5,1 GB) copied, 28,035 seconds, 183 MB/s

BTW, /postgres is $PGDATA ...

Unfortunely, we cant figure out why Postgres is not using the disks at all.
Right now, there are 3 heavy queryes running, and the disks are almost
sleeping...
The CPU is 100% used since a few hours ago. Can anyone tell why?

# dstat
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq|_read _writ|_recv _send|__in_ _out_|_int_ _csw_
 99   1   0   0   0   0|   0     0 | 574B 1710B|   0     0 |   6    55
 99   1   0   0   0   0|   0     0 | 290B  770B|   0     0 |   8    48
 99   1   0   0   0   0|   0     0 | 220B  540B|   0     0 |   6    44
 99   1   0   0   0   0|   0     0 | 354B  818B|   0     0 |  10    50
 98   2   0   0   0   0|   0     0 |1894B 4857B|   0     0 |  27    78
100   0   0   0   0   1|   0     0 | 348B 1096B|   0     0 |  12    45

The specs:
Intel(R) Pentium(R) Dual  CPU  E2160  @ 1.80GHz
Linux dbserver4 2.6.24-etchnhalf.1-686-bigmem #1 SMP Mon Jul 21 11:59:12
UTC 2008 i686 GNU/Linux
4GB RAM

--

[]´s, ACV



Re: Postgres not using array

From
"David Wilson"
Date:
On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
<andre.volpato@ecomtecnologia.com.br> wrote:

> The CPU is 100% used since a few hours ago. Can anyone tell why?

Sounds like you've just got a CPU bound query. The data may even all
be in cache.

Some information on database size, along with EXPLAIN results for your
queries, would help here.

--
- David T. Wilson
david.t.wilson@gmail.com

Re: Postgres not using array

From
André Volpato
Date:
David Wilson escreveu:
> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
> <andre.volpato@ecomtecnologia.com.br> wrote:
>
>
>> The CPU is 100% used since a few hours ago. Can anyone tell why?
>>
>
> Sounds like you've just got a CPU bound query. The data may even all
> be in cache.
>
> Some information on database size, along with EXPLAIN results for your
> queries, would help here.
>

The query itself runs smoothly, almost with no delay.

I am into some serious hardware fault. The application runs the query,
and store the results
like text files in another server. For some reason, the query pid
remains active
in the dbserver, and taking 100% CPU.

I´m gonna dig a little more.
Maybe the application is not able to store the results, or something.

--

[]´s, ACV



Re: Postgres not using array

From
André Volpato
Date:
André Volpato escreveu:
>
> David Wilson escreveu:
>> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
>> <andre.volpato@ecomtecnologia.com.br> wrote:
>>
>>
>>> The CPU is 100% used since a few hours ago. Can anyone tell why?
>>>
>>
>> Sounds like you've just got a CPU bound query. The data may even all
>> be in cache.
>>
>> Some information on database size, along with EXPLAIN results for your
>> queries, would help here.
>>
>
> The query itself runs smoothly, almost with no delay.
>

You where right about the cache.
After some experiences, I noticed that the arrays are being used, but
only for a short time...
So, what is slowing down is the CPU (Intel(R) Pentium(R) Dual  CPU
E2160  @ 1.80GHz)

In practice, I have noticed that dual 1.8 is worse than single 3.0. We
have another server wich
is a Pentium D 3.0 GHz, that runs faster.

Explain output:
 HashAggregate  (cost=19826.23..19826.96 rows=73 width=160) (actual
time=11826.754..11826.754 rows=0 loops=1)
   ->  Subquery Scan b2  (cost=19167.71..19817.21 rows=722 width=160)
(actual time=11826.752..11826.752 rows=0 loops=1)
         Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,
'qtdI'::text, 'P'::bpchar) >= 1::numeric)
         ->  Limit  (cost=19167.71..19248.89 rows=2165 width=48) (actual
time=415.157..621.043 rows=28923 loops=1)
               ->  HashAggregate  (cost=19167.71..19248.89 rows=2165
width=48) (actual time=415.155..593.309 rows=28923 loops=1)
                     ->  Bitmap Heap Scan on bds_beneficiario b
(cost=832.53..18031.61 rows=56805 width=48) (actual time=68.259..160.912
rows=56646 loops=1)
                           Recheck Cond: ((benef_referencia >= 200805)
AND (benef_referencia <= 200806))
                           ->  Bitmap Index Scan on ibds_beneficiario2
(cost=0.00..818.33 rows=56805 width=0) (actual time=63.293..63.293
rows=56646 loops=1)
                                 Index Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))
 Total runtime: 11827.374 ms

Postgres read the array in less than 1 sec, and the other 10s he takes
100% of CPU usage,
wich is, in this case, one of the two cores at 1.8GHz.

I am a bit confused about what CPU is best for Postgres. Our apps is
mostly read, with
a few connections and heavy queryes.
Does it worth a multi-core ?

--

[]´s, ACV



Re: Postgres not using array

From
Mark Mielke
Date:
André Volpato wrote:
> In practice, I have noticed that dual 1.8 is worse than single 3.0. We
> have another server wich
> is a Pentium D 3.0 GHz, that runs faster.
> ...
> Postgres read the array in less than 1 sec, and the other 10s he takes
> 100% of CPU usage,
> wich is, in this case, one of the two cores at 1.8GHz.
>
> I am a bit confused about what CPU is best for Postgres. Our apps is
> mostly read, with
> a few connections and heavy queryes.
> Does it worth a multi-core ?

How are you doing your benchmarking? If you have two or more queries
running at the same time, I would expect the 1.8 Ghz x 2 to be
significant and possibly out-perform the 3.0 Ghz x 1. If you usually
only have one query running at the same time, I expect the 3.0 Ghz x 1
to always win. PostgreSQL isn't good at splitting the load from a single
client across multiple CPU cores.

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>


Re: Postgres not using array

From
Tom Lane
Date:
=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre.volpato@ecomtecnologia.com.br> writes:
> Explain output:
>  HashAggregate  (cost=19826.23..19826.96 rows=73 width=160) (actual
> time=11826.754..11826.754 rows=0 loops=1)
>    ->  Subquery Scan b2  (cost=19167.71..19817.21 rows=722 width=160)
> (actual time=11826.752..11826.752 rows=0 loops=1)
>          Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,
> 'qtdI'::text, 'P'::bpchar) >= 1::numeric)
>          ->  Limit  (cost=19167.71..19248.89 rows=2165 width=48) (actual
> time=415.157..621.043 rows=28923 loops=1)

So I guess the question is "what is the bds_internacoes function, and
why is it so slow?"

            regards, tom lane

Re: Postgres not using array

From
André Volpato
Date:
Tom Lane escreveu:
> =?ISO-8859-1?Q?Andr=E9_Volpato?= <andre.volpato@ecomtecnologia.com.br> writes:
>
>> Explain output:
>>  HashAggregate  (cost=19826.23..19826.96 rows=73 width=160) (actual
>> time=11826.754..11826.754 rows=0 loops=1)
>>    ->  Subquery Scan b2  (cost=19167.71..19817.21 rows=722 width=160)
>> (actual time=11826.752..11826.752 rows=0 loops=1)
>>          Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,
>> 'qtdI'::text, 'P'::bpchar) >= 1::numeric)
>>          ->  Limit  (cost=19167.71..19248.89 rows=2165 width=48) (actual
>> time=415.157..621.043 rows=28923 loops=1)
>>
>
> So I guess the question is "what is the bds_internacoes function, and
> why is it so slow?"

This function is quite fast:
 Aggregate  (cost=5.17..5.18 rows=1 width=12) (actual time=0.286..0.287
rows=1 loops=1)
   ->  Index Scan using iinternacoes4 on internacoes  (cost=0.01..5.16
rows=1 width=12) (actual time=0.273..0.273 rows=0 loops=1)
         Index Cond: ((((ano * 100) + mes) >= 200801) AND (((ano * 100)
+ mes) <= 200806) AND ((cod_benef)::text = '0005375200'::text))
         Filter: (tipo_internacao = 'P'::bpchar)
 Total runtime: 0.343 ms


The problem is that its fired up against 29K rows, wich takes the
total runtime about 10s.

We are guessing that a dual core 3.0GHz will beat up a quad core 2.2,
at least in this environmnent with less than 4 concurrent queryes.

--

[]´s, ACV



Re: Postgres not using array

From
Tom Lane
Date:
=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre.volpato@ecomtecnologia.com.br> writes:
> Tom Lane escreveu:
>> So I guess the question is "what is the bds_internacoes function, and
>> why is it so slow?"

> This function is quite fast:

Well, "fast" is relative.  It's not fast enough, or you wouldn't have
been complaining.

> We are guessing that a dual core 3.0GHz will beat up a quad core 2.2,
> at least in this environmnent with less than 4 concurrent queryes.

The most you could hope for from that is less than a 50% speedup.  I'd
suggest investing some tuning effort first.  Some rethinking of your
schema, for example, might buy you orders of magnitude ... with no new
hardware investment.

            regards, tom lane

Re: Postgres not using array

From
André Volpato
Date:
Tom Lane escreveu:
>> We are guessing that a dual core 3.0GHz will beat up a quad core 2.2,
>> at least in this environmnent with less than 4 concurrent queryes.
>
> The most you could hope for from that is less than a 50% speedup.  I'd
> suggest investing some tuning effort first.  Some rethinking of your
> schema, for example, might buy you orders of magnitude ... with no new
> hardware investment.

I think we almost reached the tuning limit, without changing the schema.

You are right, the whole design must be rethinked.
But this question about single vs multi cores has bitten me.

We will rethink the investiment in new hardware too. The databases that are
used less often will be managed to a single core server.

--

[]´s, ACV



Re: Postgres not using array

From
Gregory Stark
Date:
André Volpato <andre.volpato@ecomtecnologia.com.br> writes:

> Tom Lane escreveu:
>>> We are guessing that a dual core 3.0GHz will beat up a quad core 2.2,
>>> at least in this environmnent with less than 4 concurrent queryes.
>>
>> The most you could hope for from that is less than a 50% speedup.  I'd
>> suggest investing some tuning effort first.  Some rethinking of your
>> schema, for example, might buy you orders of magnitude ... with no new
>> hardware investment.
>
> I think we almost reached the tuning limit, without changing the schema.

It's hard to tell from the plan you posted (and with only a brief look) but it
looks to me like your query with that function is basically doing a join but
because the inner side of the join is in your function's index lookup it's
effectively forcing the use of a "nested loop" join. That's usually a good
choice for small queries against big tables but if you're joining a lot of
data there are other join types which are much faster. You might find the
planner can do a better job if you write your query as a plain SQL query and
let the optimizer figure out the best way instead of forcing its hand.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Postgres not using array

From
André Volpato
Date:
<tt>Gregory Stark escreveu:</tt><blockquote cite="mid:87myj5jqc5.fsf@oxford.xeocode.com" type="cite"><pre
wrap=""><tt>AndréVolpato <a class="moz-txt-link-rfc2396E"
href="mailto:andre.volpato@ecomtecnologia.com.br"><andre.volpato@ecomtecnologia.com.br></a>writes:
 
</tt></pre><blockquote type="cite"><tt><br /></tt> <pre wrap=""><tt>I think we almost reached the tuning limit, without
changingthe schema.
 
</tt></pre></blockquote><pre wrap=""><tt>
It's hard to tell from the plan you posted (and with only a brief look) but it
looks to me like your query with that function is basically doing a join but
because the inner side of the join is in your function's index lookup it's
effectively forcing the use of a "nested loop" join. That's usually a good
choice for small queries against big tables but if you're joining a lot of
data there are other join types which are much faster. You might find the
planner can do a better job if you write your query as a plain SQL query and
let the optimizer figure out the best way instead of forcing its hand.</tt></pre></blockquote><tt><br /> Thanks Greg, I
rewrotethe query with a explicit join, removing the function.<br /><br /> The planner uses a nestloop, becouse its only
afew rows, none in the end.<br /> (A HashAggregate is used to join the same query, running against a bigger
database)<br/><br /></tt><tt>The good side about the function is the facility to write in a dinamic application. <br />
We´regonna change it and save some bucks...<br /><br /></tt><tt>Its an impressive win, look:<br /><br />
 HashAggregate (cost=19773.60..19773.61 rows=1 width=160) (actual time=0.511..0.511 rows=0 loops=1)<br />    -> 
NestedLoop  (cost=19143.21..19773.58 rows=1 width=160) (actual time=0.509..0.509 rows=0 loops=1)<br />          Join
Filter:((b.benef_cod_arquivo)::text = (internacoes.cod_benef)::text)<br />          ->  Bitmap Heap Scan on
internacoes (cost=13.34..516.70 rows=1 width=8) (actual time=0.507..0.507 rows=0 loops=1)<br />                Recheck
Cond:((((ano * 100) + mes) >= 200805) AND (((ano * 100) + mes) <= 200806))<br />                Filter:
(tipo_internacao= 'P'::bpchar)<br />                ->  Bitmap Index Scan on iinternacoes4  (cost=0.00..13.34
rows=708width=0) (actual time=0.143..0.143 rows=708 loops=1)<br />                      Index Cond: ((((ano * 100) +
mes)>= 200805) AND (((ano * 100) + mes) <= 200806))<br />          ->  Limit  (cost=19129.87..19209.26
rows=2117width=48) (never executed)<br />                ->  HashAggregate  (cost=19129.87..19209.26 rows=2117
width=48)(never executed)<br />                      ->  Bitmap Heap Scan on bds_beneficiario b 
(cost=822.41..18009.61rows=56013 width=48) (never executed)<br />                            Recheck Cond:
((benef_referencia>= 200805) AND (benef_referencia <= 200806))<br />                            ->  Bitmap
IndexScan on ibds_beneficiario2  (cost=0.00..808.41 rows=56013 width=0) (never executed)<br />
                                Index Cond: ((benef_referencia >= 200805) AND (benef_referencia <= 200806))<br />
 Totalruntime: 0.642 ms<br /><br /><br /><br /></tt> <pre class="moz-signature" cols="72"><tt>-- 
 

[]´s, ACV</tt></pre>

Re: Postgres not using array

From
Greg Smith
Date:
On Thu, 21 Aug 2008, Andr� Volpato wrote:

> So, what is slowing down is the CPU (Intel(R) Pentium(R) Dual CPU E2160
> @ 1.80GHz)..In practice, I have noticed that dual 1.8 is worse than
> single 3.0. We have another server wich is a Pentium D 3.0 GHz, that
> runs faster.

Pentium D models are all dual-core so either you've got the wrong model
number here or you've actually comparing against a 2X3.0GHz part.

The Core 2 Duo E2160 has a very small CPU cache--512KB per core.  Your
older Pentium system probably has quite a bit more.  I suspect that's the
main reason it runs faster on this application.

> I am a bit confused about what CPU is best for Postgres. Our apps is
> mostly read, with a few connections and heavy queryes.

There are a lot of things you can run into with Postgres that end up being
limited by the fact that they only run on a single core, as you've seen
here.  If you've only got a fairly small number of connections running CPU
heavy queries, you probably want a processor with lots of L2 cache and a
fast clock speed, rather than adding a large number of cores running at a
slower speed.  The very small L2 cache on your E2160 is likely what's
holding it back here, and even though the newer processors are
significantly more efficient per clock the gap between 1.8GHz and 3.0GHz
is pretty big.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD