pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
General performance questions about postgres on Apple hardware...
12+ messages / 6 participants
[nested] [flat]

* General performance questions about postgres on Apple hardware...
@ 2004-02-20 19:17 Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-24 00:17 ` Re: General performance questions about postgres on Apple hardware... Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Sean Shanny @ 2004-02-20 19:17 UTC (permalink / raw)
  To: pgsql-performance

To all,

This is a 2 question email.  First is asking about general tuning of the 
Apple hardware/postgres combination.  The second is whether is is 
possible to speed up a particular query.

PART 1

Hardware:  Apple G5 dual 2.0 with 8GB memory attached via dual fibre 
channel to a fully loaded 3.5TB XRaid.  The XRaid is configured as two 7 
disk hardware based RAID5 sets software striped to form a RAID50 set.  
The DB, WALS, etc are all on that file set.  Running OSX journaled file 
system  Running postgres 7.4.1.  OSX Server 10.3.2  Postgres is compiled 
locally with  '--enable-recode' '--enable-multibyte=UNICODE' 
'CFLAGS=-mcpu=970 -mtune=970 -mpowerpc64 -O3'

Config stuff that we have changed:

tcpip_socket = true
max_connections = 100

# - Memory -
 
shared_buffers = 16000          # min 16, at least max_connections*2, 
8KB each
sort_mem = 256000               # min 64, size in KB
vacuum_mem = 64000              # min 1024, size in KB
fsync = true                    # turns forced synchronization on or off
wal_sync_method = open_sync     # the default varies across platforms:
                                # fsync, fdatasync, open_sync, or 
open_datasync
wal_buffers = 64                # min 4, 8KB each
checkpoint_segments = 300       # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30 # range 30-3600, in seconds
effective_cache_size = 400000   # typically 8KB each
random_page_cost = 1            # units are one sequential page fetch cost
default_statistics_target = 1000        # range 1-1000


We are generally getting poor performance out of the RAID set, they 
claim 200/MB/sec per channel, the best we can get with straight OS based 
data transfers is 143MB/sec. :-( (we have a call into apple about this) 
When I execute the following, d_url is a big table,

create table temp_url as select * from d_url ;

I would expect to bound by IO but via iostat we are seeing only about 
30mb/sec with bursts of 100+ when the WAL is written.  sy is high as 
well and the tps seems low.

Can anyone shed some light on what we might do to improve performance 
for postgres on this platform?  Also, is there a test that is available 
that would we could run to show the maximum postgres can do on this 
platform?  This is a data warehouse system so generally we only have 1-3 
queries running at anytime.  More often only 1.  We are obviously 
working with very large tables so we are interested in maximizing our IO 
throughput.

          disk1           disk2           disk0       cpu
  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us sy id
 17.04 961 15.99  17.16 957 16.03   8.83   6  0.05  12 32 56
 22.75 580 12.89  22.79 578 12.87   0.00   0  0.00  10 34 56
 24.71 586 14.14  24.67 587 14.14   0.00   0  0.00  12 40 48
 21.98 648 13.91  21.97 648 13.91   0.00   0  0.00  16 27 56
 22.07 608 13.10  22.09 607 13.09   0.00   0  0.00  14 29 57
 26.54 570 14.77  26.37 575 14.80   0.00   0  0.00  12 34 54
 18.91 646 11.93  18.90 646 11.93   0.00   0  0.00   9 33 58
 15.12 636  9.38  15.12 636  9.38   0.00   0  0.00  14 22 64
 16.22 612  9.69  16.23 611  9.68   0.00   0  0.00  20 27 54
 15.02 573  8.41  15.01 574  8.41   0.00   0  0.00  14 29 57
 15.54 593  9.00  15.52 595  9.02   0.00   0  0.00  13 28 59
 22.35 596 13.01  22.42 593 12.99   0.00   0  0.00   9 32 58
 61.57 887 53.33  60.73 901 53.43   4.00   1  0.00   8 48 44
 11.13 2173 23.62  11.13 2167 23.54   0.00   0  0.00  10 68 22
 10.07 2402 23.63  10.20 2368 23.58   4.00   1  0.00  10 72 18
 14.75 1110 15.99  14.74 1116 16.06   8.92   6  0.05  12 42 46
 22.79 510 11.36  22.79 510 11.36   0.00   0  0.00  16 28 56
 23.65 519 11.99  23.50 522 11.98   0.00   0  0.00  13 42 46
 22.45 592 12.98  22.45 592 12.98   0.00   0  0.00  14 27 58
 25.38 579 14.35  25.37 579 14.35   0.00   0  0.00   8 36 56


PART 2

Trying to understand if there is a faster way to do this?  This is part 
of our nightly bulk load of a data warehouse.  We are reading in new 
data, pulling out the relevant bits, and then need to check to see if 
they already exist in the dimension tables.  Use to do this via separate 
lookups for each value, not very fast.  Trying to do this all in the DB now.

The query is

SELECT t1.id, t2.md5, t2.url FROM referral_temp t2 LEFT OUTER JOIN 
d_referral t1 ON t2.md5 = t1.referral_md5;


\d d_referral
 id                 | integer | not null
 referral_md5       | text    | not null
 referral_raw_url   | text    | not null
 referral_host      | text    |
 referral_path      | text    |
 referral_query     | text    |
 job_control_number | integer | not null


\d referral_temp
 md5    | text |
 url    | text |

Actual row count in the temp table:

select count(*) from referral_temp ;
  502347

Actual row count in d_referral table:

select count(*) from d_referral ;
  27908024
 

Note: that an analyze had not been performed on the referral_temp table 
prior to the explain analyze run.

explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5

Nested Loop Left Join  (cost=0.00..3046.00 rows=1001 width=68) (actual 
time=136.513..6440616.541 rows=502347 loops=1)
   ->  Seq Scan on referral_temp t2  (cost=0.00..20.00 rows=1000 
width=64) (actual time=21.730..10552.421 rows=502347 loops=1)
   ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
(cost=0.00..3.01 rows=1 width=40) (actual time=12.768..14.022 rows=1 
loops=502347)
         Index Cond: ("outer".md5 = t1.referral_md5)


Thanks.

--sean
 Total runtime: 6441969.698 ms
(5 rows)


Here is an explain analyze after the analyze was done.  Unfortunately I 
think a lot of the data was still in cache when I did this again :-(

explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;

Nested Loop Left Join  (cost=0.00..1468759.69 rows=480082 width=149) 
(actual time=69.576..3226854.850 rows=502347 loops=1)
   ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
width=145) (actual time=11.206..4003.521 rows=502347 loops=1)
   ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
(cost=0.00..3.01 rows=1 width=40) (actual time=6.396..6.402 rows=1 
loops=502347)
         Index Cond: ("outer".md5 = t1.referral_md5)
 Total runtime: 3227830.752 ms




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-20 21:10 ` scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  1 sibling, 1 reply; 12+ messages in thread

