Re: Add n_tup_newpage_upd to pg_stat table views

Hi,

On 2023-01-27 18:23:39 -0500, Corey Huinker wrote:
> This patch adds the n_tup_newpage_upd to all the table stat views.
>
> Just as we currently track HOT updates, it should be beneficial to track
> updates where the new tuple cannot fit on the existing page and must go to
> a different one.

I like that idea.

I wonder if it's quite detailed enough - we can be forced to do out-of-page
updates because we actually are out of space, or because we reach the max
number of line pointers we allow in a page. HOT pruning can't remove dead line
pointers, so that doesn't necessarily help.

Which e.g. means that:

> Hopefully this can give users some insight as to whether their current
> fillfactor settings need to be adjusted.

Isn't that easy, because you can have a page with just a visible single tuple
on, but still be unable to do a same-page update. The fix instead is to VACUUM
(more aggressively).

OTOH, just seeing that there's high percentage "out-of-page updates" provides
more information than we have right now. And the alternative would be to add
yet another counter.

Similarly, it's a bit sad that we can't distinguish between the number of
potential-HOT out-of-page updates and the other out-of-page updates. But
that'd mean even more counters.

I guess we could try to add tracepoints to allow to distinguish between those
cases instead? Not a lot of people use those though.

> @@ -372,8 +372,11 @@ pgstat_count_heap_update(Relation rel, bool hot)
> pgstat_info->trans->tuples_updated++;
>
> /* t_tuples_hot_updated is nontransactional, so just advance it */
> - if (hot)
> + if (hut == PGSTAT_HEAPUPDATE_HOT)
> pgstat_info->t_counts.t_tuples_hot_updated++;
> + else if (hut == PGSTAT_HEAPUPDATE_NEW_PAGE)
> + pgstat_info->t_counts.t_tuples_newpage_updated++;
> +
> }
> }
>

I think this might cause some trouble for existing monitoring setups after an
upgrade. Suddenly the number of updates will appear way lower than
before... And if we end up eventually distinguishing between different reasons
for out-of-page updates, or hot/non-hot out-of-page that'll happen again.

I wish we'd included HOT updates in the total number of updates, and just kept
HOT updates a separate counter that'd always be less than updates in total.

From that angle: Perhaps it'd be better to have counter for how many times a
page is found to be full during an update?

> --- a/src/backend/access/heap/heapam.c
> +++ b/src/backend/access/heap/heapam.c
> @@ -3155,7 +3155,8 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
> pagefree;
> bool have_tuple_lock = false;
> bool iscombo;
> - bool use_hot_update = false;
> + PgStat_HeapUpdateType update_type = PGSTAT_HEAPUPDATE_NON_HOT;
> +
> bool key_intact;
> bool all_visible_cleared = false;
> bool all_visible_cleared_new = false;
> @@ -3838,10 +3839,11 @@ l2:
> * changed.
> */
> if (!bms_overlap(modified_attrs, hot_attrs))
> - use_hot_update = true;
> + update_type = PGSTAT_HEAPUPDATE_HOT;
> }
> else
> {
> + update_type = PGSTAT_HEAPUPDATE_NEW_PAGE;
> /* Set a hint that the old page could use prune/defrag */
> PageSetFull(page);
> }
> @@ -3875,7 +3877,7 @@ l2:
> */
> PageSetPrunable(page, xid);
>
> - if (use_hot_update)
> + if (update_type == PGSTAT_HEAPUPDATE_HOT)

It's a bit weird that heap_update() uses a pgstat type to decide what to
do. But not sure there's a much better alternative.

Greetings,

Andres Freund