On Thu, 20 Sep 2007, [EMAIL PROTECTED] wrote:
Which other test can I do to find if this is a hardware, kernel o
postgres issue?
The little test hdparm does is not exactly a robust hard drive benchmark.
If you want to rule out hard drive transfer speed issues, take at look at
the tests sugges
Denes Daniel <[EMAIL PROTECTED]> writes:
> Simon Riggs <[EMAIL PROTECTED]> wrote:
>> Make the case. **I** want it is not sufficient...
> Sorry, I can't understand that... I'm far from perfect in english.
The point here is that you've repeated the same example N times without
actually making a cas
Simon Riggs <[EMAIL PROTECTED]> wrote:
> On Fri, 2007-09-21 at 21:20 +0200, Dániel Dénes wrote:
>
> > The costs may be different because I've tuned the query planner's
> > parameters.
>
> OK, understood.
>
> > > Ordering by parent, child is fairly common but the variation you've
> > > got here
Simon Riggs <[EMAIL PROTECTED]> writes:
> That's not my perspective. If the LIMIT had been applied accurately to
> the cost then the hashjoin would never even have been close to the
> nested join in the first place.
[ shrug... ] Your perspective is mistaken. There is nothing wrong with
the way t
It's a Debian 4.0r1 server without sound (alsa is disabled), I'm running the
querys locally.
It's happening running the query locally with PgAdmin and by jdbc locally too.
Yes I have win98, XP machines on my network, I will unplugged from the net and
test again. On monday I'll give you my answer.
Luiz K. Matsumura wrote:
Is connected to full 100Mb, it transfers many things quick to clients.
Is running Apache adn JBoss, transfer rate is good, I did scp to copy
many archives and is as quick as the old server.
I have no idea how to continue researching this problem. Now I'm going
to do s
>>> On Fri, Sep 21, 2007 at 3:40 PM, in message <[EMAIL PROTECTED]>,
"Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote:
> but in suddenly the performance slow down. We noticed that the problem
> was with the time to connect with the server, that was very slow.
> I think that was some DNS problem
>From: [EMAIL PROTECTED]
>Subject: Re: [PERFORM] Low CPU Usage
>
>I have no idea how to continue researching this problem. Now I'm going to
do some networks tests.
I would go back to the slow program and try to capture the slow queries in
the log file. Once you have some queries which are runn
[EMAIL PROTECTED] wrote:
> That's pretty odd. If you use \timing in psql, you can get execution
> time for each query, if it helps you track things down.
Yes, in the new server running with \timing it consumes 5.6 seconds
and in the old server it consumes 25 seconds.
> > Correct me if am
In reply to Alvaro Herrera:
> The best thing to do is paste them in a text file and send it as an
> attachment.
Okay, it's attached.
> Why did you set enable_sort=off? It's not like sorting 9 rows is going
> to take any noticeable amount of time anyway.
Of course it's no problem for 9 rows, bu
Denes Daniel wrote:
> I've just inserted some newlines, so it's better to read than when my
> email-client wraps the lines automatically. Did not touch the information
> itself. But here is the normal output of EXPLAIN ANALYZE:
The best thing to do is paste them in a text file and send it as an
> > >> > That's not what it looks like based on the EXPLAIN ANALYZE output.
> > >> > It looks like run time dropped from two seconds to half a second.
> > >>
> > >> > It seems as though you either have a network delay delivering the
> > >> > results,
> > >> > or your application is slow to read
Simon Riggs <[EMAIL PROTECTED]> írta:
> Ordering by parent, child is fairly common but the variation you've got
> here isn't that common. You'd need to make a case considering all the
> alternatives; nobody will agree without a balanced case that includes
> what is best for everyone.
>
> Your EXP
On Thu, 20 Sep 2007, Decibel! wrote:
I'm finding this rather interesting report from top on a Debian box...
how is it that linux thinks that 30G is cached?
top on Linux gives weird results when faced with situations where there's
shared memory involved. I look at /proc/meminfo and run ipcs w
In response to [EMAIL PROTECTED]:
> >> > That's not what it looks like based on the EXPLAIN ANALYZE output.
> >> > It looks like run time dropped from two seconds to half a second.
> >>
> >> > It seems as though you either have a network delay delivering the
> >> > results,
> >> > or your appli
>> > That's not what it looks like based on the EXPLAIN ANALYZE output.
>> > It looks like run time dropped from two seconds to half a second.
>>
>> > It seems as though you either have a network delay delivering the results,
>> > or your application is slow to read them.
>>
>> > Exactly how are
On Fri, 2007-09-21 at 13:53 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Fri, 2007-09-21 at 12:08 -0400, Tom Lane wrote:
> >> The reason you get a bad plan is that this rowcount estimate is so far
> >> off:
>
> > That's true, but its not relevant,
>
> Yes it is --- the
In response to [EMAIL PROTECTED]:
> > That's not what it looks like based on the EXPLAIN ANALYZE output.
> > It looks like run time dropped from two seconds to half a second.
>
> > It seems as though you either have a network delay delivering the results,
> > or your application is slow to read
> That's not what it looks like based on the EXPLAIN ANALYZE output.
> It looks like run time dropped from two seconds to half a second.
> It seems as though you either have a network delay delivering the results,
> or your application is slow to read them.
> Exactly how are you arriving at thos
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2007-09-21 at 12:08 -0400, Tom Lane wrote:
>> The reason you get a bad plan is that this rowcount estimate is so far
>> off:
> That's true, but its not relevant,
Yes it is --- the reason it wants to use a hashjoin instead of a
nestloop is exactly
I forgot to tell this plan was with Postgres 8.1.9 in the new server with
postgres 8.2.4 in the new server the plan is the same as with te old one (the
little difference in rows retrieved is that the database is yesterday snapshot).
This is the plan for the new server with postgres 8.2.4:
Hash Jo
>>> On Fri, Sep 21, 2007 at 12:30 PM, in message
<[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
wrote:
> This is the plan for the old server:
> Hash Join (cost=449.55..8879.24 rows=136316 width=904) (actual
> time=50.734..1632.491 rows=136316 loops=1)
. . .
> Total runtime: 2022.293 ms
> And this
On Fri, 2007-09-21 at 17:36 +0200, Denes Daniel wrote:
> Even though I forced the nested loop plan using both indexes (that
> returns the rows in the correct order), there is a needless sort step on
> the top, consuming half of the time even on such small tables.
> Now it's clear why the planner
On Fri, 2007-09-21 at 12:08 -0400, Tom Lane wrote:
> Csaba Nagy <[EMAIL PROTECTED]> writes:
> > Looking at Plan 2, it looks like the "limit" step is estimating wrongly
> > it's cost.
>
> The reason you get a bad plan is that this rowcount estimate is so far
> off:
That's true, but its not relevan
I'm doing several tests.
Right now I did a VACUUM FULL ANALYZE in both servers.
In the old one vacuum runs for about 354 seconds and in the new one 59 seconds.
Then I have ran
EXPLAIN ANALYZE
SELECT *
FROM fact_ven_renta fvr, dim_producto_std_producto dpp
WHERE
fvr.producto_std_producto_sk = dpp
On Fri, 2007-09-21 at 16:26 +0200, Csaba Nagy wrote:
> [snip]
>
> Ok, I was not able to follow your explanation, it's too deep for me into
> what the planner does...
I'm thinking that this case is too narrow to do too much with, when I
think about how we might do what I proposed. OTOH this isn't
>>> On Thu, Sep 20, 2007 at 4:36 PM, in message
<[EMAIL PROTECTED]>, "Kamen Stanev"
<[EMAIL PROTECTED]> wrote:
>
> Is there a way to find which query is doing large io operations and/or which
> is using cached data and which is reading from disk.
A big part of your cache is normally in the OS,
Csaba Nagy <[EMAIL PROTECTED]> writes:
> Looking at Plan 2, it looks like the "limit" step is estimating wrongly
> it's cost.
The reason you get a bad plan is that this rowcount estimate is so far
off:
>-> Index Scan using pk_table_a on table_a ta
> (cost=0.00..324786.18 rows=3
> OK, I can confirm that. I set the statistics target for column "a" on
> table_a to 1000, analyzed, and got the plan below. The only downside is
> that analyze became quite expensive on table_a, it took 15 minutes and
> touched half of the pages... I will experiment with lower settings,
> maybe it
Hi,
I think the query planner is unaware of the possibly best plan in some
situations. See the test case below:
-- --- --
CREATE TABLE tparent (
id integer NOT NULL,
ord integer NOT NULL,
CONSTRAINT par_pkey_id PRIMARY KEY (id),
CONSTRAINT
[snip]
Ok, I was not able to follow your explanation, it's too deep for me into
what the planner does...
> Incidentally, the way out of this is to improve the stats by setting
> stats target = 1000 on column a of ta. That will allow the optimizer to
> have a better estimate of the tail of the dis
>From: smiley2211
>Subject: Re: [PERFORM] Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!
>
>-> Seq Scan on encounters_questions_answers eqa
>(cost=1.00..17608.66 rows=464766 width=8) (actual
>time=0.003..735.934 rows=464766 loop
>s=7430)
I suffered the same fate when upgrading some time back. The single biggest
issue for me was that the default 8.X setup changed what had been fast query
plans using indexes to slow plans using sequential scans. Changing the
random_page_cost in postgresql.conf from 4.0 to 2.0 (which indicates to
Po
On Fri, 2007-09-21 at 14:12 +0200, Csaba Nagy wrote:
> On Fri, 2007-09-21 at 12:34 +0100, Simon Riggs wrote:
> > On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
> >
> > > > Can you plans with/without LIMIT and with/without cursor, for both b1
> > > > and b2?
> > >
> > > The limit is unfortun
In response to smiley2211 <[EMAIL PROTECTED]>:
>
> Dennis,
>
> Thanks for your reply.
>
> No, the OLD server are no longer available (decommissioned) - the new
> servers are definitely better h\w.
Says who? I've heard that one before, and I've seen it be false.
Some wonk replaced a 1Ghz system
Dennis,
Thanks for your reply.
No, the OLD server are no longer available (decommissioned) - the new
servers are definitely better h\w.
I do not have any queries to EXPLAIN ANALYZE as they are built by the
application and I am not allowed to enable logging on for that server - so
where do I go
On Fri, 2007-09-21 at 12:34 +0100, Simon Riggs wrote:
> On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
>
> > > Can you plans with/without LIMIT and with/without cursor, for both b1
> > > and b2?
> >
> > The limit is unfortunately absolutely needed part of the query
>
> Understood, but not
On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
> > Can you plans with/without LIMIT and with/without cursor, for both b1
> > and b2?
>
> The limit is unfortunately absolutely needed part of the query
Understood, but not why I asked...
--
Simon Riggs
2ndQuadrant http://www.2ndQuadran
On Fri, 2007-09-21 at 11:34 +0100, Heikki Linnakangas wrote:
> Which version of Postgres is this? In 8.3, a scan like that really won't
> suck it all into the shared buffer cache. For seq scans on tables larger
> than shared_buffers/4, it switches to the bulk read strategy, using only
> a few buff
On Fri, 2007-09-21 at 11:59 +0100, Simon Riggs wrote:
> Please re-run everything on clean tables without frigging the stats. We
> need to be able to trust what is happening is normal.
I did, the plan fiddling happened after getting the plans after a fresh
analyze, and I did run the plan again with
On Fri, 2007-09-21 at 12:08 +0200, Csaba Nagy wrote:
> On Fri, 2007-09-21 at 10:43 +0100, Gregory Stark wrote:
> > The other possibility is that Postgres just hasn't even touched a large part
> > of its shared buffers.
> >
>
> But then how do you explain the example I gave, with a 5.5GB table
>
On Fri, 2007-09-21 at 12:03 +0200, Csaba Nagy wrote:
> prepare test_001(bigint) as
> SELECT tb.*
> FROM table_a ta
> JOIN table_b2 tb ON ta.b=tb.b
> WHERE ta.a = $1
> ORDER BY ta.a, ta.b
> limit 10;
Please re-run everything on clean tables without frigging the stats. We
need to be able to trus
Csaba Nagy wrote:
> On Fri, 2007-09-21 at 10:43 +0100, Gregory Stark wrote:
>> The other possibility is that Postgres just hasn't even touched a large part
>> of its shared buffers.
>
> But then how do you explain the example I gave, with a 5.5GB table
> seq-scanned 3 times, shared buffers set to
On Fri, 2007-09-21 at 10:43 +0100, Gregory Stark wrote:
> The other possibility is that Postgres just hasn't even touched a large part
> of its shared buffers.
>
But then how do you explain the example I gave, with a 5.5GB table
seq-scanned 3 times, shared buffers set to 12 GB, and top still sho
Hi all,
Postgres version: 8.2.4
Tables:
table_a(a bigint, b bigint, primary key(a, b) );
table_b1(b bigint primary key, more columns...);
table_b2(b bigint primary key references table_b1(b), more columns...);
table_b1:
~ 27M rows;
~25 more columns;
width=309 (as reported by explain selec
"Csaba Nagy" <[EMAIL PROTECTED]> writes:
> On Fri, 2007-09-21 at 09:03 +0100, Gregory Stark wrote:
>> >> Mem: 32945280k total, 32871832k used,73448k free, 247432k buffers
>> >> Swap: 1951888k total,42308k used, 1909580k free, 30294300k cached
>> >
>> It seems to imply Linux is paging
Hi,
Le Friday 21 September 2007 01:04:01 Decibel!, vous avez écrit :
> I'm finding this rather interesting report from top on a Debian box...
I've read from people in other free software development groups that top/ps
memory usage outputs are not useful not trustable after all. A more usable
(o
On Fri, 2007-09-21 at 09:03 +0100, Gregory Stark wrote:
> >> Mem: 32945280k total, 32871832k used,73448k free, 247432k buffers
> >> Swap: 1951888k total,42308k used, 1909580k free, 30294300k cached
> >
> It seems to imply Linux is paging out sysV shared memory. In fact some of
> Heikki
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Decibel! <[EMAIL PROTECTED]> writes:
>> I'm finding this rather interesting report from top on a Debian box...
>
>> Mem: 32945280k total, 32871832k used,73448k free, 247432k buffers
>> Swap: 1951888k total,42308k used, 1909580k free, 30294300k
49 matches
Mail list logo
