Thread: 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@hub.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@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
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@hub.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@hub.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@hub.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@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.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@postgresql.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@hub.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