Subject: pg_autovacuum is nice ... but ...

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 16:10:21 -0400 (AST)

Dave and I have been spending today trying to narrow down why things are 
so slow ... using the current index.php as a test bed, since its simple 
enough to debug, we removed the (require) that was at the top, and pulled 
the file in directly, so that there was no 'reading from the file system' 
issue involved ... still slow as molasses ... comment out the database 
connections, zippy as can be ... of course, no data, since the queries 
failed ... but ...

Do a vacuum full analyze on the two databases being called, and load time 
went from 2.4sec to .46sec:

www# time fetch http://www.postgresql.org/index.php
fetch: http://www.postgresql.org/index.php: size of remote file is not known
index.php                                               24 kB 2737 kBps
0.000u 0.077s 0:00.46 15.2%

I've been able to drop Alexey's down by about half, from 5sec to 2.5sec 
(based on multiple loads, the lowest I've seen is 1.91sec) ... but, at 
least now when I go to look at the site in the browser, it isn't abysmally 
slow, only slow ...

Random samplings on index.php for the current site, I'm getting as low as 
.19sec ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: [email-masked]           Yahoo!: yscrappy              ICQ: 7615664

---

From: Justin Clift <[email-masked]>
Date: Thu, 04 Nov 2004 09:29:46 +1100

Marc G. Fournier wrote:
<snip>
> Do a vacuum full analyze on the two databases being called, and load 
> time went from 2.4sec to .46sec:

Hmmmm, is there any chance your Free Space Map settings aren't high enough?

Regards and best wishes,

Justin Clift

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 03 Nov 2004 14:36:34 -0800

> Hmmmm, is there any chance your Free Space Map settings aren't high enough?

I could be wrong but my understanding is that pg_autovacuum won't vacuum 
system tables so eventually you still need to perform a full vacuum (not 
neccessarily a vacuum full).

Could this be the issue?

> Regards and best wishes,
> 
> Justin Clift
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match

-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [email-masked] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 20:20:47 -0400 (AST)

> Hmmmm, is there any chance your Free Space Map settings aren't high enough?

I posted once asking about that, but got no responses :)

Here is a vacuum verbose on gborg's database:

INFO:  free space map: 1000 relations, 7454 pages stored; 23072 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.
and this is portal:

INFO:  free space map: 1000 relations, 7425 pages stored; 23024 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.

so, you tell me ... should I increase them?  I haven't been able to find 
any docs that talk about this :(

----

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 20:21:59 -0400 (AST)

> neccessarily a vacuum full).

I just setup a weekly cron to do a vacuum full ... I didn't realize that 
pg_autovacuum didn't catch it all, except, of course, I should have clued 
in when we had that fun with the banner database, where an index on a 12 
row table was faster then no index :)

----

---

From: Justin Clift <[email-masked]>
Date: Thu, 04 Nov 2004 12:15:38 +1100

Marc G. Fournier wrote:
<snip>
> any docs that talk about this :(

Hmmmm, what are the fsm settings for that postgresql.conf?

:)

Regards and best wishes,

Justin Clift

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 21:50:13 -0400 (AST)

> Hmmmm, what are the fsm settings for that postgresql.conf?

Ummmm ... the same as what the DETAIL line above states? :)

---

From: Tom Lane <[email-masked]>
Date: Wed, 03 Nov 2004 20:53:43 -0500

> so, you tell me ... should I increase them?

Yup. 20000 < 23072, so you're losing some proportion of FSM entries.
What's worse, the FSM relation table is maxed out (1000 = 1000) which
suggests that there are relations not being tracked at all; you have
no idea how much space is getting leaked in those.

You can determine the number of relations potentially needing FSM
entries by
	select count(*) from pg_class where relkind in ('r','i','t');
--- sum over all databases in the cluster to get the right result.

Once you've fixed max_fsm_relations, do vacuums in all databases, and
then vacuum verbose should give you a usable lower bound for
max_fsm_pages.

			regards, tom lane

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 3 Nov 2004 22:33:35 -0400 (AST)

> no idea how much space is getting leaked in those.

'k, increased to 25000/1500, and am runninga  vacuum on all databases now 
...

----

---

From: Robert Treat <[email-masked]>
Date: Wed, 3 Nov 2004 22:32:36 -0500

> >> any docs that talk about this :(

Those numbers don't look right, but I'd say increase your max_fsm_pages to 
25000 and your max_fsm_relations to  1250. Don't forget to HUP.

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---

From: Justin Clift <[email-masked]>
Date: Fri, 05 Nov 2004 09:11:33 +1100

Tom Lane wrote:
<snip>
> then vacuum verbose should give you a usable lower bound for

Would making max_fsm_relations and max_fsm_pages dynamically update 
themselves whilst PostgreSQL runs be useful?  Sounds like they're the 
kind of things that many people would receive maximum benefit if 
PostgreSQL altered these settings as needed itself.

?

Regards and best wishes,

Justin Clift

---

From: "Marc G. Fournier" <[email-masked]>
Date: Thu, 4 Nov 2004 18:26:08 -0400 (AST)

Moved to -hackers where this belongs :)

> PostgreSQL altered these settings as needed itself.

I'm not sure if I like this one too much ... but it would be nice if 
something like this triggered a warning in the logs, maybe a feature of 
pg_autovacuum itself?

