Обсуждение: Postgres not using array
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
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
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
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
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>
=?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
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
=?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
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.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
<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>
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