enough to make changes this
deep in the core functionality, nor is there enough time for me to
do so if we are going to be timely enough get this into 8.2
(and no, I can't devote 24x7 to doing pg development unless
someone is going to replace my current ways of paying my bills so
that I can.)
Ron
. If any these IO rates came
from any reasonable 300+MBps RAID array, then they are BAD.
What your simple experiment really does is prove We Have A
Problem (tm) with our IO code at either or both of the OS or the pg
level(s).
Ron
-Original Message-
From: Martijn van Oosterhout kleptog
than the method you propose.
Ron Peacetree:
1= No that was not my main example. It was the simplest example
used to frame the later more complicated examples. Please don't
get hung up on it.
2= You are incorrect. Since IO is the most expensive operation we
can do, any method that makes two
of the sorted information from HD can be
done at full HD streaming speed and whatever we've decided to
save to HD can be reused later if we desire.
Hope this helps,
Ron
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
is severely limited by the abyssmally
slow HD IO.
Ron
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through
could replace the
present sorting code with infinitely fast sorting code and we'd
still be scrod performance wise.
So why does basic IO suck so badly?
Ron
-Original Message-
From: Josh Berkus josh@agliodbs.com
Sent: Sep 30, 2005 1:23 PM
To: Ron Peacetree [EMAIL PROTECTED]
Cc: pgsql
.
The simplest solution is for us to implement a new VFS compatible
filesystem tuned to exactly our needs: pgfs.
We may be able to avoid that by some amount of hacking or
modifying of the current FSs we use, but I suspect it would be more
work for less ROI.
Ron
-Original Message-
From: Josh Berkus
to the absolute minimum
was one of the design goals.
Reducing the total amount of IO to the absolute minimum should
help as well.
Ron
-Original Message-
From: Kevin Grittner [EMAIL PROTECTED]
Sent: Sep 27, 2005 11:21 AM
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
I can't help
From: Jeffrey W. Baker [EMAIL PROTECTED]
Sent: Sep 29, 2005 12:27 AM
To: Ron Peacetree [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
You are engaging in a length and verbose exercise in mental
From: Jeffrey W. Baker [EMAIL PROTECTED]
Sent: Sep 27, 2005 1:26 PM
To: Ron Peacetree [EMAIL PROTECTED]
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
On Tue, 2005-09-27 at 13:15 -0400, Ron Peacetree wrote:
That Btree can be used to generate a physical reordering of the data
in one
, implementation of the Btree. Less pointer chasing at the expense of more
CPU calculations, but that's a trade-off in the correct direction.
Such source would be a big help in getting a prototype together.
Thanks in advance for any pointers or source,
Ron
---(end of broadcast
From: Josh Berkus josh@agliodbs.com
ent: Sep 27, 2005 12:15 PM
To: Ron Peacetree [EMAIL PROTECTED]
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
I've somehow missed part of this thread, which is a shame since this is
an area of primary concern for me.
Your suggested algorithm seems
From: Dann Corbit [EMAIL PROTECTED]
Sent: Sep 26, 2005 5:13 PM
To: Ron Peacetree [EMAIL PROTECTED], pgsql-hackers@postgresql.org,
pgsql-performance@postgresql.org
Subject: RE: [HACKERS] [PERFORM] A Better External Sort?
I think that the btrees are going to be O(n*log(n)) in construction
here.
Including my own
Ron
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
David Fetter wrote:
...log file formats in 8.0
* CSV
* YAML
* XML
* Piped logs, as Apache can do
* DB handle. I know this one will be controversial.
[...]
1. Am I the only one who would wants an option for machine-readable logs?
I'd very much like a format that can be easily loaded into
From: Ron Peacetree [EMAIL PROTECTED]
Sent: Sep 24, 2005 6:30 AM
Subject: Re: [HACKERS] [PERFORM] Releasing memory during External sorting?
... the amount of IO done is the most
important of the things that you should be optimizing for in
choosing an external sorting algorithm.
snip
Since
,
this is a Big Deal.
This discussion has gotten my creative juices flowing. I'll post
some Straw Man algorithm sketches after I've done some more
thought.
Ron
-Original Message-
From: Dann Corbit [EMAIL PROTECTED]
Sent: Friday, September 23, 2005 2:21 PM
Subject: Re: [HACKERS] [PERFORM
.
This allows us access to much better external sorting algorithms.
For example Postman Sort (the 2005 winner of the PennySort benchmark)
is basically an IO optimized version of an external Radix Sort.
Ron
---(end of broadcast)---
TIP 9: In versions below
,
up to a point it is worth it to spend more CPU cycles to save on IO.
Given the large and growing gap between CPU IO, RAM IO, and HD IO, these issues
are becoming more important for _internal_ sorts as well.
Feedback, please.
Best Regards, Simon Riggs
Hope this is useful,
Ron
to take on few values and are
therefore substantially restricted.
Given the proper resources and algorithms, O(n) sorts are very plausible
when sorting DB records.
All of the fastest external sorts of the last decade or so take advantage of
this. Check out that URL I posted.
Ron
-Original
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Oh, and 'select rowid, * from table' which returns special rowid
column that just incrementally numbers each row.
Why?
Perhaps Christopher meant
select row_number() OVER (...) as rowid
Alvaro Herrera wrote:
Or, slightly different, what are people's most wanted features?
Things I would have found useful in the past year or so include:
Standards stuff:
* Updateable views (easier to use Ruby/Rails's ActiveRecord on legacy data)
* The elementary OLAP stuff
Contrib related
Joshua D. Drake wrote:
...when you comment something out, it should restore
...the contrary position is that a comment is a comment...
...If I comment out a parameter I expect...
The most unambiguous behavior would be to not have
commented out values in the config file at all.
If someone
Mark Woodward wrote:
It is 4.4G in space in a gzip package.
I'll mail a DVD to two people who promise to host it for Hackers.
Would it be easier to release the program you did to do
this conversion?
I use this pretty short (274 line) C program:
http://www.forensiclogic.com/tmp/tgr2sql.c
small because they're empty, I'd think)
and give many of the same benefits for excluding tables
as a non-partial index on year would have given.
Ron
I like the other features Simon mentioned, though, that sound like
they're based on these constraints.
---(end of broadcast
Bruno Wolff III wrote:
Shouldn't you be using 365.2425/12 (30.436875) for the number of days per
month?
Well, ISO 8601 prefers 30 to some weird fraction when they
define the term month; and uses a different term calendar
month for the exact number of days in a known month.
They make a
similar
table-wide locks.
Ron
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Tom Lane wrote:
Hm, notice that the processor utilization doesn't actually drop all that
much, so it seems it's not fundamentally an I/O storm kind of issue.
If I read the chart on the bottom of Josh's links correctly,
it looks to me like
the fast one is spending 50% CPU in user and 30% CPU
Josh Berkus wrote:
intagg: what does this module do which is not already available
through the built-in array functions and operators? Maybe I
don't understand what it does. Unnatributed in the README. Move
to pgfoundry?
Short summary:
Is there an equivalent of int_array_enum() built in?
elein wrote:
intarray: data_types/
what does this do that arrays do not?
It provides lossy indexes that work well on big arrays;
as well as some quite useful convenience functions that
work on arrays of ints.
---(end of broadcast)---
TIP 7:
Jonah H. Harris wrote:
I'm willing to move soundex and metaphone into the backend.
Does anyone see a reason not to do so?
As a kinda strange reason, I like them in contrib because
they demonstrate a nice simple example of how one can write a
contrib extension.
This module has simple functions
Marc G. Fournier wrote:
That is what pgFoundry was setup for ... to give projects the visibiilty
they would get through the core distribution by making sure they are
referenced in a central place, but providing the maintainers with direct
CVS access to make changes to their code in a timely
for estimating
the number of pages that would be hit. I think the existing
correlation does well for the first estimate; but for many data
sets, poorly for the second type.
If you want to start a contrib project that looks into additional
stats that may help, I might be interested.
Ron
Short summary:
* It looks to me like the planner vastly overestimates
the # of pages read by index scan in quite a few of my
tables even though stats collected by ANALYZE are correct.
* The problem happens any time you have multiple columns
that have a number of repeated values
an index scan will need.
Ron
PS: I pointed out workarounds in my earlier posting
in this thread. Yes, I'm using the same TIGER data
you are.
---(end of broadcast)---
TIP 8: explain analyze is your friend
single page are.
If someone pointed me in the right direction, I might
try doing this.
Ron
PS:
I think lots of other data has the same issues.
A very large name database ordered by
lastname,firstname will have all people
of a given firstname on a relatively small
set of pages
A new organization called the Software Freedom Law Center
was announced yesterday; that seems like it may be one of
the best places open-source groups could go for questions
like this ARC pending patent.
Eben Moglen (The FSF's main lawyer and Columbia Law prof),
Diane Peters (OSDL's general
Bruce Momjian wrote:
Added to TODO based on this discusion:...
* Speed up COUNT(*)
One think I think would help lots of people is if the
documentation near the COUNT aggregate explained some
of the techniques using triggers to maintain a count
for tables where this is important.
For every one
David Fetter wrote:
Folks,
As this came up in a work situation, I was wondering a little bit
about the top-k issue. Right now, top-k is implemented (most easily,
I think) via a SELECT with a LIMIT and no OFFSET. 3 questions arise
from this.
I think the simplest LIMIT query doesn't make it easy
) Would be appreciated.
I'd love to hear what any specs, especially the SQL spec
has to say for it.
Ron
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Bruno wrote:
Can you document which part of a mixed interval (with both months and
seconds parts) gets added first to a timestamp? I haven't ever run
across anything which says which gets done first.
In the existing code, the sql spec, or the proposed implementation?
In the existing code,
such as
standards or other major applications that behave otherwise.
Thanks for this other interesting case that I need to worry about!
And yes, I'll document it as well. :-)
Ron
PS: I'm not receiving some emails I send to hackers. If you
need a timely answer please cc me -- though I
and units. Any pointers.
Ron
Any other interval annoyances I should hit at the same time?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
. You can search the
archives or I hope this kicks a fresh discussion..:-)
I'm game, though I'm also not ready to lead such a project, probably not
even the discussion on it.
--
Mike Nolan
--
+---+
| Ron Johnson, Jr. Home: [EMAIL
three hours
is a killer feature for this system.
Ron
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
to this algorithm.
(Though hopefully it's all moot now that Andrew / Tom
found/recommended the paranoid overcommit option, which
sure seems like the most sane thing for a server to me)
Ron
PS: Oracle DBAs suffer from the same pain.
http://www.cs.helsinki.fi/linux/linux-kernel/2001-12/0098.html
some.
Ron
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
: no, don't do this if you want portability. I think the charset
idea's a better one.
Ron
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
Christopher Kings-Lynne wrote:
*sigh* It's just like a standard to come up with a totally new syntax for a
feature that no-one has except MySQL who use a different syntax :)
You sure? :)
http://otn.oracle.com/products/oracle9i/daily/Aug24.html
MERGE INTO SALES_FACT D
USING SALES_JUL01
FWIW, that's the approach O*'s taking.
http://otn.oracle.com/products/oracle9i/daily/Aug24.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Peter Eisentraut
Sent: Tuesday, February 18, 2003 11:02 AM
To: Christopher Kings-Lynne
Cc: Tom Lane; Hackers
needs a single sort
set sort_mem = 65536;
...
set sort_mem = 4096;
/// some ugly aggregate-creating join generated by a reporting tool
set sort_mem = 65536;
Ron
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go
' the postmaster...
and make sure it recovers gracefully when testing for an industrial-
strength solution.
Ron
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere
more information about how we use it (or if I
misunderstood). What is it that people _don't_ like?
-ron
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
to the databases and set passwords, we could do that too, it would
just be a bit more work.
-ron
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
gigs)
-ron
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 31, 2002 3:24 PM
To: Ron Snyder
Cc: pgsql-hackers
Subject: Re: [HACKERS] Can't import large objects in most
recent cvs (20020531 -- approx 1pm PDT)
Ron Snyder [EMAIL PROTECTED] writes:
I
by several different
users.
I believe that I can safely change the ownership of the
database in the old
server to qvowner, right? And run the pg_dump and pg_restore again? Or
should pg_restore connect as the superuser and just change ownership
afterwards?
-ron
(I've thought several
But I recall a number of rounds of bug-fixes concerning quoting in
the pgsql shell scripts, so I'd not be surprised in the
least to hear
that pre-7.2 PG releases get this wrong. Or for that
matter, we might
still have some problems in this line on some platforms with oddball
Jim Mercer wrote:
On Thu, May 02, 2002 at 09:45:45PM -0400, mlw wrote:
Jim Mercer wrote:
On Thu, May 02, 2002 at 09:14:03PM -0400, mlw wrote:
Jim Mercer wrote:
On Thu, May 02, 2002 at 08:41:30PM -0400, mlw wrote:
A mission statement is like a tie.
who on the list wears
...
Regards,
Ron de Jong
the Netherlands
(Windmill Cloggyland)
(in reality drugs redlight district ;-)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Not even close!
None wrote in message 3bd02277$[EMAIL PROTECTED]">news:3bd02277$[EMAIL PROTECTED]...
Hello
psql dbname
\dt employee
Should do the trick
Ron de Jong [EMAIL PROTECTED] wrote in
9qp2et$i7q$[EMAIL PROTECTED]:">news:9qp2et$i7q$[EMAIL PROTECTED]:
Noguera [EMAIL PROTECTED]
To: Ron de Jong [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Is there no DESCRIBE TABLE; on PGSQL?
help!!!
on psql, do \? there are a lot of commands that let you do it:
\l (this list databases)
dabicho=# \l
List of databases
by perl, DBI
I also know there is an object:
@names = $dbh-tables;
but what about databases?
Please help.
Ron de Jong
the Netherlands
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users
binary
input?
-Ron-
[EMAIL PROTECTED]
pg_md5.tar.gz
Thomas Lockhart wrote:
PostgreSQL, Inc perhaps has that as a game plan.
I'm not so much concerned about exactly what PG, Inc is planning to offer
as a proprietary piece - I'm purist enough that I worry about what this
signals for their future direction.
Hmm. What has kept replication
Don Baccus wrote:
At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:
how about having a middle man between apache (or aolserver or any other
clients...) and PosgreSQL ??
that middleman could be configured to have 16 persistant connections,every
clients would deal with the middleman instead
Note: CC'd to Hackers, as this has wandered into deeper feature issues.
Tom Lane wrote:
GH [EMAIL PROTECTED] writes:
Do the "persistent-connected" Postgres backends ever timeout or die?
No. A backend will sit patiently for the client to send it another
query or close the connection.
This
The Hermit Hacker wrote:
I'm tryin to figure out how to speed up udmsearch when run under
postgresql, and am being hit by atrocious performance when using a LIKE
query ... the query looks like:
SELECT ndict.url_id,ndict.intag
FROM ndict,url
WHERE ndict.word_id=1971739852
AND
301 - 368 of 368 matches
Mail list logo