Hullo, here's one of those dreadful touchy-feely hand-waving problems.
Our 5-node 8.1.3 Slony system has just started taking /much/ longer to
VACUUM ANALYZE..
The data set has not increased more than usual (nightly backups stand
at 1.3GB, growing by 10MB per day), and no configuration has changed
> > > You may try to figure out what's the process doing (the backend
> > > obviously, not the frontend (Tcl) process) by attaching
> to it with
> > > strace.
> >
> > It's so sad when us poor Windows guys get helpful hints from people
> > assume that we're smart enough to run *NIX... ;-)
>
>
I seem to remember Oleg/Teodor recently reporting a problem with Windows
hanging on a multi-processor machine, during a heavy load operation.
In their case it seemed like a vacuum would allow it to wake up. They
did commit a patch that did not make it into the last minor version for
lack of testi
> I can just see the postgresql group getting together at the next
> O'Reilley's conference and creating that band. And it will all be your
> fault.
Finally, a chance for me to wear my black leather pants.
> A context switch storm is when your machine spends more time trying to
> figure out what
Gavin Hamill <[EMAIL PROTECTED]> writes:
> Nodes 2 and 3 take only the tables necessary to run our search (10 out
> of the full 130) and are much lighter (only 7GB on disk cf. 30GB for
> the full master) , yet the nightly VACUUM FULL has jumped from 2 hours
> to 4 in the space of one day!
I guess
This is pretty interesting - where can I read more on this? Windows isn't
actually hanging, one single command line window is - from its behaviour, it
looks like the TCL postgresql package is waiting for pg_exec to come back
from the commit (I believe the commit has actually gone through).
It c
On 10/26/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
This is pretty interesting - where can I read more on this? Windows isn't
actually hanging, one single command line window is - from its behaviour, it
looks like the TCL postgresql package is waiting for pg_exec to come back
from the commit
On Thu, 26 Oct 2006 10:47:21 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Gavin Hamill <[EMAIL PROTECTED]> writes:
> > Nodes 2 and 3 take only the tables necessary to run our search (10
> > out of the full 130) and are much lighter (only 7GB on disk cf.
> > 30GB for the full master) , yet the night
(Repost - did not appear to make it to the list the first time)
I have written a stored procedure for 8.1 that wraps a single (albeit complex)
query, and uses 2 IN parameters (BIGINT, INTEGER) in the FROM JOIN and WHERE
clauses. The procedure is written in SQL (as opposed to plpgsql - although
"Matthew Peters" <[EMAIL PROTECTED]> writes:
> How can a stored procedure containing a single query not implement the
> same execution plan (assumption based on the dramatic performance
> difference) that an identical ad-hoc query generates?
Parameterized vs non parameterized query?
Parameterized.
IE (very simplified)
CREATE OR REPLACE FUNCTION my_function(IN param1 BIGINT, IN param2
INTEGER)
RETURNS my_type
SECURITY DEFINER
AS
$$
/* my_type = (a,b,c) */
Select a,b,c
FROM my_table
WHERE indexed_column = $1
AND partition_constraint_colu
> A context switch storm is when your machine spends more time trying to
> figure out what to do than actually doing anything. The CPU spends most
> it's time switching between programs than running them.
Well, we usually use the term "thrashing" as the generic for when your
machine is spending
On Thu, 26 Oct 2006, Carlo Stonebanks wrote:
>
> It could even be that there's something wrong with the TCL package, but from
> my understanding it is one of the most complete interfaces out there - which
> is weird, because TCL seems to be the most unpopular language in the
> community.
>
Not t
> when it happens, make sure to query pg_locks and see what is going on
> there lock issues are not supposed to manifest on a commit, which
> releases locks, but you never know.
There aren't any pedning locks (assuming that pgAdmin is using pg_locks to
display pendin glocks).
> There have been r
Perl started out fast - TCL started out slow. Perl used syntax that,
although it would drive some people crazy, followed a linguistic curve
that Larry Wall claimed was healthy. The English language is crazy,
and yet, it has become standard world wide as well. Designed, regular
languages like Espera
> Perl has a wealth of modules on CPAN to do almost anything you need to.
> TCL has the beginning of one (not as rich), but comes built-in with things
> like event loops, and graphicals (Tk).
>
> I could go on and on - but I won't, because this is the PostgreSQL
> mailing list. People either get
On Wed, Oct 25, 2006 at 05:31:29PM -0400, Mark Lonsdale wrote:
> Set my sort_mem to 8192
You really need to look at what your workload is before trying to tweak
sort_mem. With 8G of memory, sort_mem=40 (~400MB) with only 10
active connections might be a good setting. It's usually better to get
On Thu, Oct 26, 2006 at 04:06:09PM +0100, Gavin Hamill wrote:
> On Thu, 26 Oct 2006 10:47:21 -0400
> Tom Lane <[EMAIL PROTECTED]> wrote:
>
> > Gavin Hamill <[EMAIL PROTECTED]> writes:
> > > Nodes 2 and 3 take only the tables necessary to run our search (10
> > > out of the full 130) and are much l
The planner has no idea what $1 and $2 are when it plans the query, so
that could easily explain why the performance is different. You can
prepare statements in psql (at least in 8.1), which would be a good way
to verify that theory (compare EXPLAIN for prepared vs. non).
On Thu, Oct 26, 2006 at 0
On Thu, 26 Oct 2006 14:17:29 -0500
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> Are you sure that there's nothing else happening on the machine that
> could affect the vacuum times? Like, say a backup? Or perhaps updates
> coming in from Slony that didn't used to be there?
I'm absolutely certain.
Hello
All-
We have a
question about numbers of fields in the select clause of a query and how that
affects query speed.
The following
query simply selects the primary key field from a table with 100,000
records:
select p.
"Tom Darci" <[EMAIL PROTECTED]> writes:
> It runs in about half a second (running in PgAdmin... the query run
> time, not the data retrieval time)
I don't have a lot of faith in PgAdmin's ability to distinguish the two.
In fact, for a query such as you have here that's just a bare seqscan,
it's
On Thu, Oct 26, 2006 at 09:35:56PM +0100, Gavin Hamill wrote:
> On Thu, 26 Oct 2006 14:17:29 -0500
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
>
> > Are you sure that there's nothing else happening on the machine that
> > could affect the vacuum times? Like, say a backup? Or perhaps updates
> > co
i have wondered myself. i wouldn't do it through pgAdmin (not sure what
the best test it, but i thought psql from the same machine might be
better--see below). anyway, the funny thing is that if you concatenate
them the time drops:
~% time psql -dXXX -hYYY -UZZZ -c"select consumer_id from consumer
On Thu, Oct 26, 2006 at 09:35:56PM +0100, Gavin Hamill wrote:
>
> I'm absolutely certain. The backups run from only one slave, given that
> it is a full copy of node 1. Our overnight traffic has not increased
> any, and the nightly backups show that the overall size of the DB has
> not increased m
On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote:
> i have wondered myself. i wouldn't do it through pgAdmin (not sure what
> the best test it, but i thought psql from the same machine might be
> better--see below). anyway, the funny thing is that if you concatenate
> them the time dro
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote:
>> anyway, the funny thing is that if you concatenate
>> them the time drops:
> Sure. Take a look at the output and you'll see there's less data to
> shove around.
Even more to the point, p
Thanks for all the feedback, folks.
Running explain analyze (see below) I get results similar to Tom Lane,
where the 2 queries run at the same speed.
And running in psql (see below) we see the expected speed degradation
for multiple fields, although concatenation is not getting us any
advantage.
Hi,
I wanted to use "exp1 is not distinct from exp2" which I tough was syntaxic
sugar for
exp1 is not null and exp2 is not null and exp1 = exp2 or exp1 is null and
exp2 is null
but my index is ignored with "is not distinct from".
Is this the expected behavior ?
create temporary table t as se
29 matches
Mail list logo
