Thread: Way to eliminate pg_dump activity from pg_stat_all ?
I've got a bunch of tables in a legacy database that I <i>know</i> are <i>never</i> used, and some more I'm not sure about. So I tried to identify and confirm with:<br /><blockquote><tt>select pg_stat_reset(); <br /> <br /> -- Wait along time<br /><br /> select * from pg_stat_all_tables where schemaname='public' order by seq_scan,seq_tup_read;<br /> selectgreatest(heap_blks_read,heap_blks_hit),relname from pg_statio_all_tables where schemaname='public' order by 1 desc;</tt><br /></blockquote> But I think I'm getting clutter from the nightly backups. Is there a way to keep pg_dump activityout of the statistics? I can think of several reasons to want such activity excluded, not just this one.<br />
I doubt it. From the server's perspective, pg_dump is just a client executing queries. If the db is never used, why are you continually backing it up? On Dec 16, 2008, at 12:55 PM, Bryce Nesbitt wrote: > I've got a bunch of tables in a legacy database that I know are > never used, and some more I'm not sure about. So I tried to > identify and confirm with: > select pg_stat_reset(); > > -- Wait a long time > > select * from pg_stat_all_tables where schemaname='public' order by > seq_scan,seq_tup_read; > select greatest(heap_blks_read,heap_blks_hit),relname from > pg_statio_all_tables where schemaname='public' order by 1 desc; > But I think I'm getting clutter from the nightly backups. Is there > a way to keep pg_dump activity out of the statistics? I can think of > several reasons to want such activity excluded, not just this one. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
It is not the db that's never used. Various tables in the db are never used. This is a database that's been continually added to and modified for ten years -- sometimes cruft accumulates. It would be nice to get pg_dump activity out of the stats for another reason --- pg_dump adds to the sequential scan activity, in a way that does not represent the "typical" use of the database. Essentially pg_dump is an atypical user of the database, so it's stats are "different" than the rest of the activity. Erik Jones wrote: > I doubt it. From the server's perspective, pg_dump is just a client > executing queries. If the db is never used, why are you continually > backing it up? > > On Dec 16, 2008, at 12:55 PM, Bryce Nesbitt wrote: > >> But I think I'm getting clutter from the nightly backups. Is there a >> way to keep pg_dump activity out of the statistics? I can think of >> several reasons to want such activity excluded, not just this one.
On Jan 20, 2009, at 11:27 AM, Bryce Nesbitt wrote: > It is not the db that's never used. Various tables in the db are > never used. This is a database that's been continually added to and > modified for ten years -- sometimes cruft accumulates. Understood. > It would be nice to get pg_dump activity out of the stats for > another reason --- pg_dump adds to the sequential scan activity, in > a way that does not represent the "typical" use of the database. > Essentially pg_dump is an atypical user of the database, so it's > stats are "different" than the rest of the activity. It sounds like the proper wording for a feature request here would be something like "Disable stats collection on a per-session basis". > Erik Jones wrote: >> I doubt it. From the server's perspective, pg_dump is just a >> client executing queries. If the db is never used, why are you >> continually backing it up? >> >> On Dec 16, 2008, at 12:55 PM, Bryce Nesbitt wrote: >> >>> But I think I'm getting clutter from the nightly backups. Is >>> there a way to keep pg_dump activity out of the statistics? I can >>> think of several reasons to want such activity excluded, not just >>> this one. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones wrote: >> It would be nice to get pg_dump activity out of the stats for another >> reason --- pg_dump adds to the sequential scan activity, in a way >> that does not represent the "typical" use of the database. >> Essentially pg_dump is an atypical user of the database, so it's >> stats are "different" than the rest of the activity. > > It sounds like the proper wording for a feature request here would be > something like "Disable stats collection on a per-session basis". And based on that, I found it's possible. I just had to teach pg_dump to do it: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01474.php (we'll see if anyone else finds it useful enough to commit the patch to postgres 8.5).