Thread: Unexplainable slow down...

Unexplainable slow down...

From
Ron Snyder
Date:
We've got this bizarre problem that we're unable to explain and solve, and I
need some pointers.  All pointers to documentation or clues will be
gratefully received.

-ron

Here's the timing information for the query that we first noticed the
problem on:

$ time psql quickview pgsql72  -c "select * from builds where
product='sourcepro_db' and state != 'N' and state != 'W' and finished >=
'03/12/2002' and finished < '03/13/2002' order by
machine,os,compiler,threadlib,debug,exportlevel,stdlibtype,linktype,state,id
,submitted,started,finished,user2,user1;" > test.out

real    1m52.781s
user    0m0.080s
sys     0m0.040s

######We removed the "ORDER BY" stuff, and limited the number of columns we
were interested in, and still see results similar:
$ time psql quickview pgsql72  -c "select finished from builds where
product='sourcepro_db' and state != 'N' and state != 'W' and finished >=
'03/12/2002' and finished < '03/13/2002';" > test.out

real    1m56.346s
user    0m0.010s
sys     0m0.000s

######We played around with LIMIT, and see the following type of thing (Note
the HUGE time increase when going from "limit 14" to "limit 15"):
$ time psql quickview pgsql72  -c "select * from builds where
product='sourcepro_db' and state != 'N' and state != 'W' and finished >=
'03/12/2002' and finished < '03/13/2002' limit 14;" > test.out

real    0m1.884s
user    0m0.000s
sys     0m0.010s
$ ls -al test.out
-rw-r--r--    1 snyder   sysadmin     8831 Mar 13 12:42 test.out
$ time psql quickview pgsql72  -c "select * from builds where
product='sourcepro_db' and state != 'N' and state != 'W' and finished >=
'03/12/2002' and finished < '03/13/2002' limit 15;" > test.out

real    0m48.913s
user    0m0.010s
sys     0m0.000s
$ ls -al test.out
-rw-r--r--    1 snyder   sysadmin     9383 Mar 13 12:43 test.out

Here's an "explain":
# explain select * from builds where product='sourcepro_db' and state != 'N'
and state != 'W' and finished >= '03/12/2002' and finished < '03/13/2002'
limit 15;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..833.71 rows=15 width=426)
  ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229 width=426)

EXPLAIN

Here's an explain without a "WHERE" clause, so it looks like the index on :
# explain select * from builds;
NOTICE:  QUERY PLAN:

Seq Scan on builds  (cost=0.00..121841.50 rows=162550 width=426)

EXPLAIN

Here's what the table looks like:

$  psql quickview pgsql72  -c "\d builds"
                     Table "builds"
     Attribute     |           Type           | Modifier
-------------------+--------------------------+----------
 id                | integer                  | not null
 visible           | boolean                  |
 state             | character(1)             |
 evaluated         | boolean                  |
 product           | character varying(30)    |
 compiler          | character varying(30)    |
 os                | character varying(30)    |
 stdlibtype        | character varying(30)    |
 linktype          | character varying(30)    |
 threadlib         | character varying(30)    |
 exportlevel       | character varying(30)    |
 usermode          | character varying(30)    |
 postbuildclean    | character varying(30)    |
 prebuildclean     | character varying(30)    |
 submitted         | timestamp with time zone |
 started           | timestamp with time zone |
 finished          | timestamp with time zone |
 machine           | character varying(100)   |
 errors            | integer                  |
 warnings          | integer                  |
 testsattempted    | integer                  |
 testspassed       | integer                  |
 testsfailed       | integer                  |
 examplesattempted | integer                  |
 examplespassed    | integer                  |
 examplesfailed    | integer                  |
 ping              | timestamp with time zone |
 start_count       | integer                  |
 user1             | character varying(50)    |
 user2             | character varying(50)    |
 user3             | character varying(50)    |
 user4             | character varying(50)    |
 user5             | character varying(50)    |
 user6             | character varying(50)    |
 debug             | character varying(30)    |
