Обсуждение: Re: General performance questions about postgres on Apple
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.
>
>
>
>
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
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
>
>
>
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
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.
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.
>
>
>
>
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