I've seen quite a few folks touting the Opteron as 2.5x
faster with postgres than a Xeon box. What makes the
Opteron so quick? Is it that Postgres really prefers to
run in 64-bit mode?
I don't know about 2.5x faster (perhaps on specific types
of loads), but the reason Opterons rock for
On Tue, 19 Apr 2005, J. Andrew Rogers wrote:
I don't know about 2.5x faster (perhaps on specific types of loads), but the
reason Opterons rock for database applications is their insanely good memory
bandwidth and latency that scales much better than the Xeon. Opterons also
have a ccNUMA-esque
On 4/20/05, Jim C. Nasby [EMAIL PROTECTED] wrote:
You should re-run the function test using SQL as the function language
instead of plpgsql. There might be some performance to be had there.
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds for CASE ... END insisde PLpgsql vs CASE...END
LANGUAGE SQL. :) I.e. its almost the same as in-place entered SQL.
Probably because simple SQL functions get inlined by the optimiser.
Chris
On 4/19/05, Mohan, Ross [EMAIL PROTECTED] wrote:
Clustered file systems is the first/best example that
comes to mind. Host A and Host B can both request from diskfarm, eg.
Something like a Global File System?
http://www.redhat.com/software/rha/gfs/
(I believe some other company did develop it
We have a table with 1M rows that contain sessions with a start and
finish timestamps. When joining this table with a 10k table with rounded
timestamps, explain shows me sequential scans are used, and the join
takes about 6 hours (2s per seq scan on session table * 1):
Nested Loop
Richard van den Berg wrote:
We have a table with 1M rows that contain sessions with a start and
finish timestamps. When joining this table with a 10k table with rounded
timestamps, explain shows me sequential scans are used, and the join
takes about 6 hours (2s per seq scan on session table *
Richard van den Berg [EMAIL PROTECTED] writes:
We have a table with 1M rows that contain sessions with a start and
finish timestamps. When joining this table with a 10k table with rounded
timestamps, explain shows me sequential scans are used, and the join
takes about 6 hours (2s per seq scan
I posted this link a few months ago and there was some surprise over the
difference in postgresql compared to other DBs. (Not much surprise in
Opteron stomping on Xeon in pgsql as most people here have had that
experience -- the surprise was in how much smaller the difference was in
other
John A Meinel wrote:
I believe the problem is that postgres doesn't recognize how restrictive
a date-range is unless it uses constants.
And it does when using BETWEEN with int for example? Impressive. :-)
select blah from du WHERE time between '2004-10-10' and '2004-10-15';
Will properly use
On Apr 14, 2005, at 7:59 AM, Richard van den Berg wrote:
How do I explain why test cases 2 and 3 do not come close to case 1?
Am I missing something obvious?
there's cost involved with enforcing the FK: if you're indexes can't be
used then you're doing a boatload of sequence scans to find and
On Apr 15, 2005, at 8:10 PM, Ron Mayer wrote:
For example, I didn't see many other $7000 proposals have
have nearly 10GB of ram, or over a dozen CPUs (even counting
the raid controllers), or over a half a terrabyte of storage ,
or capable of 5-10 Gbit/sec of network traffic... The extra
And how
On Apr 19, 2005, at 11:07 PM, Josh Berkus wrote:
RAID1 2 disks OS, pg_xlog
RAID 1+0 4 disks pgdata
This is my preferred setup, but I do it with 6 disks on RAID10 for
data, and since I have craploads of disk space I set checkpoint
segments to 256 (and checkpoint timeout to 5 minutes)
Vivek
On Apr 20, 2005, at 12:40 AM, Jeff Frost wrote:
I've seen quite a few folks touting the Opteron as 2.5x faster with
postgres than a Xeon box. What makes the Opteron so quick? Is it
that Postgres really prefers to run in 64-bit mode?
The I/O path on the opterons seems to be much faster, and
Richard van den Berg wrote:
John A Meinel wrote:
I believe the problem is that postgres doesn't recognize how restrictive
a date-range is unless it uses constants.
And it does when using BETWEEN with int for example? Impressive. :-)
select blah from du WHERE time between '2004-10-10' and
In terms of vendor specific models -
Does anyone have any good/bad experiences/recommendations for a 4-way
Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5 internal
drives) models?
This is in comparison with the new Dell 6850 (it has PCIexpress, faster
FSB 667MHz, which doesn't match up
Anjan Dave wrote:
In terms of vendor specific models -
Does anyone have any good/bad experiences/recommendations for a 4-way
Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5 internal
drives) models?
This is in comparison with the new Dell 6850 (it has PCIexpress, faster
FSB
Tom Lane wrote:
The explain shows no such thing. What is the *actual* runtime of
each plan per EXPLAIN ANALYZE, please?
I took a simplified version of the problem (the actual query that took 6
hours joins 3 tables). With cpu_tuple_cost = 0.1:
Nested Loop (cost=0.00..667700310.42
On Wednesday 20 April 2005 17:50, Bruce Momjian wrote:
Anjan Dave wrote:
In terms of vendor specific models -
Does anyone have any good/bad experiences/recommendations for a 4-way
Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5 internal
drives) models?
This is in
kewl.
Well, 8k request out of PG kernel might turn into an XKb request at
disk/OS level, but duly noted.
Did you scan the code for this, or are you pulling this recollection from
the cognitive archives? :-)
-Original Message-
From: Jim C. Nasby [mailto:[EMAIL PROTECTED]
Sent:
There have been some discussions on this list and others in general about
Dell's version of RAID cards, and server support, mainly linux support.
Before I venture into having another vendor in the shop I want to know if there
are any dos/don't's about 4-way Opteron offerings from Sun and HP.
Anjan,
Does anyone have any good/bad experiences/recommendations for a 4-way
Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5 internal
drives) models?
Last I checked, the v40z only takes 5 drives, unless you yank the cd-rom and
get an extra disk tray. That's the main defect of the
right, the oracle system uses a second low latency bus to
manage locking information (at the block level) via a
distributed lock manager. (but this is slightly different
albeit related to a clustered file system and OS-managed
locking, eg)
-Original Message-
From: [EMAIL PROTECTED]
I wonder if thats something to think about adding to Postgresql? A
setting for multiblock read count like Oracle (Although having said
that I believe that Oracle natively caches pages much more
aggressively that postgresql, which allows the OS to do the file
caching).
Alex Turner
netEconomist
Tom,
Any thoughts? This is really messing up query execution all across the
database ...
--Josh
Here is the stats = 100 version. Notice that n_distinct has gone down.
schemaname | tablename | attname | null_frac | avg_width |
n_distinct |
The HPs are at root pretty good machines -- and take 6 drives, so I expect
you're mixed up there. However, they use HP's proprietary RAID controller
which is seriously defective. So you need to factor replacing the RAID
controller into the cost.
Do you have any additional materials on
On 4/20/05, Anjan Dave [EMAIL PROTECTED] wrote:
In terms of vendor specific models -
Does anyone have any good/bad experiences/recommendations for a 4-way
Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5 internal
drives) models?
We are going with the 90nm HPs for production. They
There have been some discussions on this list and others in general about
Dell's version of RAID cards, and server support, mainly linux support.
I was pretty impressed with the Dell guy. He spent the day with me remotely
and went through my system 6650 with powervault. Changed my drives from
Joel,
I did not see any marked improvement, but I dont think my issues are
related to the hardware.
If you won't believe it, then we certainly can't convince you. AFAIK your bad
view is a bad query plan made worse by the Dell's hardware problems.
I am giving up on postgres and three
Whilst I admire your purist approach, I would say that if it is
beneficial to performance that a kernel understand drive geometry,
then it is worth investigating teaching it how to deal with that!
I was less referrring to the kernel as I was to the controller.
Lets say we invented a new protocol
-Original Message-
From: Alex Turner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 20, 2005 12:04 PM
To: Dave Held
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to improve db performance with $7K?
[...]
Lets say we invented a new protocol that including the
Josh Berkus josh@agliodbs.com writes:
Last I checked, the v40z only takes 5 drives, unless you yank the cd-rom and
get an extra disk tray. That's the main defect of the model, the second
being its truly phenominal noise level. Other than that (and price) and
excellent Opteron machine.
Michael Fuhr wrote:
On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote:
I thought that an index can be used for sorting.
I'm a little confused about the following result:
create index OperationsName on Operations(cOperationName);
explain SELECT * FROM Operations ORDER BY cOperationName;
Alex et al.,
I wonder if thats something to think about adding to Postgresql? A setting for
multiblock read count like Oracle (Although
|| I would think so, yea. GMTA: I was just having this micro-chat with Mr. Jim
Nasby.
having said that I believe that Oracle natively caches pages much
Dear Postgres Masters:
We are using postgres 7.4 in our java application on RedHat
linux. The Java application connects to Postgres via JDBC. The application goes
through a discovery phase, whereas it adds large amount of data
into postgres. Typically, we are adding about a million
Stats are updated only after transaction ends. In case you have a really
long transaction you need something else.
To help myself I made a little Perl utility to parse strace output. It
recognizes read/write calls, extracts file handle, finds the file name
using information in /proc filesystem,
I'm having a pretty serious problem with postgresql's performance.
Currently, I have a cron task that is set to restart and vacuumdb -faz
every six hours. If that doesn't happen, the disk goes from 10% full
to 95% full within 2 days (and it's a 90GB disk...with the database
being a 2MB
Sorry if you feel I am lashing out at a community.
Just to say it again, I am very appreciative of all the help everyone has
supplied.
I am running on more then just the 4 proc Dell (in fact my tests have been
mostly on desktops).
I have MSSQL running on a 2 proc dell which until my load has
Joel,
I have MSSQL running on a 2 proc dell which until my load has increased
(over aprx 2 years) it was just fine. I totally agree that there are better
solutions based on this lists comments, but I have all Dell hardware now
and resist trying different vendors just to suit Postgres. I was
He is running RHAS4, which is the latest 2.6.x kernel from RH. I believe
it should have done away with the RHAS3.0 Update 3 IO issue.
anjan
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 20, 2005 4:23 PM
To: Joel Fradkin
Cc:
No, I don't think so. I don't think there are any temp table queries
(and I'll check), but even if there are, site traffic is very low, and
queries would be very infrequent.
On Apr 20, 2005, at 12:36 PM, Rod Taylor wrote:
I'm having a pretty serious problem with postgresql's performance.
Joel Fradkin wrote:
...
I would of spent more $ with Command, but he does need my data base to help
me and I am not able to do that.
...
What if someone were to write an anonymization script. Something that
changes any of the data of the database, but leaves all of the
relational information. It
As a follow-up, I've found a function that used the following code:
CREATE TEMPORARY TABLE results
(nOrder integer,
page_id integer,
name text)
WITHOUT OIDS
ON COMMIT DROP;
I would assume that the WITHOUT OIDS would be part of the source of
the problem, so I've commented it out.
On Apr 20, 2005,
Shachindra Agarwal wrote:
Dear Postgres Masters:
We are using postgres 7.4 in our java application on RedHat linux. The
Java application connects to Postgres via JDBC. The application goes
through a discovery phase, whereas it adds large amount of data into
postgres. Typically, we are adding
Richard Plotkin [EMAIL PROTECTED] writes:
I'm having a pretty serious problem with postgresql's performance.
Currently, I have a cron task that is set to restart and vacuumdb -faz
every six hours. If that doesn't happen, the disk goes from 10% full
to 95% full within 2 days (and it's a
On Apr 20, 2005, at 4:22 PM, Josh Berkus wrote:
Realistically I don't think a 30k$ Dell is a something that needs to
be
junked. I am pretty sure if I got MSSQL running on it, it would
outperform
my two proc box. I can agree it may not have been the optimal
platform. My
decision is not based
Hi Tom,
Q: what have you got the FSM parameters set to?
Here's from postgresql.conf -- FSM at default settings.
# - Memory -
shared_buffers = 30400 # min 16, at least max_connections*2,
8KB each
work_mem = 32168# min 64, size in KB
#maintenance_work_mem = 16384 # min
No, this is a single process. And there's known issues with context
storms on Xeons, so that might be what you're seeing.
On Tue, Apr 19, 2005 at 09:37:21PM -0700, Mischa Sandberg wrote:
Quoting Tom Lane [EMAIL PROTECTED]:
Jim C. Nasby [EMAIL PROTECTED] writes:
A friend of mine has an
No, he's using either COPY or \COPY.
On Wed, Apr 20, 2005 at 12:34:27AM -0400, Greg Stark wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
What's really odd is that neither the CPU or the disk are being
hammered. The box appears to be pretty idle; the postgresql proces is
using 4-5% CPU.
Thanks for the note. Please see my responses below:
-Original Message-
From: John A Meinel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 20, 2005 3:48 PM
To: Shachindra Agarwal
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] postgres slowdown question
Shachindra Agarwal
Shachindra Agarwal wrote:
Thanks for the note. Please see my responses below:
...
We are using JDBC which supports 'inserts' and 'transactions'. We are
using both. The business logic adds one business object at a time. Each
object is added within its own transaction. Each object add results in
I did think of something similar just loading the data tables with junk
records and I may visit that idea with Josh.
I did just do some comparisons on timing of a plain select * from tbl where
indexed column = x and it was considerably slower then both MSSQL and MYSQL,
so I am still a bit
Joel Fradkin wrote:
I did think of something similar just loading the data tables with junk
records and I may visit that idea with Josh.
I did just do some comparisons on timing of a plain select * from tbl where
indexed column = x and it was considerably slower then both MSSQL and MYSQL,
so I am
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds for CASE ... END insisde PLpgsql vs CASE...END
LANGUAGE SQL. :) I.e. its almost the same as in-place entered SQL.
Regards,
Dawid
---(end of
Quoth [EMAIL PROTECTED] (Christian Sander Røsnes):
On Wednesday 20 April 2005 17:50, Bruce Momjian wrote:
Anjan Dave wrote:
In terms of vendor specific models -
Does anyone have any good/bad experiences/recommendations for a
4-way Opteron from Sun (v40z, 6 internal drives) or HP (DL585 5
Joel,
Ok, please try this:
ALTER TABLE tblresponseheader ALTER COLUMN clientnum SET STATISTICS 1000;
ALTER TABLE tblresponseheader ALTER COLUMN locationid SET STATISTICS 1000;
ALTER TABLE tbllocation ALTER COLUMN clientnum SET STATISTICS 1000;
ALTER TABLE tbllocation ALTER COLUMN divisionid SET
John A Meinel [EMAIL PROTECTED] writes:
Joel Fradkin wrote:
Postgres was on the second run
Total query runtime: 17109 ms.
Data retrieval runtime: 72188 ms.
331640 rows retrieved.
How were you measuring data retrieval time?
I suspect he's using pgadmin. We've seen reports before suggesting
The Linux kernel is definitely headed this way. The 2.6 allows for
several different I/O scheduling algorithms. A brief overview about the
different modes:
http://nwc.serverpipeline.com/highend/60400768
Although a much older article from the beta-2.5 days, more indepth info
from one of the
58 matches
Mail list logo
