[HACKERS] row-level stats and last analyze time
[ CC'ing -hackers ] On Sun, 2007-04-22 at 16:10 +0200, Guillaume Lelarge wrote: This patch adds a sentence on monitoring.sgml explaining that stats_row_level needs to be enabled if user wants to get last vacuum/analyze execution time. This behavior was introduced in r1.120 of postmaster/pgstat.c: Modify pgstats code to reduce performance penalties from oversized stats data files: avoid creating stats hashtable entries for tables that aren't being touched except by vacuum/analyze [...] which included other modifications to reduce the pgstat I/O volume in 8.1. I don't think this particular change was wise: the reduction in pgstat volume is pretty marginal, and it is counter-intuitive for stats_row_level to effect whether the last ANALYZE / VACUUM is recorded. (Plus, the optimization is not even enabled with the default postgresql.conf settings.) -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Row level stats
On Thu, 2003-03-06 at 01:13, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: [ optimizing for small frequent queries ] What if the client doesn't come back with another query for awhile? Yup.. Thats why I said you basically lose 1 seconds worth of stats (or rather can't count on when they arrive). But, that doesn't matter for most cases as you cannot cause a lot of trouble with 1 seconds worth of activity. That said, I'd make the actual value a GUC. However, having the numbers off by a little (the %age they're off by decreases with increased activity) is still a lot more useful than not having any numbers at all. Anyway, the basic proposal would be to change the on / off flags for row block level into a 'stats delay'. A large number would be off, 0 would have the same properties as today. Default to be determined (but 1 second seems to be plenty). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Row level stats
On Thu, 2003-03-06 at 11:40, Neil Conway wrote: On Thu, 2003-03-06 at 09:16, Rod Taylor wrote: Anyway, the basic proposal would be to change the on / off flags for row block level into a 'stats delay'. A large number would be off, 0 would have the same properties as today. Default to be determined (but 1 second seems to be plenty). A large number seems messy. Why not -1? I also don't really like using 0 to mean enabled, but I can't think of anything better... And the default should be to disable row-level stats, naturally. Not if we expect pg_autovacuum to be able to work. It *needs* this information. I really want an auto-vacuum in the backend, and figured the first step was to fix stats so they don't have such a high cost. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Row level stats
On Thu, 2003-03-06 at 09:16, Rod Taylor wrote: Anyway, the basic proposal would be to change the on / off flags for row block level into a 'stats delay'. A large number would be off, 0 would have the same properties as today. Default to be determined (but 1 second seems to be plenty). A large number seems messy. Why not -1? I also don't really like using 0 to mean enabled, but I can't think of anything better... And the default should be to disable row-level stats, naturally. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Row level stats
On Thu, 2003-03-06 at 11:43, Rod Taylor wrote: Not if we expect pg_autovacuum to be able to work [ by default ] I don't regard that as a very high priority at this stage of the game: since I think pg_avd should be in contrib/ for the next release at the very least, I don't think anyone capable of installing a contrib/ module will gripe about tweaking a GUC parameter. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Row level stats
Rod Taylor [EMAIL PROTECTED] writes: Not if we expect pg_autovacuum to be able to work. It *needs* this information. Why? The implementation I've had in mind for autovacuum would rely on FSM not the pg_stats daemon. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Row level stats
On Thu, 2003-03-06 at 11:49, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Not if we expect pg_autovacuum to be able to work. It *needs* this information. Why? The implementation I've had in mind for autovacuum would rely on FSM not the pg_stats daemon. FSM as I understand it gets it information from vacuum, and has an space marker removed when an insert uses the tuple. Determining when vacuum runs would be based more on Deletes / updates than anything wouldn't it? Anyhow, I'd be curious to know your full thoughts on autovacuum and how it may work. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] Row level stats
It would be nice if PGAvd could receive row level stats without a large
hit to simple queries.
Ran a simple test. Calling pgstat_report_tabstat() at a frequency of
once per second reduces the time taken for row level stats to be
negligible:
500k select TRUE statements took:
6:50 with stats off
8:35 with row level stats on
6:52 with the below applied and row level stats on
Anyone object to delaying the submission of stats by that timeframe?
Are row level stats enough for PGAvd?
More or less the below for the change on line 1912 in postgres.c
currtme = GetCurrentAbsoluteTime();
if (lasttme currtme)
{
pgstat_report_tabstat();
lasttme = currtme;
}
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Row level stats
Rod Taylor [EMAIL PROTECTED] writes: [ optimizing for small frequent queries ] What if the client doesn't come back with another query for awhile? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
