On E, 2005-10-03 at 14:16 -0700, Josh Berkus wrote:
Jeff,
Nope, LOTS of testing, at OSDL, GreenPlum and Sun. For comparison, A
Big-Name Proprietary Database doesn't get much more than that either.
I find this claim very suspicious. I get single-threaded reads in
excess of 1GB/sec
On 10/3/05, Ron Peacetree [EMAIL PROTECTED] wrote:
[snip]
Just how bad is this CPU bound condition? How powerful a CPU is
needed to attain a DB IO rate of 25MBps?
If we replace said CPU with one 2x, 10x, etc faster than that, do we
see any performance increase?
If a modest CPU can drive a
Some additional thoughts: what appears to take the most time (i.e.
account for the highest cost in the explain), is _not_ running the
function itself (cost=0.00..0.01), but comparing the result from that
function with the name1 column in the mappings table
(cost=0.00..35935.05). Am I right? (See
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of PFC
Sent: Thursday, September 29, 2005 9:10 AM
To: [EMAIL PROTECTED]
Cc: Pg Hackers; pgsql-performance@postgresql.org
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
Hi,
I'm trying to include a custom function in my SQL-queries, which
unfortunately leaves the server hanging...
I basically search through two tables:
* TABLE_MAPPING: lists that 'abc' is mapped to 'def'
id1 | name1 | id2 | name2
-
1 | abc | 2 | def
3 |
First off, Mr. Trainor's response proves nothing about anyone or
anything except Mr. Trainor.
I'm going to offer an opinion on the caching topic. I don't have
any benchmarks; I'm offering a general sense of the issue based on
decades of experience, so I'll give a short summary of that.
I've
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Friday, September 30, 2005 11:02 PM
To: Jeffrey W. Baker
Cc: Luke Lonergan; Josh Berkus; Ron Peacetree; pgsql-
[EMAIL PROTECTED]; pgsql-performance@postgresql.org
I have perused the tuple sort stuff.
The good:
The documentation of the sort algorithm from Knuth's TAOCP was
beautifully done. Everyone who writes an algorithm should credit the
original source like this, and also where it deviates. That was done
very nicely.
The bad:
With random access, tape
Judy definitely rates a WOW!!
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Gregory Maxwell
Sent: Friday, September 30, 2005 7:07 PM
To: Ron Peacetree
Cc: Jeffrey W. Baker; pgsql-hackers@postgresql.org; pgsql-
[EMAIL PROTECTED]
I see the following routines that seem to be related to sorting.
If I were to examine these routines to consider ways to improve it, what
routines should I key in on? I am guessing that tuplesort.c is the hub
of activity for database sorting.
Directory of
On Sat, Oct 01, 2005 at 10:22:40AM -0400, Ron Peacetree wrote:
Assuming we get the abyssmal physical IO performance fixed...
(because until we do, _nothing_ is going to help us as much)
I'm still not convinced this is the major problem. For example, in my
totally unscientific tests on an oldish
On 9/28/05, Ron Peacetree [EMAIL PROTECTED] wrote:
2= We use my method to sort two different tables. We now have these
very efficient representations of a specific ordering on these tables. A
join operation can now be done using these Btrees rather than the
original data tables that involves
FreeBSD or Linux , which system has better performance for PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Ron Peacetree wrote:
The good news is all this means it's easy to demonstrate that we can
improve the performance of our sorting functionality.
Assuming we get the abyssmal physical IO performance fixed...
(because until we do, _nothing_ is going to help us as much)
I for one would be
On 9/30/05, Ron Peacetree [EMAIL PROTECTED] wrote:
4= I'm sure we are paying all sorts of nasty overhead for essentially
emulating the pg filesystem inside another filesystem. That means
~2x as much overhead to access a particular piece of data.
The simplest solution is for us to implement a
I think this question may be more appropriate for
[EMAIL PROTECTED]
Anyrate for the below. Sounds like you maybe already have a table or
sequence called ai_id;
Try doing a DROP SEQUENCE ai_id;
First
Also if you plan to use this sequence only for this table it would be better
to use serial8
On Thu, Sep 29, 2005 at 10:06:52AM -0700, Luke Lonergan wrote:
Josh,
On 9/29/05 9:54 AM, Josh Berkus josh@agliodbs.com wrote:
Following an index creation, we see that 95% of the time required
is the external sort, which averages 2mb/s. This is with seperate
drives for the WAL, the
Am trying to port a mysql statement to postgres.
Please help me in finding the error in this,
CREATE SEQUENCE ai_id;
CREATE TABLE badusers (
id int DEFAULT nextval('ai_id') NOT NULL,
UserName varchar(30),
Date datetime DEFAULT '-00-00 00:00:00' NOT NULL,
Reason varchar(200),
In my original example, a sequential scan of the 1TB of 2KB
or 4KB records, = 250M or 500M records of data, being sorted
on a binary value key will take ~1000x more time than reading
in the ~1GB Btree I described that used a Key+RID (plus node
pointers) representation of the data.
Imho
R, Rajesh (STSD) wrote:
Thanks.
I've already understood that
I need to post it in another list.
Sorry for wasting your precious time.
No time wasted. It was a perfectly reasonable question, just to the
wrong lists.
--
Richard Huxton
Archonet Ltd
---(end of
On Sat, Oct 01, 2005 at 06:19:41PM +0200, Martijn van Oosterhout wrote:
COPY TO /dev/null WITH binary
13MB/s55% user 45% system (ergo, CPU bound)
[snip]
the most expensive. But it does point out that the whole process is
probably CPU bound more than anything else.
Note that 45% of that
ALÝ ÇELÝK wrote:
FreeBSD or Linux , which system has better performance for PostgreSQL
Depends on the underlying hardware and your experience. I'd recommend
going with whichever you are more familiar, so long as it will support
the hardware you need to buy.
--
Richard Huxton
Archonet
R, Rajesh (STSD) wrote:
Am trying to port a mysql statement to postgres.
Please help me in finding the error in this,
Can I recommend the reference section of the manuals for this sort of
thing? There is an excellent section detailing the valid SQL for the
CREATE TABLE command.
Also -
On Tue, Oct 04, 2005 at 12:43:10AM +0300, Hannu Krosing wrote:
Just FYI, I run a count(*) on a 15.6GB table on a lightly loaded db and
it run in 163 sec. (Dual opteron 2.6GHz, 6GB RAM, 6 x 74GB 15k disks in
RAID10, reiserfs). A little less than 100MB sec.
And none of that 15G table is in the
Hey, you can say what you want about my style, but you
still haven't pointed to even one article from the vast literature
that you claim supports your argument. And I did include a
smiley. Your original email that PostgreSQL is wrong and
that you are right led me to believe that you, like
I'm sure there will be cases when some human assisted caching algorithm will
perform better than an mathetical statistical based design, but it will also
depend on the human. And it probably will make thing worse when workload
changes and human doesn't realize. It must be considered that, today,
Can anyone tell me what precisely a WAL buffer contains,
so that I can compute an appropriate setting for
wal_buffers (in 8.0.3)?
I know the documentation suggests there is little
evidence that supports increasing wal_buffers, but we
are inserting a large amount of data that, I believe,
easily
[to K C:] sorry, was out on vactation all last week. I was visualizing
the problem incorrectly anyways...
Jim wrote:
That function is not immutable, it should be defined as stable.
That is 100% correct: however now and then I declare stable functions as
immutable in some cases because the
** Low Priority **
Human feedback from testers and users has proven pretty effective
at catching errors in the human assisted cache configuration. When
people setting up the servers have missed the named cache configuration,
and all they had was the single general purpose cache, it has been
It's a quad opteron system. RAID controller is a 4 channel LSILogic
Megaraid
320 connected to 10 15k 36.7G SCSI disks. The disks are configured in
5
mirrored partitions. The pg_xlog is on one mirror and the data and
indexes
are spread over the other 4 using tablespaces. These numbers from
On Tue, 4 Oct 2005 23:06:54 -0400 (EDT)
Ron Peacetree [EMAIL PROTECTED] wrote:
Then there's the large library of research on caching strategies
in just about every HW and SW domain, including DB theory,
that points put that the more context dependent, ie application
or domain specific
Nope - it would be disk wait.
COPY is CPU bound on I/O subsystems faster that 50 MB/s on COPY (in) and about
15 MB/s (out).
- Luke
-Original Message-
From: Michael Stone [mailto:[EMAIL PROTECTED]
Sent: Wed Oct 05 09:58:41 2005
To: Martijn van Oosterhout
Cc:
On Wed, Oct 05, 2005 at 11:24:07AM -0400, Luke Lonergan wrote:
Nope - it would be disk wait.
I said I/O overhead; i.e., it could be the overhead of calling the
kernel for I/O's. E.g., the following process is having I/O problems:
time dd if=/dev/sdc of=/dev/null bs=1 count=1000
I've now gotten verification from multiple working DBA's that DB2, Oracle, and
SQL Server can achieve ~250MBps ASTR (with as much as ~500MBps ASTR in
setups akin to Oracle RAC) when attached to a decent (not outrageous, but
decent) HD subsystem...
I've not yet had any RW DBA verify Jeff Baker's
We have to fix this.
Ron
The source is freely available for your perusal. Please feel free to
point us in specific directions in the code where you may see some
benefit. I am positive all of us that can, would put resources into
fixing the issue had we a specific direction to attack.
From: Kevin Grittner [EMAIL PROTECTED]
Sent: Oct 5, 2005 2:16 AM
Subject: Re: [PERFORM] Is There Any Way
First off, Mr. Trainor's response proves nothing about anyone or
anything except Mr. Trainor.
Fair Enough. I apologize for the inappropriately general statement.
I'm going to offer
[EMAIL PROTECTED] (Dan Harris) writes:
On Oct 3, 2005, at 5:02 AM, Steinar H. Gunderson wrote:
I thought this might be interesting, not the least due to the
extremely low
price ($150 + the price of regular DIMMs):
Replying before my other post came through.. It looks like their
benchmarks
First I wanted to verify that pg's IO rates were inferior to The Competition.
Now there's at least an indication that someone else has solved similar
problems. Existence proofs make some things easier ;-)
Is there any detailed programmer level architectual doc set for pg? I know
the best doc is
Hello, just a little question, It's preferable to use Text Fields or
varchar(255) fields in a table? Are there any performance differences in the
use of any of them?
Thanks a lot for your answer!
---(end of broadcast)---
TIP 4: Have you searched
On Wed, Oct 05, 2005 at 12:21:35PM -0600, Cristian Prieto wrote:
Hello, just a little question, It's preferable to use Text Fields or
varchar(255) fields in a table? Are there any performance differences in the
use of any of them?
They are essentially the same. Note that you can have varchar
On Wed, 2005-10-05 at 12:14 -0400, Ron Peacetree wrote:
I've now gotten verification from multiple working DBA's that DB2, Oracle, and
SQL Server can achieve ~250MBps ASTR (with as much as ~500MBps ASTR in
setups akin to Oracle RAC) when attached to a decent (not outrageous, but
decent) HD
Chris wrote:
[EMAIL PROTECTED] (Dan Harris) writes:
On Oct 3, 2005, at 5:02 AM, Steinar H. Gunderson wrote:
I thought this might be interesting, not the least due to the
extremely low
price ($150 + the price of regular DIMMs):
Replying before my other post came through.. It looks
Cristian,
Hello, just a little question, It's preferable to use Text Fields or
varchar(255) fields in a table? Are there any performance differences in
the use of any of them?
TEXT, VARCHAR, and CHAR use the same underlying storage mechanism. This
means that TEXT is actually the fastest
A blast from the past is forwarded below.
douglas
Begin forwarded message:
From: Tom Lane [EMAIL PROTECTED]>
Date: August 23, 2005 3:23:43 PM EDT
To: Donald Courtney [EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org, Frank Wiles [EMAIL PROTECTED]>, gokulnathbabu manoharan [EMAIL
What kind of order of improvement do you need to see?
A lot since the load on the system is expected to increase by up to 100% over
the next 6 months.
What period are these number for? Were they collected over 1 hour, 1 day, 1
month?
I thought I mentioned that in the earlier post but it
I'm putting in as much time as I can afford thinking about pg related
performance issues. I'm doing it because of a sincere desire to help
understand and solve them, not to annoy people.
If I didn't believe in pg, I would't be posting thoughts about how to
make it better.
It's probably worth
On Wed, Oct 05, 2005 at 04:55:51PM -0700, Luke Lonergan wrote:
In COPY, we found lots of libc functions like strlen() being called
ridiculous numbers of times, in one case it was called on every
timestamp/date attribute to get the length of TZ, which is constant. That
one function call was in
47 matches
Mail list logo
