Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb - Mailing list pgsql-hackers
| From | Jeff Janes |
|---|---|
| Subject | Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb |
| Date | |
| Msg-id | CAMkU=1zvVR3F-2X1Rc8AYzzU+qGWHDgR1KT-XqnNoMeuQVxOng@mail.gmail.com Whole thread Raw |
| In response to | Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb (Oskari Saarenmaa <os@ohmu.fi>) |
| Responses |
Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb
|
| List | pgsql-hackers |
On Tue, Jan 21, 2014 at 9:06 AM, Oskari Saarenmaa <os@ohmu.fi> wrote:
09.01.2014 05:15, Peter Eisentraut kirjoitti:I think the three stage analyze is a wrong solution to the "slow analyze" problem.pg_upgrade creates a script analyze_new_cluster.{sh|bat} that runs
vacuumdb --analyze-only in three stages with different statistics target
settings to get a fresh cluster analyzed faster. I think this behavior
is also useful for clusters or databases freshly created by pg_restore
or any other loading mechanism, so it's suboptimal to have this
constrained to pg_upgrade.
It certainly is not the best possible solution. But it might be the best one that can be arrived at within a reasonable amount of time.
If we really want to micromanage the process we could put a lot of work into it. Take the case of a small table of about 300 pages. We read the table 3 times (separated by enough time that it is probably no longer cached), first keeping one tuple per page, then 10, then 100. Instead it should probably just jump directly to sampling at statistics target of 100 and then forget those small tables, at least if all we are concerned about is IO costs. (Since the selected tuples are sorted once for each column, there might be a CPU reason to take a small sample at the first pass, if we are more concerned with CPU than IO.)
But I do wonder what experience people have with the 3 stage process, how useful is it empirically? If you can't open the database for general use until the 3rd phase is done, then you would just jump to doing that stage, rather than working through all 3 of them. If you can open the database and muddle through without statistics for a while, why not muddle through for the little bit longer that it would take to collect the full set right off the bat, rather than making intermediate passes?
So I agree that the current system in not optimal. But this patch is just moving existing behavior from a less general location to a more general one, so I don't think it should be held hostage to improvements that could theoretically be made but which no one has offered to do. I wouldn't want to put in a change that forces users to learn something new for 9.4 only to have it completely redone in 9.5 and then make them learn that. But the documentation and training burden of this change seems small enough that I wouldn't worry about that. (On the other hand, the benefit of the change also seems pretty small.)
In my experience most of the analyze time goes to reading random blocks from the disk but we usually use only a small portion of that data (1 row per block.)
If we were able to better utilize the data we read we could get good statistics with a lot less IO than we currently need. This was discussed in length at
http://www.postgresql.org/message-id/CAM-w4HOjRbNPMW=SHjHw_Qfapcuu5Ege1tMdR0ZQU+kqX8Qeug@mail.gmail.com but it hasn't turned into patches so far.
I don't think it is an accident that it hasn't turned into patches. You can't change the laws of statistics just by wanting it badly enough. Our current sampling method is already insufficiently random. We aren't going to fix things by making it even less random. But I guess that that is an empirical question again, have most statistics problems been due to the sample being insufficiently large, or insufficiently random? (Or insufficiently recent?)
There could be a different stage-by-stage approach where default_statistics_target is fixed but in the first pass you just take the first 30,000 rows in each table, and then in second pass you take a random 30,000 rows. But the knobs to do that currently do not exist, and I doubt they would be welcomed if their only use is to support pg_upgrade. So that idea is not a blocker to this patch, either.
Cheers,
Jeff
pgsql-hackers by date: