pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Stats Disappear After Minor Upgrade? 3+ messages / 2 participants [nested] [flat]
* Stats Disappear After Minor Upgrade? @ 2017-08-15 21:49 Don Seiler <don@seiler.us> 0 siblings, 1 reply; 3+ messages in thread From: Don Seiler @ 2017-08-15 21:49 UTC (permalink / raw) To: pgsql-admin Me again. I'm testing an upgrade of a database from 9.2.10 to 9.2.22. The upgrade seemingly had no problems and I started the DB. However when I went to look at some vacuum-related status, every table had n_dead_tup and n_live_tup = 0. For what it's worth, this DB was a clone of a prod standby. I didn't think to check the stats after opening the new clone (renamed the recovery.conf), so I can't say for sure what it looked like prior to upgrade. I've kicked off a DB-wide analyze but obviously that takes a long time and more or less would mean prod app downtime as the optimizer would make all sorts of goofy decisions until the stats are in place. I just wanted to ask here if anything like this is known. I know 10->22 is a bit of a leap but it's still the same major branch so I didn't expect any trouble. My other tests in small sandboxes were from 9.2.18 or .21 to 9.2.22 and had no problems like this. Don. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stats Disappear After Minor Upgrade? @ 2017-08-15 22:50 Tom Lane <tgl@sss.pgh.pa.us> parent: Don Seiler <don@seiler.us> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2017-08-15 22:50 UTC (permalink / raw) To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin Don Seiler <don@seiler.us> writes: > I'm testing an upgrade of a database from 9.2.10 to 9.2.22. The upgrade > seemingly had no problems and I started the DB. However when I went to look > at some vacuum-related status, every table had n_dead_tup and n_live_tup = > 0. How did you shut down the existing installation? This'd be expected if you used "--mode immediate". Otherwise, I'd have thought those stats would still be there. > For what it's worth, this DB was a clone of a prod standby. I didn't think > to check the stats after opening the new clone (renamed the recovery.conf), > so I can't say for sure what it looked like prior to upgrade. I've kicked > off a DB-wide analyze but obviously that takes a long time and more or less > would mean prod app downtime as the optimizer would make all sorts of goofy > decisions until the stats are in place. The optimizer's decisions are generally not based on the stats collector's info, but on the contents of pg_statistic. This observation doesn't prove anything about whether that was intact ... but I'd be really surprised if it wasn't. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stats Disappear After Minor Upgrade? @ 2017-08-16 01:50 Don Seiler <don@seiler.us> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Don Seiler @ 2017-08-16 01:50 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin On Tue, Aug 15, 2017 at 5:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > How did you shut down the existing installation? This'd be expected if > you used "--mode immediate". Otherwise, I'd have thought those stats > would still be there. > pg_ctl stop -m fast That's how I've been doing it this whole while. My connotation is that postgres "fast" shutdown equates to oracle's immediate shutdown, but postgres "immediate" shutdown is akin to oracle's "shutdown abort". So fast should be a safe, consistent shutdown that doesn't require crash recovery after. The optimizer's decisions are generally not based on the stats collector's > info, but on the contents of pg_statistic. This observation doesn't prove > anything about whether that was intact ... but I'd be really surprised > if it wasn't. Interesting, I'll have to read up on the difference here. I'm going to see if I can get another clone made up and test again, being sure to get the info before and after. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-08-16 01:50 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-08-15 21:49 Stats Disappear After Minor Upgrade? Don Seiler <don@seiler.us> 2017-08-15 22:50 ` Tom Lane <tgl@sss.pgh.pa.us> 2017-08-16 01:50 ` Don Seiler <don@seiler.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox