Re: Postgres not using array

David Wilson escreveu:
> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
> <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

André Volpato escreveu:
>
> David Wilson escreveu:
>> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
>> <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

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(at)mielke(dot)cc>

=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

Tom Lane escreveu:
> =?ISO-8859-1?Q?Andr=E9_Volpato?= <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

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

André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Gregory Stark escreveu:</tt>
<blockquote cite="mid:87myj5jqc5(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap=""><tt>Andr&eacute; Volpato <a class="moz-txt-link-rfc2396E" href="mailto:andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br">&lt;andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br&gt;</a> writes:
</tt></pre>
<blockquote type="cite"><tt><br>
</tt>
<pre wrap=""><tt>I think we almost reached the tuning limit, without changing the 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 rewrote the query with a explicit join, removing the
function.<br>
<br>
The planner uses a nestloop, becouse its only a few 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&acute;re gonna change it and save some bucks...<br>
<br>
</tt><tt>Its an impressive win, look:<br>
<br>
&nbsp;HashAggregate&nbsp; (cost=19773.60..19773.61 rows=1 width=160) (actual
time=0.511..0.511 rows=0 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=19143.21..19773.58 rows=1 width=160)
(actual time=0.509..0.509 rows=0 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Join Filter: ((b.benef_cod_arquivo)::text =
(internacoes.cod_benef)::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on internacoes&nbsp; (cost=13.34..516.70
rows=1 width=8) (actual time=0.507..0.507 rows=0 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: ((((ano * 100) + mes) &gt;= 200805) AND
(((ano * 100) + mes) &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (tipo_internacao = 'P'::bpchar)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on iinternacoes4&nbsp;
(cost=0.00..13.34 rows=708 width=0) (actual time=0.143..0.143 rows=708
loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((((ano * 100) + mes) &gt;= 200805)
AND (((ano * 100) + mes) &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Limit&nbsp; (cost=19129.87..19209.26 rows=2117 width=48)
(never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; HashAggregate&nbsp; (cost=19129.87..19209.26 rows=2117
width=48) (never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on bds_beneficiario b&nbsp;
(cost=822.41..18009.61 rows=56013 width=48) (never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: ((benef_referencia &gt;=
200805) AND (benef_referencia &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on
ibds_beneficiario2&nbsp; (cost=0.00..808.41 rows=56013 width=0) (never
executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((benef_referencia &gt;=
200805) AND (benef_referencia &lt;= 200806))<br>
&nbsp;Total runtime: 0.642 ms<br>
<br>
<br>
<br>
</tt>
<pre class="moz-signature" cols="72"><tt>--

[]&acute;s, ACV</tt></pre>
</body>
</html>

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD