Thread: When was ANALYZE run in the past?
On https://wiki.postgresql.org/wiki/Slow_Counting I read that
SELECT reltuples FROM pg_class WHERE relname = 'tbl';
may be a good way to get an estimate of the # of rows in the table, but depends on how frequently ANALYZE has been running. I run autovacuum under a default configuration, but I suspect ANALYZE is not running frequently enough for my purposes (when I ran the above command on my table, it consistently returned 1.4 million for ~20 minutes straight; when I explicitly ran an ANALYZE command at that point (when I realized the estimate was not updating even every few minutes), the ANALYZE command took a few seconds, then the above command returned .7 million which matches what ‘select count(*)’ was returning). So I suspect ANALYZE is not running frequently enough or is stepping over my table for some reason.
So, given the above context, my question is, is there any way to tell at what times ANALYZE has been run in the past on the db or on a particular table? I am running a fairly vanilla postgres 8.4 db on linux, with a few minor tweaks to postgresql.conf:
synchronous_commit = off
log_line_prefix = '%t '
log_min_messages = info
I don’t see anything in the stderr of the db other than the following:
2014-11-26 20:01:55 GMT LOG: database system was shut down at 2014-11-26 18:39:41 GMT
2014-11-26 20:01:55 GMT LOG: database system is ready to accept connections
2014-11-26 20:01:55 GMT LOG: autovacuum launcher started
Thanks!
--
Benjamin Rutt
On Sat, 29 Nov 2014 15:27:07 -0500 Benjamin Rutt <rutt.4@osu.edu> wrote: > On *https://wiki.postgresql.org/wiki/Slow_Counting* > <https://wiki.postgresql.org/wiki/Slow_Counting> I read that > > SELECT reltuples FROM pg_class WHERE relname = 'tbl'; > > may be a good way to get an estimate of the # of rows in the table, but > depends on how frequently ANALYZE has been running. I run autovacuum under > a default configuration, but I suspect ANALYZE is not running frequently > enough for my purposes (when I ran the above command on my table, it > consistently returned 1.4 million for ~20 minutes straight; when I > explicitly ran an ANALYZE command at that point (when I realized the > estimate was not updating even every few minutes), the ANALYZE command took > a few seconds, then the above command returned .7 million which matches > what ?select count(*)? was returning). So I suspect ANALYZE is not running > frequently enough or is stepping over my table for some reason. > > So, given the above context, my question is, is there any way to tell at > what times ANALYZE has been run in the past on the db or on a particular > table? I am running a fairly vanilla postgres 8.4 db on linux, with a few > minor tweaks to postgresql.conf: I would tell you to look at the pg_stat_all_tables view, but I'm not even sure if the ancient, unsupported 8.4 version of PostgreSQL had that table. -- Bill Moran I need your help to succeed: http://gamesbybill.com
On Sat, 29 Nov 2014 15:27:07 -0500 Benjamin Rutt <rutt.4@osu.edu> wrote: >I run autovacuum under > a default configuration, but I suspect ANALYZE is not running frequently > enough for my purposes (when I ran the above command on my table, it > consistently returned 1.4 million for ~20 minutes straight; when I > explicitly ran an ANALYZE command at that point (when I realized the > estimate was not updating even every few minutes), the ANALYZE command took > a few seconds, then the above command returned .7 million which matches > what ‘select count(*)’ was returning). So I suspect ANALYZE is not running > frequently enough or is stepping over my table for some reason. > Analyze is run by the autovacuum daemon, but you may be hitting the Cost-based Vacuum Delay if you deleted .7 million rows? The documentation for postgresql 8.4 is no longer online, and things changed significantly since, so I'm not sure this appliesexactly, but you can set table specific settings for the auto-vacuuming process; see : http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM and http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
On 11/30/2014 06:55 AM, Vincent Veyron wrote: > On Sat, 29 Nov 2014 15:27:07 -0500 > Benjamin Rutt <rutt.4@osu.edu> wrote: > >> I run autovacuum under >> a default configuration, but I suspect ANALYZE is not running frequently >> enough for my purposes (when I ran the above command on my table, it >> consistently returned 1.4 million for ~20 minutes straight; when I >> explicitly ran an ANALYZE command at that point (when I realized the >> estimate was not updating even every few minutes), the ANALYZE command took >> a few seconds, then the above command returned .7 million which matches >> what ‘select count(*)’ was returning). So I suspect ANALYZE is not running >> frequently enough or is stepping over my table for some reason. >> > > Analyze is run by the autovacuum daemon, but you may be hitting the Cost-based Vacuum Delay if you deleted .7 million rows? > > The documentation for postgresql 8.4 is no longer online, and things changed significantly since, so I'm not sure thisapplies exactly, but you can set table specific settings for the auto-vacuuming process; see : > > http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM FYI the docs are still online. So for example, if you go to the above page and look at the top of the page you will see links for documentation going back to 7.2 and forward to devel. Clicking on the link will take you to the equivalent page in the respective version. This is one of the many gems of the Postgres docs. > > and > > http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > -- Adrian Klaver adrian.klaver@aklaver.com