From: scott.marlowe @ 2004-02-20 21:10 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; +Cc: pgsql-performance

On Fri, 20 Feb 2004, Sean Shanny wrote:

> max_connections = 100
> 
> # - Memory -
>  
> shared_buffers = 16000          # min 16, at least max_connections*2, 
> 8KB each
> sort_mem = 256000               # min 64, size in KB

You might wanna drop sort_mem somewhat and just set it during your imports 
to something big like 512000 or larger.  That way with 100 users during 
the day you won't have to worry about swap storms, and when you run your 
updates, you get all that sort_mem.

> Actual row count in the temp table:
> 
> select count(*) from referral_temp ;
>   502347
> 
> Actual row count in d_referral table:
> 
> select count(*) from d_referral ;
>   27908024
>  
> 
> Note: that an analyze had not been performed on the referral_temp table 
> prior to the explain analyze run.
> 
> explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
> OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5
> 
> Nested Loop Left Join  (cost=0.00..3046.00 rows=1001 width=68) (actual 
> time=136.513..6440616.541 rows=502347 loops=1)
>    ->  Seq Scan on referral_temp t2  (cost=0.00..20.00 rows=1000 
> width=64) (actual time=21.730..10552.421 rows=502347 loops=1)
>    ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
> (cost=0.00..3.01 rows=1 width=40) (actual time=12.768..14.022 rows=1 
> loops=502347)
>          Index Cond: ("outer".md5 = t1.referral_md5)
> 
> 
> Thanks.
> 
> --sean
>  Total runtime: 6441969.698 ms
> (5 rows)
> 
> 
> Here is an explain analyze after the analyze was done.  Unfortunately I 
> think a lot of the data was still in cache when I did this again :-(
> 
> explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
> OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;
> 
> Nested Loop Left Join  (cost=0.00..1468759.69 rows=480082 width=149) 
> (actual time=69.576..3226854.850 rows=502347 loops=1)
>    ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
> width=145) (actual time=11.206..4003.521 rows=502347 loops=1)
>    ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
> (cost=0.00..3.01 rows=1 width=40) (actual time=6.396..6.402 rows=1 
> loops=502347)
>          Index Cond: ("outer".md5 = t1.referral_md5)
>  Total runtime: 3227830.752 ms

Hmmm.  It looks like postgresql is still picking a nested loop when it 
should be sorting something faster.  Try doing a "set enable_nestloop = 
off" and see what you get.

If that makes it faster, you may want to adjust the costs of the cpu_* 
stuff higher to see if that can force it to do the right thing.

Looking at the amount of time taken by the nested loop, it looks like the 
problem to me.

And why are you doing a left join of ONE row from one table against the 
whole temp table?  Do you really need to do that?  since there's only one 
row in the source table, and I'd guess is only matches one or a few rows 
from the temp table, this means you're gonna have that one row and a bunch 
of null filled rows to go with it.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-20 21:57   ` Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Sean Shanny @ 2004-02-20 21:57 UTC (permalink / raw)
  To: pgsql-performance



scott.marlowe wrote:

>On Fri, 20 Feb 2004, Sean Shanny wrote:
>
>  
>
>>max_connections = 100
>>
>># - Memory -
>> 
>>shared_buffers = 16000          # min 16, at least max_connections*2, 
>>8KB each
>>sort_mem = 256000               # min 64, size in KB
>>    
>>
>
>You might wanna drop sort_mem somewhat and just set it during your imports 
>to something big like 512000 or larger.  That way with 100 users during 
>the day you won't have to worry about swap storms, and when you run your 
>updates, you get all that sort_mem.
>
>  
>
>>Actual row count in the temp table:
>>
>>select count(*) from referral_temp ;
>>  502347
>>
>>Actual row count in d_referral table:
>>
>>select count(*) from d_referral ;
>>  27908024
>> 
>>
>>Note: that an analyze had not been performed on the referral_temp table 
>>prior to the explain analyze run.
>>
>>explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
>>OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5
>>
>>Nested Loop Left Join  (cost=0.00..3046.00 rows=1001 width=68) (actual 
>>time=136.513..6440616.541 rows=502347 loops=1)
>>   ->  Seq Scan on referral_temp t2  (cost=0.00..20.00 rows=1000 
>>width=64) (actual time=21.730..10552.421 rows=502347 loops=1)
>>   ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
>>(cost=0.00..3.01 rows=1 width=40) (actual time=12.768..14.022 rows=1 
>>loops=502347)
>>         Index Cond: ("outer".md5 = t1.referral_md5)
>>
>>
>>Thanks.
>>
>>--sean
>> Total runtime: 6441969.698 ms
>>(5 rows)
>>
>>
>>Here is an explain analyze after the analyze was done.  Unfortunately I 
>>think a lot of the data was still in cache when I did this again :-(
>>
>>explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
>>OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;
>>
>>Nested Loop Left Join  (cost=0.00..1468759.69 rows=480082 width=149) 
>>(actual time=69.576..3226854.850 rows=502347 loops=1)
>>   ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
>>width=145) (actual time=11.206..4003.521 rows=502347 loops=1)
>>   ->  Index Scan using d_referral_referral_md5_key on d_referral t1  
>>(cost=0.00..3.01 rows=1 width=40) (actual time=6.396..6.402 rows=1 
>>loops=502347)
>>         Index Cond: ("outer".md5 = t1.referral_md5)
>> Total runtime: 3227830.752 ms
>>    
>>
>
>Hmmm.  It looks like postgresql is still picking a nested loop when it 
>should be sorting something faster.  Try doing a "set enable_nestloop = 
>off" and see what you get.
>  
>
New results with the above changes: (Rather a huge improvement!!!)  
Thanks Scott.  I will next attempt to make the cpu_* changes to see if 
it the picks the correct plan.

explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=1669281.60..3204008.48 rows=480082 width=149) 
(actual time=157221.125..-412311.378 rows=502347 loops=1)
   Hash Cond: ("outer".md5 = "inner".referral_md5)
   ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
width=145) (actual time=11.537..1852.336 rows=502347 loops=1)
   ->  Hash  (cost=1356358.48..1356358.48 rows=30344048 width=40) 
(actual time=157187.530..157187.530 rows=0 loops=1)
         ->  Seq Scan on d_referral t1  (cost=0.00..1356358.48 
rows=30344048 width=40) (actual time=14.134..115048.285 rows=27908024 
loops=1)
 Total runtime: 212595.909 ms
(6 rows)
 
Time: 213094.984 ms
tripmaster=# explain analyze  SELECT t1.id, t2.md5, t2.url from url_temp 
t2 LEFT OUTER JOIN d_url t1 ON t2.md5 = t1.url_md5;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=2023843.40..3157938.15 rows=1379872 width=191) 
(actual time=178150.113..867074.579 rows=1172920 loops=1)
   Hash Cond: ("outer".md5 = "inner".url_md5)
   ->  Seq Scan on url_temp t2  (cost=0.00..50461.72 rows=1379872 
width=187) (actual time=6.597..6692.324 rows=1172920 loops=1)
   ->  Hash  (cost=1734904.72..1734904.72 rows=28018272 width=40) 
(actual time=178124.568..178124.568 rows=0 loops=1)
         ->  Seq Scan on d_url t1  (cost=0.00..1734904.72 rows=28018272 
width=40) (actual time=16.912..2639059.078 rows=23239137 loops=1)
 Total runtime: 242846.965 ms
(6 rows)
 
Time: 243190.900 ms

>If that makes it faster, you may want to adjust the costs of the cpu_* 
>stuff higher to see if that can force it to do the right thing.
>
>Looking at the amount of time taken by the nested loop, it looks like the 
>problem to me.
>
>And why are you doing a left join of ONE row from one table against the 
>whole temp table?  Do you really need to do that?  since there's only one 
>row in the source table, and I'd guess is only matches one or a few rows 
>from the temp table, this means you're gonna have that one row and a bunch 
>of null filled rows to go with it.
>
>
>  
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-22 23:30     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  0 siblings, 1 reply; 12+ messages in thread

From: Tom Lane @ 2004-02-22 23:30 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; +Cc: pgsql-performance

Sean Shanny <shannyconsulting@earthlink.net> writes:
> New results with the above changes: (Rather a huge improvement!!!)  
> Thanks Scott.  I will next attempt to make the cpu_* changes to see if 
> it the picks the correct plan.

> explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
> OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;
>                                                                   QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Left Join  (cost=1669281.60..3204008.48 rows=480082 width=149) 
> (actual time=157221.125..-412311.378 rows=502347 loops=1)
>    Hash Cond: ("outer".md5 = "inner".referral_md5)
>    ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
> width=145) (actual time=11.537..1852.336 rows=502347 loops=1)
>    ->  Hash  (cost=1356358.48..1356358.48 rows=30344048 width=40) 
> (actual time=157187.530..157187.530 rows=0 loops=1)
>          ->  Seq Scan on d_referral t1  (cost=0.00..1356358.48 
> rows=30344048 width=40) (actual time=14.134..115048.285 rows=27908024 
> loops=1)
>  Total runtime: 212595.909 ms
> (6 rows)

It seems like the planner is overestimating the cost of a seqscan
relative to indexed access.  Note that the above large seqscan is priced
at 1356358.48 cost units vs 115048.285 actual msec, which says that a
sequential page fetch is taking about 0.1 msec on your hardware.
(You should check the actual size of d_referral to verify this, though.)
The other plan made it look like an indexed fetch was costing several
milliseconds.  You may have a situation where you need to raise
random_page_cost, rather than lowering it as people more often do.

What are you using for random_page_cost anyway?  It doesn't look like
you are at the default.

This also suggests that the performance issue with your RAID array
has to do with seek time rather than transfer bandwidth...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-23 02:48       ` Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-23 03:24         ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 16:25         ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Sean Shanny @ 2004-02-23 02:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

