Re: pg_autovacuum is nice ... but ...

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: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

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

Justin Clift wrote:
> 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?
>

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 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

On Thu, 4 Nov 2004, Justin Clift wrote:

> 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?

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 :(

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wed, 3 Nov 2004, Joshua D. Drake wrote:

> Justin Clift wrote:
>> 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?
>>
>
> 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?

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 :)

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Marc G. Fournier wrote:
<snip>
> 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 :(

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

:)

Regards and best wishes,

Justin Clift

> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Thu, 4 Nov 2004, Justin Clift wrote:

> Marc G. Fournier wrote:
> <snip>
>> 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 :(
>
> Hmmmm, what are the fsm settings for that postgresql.conf?

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

Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> 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?

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

On Wed, 3 Nov 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
>> 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?
>
> 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.

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

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Wednesday 03 November 2004 20:50, Marc G. Fournier wrote:
> On Thu, 4 Nov 2004, Justin Clift wrote:
> > Marc G. Fournier wrote:
> >> 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 :(
> >

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

Tom Lane wrote:
<snip>
> 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.

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

> regards, tom lane

Moved to -hackers where this belongs :)

On Fri, 5 Nov 2004, Justin Clift wrote:

> Tom Lane wrote:
> <snip>
>> 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.
>
> 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.

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?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> Moved to -hackers where this belongs :)

> On Fri, 5 Nov 2004, Justin Clift wrote:
>> Would making max_fsm_relations and max_fsm_pages dynamically update
>> 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.

> 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?

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

On Thu, 4 Nov 2004, Marc G. Fournier wrote:

>
> Moved to -hackers where this belongs :)
>
> On Fri, 5 Nov 2004, Justin Clift wrote:
>
> > Tom Lane wrote:
> > <snip>
> >> 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.
> >
> > 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.
>
> 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?

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

On Thu, 4 Nov 2004, Tom Lane wrote:

>> 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?
>
> 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.

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?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Tom Lane wrote:
> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
>
>>Moved to -hackers where this belongs :)
>
>
>>On Fri, 5 Nov 2004, Justin Clift wrote:
>>
>>>Would making max_fsm_relations and max_fsm_pages dynamically update
>>>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.

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

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.

-Neil

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 ?

Regards
Gaetano Mendola

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBi7qz7UpzwH2SGd4RAtaUAKC2Fij5hA9FF+TxFEGBq72LSGahpgCg4+D3
OTeNKU02YK8OgsJCaODZLn0=
=CI+E
-----END PGP SIGNATURE-----

On 11/4/2004 5:44 PM, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
>> Moved to -hackers where this belongs :)
>
>> On Fri, 5 Nov 2004, Justin Clift wrote:
>>> Would making max_fsm_relations and max_fsm_pages dynamically update
>>> 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.
>
>> 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?
>
> 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.

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. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> On 11/4/2004 5:44 PM, Tom Lane wrote:
>> 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.

> Don't know why this must be an "integrated" autovacuum. Can't the info
> 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

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

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

Marc G. Fournier wrote:
>
> Moved to -hackers where this belongs :)
>
> On Fri, 5 Nov 2004, Justin Clift wrote:
>
> > Tom Lane wrote:
> > <snip>
> >> 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.
> >
> > 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.
>
> 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?
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

Bruce Momjian wrote:
> 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

Justin Clift wrote:
> Bruce Momjian wrote:
> > 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?

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.

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