Indices: builds_compiler,
         builds_machine,
         builds_os,
         builds_pkey,
         builds_product,
         builds_product_os_compiler,
         builds_product_state_finished,
         builds_product_state_submitted,
         builds_started,
         builds_vis_compiler_fin_state,
         builds_vis_compiler_submitted,
         builds_vis_machine_fin_state,
         builds_vis_machine_submitted,
         builds_vis_os_fin_state,
         builds_vis_os_submitted,
         builds_vis_prod_fin_state,
         builds_visible_product,
         builds_visible_product_submitte


Re: Unexplainable slow down...

From
Stephan Szabo
Date:
> Here's an "explain":
> # explain select * from builds where product='sourcepro_db' and state != 'N'
> and state != 'W' and finished >= '03/12/2002' and finished < '03/13/2002'
> limit 15;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=0.00..833.71 rows=15 width=426)
>   ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229 width=426)
>
> EXPLAIN

What's the explain for limit 14? And what if you set enable_seqscan=off?

> Here's what the table looks like:
>
> $  psql quickview pgsql72  -c "\d builds"
>                      Table "builds"
>      Attribute     |           Type           | Modifier
> -------------------+--------------------------+----------
>  id                | integer                  | not null
>  visible           | boolean                  |
>  state             | character(1)             |
>  evaluated         | boolean                  |
>  product           | character varying(30)    |
>  compiler          | character varying(30)    |
>  os                | character varying(30)    |
>  stdlibtype        | character varying(30)    |
>  linktype          | character varying(30)    |
>  threadlib         | character varying(30)    |
>  exportlevel       | character varying(30)    |
>  usermode          | character varying(30)    |
>  postbuildclean    | character varying(30)    |
>  prebuildclean     | character varying(30)    |
>  submitted         | timestamp with time zone |
>  started           | timestamp with time zone |
>  finished          | timestamp with time zone |
>  machine           | character varying(100)   |
>  errors            | integer                  |
>  warnings          | integer                  |
>  testsattempted    | integer                  |
>  testspassed       | integer                  |
>  testsfailed       | integer                  |
>  examplesattempted | integer                  |
>  examplespassed    | integer                  |
>  examplesfailed    | integer                  |
>  ping              | timestamp with time zone |
>  start_count       | integer                  |
>  user1             | character varying(50)    |
>  user2             | character varying(50)    |
>  user3             | character varying(50)    |
>  user4             | character varying(50)    |
>  user5             | character varying(50)    |
>  user6             | character varying(50)    |
>  debug             | character varying(30)    |
> Indices: builds_compiler,
>          builds_machine,
>          builds_os,
>          builds_pkey,
>          builds_product,
>          builds_product_os_compiler,
>          builds_product_state_finished,
>          builds_product_state_submitted,
>          builds_started,
>          builds_vis_compiler_fin_state,
>          builds_vis_compiler_submitted,
>          builds_vis_machine_fin_state,
>          builds_vis_machine_submitted,
>          builds_vis_os_fin_state,
>          builds_vis_os_submitted,
>          builds_vis_prod_fin_state,
>          builds_visible_product,
>          builds_visible_product_submitte

Some of these indices seem obvious others I'm not sure what they're
supposed to cover.


Re: Unexplainable slow down...

From
Ron Snyder
Date:

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> > Limit  (cost=0.00..833.71 rows=15 width=426)
> >   ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229 width=426)
> >
> > EXPLAIN
>
> What's the explain for limit 14? And what if you set
> enable_seqscan=off?