----

---

From: Tom Lane <[email-masked]>
Date: Thu, 04 Nov 2004 17:44:32 -0500

>> themselves whilst PostgreSQL runs be useful?

Possibly, but it isn't happening in the foreseeable future, for the same
reason that we don't auto-update shared_buffers and the other shared
memory sizing parameters: we can't resize shared memory on the fly.

> something like this triggered a warning in the logs, maybe a feature of 

autovacuum would probably be a reasonable place to put it.  We don't
currently have any good way for autovacuum to get at the information,
but I suppose that an integrated autovacuum daemon could do so.

			regards, tom lane

---

From: Gavin Sherry <[email-masked]>
Date: Fri, 5 Nov 2004 10:09:05 +1100 (EST)

> something like this triggered a warning in the logs, maybe a feature of

Without a bit of hacking, its hard to increase the size of the free
space map dynamically. This is because the free space map resides in
shared memory and its the reason why the FSM GUC vars can only be changed
on postmaster restart -- because its at that time we can calculate how
much shared memory we need (for caching, fsm, other global resources) and
allocate it.

I think a contrib script which ran through each database and generated
some optimal FSM settings for a target database would be a good medium
term solution.

Thanks,

Gavin

---

From: "Marc G. Fournier" <[email-masked]>
Date: Thu, 4 Nov 2004 22:19:12 -0400 (AST)

> but I suppose that an integrated autovacuum daemon could do so.

You had mentioned doing the select on pg_class for the relations variable 
... pg_autovacuum could just as easily do that as part of its start up 
routine, no?  when it 'loads' all the table information?

----

---

From: Gaetano Mendola <[email-masked]>
Date: Fri, 05 Nov 2004 13:00:56 +0100

> memory sizing parameters: we can't resize shared memory on the fly.

Right but we can create a new segment and use it too. I don't know how
these segments are used but I used to do it in the past, of course you have
to create a memory manager that handle not ccntinuous segments.
Of course this only if the effort to do it can justify the man power working
on it.

Regards
Gaetano Mendola

---

From: Neil Conway <[email-masked]>
Date: Fri, 05 Nov 2004 23:33:06 +1100

> to create a memory manager that handle not ccntinuous segments.

The TelegraphCQ folks have already done this:

   http://archives.postgresql.org/pgsql-hackers/2003-05/msg00336.php

I haven't had a chance to look at the patch, though.

-Neil

---

From: Gaetano Mendola <[email-masked]>
Date: Fri, 05 Nov 2004 13:48:22 +0100

> I haven't had a chance to look at the patch, though.

Not bad, however that post is more than one year old. Implement a resizable
shared memory could really improve postgres performances ?

Regards
Gaetano Mendola

---

From: Gaetano Mendola <[email-masked]>
Date: Fri, 05 Nov 2004 18:39:02 +0100

Robert Treat wrote:
| On Friday 05 November 2004 07:48, Gaetano Mendola wrote:
|
|>Neil Conway wrote:
|> > Gaetano Mendola wrote:
|> >> Right but we can create a new segment and use it too. I don't know how
|> >> these segments are used but I used to do it in the past, of course you
|> >> have
|> >> to create a memory manager that handle not ccntinuous segments.
|> >
|> > The TelegraphCQ folks have already done this:
|> >
|> >   http://archives.postgresql.org/pgsql-hackers/2003-05/msg00336.php
|> >
|> > I haven't had a chance to look at the patch, though.
|>
|>Not bad, however that post is more than one year old. Implement a resizable
|>shared memory could really improve postgres performances ?
|>
|
|
| Well it certainly would in the sense that we could make the database a little
| more self tuning.

Yes, but someone ( I do not remember who in this list ) is scared about to be "paged" during
the night because the DB is slow because a planned changed autonomously. Unfortunatelly this
is the "major" argument ( I do not understand why ) against have a database self tuning.

Regards
Gaetano Mendola

---

From: Jan Wieck <[email-masked]>
Date: Mon, 08 Nov 2004 11:40:25 -0500

> but I suppose that an integrated autovacuum daemon could do so.

Don't know why this must be an "integrated" autovacuum. Can't the info 
about the FSM usage be presented as system views?

Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== [email-masked] #

---

From: Tom Lane <[email-masked]>
Date: Mon, 08 Nov 2004 13:05:03 -0500

> about the FSM usage be presented as system views?

No doubt, but that's not free either --- it'd still need supporting
code in the backend.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 28 Nov 2004 21:03:54 -0500 (EST)

Should I add a TODO to warn if FSM values are too small?  Is that doable?

---------------------------------------------------------------------------

Marc G. Fournier wrote:
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Justin Clift <[email-masked]>
Date: Mon, 29 Nov 2004 17:13:28 +1100

> Should I add a TODO to warn if FSM values are too small?  Is that doable?

It sounds like it should be, and it would be a valuable pointer to 
people, so yep.

Any idea who'd be interested in claiming it?

Regards and best wishes,

Justin Clift

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 29 Nov 2004 08:13:56 -0500 (EST)

> Any idea who'd be interested in claiming it?

Turns out it was already on the TODO list:
	
	* Allow free space map to be auto-sized or warn when it is too small
	
	  The free space map is in shared memory so resizing is difficult.