Обсуждение: Unexplainable slow down...
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
> 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.
> -----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
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).
[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). >
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...)
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
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
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 >
> > > > 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
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