We have the following setting for random page cost:

random_page_cost = 1            # units are one sequential page fetch cost

Any suggestions on what to bump it up to?

We are waiting to hear back from Apple on the speed issues, so far we 
are not impressed with the hardware in helping in the IO department.  
Our DB is about 263GB with indexes now so there is not way it is going 
to fit into memory. :-(  I have taken the step of breaking out the data 
into month based groups just to keep the table sizes down.  Our current 
months table has around 72 million rows in it as of today.  The joys of 
building a data warehouse and trying to make it as fast as possible.

Thanks.

--sean



Tom Lane wrote:

>Sean Shanny <shannyconsulting@earthlink.net> writes:
>  
>
>>New results with the above changes: (Rather a huge improvement!!!)  
>>Thanks Scott.  I will next attempt to make the cpu_* changes to see if 
>>it the picks the correct plan.
>>    
>>
>
>  
>
>>explain analyze  SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT 
>>OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5;
>>                                                                  QUERY PLAN
>>----------------------------------------------------------------------------------------------------------------------------------------------
>> Hash Left Join  (cost=1669281.60..3204008.48 rows=480082 width=149) 
>>(actual time=157221.125..-412311.378 rows=502347 loops=1)
>>   Hash Cond: ("outer".md5 = "inner".referral_md5)
>>   ->  Seq Scan on referral_temp t2  (cost=0.00..16034.81 rows=480081 
>>width=145) (actual time=11.537..1852.336 rows=502347 loops=1)
>>   ->  Hash  (cost=1356358.48..1356358.48 rows=30344048 width=40) 
>>(actual time=157187.530..157187.530 rows=0 loops=1)
>>         ->  Seq Scan on d_referral t1  (cost=0.00..1356358.48 
>>rows=30344048 width=40) (actual time=14.134..115048.285 rows=27908024 
>>loops=1)
>> Total runtime: 212595.909 ms
>>(6 rows)
>>    
>>
>
>It seems like the planner is overestimating the cost of a seqscan
>relative to indexed access.  Note that the above large seqscan is priced
>at 1356358.48 cost units vs 115048.285 actual msec, which says that a
>sequential page fetch is taking about 0.1 msec on your hardware.
>(You should check the actual size of d_referral to verify this, though.)
>The other plan made it look like an indexed fetch was costing several
>milliseconds.  You may have a situation where you need to raise
>random_page_cost, rather than lowering it as people more often do.
>
>What are you using for random_page_cost anyway?  It doesn't look like
>you are at the default.
>
>This also suggests that the performance issue with your RAID array
>has to do with seek time rather than transfer bandwidth...
>
>			regards, tom lane
>
>  
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-23 03:24         ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 12+ messages in thread

From: Tom Lane @ 2004-02-23 03:24 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; +Cc: pgsql-performance

Sean Shanny <shannyconsulting@earthlink.net> writes:
> We have the following setting for random page cost:
> random_page_cost = 1            # units are one sequential page fetch cost
> Any suggestions on what to bump it up to?

Well, the default setting is 4 ... what measurements prompted you to
reduce it to 1?  The particular example you showed suggested that the
true value on your setup might be 10 or more.

Now I would definitely not suggest that you settle on any particular
value based on only one test case.  You need to try to determine an
appropriate average value, bearing in mind that there's likely to be
lots of noise in any particular measurement.

But in general, setting random_page_cost to 1 is only reasonable when
you are dealing with a fully-cached-in-RAM database, which yours isn't.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-23 16:25         ` scott.marlowe <scott.marlowe@ihs.com>
  2004-02-23 16:50           ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  1 sibling, 1 reply; 12+ messages in thread

From: scott.marlowe @ 2004-02-23 16:25 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance

On Sun, 22 Feb 2004, Sean Shanny wrote:

> Tom,
> 
> We have the following setting for random page cost:
> 
> random_page_cost = 1            # units are one sequential page fetch cost
> 
> Any suggestions on what to bump it up to?
> 
> We are waiting to hear back from Apple on the speed issues, so far we 
> are not impressed with the hardware in helping in the IO department.  
> Our DB is about 263GB with indexes now so there is not way it is going 
> to fit into memory. :-(  I have taken the step of breaking out the data 
> into month based groups just to keep the table sizes down.  Our current 
> months table has around 72 million rows in it as of today.  The joys of 
> building a data warehouse and trying to make it as fast as possible.

You may be able to achieve similar benefits with a clustered index.

see cluster:

\h cluster
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER

I've found this can greatly increase speed, but on 263 gigs of data, I'd 
run it when you had a couple days free.  You might wanna test it on a 
smaller test set you can afford to chew up some I/O CPU time on over a 
weekend.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-23 16:25         ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-23 16:50           ` Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-23 20:11             ` Re: General performance questions about postgres on Apple Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Sean Shanny @ 2004-02-23 16:50 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance

Scott,

We did try clustering on the date_key for the fact table below for a 
months worth of data as most of our requests for data are date range 
based, i.e. get me info for the time period between 2004-02-01 and 
2004-02-07.  This normally results in a plan that is doing an index scan 
on the date_key which in theory should be fast.  However we have found 
that it is almost always faster to run a sequential scan on the data set 
due to the size, and probably as Tom pointed out, the high seek time we 
seem to be experiencing with the MAC hardware which kills us when using 
the index to pop all over the disk.  We saw no improvement after having 
clustered based on the date_key.

I am certainly open to any suggestions on how to deal with speed issues 
on these sorts of large tables, it isn't going to go away for us. :-( 

We are working on trying to make the table below smaller in record size 
so we can get more records in a page.  An example is we are removing the 
subscriber_key which is 32 characters wide and replacing it with an int 
(user_id) which is an FK to a dimension table. 

I welcome any advice from folks that have used postgres to build data 
warehouses.

Thanks.

--sean


 Table "public.f_pageviews"
         Column         |  Type   |                          Modifiers
------------------------+---------+-------------------------------------------------------------
 id                     | integer | not null default 
nextval('public.f_pageviews_id_seq'::text)
 date_key               | integer | not null
 time_key               | integer | not null
 content_key            | integer | not null
 location_key           | integer | not null
 session_key            | integer | not null
 subscriber_key         | text    | not null
 persistent_cookie_key  | integer | not null
 ip_key                 | integer | not null
 referral_key           | integer | not null
 servlet_key            | integer | not null
 tracking_key           | integer | not null
 provider_key           | text    | not null
 marketing_campaign_key | integer | not null
 orig_airport           | text    | not null
 dest_airport           | text    | not null
 commerce_page          | boolean | not null default false
 job_control_number     | integer | not null
 sequenceid             | integer | not null default 0
 url_key                | integer | not null
 useragent_key          | integer | not null
 web_server_name        | text    | not null default 'Not Available'::text
 cpc                    | integer | not null default 0
 referring_servlet_key  | integer | not null default 1
 first_page_key         | integer | not null default 1
 newsletterid_key       | text    | not null default 'Not Available'::text
 userid_key             | integer |
Indexes:
    "f_pageviews_pkey" primary key, btree (id)
    "idx_pageviews_date" btree (date_key)
    "idx_pageviews_session" btree (session_key)


scott.marlowe wrote:

>On Sun, 22 Feb 2004, Sean Shanny wrote:
>
>  
>
>>Tom,
>>
>>We have the following setting for random page cost:
>>
>>random_page_cost = 1            # units are one sequential page fetch cost
>>
>>Any suggestions on what to bump it up to?
>>
>>We are waiting to hear back from Apple on the speed issues, so far we 
>>are not impressed with the hardware in helping in the IO department.  
>>Our DB is about 263GB with indexes now so there is not way it is going 
>>to fit into memory. :-(  I have taken the step of breaking out the data 
>>into month based groups just to keep the table sizes down.  Our current 
>>months table has around 72 million rows in it as of today.  The joys of 
>>building a data warehouse and trying to make it as fast as possible.
>>    
>>
>
>You may be able to achieve similar benefits with a clustered index.
>
>see cluster:
>
>\h cluster
>Command:     CLUSTER
>Description: cluster a table according to an index
>Syntax:
>CLUSTER indexname ON tablename
>CLUSTER tablename
>CLUSTER
>
>I've found this can greatly increase speed, but on 263 gigs of data, I'd 
>run it when you had a couple days free.  You might wanna test it on a 
>smaller test set you can afford to chew up some I/O CPU time on over a 
>weekend.
>
>
>  
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-23 16:25         ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
  2004-02-23 16:50           ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-23 20:11             ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Josh Berkus @ 2004-02-23 20:11 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; scott.marlowe <scott.marlowe@ihs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance

Scott,

> I am certainly open to any suggestions on how to deal with speed issues 
> on these sorts of large tables, it isn't going to go away for us. :-( 

I'm not sure what to suggest.   I can't think of anything off the top of my 
head that would improve cripplingly slow random seek times.

This sort of problem has personally caused me to dump and replace various RAID 
controllers in the past.   I have to say that I have not been impressed with 
the Mac as a database server platform in the past; I've had no end of issues 
with memory and disk management.

I talked to the Apple Server staff at the last MacWorld about some of these 
issues and they admitted that servers are still a "new thing" for Apple as a 
company, and they're still tweaking OSX server.

BTW, I wasn't clear from your description: did you mean that you have 14 
disks?

Oh, and for testing real random seek time, you can run bonnie++ which is 
findable on freshmeat.   This should give you a benchmark to e-mail the Apple 
people.   I'd be interested in being cc'd on your communications with them, 
as we use OSX for webserving and would like to see better support for 
database serving.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple hardware...
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
@ 2004-02-24 00:17 ` Simon Riggs <simon@2ndquadrant.com>
  2004-02-24 00:54   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
  1 sibling, 1 reply; 12+ messages in thread

From: Simon Riggs @ 2004-02-24 00:17 UTC (permalink / raw)
  To: 'Sean Shanny' <shannyconsulting@earthlink.net>; pgsql-performance

>Sean Shanny
> Hardware:  Apple G5 dual 2.0 with 8GB memory attached via dual fibre
> channel to a fully loaded 3.5TB XRaid.  The XRaid is configured as two
7
> disk hardware based RAID5 sets software striped to form a RAID50 set.
> The DB, WALS, etc are all on that file set.  Running OSX journaled
file
> system  Running postgres 7.4.1.  OSX Server 10.3.2  Postgres is
compiled
> locally with  '--enable-recode' '--enable-multibyte=UNICODE'
> 'CFLAGS=-mcpu=970 -mtune=970 -mpowerpc64 -O3'

Have you tried altering the blocksize to a higher value? Say 32K?

> max_connections = 100

Why have you set this to 100 when you have typically 1-3 users?

> sort_mem = 256000               # min 64, size in KB

If you have only 1-3 users, then that value seems reasonable.

> The query is
> 
> SELECT t1.id, t2.md5, t2.url FROM referral_temp t2 LEFT OUTER JOIN
> d_referral t1 ON t2.md5 = t1.referral_md5;
> 
> 
> \d d_referral
>  id                 | integer | not null
>  referral_md5       | text    | not null
>  referral_raw_url   | text    | not null
>  referral_host      | text    |
>  referral_path      | text    |
>  referral_query     | text    |
>  job_control_number | integer | not null
> 
> 
> \d referral_temp
>  md5    | text |
>  url    | text |

Have you looked at using reversed indexes, as per recent postings in
[performance]? These seemed to help considerably with lookup speed when
using a large URL database, which seems to be your situation here.

...
>Jeff Boes writes
> We have a large (several million row) table with a field containing
> URLs. Now, funny thing about URLs: they mostly start with a common
> substring ("http://www.";). But not all the rows start with this, so we
> can't just lop off the first N characters. However, we noticed some
time
> ago that an index on this field wasn't as effective as an index on the
> REVERSE of the field. So ...
> 
> CREATE OR REPLACE FUNCTION fn_urlrev(text) returns text as '
> return reverse(lc($_[0]))
> ' language 'plperl' with (iscachable,isstrict);
> 
> and then
> 
> CREATE UNIQUE INDEX ix_links_3 ON links
> (fn_urlrev(path_base));

You have 2 CPUs: have you tried splitting your input data file into two
tables, then executing the same query simultaneously, to split the
processing? If you get the correct plan, you should use roughly the same
I/O but use all of the available CPU power.

I'm sure we'd all be interested in your further results!

Best Regards, Simon Riggs




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
  2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
  2004-02-24 00:17 ` Re: General performance questions about postgres on Apple hardware... Simon Riggs <simon@2ndquadrant.com>
@ 2004-02-24 00:54   ` Sean Shanny <shannyconsulting@earthlink.net>
  0 siblings, 0 replies; 12+ messages in thread

From: Sean Shanny @ 2004-02-24 00:54 UTC (permalink / raw)
  To: simon@2ndquadrant.com; +Cc: pgsql-performance

Simon Riggs wrote:

>>Sean Shanny
>>Hardware:  Apple G5 dual 2.0 with 8GB memory attached via dual fibre
>>channel to a fully loaded 3.5TB XRaid.  The XRaid is configured as two
>>    
>>
>7
>  
>
>>disk hardware based RAID5 sets software striped to form a RAID50 set.
>>The DB, WALS, etc are all on that file set.  Running OSX journaled
>>    
>>
>file
>  
>
>>system  Running postgres 7.4.1.  OSX Server 10.3.2  Postgres is
>>    
>>
>compiled
>  
>
>>locally with  '--enable-recode' '--enable-multibyte=UNICODE'
>>'CFLAGS=-mcpu=970 -mtune=970 -mpowerpc64 -O3'
>>    
>>
>
>Have you tried altering the blocksize to a higher value? Say 32K?
>  
>
That is on our to do list.  We had made that change while running on BSD 
5.1on a Dell 2650 with 4GB and 5 10K SCSI drive in RAID 0.  Did not see 
a huge improvement. 

>  
>
>>max_connections = 100
>>    
>>
>
>Why have you set this to 100 when you have typically 1-3 users?
>  
>
Have already addressed that by lowering this to 50.  Will drop it lower 
as time goes on.

>  
>
>>sort_mem = 256000               # min 64, size in KB
>>    
>>
>
>If you have only 1-3 users, then that value seems reasonable.
>
>  
>
>>The query is
>>
>>SELECT t1.id, t2.md5, t2.url FROM referral_temp t2 LEFT OUTER JOIN
>>d_referral t1 ON t2.md5 = t1.referral_md5;
>>
>>
>>\d d_referral
>> id                 | integer | not null
>> referral_md5       | text    | not null
>> referral_raw_url   | text    | not null
>> referral_host      | text    |
>> referral_path      | text    |
>> referral_query     | text    |
>> job_control_number | integer | not null
>>
>>
>>\d referral_temp
>> md5    | text |
>> url    | text |
>>    
>>
>
>Have you looked at using reversed indexes, as per recent postings in
>[performance]? These seemed to help considerably with lookup speed when
>using a large URL database, which seems to be your situation here.
>  
>
We create an MD5 of the URL and store it as referral_md5.  This is our 
key for lookup.  We ran into problems with the URL as the index. The 
postgres indexing code was complaining about the URL being too long, 
hence the MD5 which thought longer to compute during the ETL phase is 
much quicker to match on.

>...
>  
>
>>Jeff Boes writes
>>We have a large (several million row) table with a field containing
>>URLs. Now, funny thing about URLs: they mostly start with a common
>>substring ("http://www.";). But not all the rows start with this, so we
>>can't just lop off the first N characters. However, we noticed some
>>    
>>
>time
>  
>
>>ago that an index on this field wasn't as effective as an index on the
>>REVERSE of the field. So ...
>>
>>CREATE OR REPLACE FUNCTION fn_urlrev(text) returns text as '
>>return reverse(lc($_[0]))
>>' language 'plperl' with (iscachable,isstrict);
>>
>>and then
>>
>>CREATE UNIQUE INDEX ix_links_3 ON links
>>(fn_urlrev(path_base));
>>    
>>
>
>You have 2 CPUs: have you tried splitting your input data file into two
>tables, then executing the same query simultaneously, to split the
>processing? If you get the correct plan, you should use roughly the same
>I/O but use all of the available CPU power.
>  
>
Have not considered that.

>I'm sure we'd all be interested in your further results!
>  
>
I will post things as I discover them.

--sean

>Best Regards, Simon Riggs
>
>
>  
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: General performance questions about postgres on Apple
@ 2004-06-07 05:54 Gregory S. Williamson <gsw@globexplorer.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Gregory S. Williamson @ 2004-06-07 05:54 UTC (permalink / raw)
  To: Sean Shanny <shannyconsulting@earthlink.net>; +Cc: pgsql-performance


Why is my name on a mail from Tom Lane ? Really, he knows a *lot* more than I and should get due credit.

Seriously, is this the peformance remailer mangling something ?

Greg Williamson
(the real one)

-----Original Message-----
From:	Gregory S. Williamson
Sent:	Sun 6/6/2004 10:46 PM
To:	Sean Shanny
Cc:	pgsql-performance@postgresql.org
Subject:	Re: [PERFORM] General performance questions about postgres on Apple
In-reply-to: <40396A16.9040301@earthlink.net> 
References: <Pine.LNX.4.33.0402201405160.11556-100000@css120.ihs.com>
	<403682D6.60807@earthlink.net> <19368.1077492611@sss.pgh.pa.us>
	<40396A16.9040301@earthlink.net>
Comments: In-reply-to Sean Shanny <shannyconsulting@earthlink.net>message
	dated "Sun, 22 Feb 2004 21:48:54 -0500"
Date: Sun, 22 Feb 2004 22:24:29 -0500
Message-ID: <20382.1077506669@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org
X-imss-version: 2.5
X-imss-result: Passed
X-imss-scores: Clean:99.90000 C:21 M:2 S:5 R:5
X-imss-settings: Baseline:2 C:2 M:2 S:2 R:2 (0.1500 0.3000)
Return-Path: pgsql-performance-owner+M5783@postgresql.org
X-OriginalArrivalTime: 07 Jun 2004 05:27:21.0994 (UTC) FILETIME=[1BC0EEA0:01C44C50]

Sean Shanny <shannyconsulting@earthlink.net> writes:
> We have the following setting for random page cost:
> random_page_cost = 1            # units are one sequential page fetch cost
> Any suggestions on what to bump it up to?

Well, the default setting is 4 ... what measurements prompted you to
reduce it to 1?  The particular example you showed suggested that the
true value on your setup might be 10 or more.

Now I would definitely not suggest that you settle on any particular
value based on only one test case.  You need to try to determine an
appropriate average value, bearing in mind that there's likely to be
lots of noise in any particular measurement.

But in general, setting random_page_cost to 1 is only reasonable when
you are dealing with a fully-cached-in-RAM database, which yours isn't.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend







^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2004-06-07 05:54 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 19:17 General performance questions about postgres on Apple hardware... Sean Shanny <shannyconsulting@earthlink.net>
2004-02-20 21:10 ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
2004-02-20 21:57   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
2004-02-22 23:30     ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
2004-02-23 02:48       ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
2004-02-23 03:24         ` Re: General performance questions about postgres on Apple Tom Lane <tgl@sss.pgh.pa.us>
2004-02-23 16:25         ` Re: General performance questions about postgres on Apple scott.marlowe <scott.marlowe@ihs.com>
2004-02-23 16:50           ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
2004-02-23 20:11             ` Re: General performance questions about postgres on Apple Josh Berkus <josh@agliodbs.com>
2004-02-24 00:17 ` Simon Riggs <simon@2ndquadrant.com>
2004-02-24 00:54   ` Re: General performance questions about postgres on Apple Sean Shanny <shannyconsulting@earthlink.net>
2004-06-07 05:54 Re: General performance questions about postgres on Apple Gregory S. Williamson <gsw@globexplorer.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