IIRC, the win32 installer will enable autovacuum by default.
And yes,
autovacuum was my first thought as well after Thomas last
mail - that
would be a good explanation to why it happens when the postmaster is
idle.
I used the win32 installer defaults so autovacuum is
probably
Magnus Hagander wrote:
Right. Please try turning it off and see if the problem goes away.
It does (go away).
- thomas
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Magnus Hagander wrote:
Right. Please try turning it off and see if the problem goes away.
No, wait! It does *not* go away. Do I need to do anything more than
setting this in my postgresql.conf file:
autovacuum = false# enable autovacuum subprocess?
and restart the service?
Right. Please try turning it off and see if the problem goes away.
No, wait! It does *not* go away. Do I need to do anything
more than setting this in my postgresql.conf file:
autovacuum = false# enable autovacuum subprocess?
and restart the service?
The two zombie
Looks like there more unprotable code in the recent changes to pgbench
:-(
Regards, Dave.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: 30 September 2005 02:17
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: PGBuildfarm member snake Branch HEAD
On Fri, Sep 30, 2005 at 08:29:07AM +0200, Thomas Hallgren wrote:
Magnus Hagander wrote:
Right. Please try turning it off and see if the problem goes away.
No, wait! It does *not* go away. Do I need to do anything more than
setting this in my postgresql.conf file:
autovacuum = false
Dear Tom,
While testing the recent pgxs patch, I noticed that you can build and
install contrib with PGXS:
...
It seems that it ought to work to run installcheck too:
gmake USE_PGXS=1 installcheck
but this does not quite work because the pg_regress script isn't
included in the
From: Pailloncy Jean-Gerard [EMAIL PROTECTED]
Sent: Sep 29, 2005 7:11 AM
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
Jeff Baker:
Your main example seems to focus on a large table where a key
column has constrained values. This case is interesting in
proportion to the number of
From: Zeugswetter Andreas DAZ SD [EMAIL PROTECTED]
Sent: Sep 29, 2005 9:28 AM
Subject: RE: [HACKERS] [PERFORM] A Better External Sort?
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
From: Josh Berkus josh@agliodbs.com
Sent: Sep 29, 2005 12:54 PM
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
The biggest single area where I see PostgreSQL external
sort sucking is on index creation on large tables. For
example, for free version of TPCH, it takes only 1.5 hours to
Thanks for your reply Luke.
Bizgres looks like a very promissing project. I'll be sure to follow
it.
Thanks to everyone for their comments. I'm starting to understand the
truth behind the hype and where these performance gains and hits stem
from.
-Dan
---(end of
What about clustered filesystems? At first blush I would think the
overhead of something like GFS might kill performance. Could one
potentially achieve a fail-over config using multiple nodes with GFS,
each having there own instance of PostgreSQL (but only one running at
any given moment)?
Just to add a little anarchy in your nice debate...
Who really needs all the results of a sort on your terabyte table ?
I guess not many people do a SELECT from such a table and want all the
results. So, this leaves :
- Really wanting all the results, to fetch using
What is the relationship between database support for clustering and grid
computing and support for distributed databases?
Two-phase COMMIT is comming in 8.1. What effect will this have in promoting
FOSS grid support or distribution solutions for Postgresql?
---(end
On Thu, Sep 29, 2005 at 03:24:15PM +0200, Fabien COELHO wrote:
Dear Tom,
While testing the recent pgxs patch, I noticed that you can build and
install contrib with PGXS:
...
It seems that it ought to work to run installcheck too:
gmake USE_PGXS=1 installcheck
but this does not
Alvaro Herrera [EMAIL PROTECTED] writes:
(Maybe we should audit it for security problems, just in case the
distributors choose to include it in packages. The temp file usage for
$TMPFILE looks predictable, thus maybe it _is_ insecure. Apparently
it can be made to clobber any existing file
Alvaro Herrera [EMAIL PROTECTED] writes:
If it's two zombies per minute, then I bet it's the stat collector and
stat bufferer. They are restarted by the postmaster if not found to be
running.
That would make some sense, because the stat processes need to set up new
sockets (for the pipe
If it's two zombies per minute, then I bet it's the stat
collector and
stat bufferer. They are restarted by the postmaster if not
found to
be running.
That would make some sense, because the stat processes need
to set up new sockets (for the pipe between them). The
autovacuum
Tom Lane wrote:
Mike Rylander [EMAIL PROTECTED] writes:
Using that logic, a functions with one OUT param would be the same as
a function returning a rowtype with only one column,
But it's not (and no, I don't want to make it so, because the overhead
for the useless record result
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean a performance overhead)
Um, please read the documention. Returning
Tom Lane wrote:
However, why two zombies? That would mean that the grandchild process
started, which should mean that the pipe was already created ...
To clarify, I talk about the tcpview window and connections, and thus
zombi-connections. They both belong to the same pid and seems to
Ron,
Hmmm.
60GB/5400secs= 11MBps. That's ssllooww. So the first
problem is evidently our physical layout and/or HD IO layer
sucks.
Actually, it's much worse than that, because the sort is only dealing
with one column. As I said, monitoring the iostat our top speed was
2.2mb/s.
--Josh
Martijn van Oosterhout wrote:
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean a performance overhead)
Um,
-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?
So you might notice little performance hit bringing back a million rows,
and most of these type of single OUT params functions only return one
row/value anyway.
There would be zero perceivable difference in performance regardless of
the extra overhead for a single value/row.
Sounds like we
On Friday 30 September 2005 11:49, Martijn van Oosterhout wrote:
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean
Ron,
That 11MBps was your =bulk load= speed. If just loading a table
is this slow, then there are issues with basic physical IO, not just
IO during sort operations.
Oh, yeah. Well, that's separate from sort. See multiple posts on this
list from the GreenPlum team, the COPY patch for 8.1,
Ron,
On 9/30/05 1:20 PM, Ron Peacetree [EMAIL PROTECTED] wrote:
That 11MBps was your =bulk load= speed. If just loading a table
is this slow, then there are issues with basic physical IO, not just
IO during sort operations.
Bulk loading speed is irrelevant here - that is dominated by
On Fri, Sep 30, 2005 at 10:08:11AM +0100, Dave Page wrote:
Looks like there more unprotable code in the recent changes to pgbench
:-(
Here, the culprits are tfind() and tsearch(). These apparently aren't
portable enough, but they seem to exist on all other platforms. Maybe
we could come up
Dan,
On 9/29/05 3:23 PM, Daniel Duvall [EMAIL PROTECTED] wrote:
What about clustered filesystems? At first blush I would think the
overhead of something like GFS might kill performance. Could one
potentially achieve a fail-over config using multiple nodes with GFS,
each having there own
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
Luke Lonergan wrote:
Dan,
On 9/29/05 3:23 PM, Daniel Duvall [EMAIL PROTECTED] wrote:
What about clustered filesystems? At first blush I would think the
overhead of something like GFS might kill performance. Could one
potentially achieve a fail-over config using multiple nodes with GFS,
On Wed, Sep 28, 2005 at 11:35:31AM -0400, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
Sorry, remembered it wrong. It's 'Did not find any relation named',
which appears to be in bin/psql/describe.c. It does occur when trying to
do a \d on a specific table.
Hmm, no immediate
http://archives.postgresql.org/pgsql-bugs/2005-02/msg00135.php
I think it may have been a bit early to disable pthread python support
(http://archives.postgresql.org/pgsql-hackers/2005-09/msg01136.php), as
Python was working fine on buildfarm member platypus. Maybe it's only an
issue with 4.x
On Thu, Sep 29, 2005 at 12:50:13AM +0300, Hannu Krosing wrote:
On T, 2005-09-27 at 17:57 -0500, Jim C. Nasby wrote:
On Tue, Sep 27, 2005 at 02:47:46PM -0400, Jan Wieck wrote:
On 9/24/2005 8:17 PM, Jim C. Nasby wrote:
Would it be difficult to vacuum as part of a dump? The reasoning
On Mon, Sep 26, 2005 at 06:23:06PM +0200, Andreas Pflug wrote:
Tom Lane wrote:
Dennis Bjorklund [EMAIL PROTECTED] writes:
Do the sql standard say anything on the matter?
It doesn't seem very helpful. AFAICS, we should interpret storing
'23:59:59.99' into a TIME(0) field as a cast from
On Mon, Sep 26, 2005 at 11:46:47AM -0400, Tom Lane wrote:
Jochem van Dieten [EMAIL PROTECTED] writes:
On 9/26/05, Dennis Bjorklund wrote:
One reason is because it's what the standard demand.
Could you cite that? The only thing I can find in the SQL standard is
that the hour field in an
Alvaro Herrera wrote:
On Fri, Sep 30, 2005 at 10:08:11AM +0100, Dave Page wrote:
Looks like there more unprotable code in the recent changes to pgbench
:-(
Here, the culprits are tfind() and tsearch(). These apparently aren't
portable enough, but they seem to exist on all other
On Wed, Sep 28, 2005 at 06:07:02PM -0400, Neil Conway wrote:
On Wed, 2005-28-09 at 18:35 -0300, Marc G. Fournier wrote:
The problem isn't whether or not they should be changed, the problem is
that they were changed *during* beta AND *against* the direction that
discussion on these changes
On Mon, Sep 26, 2005 at 10:57:54AM -0700, Ron Mayer wrote:
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
On Fri, 2005-30-09 at 17:47 -0500, Jim C. Nasby wrote:
What's wrong with adding pg_cancel_backend(...) RETURNS int as an alias
for the one that returns boolean, and document that it's deprecated and
will be removed in the future.
You can't overload functions based on their return type alone.
Jim C. Nasby wrote:
On Wed, Sep 28, 2005 at 06:07:02PM -0400, Neil Conway wrote:
On Wed, 2005-28-09 at 18:35 -0300, Marc G. Fournier wrote:
The problem isn't whether or not they should be changed, the problem is
that they were changed *during* beta AND *against* the direction that
On Mon, Sep 26, 2005 at 09:07:45PM -0700, Joshua D. Drake wrote:
A nice pg_upgrade utility would make a big difference. Clearly an
in-place upgrade is possible, but maintaining is hard. There are two
broad ways of running a pg_upgrade project - one that is entirely
independent of the main
Bulk loading speed is irrelevant here - that is dominated by parsing,
which
we have covered copiously (har har) previously and have sped up by 500%,
which still makes Postgres 1/2 the loading speed of MySQL.
Let's ask MySQL 4.0
LOAD DATA INFILE blah
0 errors, 666 warnings
SHOW
That 11MBps was your =bulk load= speed. If just loading a table
is this slow, then there are issues with basic physical IO, not just
IO during sort operations.
As I said, the obvious candidates are inefficient physical layout
and/or flawed IO code.
Until the basic IO issues are addressed, we
Back in 2003 Bruce Momjian proposed adding a flag (-I) to psql to force
it into interactive mode. (See
http://archives.postgresql.org/pgsql-hackers/2003-11/msg00013.php for
the thread.) The proposal was rejected because there was no proven need
for it at that time. I'd like to raise this
I have seen similar performance as Josh and my reasoning is as follows:
* WAL is the biggest bottleneck with its default size of 16MB. Many
people hate to recompile the code to change its default, and
increasing checkpoint segments help but still there is lot of overhead
in the rotation of
On Mon, Sep 26, 2005 at 08:54:49PM -0700, Josh Berkus wrote:
Tom,
Or, as you say, we could take the viewpoint that there are commercial
companies willing to take on the burden of supporting back releases, and
the development community ought not spend its limited resources on doing
that.
On Wed, Sep 28, 2005 at 10:22:51AM -0400, Tom Lane wrote:
Qingqing Zhou [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] wrote in message
There is a possible sequence like this:
ALIGNOF_LONG4
ALIGNOF_DOUBLE 8
MAXIMUM_ALIGNOF 8
vs.
ALIGNOF_LONG8
On Tue, Sep 27, 2005 at 07:00:14PM +0530, Gnanavel S wrote:
On 9/27/05, Jonah H. Harris [EMAIL PROTECTED] wrote:
Were you looking for a call graph?
Yes. I want to know the list and sequence of files involved during a call.
Total non-coder question, but is there an open-source utility
On Wed, Sep 28, 2005 at 10:24:18PM +0200, Martijn van Oosterhout wrote:
On Wed, Sep 28, 2005 at 11:00:46PM +0400, Ilia Kantor wrote:
Your suggestion is essentially the same as mine..
There exists tableoid, pretty much suited to tell between tables in the case
of inheritance.. I can't see a
On Wed, Sep 28, 2005 at 07:25:46PM +0100, Simon Riggs wrote:
Include the Discriminator as a column in A and it will be inherited by
all A1, A2, A3.
e.g. concrete_class char(1) not null
snip
This will add 1 byte per row in your superclass... and requires no
I thought char was actually
25MBps should not be a CPU bound limit for IO, nor should it be
an OS limit. It should be something ~100x (Single channel RAM)
to ~200x (dual channel RAM) that.
For an IO rate of 25MBps to be pegging the CPU at 100%, the CPU
is suffering some combination of
A= lot's of cache misses (cache
On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
So you might notice little performance hit bringing back a million rows,
and most of these type of single OUT params functions only return one
row/value anyway.
There would be zero perceivable difference in performance
On Thu, Sep 29, 2005 at 09:28:38PM +0800, Christopher Kings-Lynne wrote:
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),
Admin varchar(30)
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
On Thu, Sep 29, 2005 at 11:05:47AM -0400, Jonah H. Harris wrote:
Has there been any movement on this? If not, I finally have some time to
look at it.
Well, here's the original thread:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00637.php
I think the problem is that the testing
On Fri, Sep 30, 2005 at 06:58:05PM -0400, Bruce Momjian wrote:
We don't have the ability to have to functions that take the same
parameters and return different results because there is no facility to
decide which function to call based on what return value is expected,
because a simple query
On Fri, 30 Sep 2005, Jim C. Nasby wrote:
ERROR: cache lookup failed for relation 1906465919
It is on an exact table name. When we retry the describe on a failure,
sometimes it works and sometimes it fails again. When it fails again
the relation number is different. Also, \d schema.table
Jim C. Nasby wrote:
On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
So you might notice little performance hit bringing back a million rows,
and most of these type of single OUT params functions only return one
row/value anyway.
There would be zero perceivable
On 9/30/05, Jim C. Nasby [EMAIL PROTECTED] wrote:
On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
So you might notice little performance hit bringing back a million rows,
and most of these type of single OUT params functions only return one
row/value anyway.
There
On Fri, Sep 30, 2005 at 05:54:49PM -0500, Jim C. Nasby wrote:
On Mon, Sep 26, 2005 at 10:57:54AM -0700, Ron Mayer wrote:
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.
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
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
I've noticed that row count estimates for expression indexes appear
to rely on default_statistics_target rather than on a column's
actual statistics target. That is, if I use ALTER TABLE SET
STATISTICS to increase a column's statistics target and then run
ANALYZE, then estimates for
This is expected. The main TODO items is:
* Allow accurate statistics to be collected on indexes with more than
one column or expression indexes, perhaps using per-index statistics
Basically, we don't have multi-column or expression statistics. ANALYZE
just analyzes
Michael Fuhr [EMAIL PROTECTED] writes:
I've noticed that row count estimates for expression indexes appear
to rely on default_statistics_target rather than on a column's
actual statistics target. That is, if I use ALTER TABLE SET
STATISTICS to increase a column's statistics target and then
On Fri, Sep 30, 2005 at 11:59:26PM -0400, Bruce Momjian wrote:
This is expected. The main TODO items is:
* Allow accurate statistics to be collected on indexes with more than
one column or expression indexes, perhaps using per-index statistics
Basically, we don't have
On Sat, Oct 01, 2005 at 12:53:03AM -0400, Tom Lane wrote:
The code does in fact honor per-column statistics targets attached to
expression indexes, viz
alter table myfuncindex alter column pg_expression_1 set statistics 100;
Aha -- that's the piece I didn't know about. I was wondering where
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]
70 matches
Mail list logo