Craig Ringer writes:
> Erik Jones wrote:
>> current_user() is a stable function and the manual is explicit that the
>> result of stable function can be used in an index scan:
> Yes ... but the planner doesn't know the value current_user will return,
I think it's got nothing to do with that and
Dmitri Girski writes:
> I am running a PostgreSQL server 8.3.5 with a pretty much standard config.
> The web application server which runs Apache 1.3/PHP2.9 has an intermittent
> problem:
> pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
> The long request happens at a
+Madison Kelly wrote:
You are right, autovacuum is not running after all. From your comment, I
am wondering if you'd recommend I turn it on or not? If so, given that I
doubt I will upgrade any time soon, how would I enable it? I suppose I
could google that, but google rarely shares gotcha's. :)
Dmitri Girski wrote:
I am running a PostgreSQL server 8.3.5 with a pretty much standard config.
The web application server which runs Apache 1.3/PHP2.9 has an
intermittent problem:
pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
The long request happens at approxima
Erik Jones wrote:
> On Jan 4, 2010, at 1:59 PM, Robert Haas wrote:
>
>> The thing is, PostgreSQL doesn't know at planning time what the value of
>> current_user() will be, so the plan can't depend on that; the planner
>> just takes its best shot.
>
> current_user() is a stable function and the m
Hi everybody,
I am running a PostgreSQL server 8.3.5 with a pretty much standard config.
The web application server which runs Apache 1.3/PHP2.9 has an intermittent
problem:
pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.
The long request happens at approximate rate 1:
2010/1/4 Erik Jones :
> On Jan 4, 2010, at 1:59 PM, Robert Haas wrote:
>> The thing is, PostgreSQL doesn't know at planning time what the value of
>> current_user() will be, so the plan can't depend on that; the planner
>> just takes its best shot.
>
> current_user() is a stable function and the ma
Madison Kelly wrote:
You are right, autovacuum is not running after all. From your comment,
I am wondering if you'd recommend I turn it on or not?...
I see you are considering an upgrade but FWIW on your 8.1 instance, my
remaining 8.1 server has been running for years with it on. Read up on
Scott Marlowe wrote:
On Mon, Jan 4, 2010 at 3:13 PM, Greg Smith wrote:
Madison Kelly wrote:
I think for now, I will stick with 8.1, but I will certainly try out your
repo edit above on a test machine and see how that works out. I am always
reticent to change something as fundamental as postgre
Rosser Schwarz wrote:
On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly wrote:
As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it
by default. How would I confirm that it's running or not?
I believe it's not enabled by default in 8.1-land, and is as of 8.2
and later. Whe
On Jan 4, 2010, at 1:59 PM, Robert Haas wrote:
> The thing is, PostgreSQL doesn't know at planning time what the value of
> current_user() will be, so the plan can't depend on that; the planner
> just takes its best shot.
current_user() is a stable function and the manual is explicit that the r
On Mon, Jan 4, 2010 at 3:13 PM, Greg Smith wrote:
> Madison Kelly wrote:
>>
>> I think for now, I will stick with 8.1, but I will certainly try out your
>> repo edit above on a test machine and see how that works out. I am always
>> reticent to change something as fundamental as postgres without "
Actually table itself has ~8000 records. I don't know why does it report 42
rows, since there is not even a matching row in the table for this specific
condition.. But as we all know, the universal answer for every question is
42 ;) Autovacuum is on, and I also did some vacuuming before I started t
On 01/04/2010 04:53 PM, Robert Haas [robertmh...@gmail.com] wrote:
PREPARE foo AS
EXPLAIN EXECUTE foo();
Thanks for the response. Results below. Brian
cemdb=> prepare foo as select count(distinct b.ts_id) from
ts_stats_transetgroup_user_weekly b, ts_stats_transet_user_interval c,
ts_transet
Madison Kelly wrote:
I think for now, I will stick with 8.1, but I will certainly try out
your repo edit above on a test machine and see how that works out. I
am always reticent to change something as fundamental as postgres
without "good reason". I guess I am a fan of "if it ain't broke...". :
On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly wrote:
> As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it
> by default. How would I confirm that it's running or not?
I believe it's not enabled by default in 8.1-land, and is as of 8.2
and later. Whether it's running or n
2010/1/4 Keresztury Balázs :
> just a small question: is it normal that PostgreSQL 8.4.1 always uses
> sequential scanning on any table when there is a condition having the
> constant "current_user"? Of course there is a btree index set on that table,
> but the DBMS just doesn't want to utilize it.
Steve Crawford wrote:
Madison Kelly wrote:
I wanted to get ahead of the problem, hence my question here. :)
I've set this to run at night ('iwt' being the DB in question):
su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"
And why not the vacuumdb command?:
su postgres -c "vacuumdb
Steve Crawford wrote:
Madison Kelly wrote:
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the
database...
Yup, I even tried man
Madison Kelly wrote:
I wanted to get ahead of the problem, hence my question here. :)
I've set this to run at night ('iwt' being the DB in question):
su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"
And why not the vacuumdb command?:
su postgres -c "vacuumdb --analyze --verbose iw
On Mon, Jan 4, 2010 at 2:41 PM, Brian Cox wrote:
> The query shown below [select count(distinct...] seems to be looping
> (99-101% CPU as shown by top for 11+ hours). This using postgres 8.3.5 on a
> dual quad core machine (Intel(R) Xeon(R) CPU X5460 @ 3.16GHz) with 32G RAM.
> Can I provide any ot
hi,
just a small question: is it normal that PostgreSQL 8.4.1 always uses
sequential scanning on any table when there is a condition having the
constant "current_user"? Of course there is a btree index set on that table,
but the DBMS just doesn't want to utilize it. When I replace current_user to
Madison Kelly wrote:
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the
database...
Yup, I even tried manually running 'VACUUM F
Brad Nicholson wrote:
I think you are going down the wrong route here - you should be looking
at preventative maintenance instead of fixing it after its broken.
Ensure that autovacuum is running for the database (assuming that you
are on a relatively modern version of PG), and possibly tune it t
Kevin Grittner wrote:
Madison Kelly wrote:
I've added CLUSTER -> ANALYZE -> VACUUM to my nightly
routine and dropped the VACUUM FULL call.
The CLUSTER is probably not going to make much difference once
you've eliminated bloat, unless your queries do a lot of searches in
the sequence of the
Madison Kelly wrote:
> I've added CLUSTER -> ANALYZE -> VACUUM to my nightly
> routine and dropped the VACUUM FULL call.
The CLUSTER is probably not going to make much difference once
you've eliminated bloat, unless your queries do a lot of searches in
the sequence of the index used. Be sure
On Mon, 2010-01-04 at 15:53 -0500, Madison Kelly wrote:
> Gary Doades wrote:
> > From your queries it definitely looks like its your stats that are the
> > problem. When the stats get well out of date the planner is choosing a
> > hash join because it thinks thousands of rows are involved where
Gary Doades wrote:
From your queries it definitely looks like its your stats that are the
problem. When the stats get well out of date the planner is choosing a
hash join because it thinks thousands of rows are involved where as only
a few are actually involved. Thats why, with better stats, t
On 04/01/2010 8:30 PM, Madison Kelly wrote:
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the
database...
Some questions:
Is
Yup, I even tried manually running 'VACUUM FULL' and it didn't help. As
for upgrading;
VACUUM FULL is usually considered a bad idea. What you probably want to
do instead is CLUSTER, followed by ANALYZE.
Basically, VACUUM makes the indexes smaller (but doesn't reclaim much
space from the ta
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the database...
Some questions:
Is autovacuum running? This is the most likely susp
The query shown below [select count(distinct...] seems to be looping
(99-101% CPU as shown by top for 11+ hours). This using postgres 8.3.5
on a dual quad core machine (Intel(R) Xeon(R) CPU X5460 @ 3.16GHz) with
32G RAM. Can I provide any other info to help investigate this issue? Or
any though
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the database...
Some questions:
Is autovacuum running? This is the most likely suspect. If not, things
wi
On 04/01/2010 7:10 PM, Madison Kelly wrote:
Hi all,
I've got a fairly small DB (~850MB when pg_dump'ed) running on PgSQL
v8.1.11 a CentOS 5.3 x86_64 Xen-based virtual machine. The filesystem is
ext3 on LVM with 32MB extents. It's about the only real resource-hungry
VM on the server.
It slow
Hi all,
I've got a fairly small DB (~850MB when pg_dump'ed) running on PgSQL
v8.1.11 a CentOS 5.3 x86_64 Xen-based virtual machine. The filesystem is
ext3 on LVM with 32MB extents. It's about the only real resource-hungry
VM on the server.
It slows down over time and I can't seem to find a w
35 matches
Mail list logo
