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 prog
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 befo
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 S
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)
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 broadcast)-
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 s
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 confused
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 5
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 wr
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
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
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 102
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 sole
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
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 a
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
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
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.
Current
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: pgsql-performance@postgr
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 wa
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 incr
> 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
Hi,
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 do
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, t
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 re
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 m
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;
Josh Berkus 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.
Incidentally,
> -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 includi
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
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 dev
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 ext3
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.
> 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 o
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 | mos
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
P.S
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] [m
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 t
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.
Do
kewl.
Well, 8k request out of PG kernel might turn into an "X"Kb 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: Tues
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 i
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 rows=10354
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
> F
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
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 '2004-1
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 hav
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 Khera
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 mu
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 loc
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
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 DBs.)
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
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 * 1
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 (cost=252.
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
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/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
57 matches
Mail list logo