Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Thu, Jun 07, 2007 at 06:09:36PM -0400, Alvaro Herrera wrote: >> Gregory Stark wrote: >>> When does this bug date to? >> >> It was in 8.1. I didn't verify whether it affects on 8.0; I think we >> separated the shared tables in pgstats in 8.1, with the advent of >> autovacuum, so I assume it doesn't. > Doesn't appear to, at least not using the test case I found for 8.1 > and later. Yeah, the separate hashtable for shared rels was added for 8.1: http://archives.postgresql.org/pgsql-committers/2005-07/msg00627.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
On Thu, Jun 07, 2007 at 06:09:36PM -0400, Alvaro Herrera wrote: > Gregory Stark wrote: > > When does this bug date to? > > It was in 8.1. I didn't verify whether it affects on 8.0; I think we > separated the shared tables in pgstats in 8.1, with the advent of > autovacuum, so I assume it doesn't. Doesn't appear to, at least not using the test case I found for 8.1 and later. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
On Thu, Jun 07, 2007 at 11:46:17PM +0100, Gregory Stark wrote: > Does anything use the pgstats data for anything other than presenting feedback > to users? > > Autovacuum uses it to estimate when tables should be vacuumed right? This > wouldn't have caused autovacuum to go nuts vacuuming these tables would it? > But I doubt even then that it could consume much i/o bandwidth. I discovered this problem after noticing that pg_shdepend had gotten horribly bloated -- apparently due to heavy use of temporary tables by an application whose performance I was investigating -- despite autovacuum being enabled. When I looked at the statistics for pg_shdepend the values for n_tup_{ins,upd,del} were much lower than I expected. After watching the numbers grow for a few minutes I saw them reset; after observing this behavior several times I was able to correlate the resets with vacuums of other tables. Since the statistics for pg_shdepend rarely got high enough to trigger an autovacuum that table was almost never being vacuumed. I suggested to the DBA that he vacuum it manually; after five minutes the vacuum completed and the application's performance improved immediately. INFO: "pg_shdepend": found 8475403 removable, 3907 nonremovable row versions in 76783 pages -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
Gregory Stark wrote: > "Alvaro Herrera" <[EMAIL PROTECTED]> writes: > > > Gregory Stark wrote: > > > > > is it possible it's related to the performance drop immediately > > > following a vacuum analyze we've been seeing? > > > > I don't think so, unless you were counting on pgstats data of shared > > tables for something. The optimizer, for one, doesn't, so I doubt it > > would affect query planning. And it would only affect you if your > > queries were using shared tables, which I very much doubt ... > > Does anything use the pgstats data for anything other than presenting feedback > to users? Not that I know of. > Autovacuum uses it to estimate when tables should be vacuumed right? Yep > This wouldn't have caused autovacuum to go nuts vacuuming these tables > would it? But I doubt even then that it could consume much i/o > bandwidth. Yes but keep in mind that these are only the shared tables: pg_database, pg_authid, pg_shdepend, etc. Those are not tables that you're going to use regularly, much less _bloat_ regularly that they need frequent vacuuming. Maybe pg_shdepend, because it would be used when creating temp tables. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Postgres is bloatware by design: it was built to house PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: > > > is it possible it's related to the performance drop immediately > > following a vacuum analyze we've been seeing? > > I don't think so, unless you were counting on pgstats data of shared > tables for something. The optimizer, for one, doesn't, so I doubt it > would affect query planning. And it would only affect you if your > queries were using shared tables, which I very much doubt ... Does anything use the pgstats data for anything other than presenting feedback to users? Autovacuum uses it to estimate when tables should be vacuumed right? This wouldn't have caused autovacuum to go nuts vacuuming these tables would it? But I doubt even then that it could consume much i/o bandwidth. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
Gregory Stark wrote: > > "Alvaro Herrera" <[EMAIL PROTECTED]> writes: > > > Log Message: > > --- > > Avoid losing track of data for shared tables in pgstats. Report by Michael > > Fuhr, patch from Tom Lane after a messier suggestion by me. > > When does this bug date to? It was in 8.1. I didn't verify whether it affects on 8.0; I think we separated the shared tables in pgstats in 8.1, with the advent of autovacuum, so I assume it doesn't. The patch doesn't apply cleanly to 8.0 anyway, and I decided not to spent much time on it seeing that nobody has noticed it in years. > is it possible it's related to the performance drop immediately > following a vacuum analyze we've been seeing? I don't think so, unless you were counting on pgstats data of shared tables for something. The optimizer, for one, doesn't, so I doubt it would affect query planning. And it would only affect you if your queries were using shared tables, which I very much doubt ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.
"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > Log Message: > --- > Avoid losing track of data for shared tables in pgstats. Report by Michael > Fuhr, patch from Tom Lane after a messier suggestion by me. When does this bug date to? is it possible it's related to the performance drop immediately following a vacuum analyze we've been seeing? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq