Thread: analyze after a database restore?
I just dumped and restored a rather large database, I upgraded from 7.2.x to 7.3.x. When I went to test my application against the new database, it was dog slow. It had all the indexes, and looked fine. Then it dawned on me, Doh! ANALYZE! Should pg_dump appened an ANALYZE for each table? On small tables, this shouldn't take too long. On large tables, you're gonna have to do it anyway. I guess it could be an option as well. It just seems like on of the tasks that is required for a "restored" database to work properly, and as such, should probably be specified in the backup procedure.
mlw <pgsql@mohawksoft.com> writes:
> Should pg_dump appened an ANALYZE for each table?
A single ANALYZE at the end of the script would be sufficient. I'm not
sure that pg_dump should do this automatically though. If you're not
done restoring then it's mostly a waste of cycles, and how is pg_dump to
know that?
I do note that the docs are rather stingy with this important bit of
knowhow :-( Neither of the obvious places that I looked in (pg_dump
reference page and admin guide's backup/restore chapter) mention the
need to issue an ANALYZE after completing a restore. I'm pretty sure it
is mentioned *somewhere* ;-) ... but it needs to be more prominent.
regards, tom lane
On Thu, 2003-02-27 at 12:27, mlw wrote: > I just dumped and restored a rather large database, I upgraded from > 7.2.x to 7.3.x. When I went to test my application against the new > database, it was dog slow. It had all the indexes, and looked fine. > > Then it dawned on me, Doh! ANALYZE! > > Should pg_dump appened an ANALYZE for each table? > > On small tables, this shouldn't take too long. On large tables, you're > gonna have to do it anyway. I guess it could be an option as well. > > It just seems like on of the tasks that is required for a "restored" > database to work properly, and as such, should probably be specified in > the backup procedure. It's been debated before whether pg_dump should append ANALYZE to the end of the load script. It has always been determined that it shouldn't (see archives for arguments). However, an 'Auto-vacuum' process should watch stats and re-analyze the table when the larger of 30% or 1000 rows has been affected (inserts, or deletes mostly). That is probably a better solution overall. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Tom Lane wrote: >mlw <pgsql@mohawksoft.com> writes: > > >>Should pg_dump appened an ANALYZE for each table? >> >> > >A single ANALYZE at the end of the script would be sufficient. I'm not >sure that pg_dump should do this automatically though. If you're not >done restoring then it's mostly a waste of cycles, and how is pg_dump to >know that? > >I do note that the docs are rather stingy with this important bit of >knowhow :-( Neither of the obvious places that I looked in (pg_dump >reference page and admin guide's backup/restore chapter) mention the >need to issue an ANALYZE after completing a restore. I'm pretty sure it >is mentioned *somewhere* ;-) ... but it needs to be more prominent. > > While these are all comforting points, I *know* about analyze and I occasionally forget. It just seems like a nessisary step after restoring a backup. Conceptually, one could consider it just as important as an index, i.e. the system will perform poorly without it. From an "ease of use" perspective, it would be one less step.
On Thursday 27 February 2003 13:12, mlw wrote:
> Tom Lane wrote:
> >A single ANALYZE at the end of the script would be sufficient. I'm not
> >sure that pg_dump should do this automatically though. If you're not
> >done restoring then it's mostly a waste of cycles, and how is pg_dump to
> >know that?
> [...]
> From an "ease of use" perspective, it would be one less step.
Why not have pg_dump emit a friendly reminder?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
D'Arcy J.M. Cain wrote: >On Thursday 27 February 2003 13:12, mlw wrote: > > >>Tom Lane wrote: >> >> >>>A single ANALYZE at the end of the script would be sufficient. I'm not >>>sure that pg_dump should do this automatically though. If you're not >>>done restoring then it's mostly a waste of cycles, and how is pg_dump to >>>know that? >>> >>> >>[...] >> From an "ease of use" perspective, it would be one less step. >> >> > >Why not have pg_dump emit a friendly reminder? > > > The reminder won't work, because the backup may be happening in an automatic fashion, and anything but error messages will be lost. I dislike having to have an "expert" be present at the database "restore" phase of operation. Suppose a company loses the PG admin and a reasonably experienced person takes his or her place temporarily, This scenario happens all the time with all sorts of projects. A reasonably experienced person will be able to accomplish a DB restore but will probably not know about performing an analyze. Under the pressure of restoring after a crash on a live system, even a reasonably experienced PG admin may forget, hell I forgot and I've been using PG since 1997. The "correct" view of a database backup should be to include the statistics of the database as it existed at the time backup, these statistics are part of this state "snapshot" because the directly affect the operation of the database. I do not want to evoke the name of Larry's evil product, but it saves its statistics when the data is exported. Short of including the relevant statistics, there should be an option on pg_dump to emit an "ANALYZE;" at the end of a database dump. This will allow a "knowledgeable" admin to selectively add the vacuum so that someone possibly less qualified than he can do the restore. Does anyone disagree that a query's "explain" should look the same or better after a successful restore? From a product QA point of view, if a valid backup set, when restored, does not recreate the system in a state at least as efficient and workable as the system when it was backed up, you did not have a successful restore. Any QA department would rate this as a serious bug. Are there any reasons why it should not be an option on pg_dump?
mlw <pgsql@mohawksoft.com> writes:
> From an "ease of use" perspective, it would be one less step.
There is something to be said for that. As Rod notes, this has been
considered and rejected before --- but I think that was back when
ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on
scanning the whole table. The current implementation is vastly
lighter-weight than what we were looking at back then. Perhaps it's
time to reconsider.
Although I suggested doing a single unconditional ANALYZE at the end
of the script, second thought leads me to think the per-table ANALYZE
(probably issued right after the table's data-load step) might be
better. That way you'd not have any side-effects on already-existing
tables in the database you are loading to. OTOH, that way would leave
the system catalogs un-analyzed, which might be bad.
regards, tom lane
On Thu, Feb 27, 2003 at 02:45:46PM -0500, mlw wrote: > > Are there any reasons why it should not be an option on pg_dump? I wonder whether that mightn't be the best answer. Maybe it should even be the default, and --noanalyse an option. I agree that from the point of view of simplifying administration, it'd be a nice addition. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
On Thu, Feb 27, 2003 at 03:12:36PM -0500, Tom Lane wrote: > Although I suggested doing a single unconditional ANALYZE at the end > of the script, second thought leads me to think the per-table ANALYZE > (probably issued right after the table's data-load step) might be > better. That way you'd not have any side-effects on already-existing > tables in the database you are loading to. OTOH, that way would leave > the system catalogs un-analyzed, which might be bad. Huh... is there a way to analyze a specific schema? Maybe that can solve the problem of system catalogs being left un-analyzed, by having pg_dump emit an "ANALYZE SCHEMA pg_catalog" or something. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)
Tom Lane wrote: > mlw <pgsql@mohawksoft.com> writes: > >> From an "ease of use" perspective, it would be one less step. > > > There is something to be said for that. As Rod notes, this has been > considered and rejected before --- but I think that was back when > ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on > scanning the whole table. The current implementation is vastly > lighter-weight than what we were looking at back then. Perhaps it's > time to reconsider. > > Although I suggested doing a single unconditional ANALYZE at the end > of the script, second thought leads me to think the per-table ANALYZE > (probably issued right after the table's data-load step) might be > better. That way you'd not have any side-effects on already-existing > tables in the database you are loading to. OTOH, that way would leave > the system catalogs un-analyzed, which might be bad. Analyzing the impact a bit further, which has the more positives/negatives? o Adding an ANALYZE (per table or not) + Restoring from backup and getting decent performance is more reliably, especially for people new to PostgreSQL. + Reduces the number of things an admin has to remember or checklist - Could be redundant if the restore is part of a larger group of restores - Adds a bit more time to the restoration process (not much through) o Leaving it the way it is (no ANALYZE included) + Stops the potential problem of it being redundant if the restore is part of a larger group of restores - The SysAdmin/DBA has to know/remember to do this. Especially bad for people new to PostgreSQL It seems like the maximum benefits would be on the side of including it, and the only real negative from including an ANALYZE would be in the extra time taken for it. Of course we may be forgetting Other Big Consequences. ;-) As a thought, we could introduce an option to explicitly not include it, but that doesn't feel very worthwhile. Regards and best wishes, Justin Clift > regards, tom lane -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
> Although I suggested doing a single unconditional ANALYZE at the end > of the script, second thought leads me to think the per-table ANALYZE > (probably issued right after the table's data-load step) might be > better. That way you'd not have any side-effects on already-existing > tables in the database you are loading to. OTOH, that way would leave > the system catalogs un-analyzed, which might be bad. How about adding an ANALYZE SCHEMA pg_catalog; option :) Chris
Attached is a committed patch to add a recommendation for ANALYZE after restore. It is a shame we only have vacuumdb -a to do analyze _and_ vacuum, and no analyze-only option. --------------------------------------------------------------------------- Tom Lane wrote: > mlw <pgsql@mohawksoft.com> writes: > > From an "ease of use" perspective, it would be one less step. > > There is something to be said for that. As Rod notes, this has been > considered and rejected before --- but I think that was back when > ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on > scanning the whole table. The current implementation is vastly > lighter-weight than what we were looking at back then. Perhaps it's > time to reconsider. > > Although I suggested doing a single unconditional ANALYZE at the end > of the script, second thought leads me to think the per-table ANALYZE > (probably issued right after the table's data-load step) might be > better. That way you'd not have any side-effects on already-existing > tables in the database you are loading to. OTOH, that way would leave > the system catalogs un-analyzed, which might be bad. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v retrieving revision 2.24 diff -c -c -r2.24 backup.sgml *** doc/src/sgml/backup.sgml 11 Nov 2002 20:14:02 -0000 2.24 --- doc/src/sgml/backup.sgml 17 Mar 2003 23:58:37 -0000 *************** *** 126,131 **** --- 126,138 ---- </para> <para> + Once restored, it is wise to run <command>ANALYZE</> on each + database so the optimizer has useful statistics. You + can also run <command>vacuumdb -a -z</> to <command>ANALYZE</> all + databases. + </para> + + <para> The ability of <application>pg_dump</> and <application>psql</> to write to or read from pipes makes it possible to dump a database directly from one server to another, for example Index: doc/src/sgml/ref/pg_dump.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.56 diff -c -c -r1.56 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 13 Feb 2003 04:54:15 -0000 1.56 --- doc/src/sgml/ref/pg_dump.sgml 17 Mar 2003 23:58:38 -0000 *************** *** 650,655 **** --- 650,660 ---- </programlisting> </para> + <para> + Once restored, it is wise to run <command>ANALYZE</> on each + restored object so the optimizer has useful statistics. + </para> + <para> <application>pg_dump</application> has a few limitations: *************** *** 682,687 **** --- 687,698 ---- other output formats is not limited, except possibly by the operating system. </para> + + <para> + Once restored, it is wise to run <command>ANALYZE</> on each + restored object so the optimizer has useful statistics. + </para> + </refsect1> <refsect1 id="pg-dump-examples"> Index: doc/src/sgml/ref/pg_dumpall.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dumpall.sgml,v retrieving revision 1.36 diff -c -c -r1.36 pg_dumpall.sgml *** doc/src/sgml/ref/pg_dumpall.sgml 6 Jan 2003 18:53:24 -0000 1.36 --- doc/src/sgml/ref/pg_dumpall.sgml 17 Mar 2003 23:58:38 -0000 *************** *** 258,267 **** <application>pg_dumpall</application> will need to connect several times to the <productname>PostgreSQL</productname> server. If password authentication is configured, it will ask for a password each time. In ! that case it would be convenient to set up a password file. </para> - <comment>But where is that password file documented?</comment> </refsect1> --- 258,274 ---- <application>pg_dumpall</application> will need to connect several times to the <productname>PostgreSQL</productname> server. If password authentication is configured, it will ask for a password each time. In ! that case it would be convenient to set up a <filename>.pgpass</> ! password file. ! </para> ! ! <para> ! Once restored, it is wise to run <command>ANALYZE</> on each ! database so the optimizer has useful statistics. You ! can also run <command>vacuumdb -a -z</> to <command>ANALYZE</> all ! databases. </para> </refsect1> Index: doc/src/sgml/ref/pg_restore.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.35 diff -c -c -r1.35 pg_restore.sgml *** doc/src/sgml/ref/pg_restore.sgml 19 Jan 2003 00:13:31 -0000 1.35 --- doc/src/sgml/ref/pg_restore.sgml 17 Mar 2003 23:58:47 -0000 *************** *** 589,594 **** --- 589,600 ---- See also the <xref linkend="app-pgdump"> documentation for details on limitations of <application>pg_dump</application>. </para> + + <para> + Once restored, it is wise to run <command>ANALYZE</> on each + restored object so the optimizer has useful statistics. + </para> + </refsect1>
Bruce Momjian writes: > Attached is a committed patch to add a recommendation for ANALYZE after > restore. It is a shame we only have vacuumdb -a to do analyze _and_ > vacuum, and no analyze-only option. Isn't one copy of the text per reference page enough? -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Bruce Momjian writes: > > > Attached is a committed patch to add a recommendation for ANALYZE after > > restore. It is a shame we only have vacuumdb -a to do analyze _and_ > > vacuum, and no analyze-only option. > > Isn't one copy of the text per reference page enough? Thanks. Fixed. There were two in pg_dump by accident. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073