pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres not using array
12+ messages / 6 participants
[nested] [flat]
* Postgres not using array
@ 2008-08-20 18:30 André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 12+ messages in thread
From: André Volpato @ 2008-08-20 18:30 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-20 20:02 David Wilson <david.t.wilson@gmail.com>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 0 replies; 12+ messages in thread
From: David Wilson @ 2008-08-20 20:02 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-20 20:46 André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 12+ messages in thread
From: André Volpato @ 2008-08-20 20:46 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 13:53 André Volpato <andre.volpato@ecomtecnologia.com.br>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 3 replies; 12+ messages in thread
From: André Volpato @ 2008-08-21 13:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 14:16 Mark Mielke <mark@mark.mielke.cc>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
2 siblings, 0 replies; 12+ messages in thread
From: Mark Mielke @ 2008-08-21 14:16 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 14:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
2 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2008-08-21 14:28 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
=?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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 14:51 André Volpato <andre.volpato@ecomtecnologia.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: André Volpato @ 2008-08-21 14:51 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 19:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2008-08-21 19:10 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
=?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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-21 20:05 André Volpato <andre.volpato@ecomtecnologia.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: André Volpato @ 2008-08-21 20:05 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-22 10:28 Gregory Stark <stark@enterprisedb.com>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 12+ messages in thread
From: Gregory Stark @ 2008-08-22 10:28 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-22 17:05 André Volpato <andre.volpato@ecomtecnologia.com.br>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 12+ messages in thread
From: André Volpato @ 2008-08-22 17:05 UTC (permalink / raw)
To: pgsql-performance; +Cc: Gregory Stark <stark@enterprisedb.com>
<!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.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 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´re gonna 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>
-> Nested Loop (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=708 width=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=2117 width=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.61 rows=56013 width=48) (never executed)<br>
Recheck Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))<br>
-> Bitmap Index Scan 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>
Total runtime: 0.642 ms<br>
<br>
<br>
<br>
</tt>
<pre class="moz-signature" cols="72"><tt>--
[]´s, ACV</tt></pre>
</body>
</html>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using array
@ 2008-08-29 02:29 Greg Smith <gsmith@gregsmith.com>
parent: André Volpato <andre.volpato@ecomtecnologia.com.br>
2 siblings, 0 replies; 12+ messages in thread
From: Greg Smith @ 2008-08-29 02:29 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-08-29 02:29 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-20 18:30 Postgres not using array André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-20 20:02 ` David Wilson <david.t.wilson@gmail.com>
2008-08-20 20:46 Re: Postgres not using array André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-21 13:53 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-21 14:16 ` Mark Mielke <mark@mark.mielke.cc>
2008-08-21 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-21 14:51 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-21 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-21 20:05 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-22 10:28 ` Gregory Stark <stark@enterprisedb.com>
2008-08-22 17:05 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2008-08-29 02:29 ` Greg Smith <gsmith@gregsmith.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox