Обсуждение: Re: [pgsql-www] pg_autovacuum is nice ... but ...
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@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.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@hub.org Yahoo!: yscrappy ICQ: 7615664
Tom Lane wrote:> "Marc G. Fournier" <scrappy@postgresql.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>>>themselveswhilst PostgreSQL runs be useful?>>> Possibly, but it isn't happening in the foreseeable future, for thesame> reason that we don't auto-update shared_buffers and the other shared> memory sizing parameters: we can't resizeshared 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>> thesesegments are used but I used to do it in the past, of course you>> have>> to create a memory manager that handle notccntinuous 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@postgresql.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@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.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@hub.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@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
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