The explain for limit 14 was the same (except that rows=14).  I know I can
disable seqscan in the .conf file-- is there a way that I can turn that off
in the psql client?  (It's a production database and they (the users) have
about 300 client connections that don't handle disconnections very well.)

We did increase shared_buffers from 1024 to 8096, but it doesn't seem to
have helped.

>
> Some of these indices seem obvious others I'm not sure what
> they're supposed to cover.
>

I agree, unfortunately I don't have a lot of information about the indices.

Thanks for taking a look.

-ron

Re: Unexplainable slow down...

From
Stephan Szabo
Date:
On Wed, 13 Mar 2002, Ron Snyder wrote:

>
>
> > -----Original Message-----
> > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> > > Limit  (cost=0.00..833.71 rows=15 width=426)
> > >   ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229 width=426)
> > >
> > > EXPLAIN
> >
> > What's the explain for limit 14? And what if you set
> > enable_seqscan=off?
>
> The explain for limit 14 was the same (except that rows=14).  I know I can
> disable seqscan in the .conf file-- is there a way that I can turn that off
> in the psql client?  (It's a production database and they (the users) have
> about 300 client connections that don't handle disconnections very well.)
set enable_seqscan=off;
should do it.

> > Some of these indices seem obvious others I'm not sure what
> > they're supposed to cover.
> >
>
> I agree, unfortunately I don't have a lot of information about the indices.
What does pg_indexes show for them (it'll get back a human readable index
statement).


Re: Unexplainable slow down...

From
Ron Snyder
Date:
[snyder@vault snyder]$ time psql quickview pgsql72  -c "select * from builds
where product='sourcepro_db' and state != 'N' and state != 'W' and finished
>= '03/12/2002' and finished < '03/13/2002' limit 15;" > test.out

real    1m5.387s
user    0m0.010s
sys     0m0.000s
[snyder@vault snyder]$ time psql quickview pgsql72  -c "set
enable_seqscan=off;select * from builds where product='sourcepro_db' and
state != 'N' and state != 'W' and finished >= '03/12/2002' and finished <
'03/13/2002' limit 15;" > test.out

real    0m31.689s
user    0m0.000s
sys     0m0.050s

[snyder@vault snyder]$ psql quickview pgsql72  -c "explain select * from
builds where product='sourcepro_db' and state != 'N' and state != 'W' and
finished >= '03/12/2002' and finished < '03/13/2002' limit 15;"
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..14629.38 rows=15 width=427)
  ->  Seq Scan on builds  (cost=0.00..133977.02 rows=137 width=427)

EXPLAIN
[snyder@vault snyder]$ psql quickview pgsql72  -c "set
enable_seqscan=off;explain select * from builds where product='sourcepro_db'
and state != 'N' and state != 'W' and finished >= '03/12/2002' and finished
< '03/13/2002' limit 15;"
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..22801.41 rows=15 width=427)
  ->  Index Scan using builds_product_state_finished on builds
(cost=0.00..208817.06 rows=137 width=427)

EXPLAIN

OK, I'm now more confused.  What do I do next to figure out why postgres
isn't choosing the better query?  We're running a vacuum analyze every
night-- do I need to tweak the weights so that seq_scan is less likely?

Here are the indices (I apologize for the formatting-- is there a different
format you'd prefer?):
quickview=# select * from pg_indexes where tablename='builds';
 tablename |            indexname            |
indexdef
-----------+---------------------------------+------------------------------
-------------------------------------------------------------------------
 builds    | builds_pkey                     | CREATE UNIQUE INDEX
builds_pkey ON builds USING btree (id)
 builds    | builds_product_os_compiler      | CREATE INDEX
builds_product_os_compiler ON builds USING btree (product, os, compiler)
 builds    | builds_product_state_finished   | CREATE INDEX
builds_product_state_finished ON builds USING btree (product, state,
finished)
 builds    | builds_product_state_submitted  | CREATE INDEX
builds_product_state_submitted ON builds USING btree (product, state,
submitted)
 builds    | builds_os                       | CREATE INDEX builds_os ON
builds USING btree (os)
 builds    | builds_compiler                 | CREATE INDEX builds_compiler
ON builds USING btree (compiler)
 builds    | builds_vis_machine_fin_state    | CREATE INDEX
builds_vis_machine_fin_state ON builds USING btree (visible, machine,
finished, state)
 builds    | builds_vis_os_fin_state         | CREATE INDEX
builds_vis_os_fin_state ON builds USING btree (visible, os, finished, state)
 builds    | builds_vis_compiler_fin_state   | CREATE INDEX
builds_vis_compiler_fin_state ON builds USING btree (visible, compiler,
finished, state)
 builds    | builds_machine                  | CREATE INDEX builds_machine
ON builds USING btree (machine)
 builds    | builds_vis_machine_submitted    | CREATE INDEX
builds_vis_machine_submitted ON builds USING btree (visible, machine,
submitted)
 builds    | builds_vis_os_submitted         | CREATE INDEX
builds_vis_os_submitted ON builds USING btree (visible, os, submitted)
 builds    | builds_vis_compiler_submitted   | CREATE INDEX
builds_vis_compiler_submitted ON builds USING btree (visible, compiler,
submitted)
 builds    | builds_started                  | CREATE INDEX builds_started
ON builds USING btree (started)
 builds    | builds_visible_product_submitte | CREATE INDEX
builds_visible_product_submitte ON builds USING btree (visible, product,
submitted)
 builds    | builds_vis_prod_fin_state       | CREATE INDEX
builds_vis_prod_fin_state ON builds USING btree (visible, product, finished,
state)
 builds    | builds_visible_product          | CREATE INDEX
builds_visible_product ON builds USING btree (visible, product)
 builds    | builds_product                  | CREATE INDEX builds_product
ON builds USING btree (product)
(18 rows)


-ron

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Thursday, March 14, 2002 7:38 AM
> To: Ron Snyder
> Cc: 'pgsql-general@postgresql.org'
> Subject: RE: [GENERAL] Unexplainable slow down...
>
>
> On Wed, 13 Mar 2002, Ron Snyder wrote:
>
> >
> >
> > > -----Original Message-----
> > > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> > > > Limit  (cost=0.00..833.71 rows=15 width=426)
> > > >   ->  Seq Scan on builds  (cost=0.00..123873.38 rows=2229
> > > > width=426)
> > > >
> > > > EXPLAIN
> > >
> > > What's the explain for limit 14? And what if you set
> > > enable_seqscan=off?
> >
> > The explain for limit 14 was the same (except that
> rows=14).  I know I
> > can disable seqscan in the .conf file-- is there a way that
> I can turn
> > that off in the psql client?  (It's a production database and they
> > (the users) have about 300 client connections that don't handle
> > disconnections very well.)
> set enable_seqscan=off;
> should do it.
>
> > > Some of these indices seem obvious others I'm not sure
> what they're
> > > supposed to cover.
> > >
> >
> > I agree, unfortunately I don't have a lot of information about the
> > indices.
> What does pg_indexes show for them (it'll get back a human
> readable index statement).
>

Re: Unexplainable slow down...

From
Stephan Szabo
Date:
On Thu, 14 Mar 2002, Ron Snyder wrote:

> [snyder@vault snyder]$ time psql quickview pgsql72  -c "select * from builds
> where product='sourcepro_db' and state != 'N' and state != 'W' and finished
> >= '03/12/2002' and finished < '03/13/2002' limit 15;" > test.out
>
> real    1m5.387s
> user    0m0.010s
> sys     0m0.000s
> [snyder@vault snyder]$ time psql quickview pgsql72  -c "set
> enable_seqscan=off;select * from builds where product='sourcepro_db' and
> state != 'N' and state != 'W' and finished >= '03/12/2002' and finished <
> '03/13/2002' limit 15;" > test.out
>
> real    0m31.689s
> user    0m0.000s
> sys     0m0.050s
>
> [snyder@vault snyder]$ psql quickview pgsql72  -c "explain select * from
> builds where product='sourcepro_db' and state != 'N' and state != 'W' and
> finished >= '03/12/2002' and finished < '03/13/2002' limit 15;"
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=0.00..14629.38 rows=15 width=427)
>   ->  Seq Scan on builds  (cost=0.00..133977.02 rows=137 width=427)
>
> EXPLAIN
> [snyder@vault snyder]$ psql quickview pgsql72  -c "set
> enable_seqscan=off;explain select * from builds where product='sourcepro_db'
> and state != 'N' and state != 'W' and finished >= '03/12/2002' and finished
> < '03/13/2002' limit 15;"
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=0.00..22801.41 rows=15 width=427)
>   ->  Index Scan using builds_product_state_finished on builds
> (cost=0.00..208817.06 rows=137 width=427)
>
> EXPLAIN
>
> OK, I'm now more confused.  What do I do next to figure out why postgres
> isn't choosing the better query?  We're running a vacuum analyze every
> night-- do I need to tweak the weights so that seq_scan is less likely?

Hmm, I'm not sure what the best answer is for this, it's getting beyond my
depth. I'd guess that it's possible that it's over estimating the number
of reads necessary to do the index scan because the rows are clustered
together which would make it over-estimate the index scan cost and/or it
could be underestimating the cost of the sequence scan/limit set as well
(for example if the rows you want are late in the table it's going to
underestimate the final cost I think.)

Unfortunately I can't think of a good setting to tweak.  You can turn off
enable_seqscan for just the one query or chaning random_page_cost in the
config, but neither of those are particularly appealing.

> Here are the indices (I apologize for the formatting-- is there a different
> format you'd prefer?):
That was fine...  I must have been braindamaged the first time (I didn't
follow that builds_product_state_finished meant on those three columns
in order...)






RPM vs. Source

From
"Carlo Florendo"
Date:
Hello,

I've successfully set-up Postgres 7.2 via RPM under Mandrake 8.0.  I've also set it up perfectly compiling the source.
However,
there is one difference I need to resolve:

When I install via RPM, the script /etc/rc.d/init.d/postgresql gets installed and that enables me to start postgres as
aservice. 
However, I also want to install postgres from source and run it as a service in other Linux distros.

How would I do this?


Thanks!

Best Regards,

Carlo Florendo
NEC Telecoms
fcarlo@ntsp.nec.co.jp







Closing idle connections

From
Steve Lane
Date:
Can someone tell me the easiest way to have the postmaster shut down idle
connections after some period of time? Or is this even the right way to
think about it?

I'm using Postgres as a back end with PHP/Apache in the middle. The server
keeps bogging down because apache doesn't seem to reuse old processes with
their connections very well -- the number of open connections keeps growing
until postmaster refuses any new ones.

Do I just need a way of shutting down the idle connections? Or is there
something else I'm missing?

-- sgl


Re: Closing idle connections

From
Philip Hallstrom
Date:
Not the most ideal solution, but you can set MaxRequestsPerChild to
soem value (several thousand or so).  By doing that apache will kill of
that child process which will close any db connections it is using.  That
should keep the number down...  or play around with Min/Max Servers so
that during idle times apache kills off it's children...

-philip



On Thu, 14 Mar 2002, Steve Lane wrote:

> Can someone tell me the easiest way to have the postmaster shut down idle
> connections after some period of time? Or is this even the right way to
> think about it?
>
> I'm using Postgres as a back end with PHP/Apache in the middle. The server
> keeps bogging down because apache doesn't seem to reuse old processes with
> their connections very well -- the number of open connections keeps growing
> until postmaster refuses any new ones.
>
> Do I just need a way of shutting down the idle connections? Or is there
> something else I'm missing?
>
> -- sgl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Re: Unexplainable slow down...

From
Ron Snyder
Date:
> >
> > OK, I'm now more confused.  What do I do next to figure out why
> > postgres isn't choosing the better query?  We're running a vacuum
> > analyze every
> > night-- do I need to tweak the weights so that seq_scan is
> less likely?
>
> Hmm, I'm not sure what the best answer is for this, it's
> getting beyond my depth. I'd guess that it's possible that
> it's over estimating the number of reads necessary to do the
> index scan because the rows are clustered together which
> would make it over-estimate the index scan cost and/or it
> could be underestimating the cost of the sequence scan/limit
> set as well (for example if the rows you want are late in the
> table it's going to underestimate the final cost I think.)

Hmm, I don't if it would be related, but the data only grows.
Additionally, we (about a week ago) migrated all of this data
(via pg_dump/pg_restore) from 7.1.3.  Perhaps this is related?

Thanks again for all the help!

-ron

Re: RPM vs. Source

From
Jean-Michel POURE
Date:
Le Vendredi 15 Mars 2002 01:30, Carlo Florendo a écrit :
> However, I also want to install postgres from source and run it as a
> service in other Linux distros.

Why not rebuild PostgreSQL RPM from source on your target distro with :
rpm --rebuild --target i586 rpm_name.src.rpm

I don't remember if it includes /etc/init.d/postgresql or not. I guess yes.

Cheers, Jean-Michel