pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedscalablility problem
18+ messages / 10 participants
[nested] [flat]
* scalablility problem
@ 2007-03-30 20:25 Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 20:56 ` Re: scalablility problem Dave Dutcher <dave@tridecap.com>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:41 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 01:18 ` Re: scalablility problem Christopher Browne <cbbrowne@acm.org>
0 siblings, 4 replies; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-30 20:25 UTC (permalink / raw)
To: pgsql-performance
Hi all,
When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18. I
run multiple
q2 queries simultaneously. The results are:
1 process takes 0.65 second to finish.
2 processes take 1.07 seconds.
4 processes take 4.93 seconds.
8 processes take 16.95 seconds.
For 4-process case and 8-process case, queries takes even more time than
they are executed serially one after another. Because the system has 8GB
memory, which is much bigger than the DB size(SF=1), and I warmed the cache
before I run the test, I do not think the problem was caused by disk I/O.
I think it might be caused by some contentions. But I do not know postgresql
much. May anybody give me some clue to find the reasons?
Thanks!
Xiaoning
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-30 20:56 ` Dave Dutcher <dave@tridecap.com>
3 siblings, 0 replies; 18+ messages in thread
From: Dave Dutcher @ 2007-03-30 20:56 UTC (permalink / raw)
To: 'Xiaoning Ding' <dingxn@cse.ohio-state.edu>; pgsql-performance
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Xiaoning Ding
>
>
> Hi all,
>
> When I run multiple TPC-H queries (DBT3) on postgresql, I
> found the system
> is not scalable. My machine has 8GB memory, and 4 Xeon Dual
> Core processor
> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is
> 7.3.18.
Is there anyway you can upgrade to 8.2? There have been a lot of
performance and scalability enhancements.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-30 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
3 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2007-03-30 21:12 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: pgsql-performance
Xiaoning Ding <dingxn@cse.ohio-state.edu> writes:
> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18.
If you are not running PG 8.1 or later, it's really not worth your time
to test this. Multiprocessor scalability was hardly even on the radar
in 7.3 days.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-30 21:38 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:44 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-01 10:22 ` Re: scalablility problem Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 2 replies; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-30 21:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks guys,
I update PG to 8.2.3. The results are much better now.
1 process : 0.94 second
2 processes: 1.32 seconds
4 processes: 2.03 seconds
8 processes: 2.54 seconds
Do you think they are good enough?
BTW where can I found some info on what 8.2.3 did to improve
scalability compared with pre 8.1 versions?
Xiaoning
Tom Lane wrote:
> Xiaoning Ding <dingxn@cse.ohio-state.edu> writes:
>> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
>> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
>> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18.
>
> If you are not running PG 8.1 or later, it's really not worth your time
> to test this. Multiprocessor scalability was hardly even on the radar
> in 7.3 days.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-30 21:44 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 18+ messages in thread
From: Scott Marlowe @ 2007-03-30 21:44 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: pgsql-performance
On Fri, 2007-03-30 at 16:38, Xiaoning Ding wrote:
> Thanks guys,
>
> I update PG to 8.2.3. The results are much better now.
> 1 process : 0.94 second
> 2 processes: 1.32 seconds
> 4 processes: 2.03 seconds
> 8 processes: 2.54 seconds
>
> Do you think they are good enough?
> BTW where can I found some info on what 8.2.3 did to improve
> scalability compared with pre 8.1 versions?
Very nice, eh?
I'd say look through -hackers and -perform to see some of it, but as
usual, the source code is the reference. You'd be surprised how well
commented it is.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-04-01 10:22 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-04-01 17:51 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 1 reply; 18+ messages in thread
From: Ron Mayer @ 2007-04-01 10:22 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>
Xiaoning Ding wrote:
> Postgresql is 7.3.18. [...]
> 1 process takes 0.65 second to finish.
> I update PG to 8.2.3. The results are [...] now.
> 1 process : 0.94 second
You sure about your test environment? Anything else
running at the same time, perhaps?
I'm a bit surprised that 8.2.3 would be 40% slower than 7.3.18
even in the single process case.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-01 10:22 ` Re: scalablility problem Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2007-04-01 17:51 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-02 00:26 ` Re: scalablility problem Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 18+ messages in thread
From: Xiaoning Ding @ 2007-04-01 17:51 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: pgsql-performance
I repeated the test again. It took 0.92 second under 8.2.3.
I checked system load using top and ps. There is no other
active processes.
Xiaoning
Ron Mayer wrote:
> Xiaoning Ding wrote:
>> Postgresql is 7.3.18. [...]
>> 1 process takes 0.65 second to finish.
>
>> I update PG to 8.2.3. The results are [...] now.
>> 1 process : 0.94 second
>
> You sure about your test environment? Anything else
> running at the same time, perhaps?
>
> I'm a bit surprised that 8.2.3 would be 40% slower than 7.3.18
> even in the single process case.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:12 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-01 10:22 ` Re: scalablility problem Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-04-01 17:51 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-04-02 00:26 ` Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 18+ messages in thread
From: Dave Cramer @ 2007-04-02 00:26 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: Ron Mayer <rm_pg@cheapcomplexdevices.com>; pgsql-performance
I may have missed this but have you tuned your postgresql
configuration ?
8.2 tuning guidelines are significantly different than 7.3
Dave
On 1-Apr-07, at 1:51 PM, Xiaoning Ding wrote:
> I repeated the test again. It took 0.92 second under 8.2.3.
> I checked system load using top and ps. There is no other
> active processes.
>
> Xiaoning
>
> Ron Mayer wrote:
>> Xiaoning Ding wrote:
>>> Postgresql is 7.3.18. [...]
>>> 1 process takes 0.65 second to finish.
>>> I update PG to 8.2.3. The results are [...] now.
>>> 1 process : 0.94 second
>> You sure about your test environment? Anything else
>> running at the same time, perhaps?
>> I'm a bit surprised that 8.2.3 would be 40% slower than 7.3.18
>> even in the single process case.
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-30 21:41 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 15:43 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
3 siblings, 1 reply; 18+ messages in thread
From: Scott Marlowe @ 2007-03-30 21:41 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: pgsql-performance
On Fri, 2007-03-30 at 15:25, Xiaoning Ding wrote:
> Hi all,
>
> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18. I
> run multiple
> q2 queries simultaneously. The results are:
>
> 1 process takes 0.65 second to finish.
> 2 processes take 1.07 seconds.
> 4 processes take 4.93 seconds.
> 8 processes take 16.95 seconds.
>
> For 4-process case and 8-process case, queries takes even more time than
> they are executed serially one after another. Because the system has 8GB
> memory, which is much bigger than the DB size(SF=1), and I warmed the cache
> before I run the test, I do not think the problem was caused by disk I/O.
You may be right, you may be wrong. What did top / vmstat have to say
about IO wait / disk idle time?
PostgreSQL has to commit transactions to disk. TPC-H does both business
decision mostly read queries, as well as mixing in writes. If you have
one hard drive, it may well be that activity is stacking up waiting on
those writes.
> I think it might be caused by some contentions. But I do not know postgresql
> much. May anybody give me some clue to find the reasons?
Others have mentioned your version of postgresql. 7.3 is quite old, as
it came out at the end of 2002. Seeing as 7.3 is the standard pgsql
version supported by RHEL3, and RHEL came with a 2.6.9 kernel, I'm gonna
guess your OS is about that old too.
pgsql 7.3 cannot take advantage of lots of shared memory, and has some
issues scaling to lots of CPUs / processes.
While RHEL won't be EOLed for a few more years (redhat promises 7 years
I think) it's really not a great choice for getting started today.
RHEL5 just released and RHEL4 is very stable.
There are several things to look at to get better performance.
1: Late model PostgreSQL. Go with 8.2.3 or as a minimum 8.1.8
2: Late model Unix.
3: RAID controller with battery backed cache
4: Plenty of memory.
5: Lots of hard drives
6: 4 to 8 CPUs.
Then, google postgresql performance tuning. There are three or four good
tuning guides that pop up right at the top.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:41 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-03-31 15:43 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 15:49 ` Re: scalablility problem Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-31 15:43 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
Scott Marlowe wrote:
> On Fri, 2007-03-30 at 15:25, Xiaoning Ding wrote:
>> Hi all,
>>
>> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
>> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
>> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18. I
>> run multiple
>> q2 queries simultaneously. The results are:
>>
>> 1 process takes 0.65 second to finish.
>> 2 processes take 1.07 seconds.
>> 4 processes take 4.93 seconds.
>> 8 processes take 16.95 seconds.
>>
>> For 4-process case and 8-process case, queries takes even more time than
>> they are executed serially one after another. Because the system has 8GB
>> memory, which is much bigger than the DB size(SF=1), and I warmed the cache
>> before I run the test, I do not think the problem was caused by disk I/O.
>
> You may be right, you may be wrong. What did top / vmstat have to say
> about IO wait / disk idle time?
>
> PostgreSQL has to commit transactions to disk. TPC-H does both business
> decision mostly read queries, as well as mixing in writes. If you have
> one hard drive, it may well be that activity is stacking up waiting on
> those writes.
Shouldn't writes be asynchronous in linux ?
>> I think it might be caused by some contentions. But I do not know postgresql
>> much. May anybody give me some clue to find the reasons?
>
> Others have mentioned your version of postgresql. 7.3 is quite old, as
> it came out at the end of 2002. Seeing as 7.3 is the standard pgsql
> version supported by RHEL3, and RHEL came with a 2.6.9 kernel, I'm gonna
> guess your OS is about that old too.
>
> pgsql 7.3 cannot take advantage of lots of shared memory, and has some
> issues scaling to lots of CPUs / processes.
I use RHEL 4. I can not understand how the scalability related with
shared memory?
> While RHEL won't be EOLed for a few more years (redhat promises 7 years
> I think) it's really not a great choice for getting started today.
> RHEL5 just released and RHEL4 is very stable.
>
> There are several things to look at to get better performance.
>
> 1: Late model PostgreSQL. Go with 8.2.3 or as a minimum 8.1.8
> 2: Late model Unix.
> 3: RAID controller with battery backed cache
> 4: Plenty of memory.
> 5: Lots of hard drives
> 6: 4 to 8 CPUs.
>
> Then, google postgresql performance tuning. There are three or four good
> tuning guides that pop up right at the top.
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:41 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 15:43 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-31 15:49 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-31 16:31 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Joshua D. Drake @ 2007-03-31 15:49 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
>> pgsql 7.3 cannot take advantage of lots of shared memory, and has some
>> issues scaling to lots of CPUs / processes.
>
> I use RHEL 4. I can not understand how the scalability related with
> shared memory?
It isn't RHEL4 and shared memory. It is PostgreSQL and shared memory.
Things have changed with PostgreSQL since 7.3 (7.3 is really god awful
old) that allow it to more effectively access shared memory and thus
provide better performance.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:41 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 15:43 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 15:49 ` Re: scalablility problem Joshua D. Drake <jd@commandprompt.com>
@ 2007-03-31 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-31 17:08 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2007-03-31 16:31 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
"Joshua D. Drake" <jd@commandprompt.com> writes:
>> I use RHEL 4. I can not understand how the scalability related with
>> shared memory?
> It isn't RHEL4 and shared memory. It is PostgreSQL and shared memory.
> Things have changed with PostgreSQL since 7.3 (7.3 is really god awful
> old) that allow it to more effectively access shared memory and thus
> provide better performance.
Some specifics:
* bufmgr algorithms redesigned to allow larger number of shared buffers
to be used effectively
* bufmgr redesigned to not have a single lock for management of all
shared buffers; likewise for lockmgr
* lots of marginal tweaks such as paying attention to cache line
alignment of "hot" shared data structures
I'm probably forgetting some things but I think the bufmgr and lockmgr
changes were the biggest improvements in this area.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:41 ` Re: scalablility problem Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 15:43 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 15:49 ` Re: scalablility problem Joshua D. Drake <jd@commandprompt.com>
2007-03-31 16:31 ` Re: scalablility problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-31 17:08 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 0 replies; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-31 17:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>> I use RHEL 4. I can not understand how the scalability related with
>>> shared memory?
>
>> It isn't RHEL4 and shared memory. It is PostgreSQL and shared memory.
>> Things have changed with PostgreSQL since 7.3 (7.3 is really god awful
>> old) that allow it to more effectively access shared memory and thus
>> provide better performance.
>
> Some specifics:
>
> * bufmgr algorithms redesigned to allow larger number of shared buffers
> to be used effectively
>
> * bufmgr redesigned to not have a single lock for management of all
> shared buffers; likewise for lockmgr
>
> * lots of marginal tweaks such as paying attention to cache line
> alignment of "hot" shared data structures
>
> I'm probably forgetting some things but I think the bufmgr and lockmgr
> changes were the biggest improvements in this area.
>
> regards, tom lane
That is very helpful. Thanks!
Xiaoning
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
@ 2007-03-31 01:18 ` Christopher Browne <cbbrowne@acm.org>
2007-03-31 04:00 ` Re: scalablility problem Guido Neitzer <lists@event-s.net>
2007-03-31 15:54 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
3 siblings, 2 replies; 18+ messages in thread
From: Christopher Browne @ 2007-03-31 01:18 UTC (permalink / raw)
To: pgsql-performance
Quoth dingxn@cse.ohio-state.edu (Xiaoning Ding):
> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18. I
> I think it might be caused by some contentions. But I do not know postgresql
> much. May anybody give me some clue to find the reasons?
Two primary issues:
1. You're running a horrendously ancient version of PostgreSQL. The
7.3 series is Really Old. Newer versions have *enormous*
improvements that are likely to be *enormously* relevant.
Upgrade to 8.2.
2. There are known issues with the combination of Xeon processors and
PAE memory addressing; that sort of hardware tends to be *way* less
speedy than the specs would suggest.
There have been "context switching" issues on this sort of hardware
that are enormously worsened if you're running on a version of
PostgreSQL that is 4 major releases out of date.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://cbbrowne.com/info/x.html
I am not a number!
I am a free man!
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 01:18 ` Re: scalablility problem Christopher Browne <cbbrowne@acm.org>
@ 2007-03-31 04:00 ` Guido Neitzer <lists@event-s.net>
2007-03-31 11:41 ` Re: scalablility problem Michael Stone <mstone+postgres@mathom.us>
1 sibling, 1 reply; 18+ messages in thread
From: Guido Neitzer @ 2007-03-31 04:00 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
On 30.03.2007, at 19:18, Christopher Browne wrote:
> 2. There are known issues with the combination of Xeon processors and
> PAE memory addressing; that sort of hardware tends to be *way* less
> speedy than the specs would suggest.
That is not true as the current series of processors (Woodcrest and
the like) are also called Xeon. You probably mean the Pentium IV era
Xeons.
cug
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 01:18 ` Re: scalablility problem Christopher Browne <cbbrowne@acm.org>
2007-03-31 04:00 ` Re: scalablility problem Guido Neitzer <lists@event-s.net>
@ 2007-03-31 11:41 ` Michael Stone <mstone+postgres@mathom.us>
2007-03-31 15:54 ` Re: scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 1 reply; 18+ messages in thread
From: Michael Stone @ 2007-03-31 11:41 UTC (permalink / raw)
To: pgsql-performance
On Fri, Mar 30, 2007 at 10:00:30PM -0600, Guido Neitzer wrote:
>On 30.03.2007, at 19:18, Christopher Browne wrote:
>>2. There are known issues with the combination of Xeon processors and
>>PAE memory addressing; that sort of hardware tends to be *way* less
>>speedy than the specs would suggest.
>
>That is not true as the current series of processors (Woodcrest and
>the like) are also called Xeon. You probably mean the Pentium IV era
>Xeons.
Well, the newer ones can address large amounts of memory directly,
without using PAE, but the original comment was correct--PAE is slow
regardless of what processor implements it.
Mike Stone
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 01:18 ` Re: scalablility problem Christopher Browne <cbbrowne@acm.org>
2007-03-31 04:00 ` Re: scalablility problem Guido Neitzer <lists@event-s.net>
2007-03-31 11:41 ` Re: scalablility problem Michael Stone <mstone+postgres@mathom.us>
@ 2007-03-31 15:54 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 0 replies; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-31 15:54 UTC (permalink / raw)
To: pgsql-performance
Michael Stone wrote:
> On Fri, Mar 30, 2007 at 10:00:30PM -0600, Guido Neitzer wrote:
>> On 30.03.2007, at 19:18, Christopher Browne wrote:
>>> 2. There are known issues with the combination of Xeon processors and
>>> PAE memory addressing; that sort of hardware tends to be *way* less
>>> speedy than the specs would suggest.
>>
>> That is not true as the current series of processors (Woodcrest and
>> the like) are also called Xeon. You probably mean the Pentium IV era
>> Xeons.
>
> Well, the newer ones can address large amounts of memory directly,
> without using PAE, but the original comment was correct--PAE is slow
> regardless of what processor implements it.
Here is the information in /proc/cpuinfo
processor : 6
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 8
cpu MHz : 2793.091
cache size : 2048 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall
nx lm pni monitor ds_cpl est cid cx16 xtpr
bogomips : 5586.08
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
>
> Mike Stone
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: scalablility problem
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 01:18 ` Re: scalablility problem Christopher Browne <cbbrowne@acm.org>
@ 2007-03-31 15:54 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 0 replies; 18+ messages in thread
From: Xiaoning Ding @ 2007-03-31 15:54 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Christopher Browne wrote:
> Quoth dingxn@cse.ohio-state.edu (Xiaoning Ding):
>> When I run multiple TPC-H queries (DBT3) on postgresql, I found the system
>> is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor
>> ( 8 cores in total). OS kernel is linux 2.6.9. Postgresql is 7.3.18. I
>
>> I think it might be caused by some contentions. But I do not know postgresql
>> much. May anybody give me some clue to find the reasons?
>
> Two primary issues:
>
> 1. You're running a horrendously ancient version of PostgreSQL. The
> 7.3 series is Really Old. Newer versions have *enormous*
> improvements that are likely to be *enormously* relevant.
>
> Upgrade to 8.2.
8.2 is really much better.
>
> 2. There are known issues with the combination of Xeon processors and
> PAE memory addressing; that sort of hardware tends to be *way* less
> speedy than the specs would suggest.
I think PAE slows each query process. It would not affect scalability.
> There have been "context switching" issues on this sort of hardware
> that are enormously worsened if you're running on a version of
> PostgreSQL that is 4 major releases out of date.
How does PG 8.2 address this issue? by setting processor affinity?
Thanks!
Xiaoning
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2007-04-02 00:26 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-30 20:25 scalablility problem Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 20:56 ` Dave Dutcher <dave@tridecap.com>
2007-03-30 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 21:38 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-30 21:44 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-01 10:22 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-04-01 17:51 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-02 00:26 ` Dave Cramer <pg@fastcrypt.com>
2007-03-30 21:41 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-31 15:43 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 15:49 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-31 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-31 17:08 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 01:18 ` Christopher Browne <cbbrowne@acm.org>
2007-03-31 04:00 ` Guido Neitzer <lists@event-s.net>
2007-03-31 11:41 ` Michael Stone <mstone+postgres@mathom.us>
2007-03-31 15:54 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-03-31 15:54 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
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