sql: determine impact of sql.stats.automatic_collection.enabled cluster setting on sysbench oltp_read_write

[tbg]

See here.

Epic: CRDB-42584

Jira issue: CRDB-44818

[blathers-crl]

This issue has multiple T-eam labels. Please make sure it only has one, or else issue synchronization will not work correctly.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[michae2]

@tbg could you clarify whether this is about SQL table statistics, or observability statement statistics? They're both informally called "SQL stats" unfortunately, but they are not the same.

[michae2]

Oh, based on this thread, it seems to be the SQL statement stats owned by observability? Moving.

[ajstorm]

@tbg I opened #136324 for the specific issue of flushing SQL stats. Along with #135996, does that cover everything in this issue?

[tbg]

This issue came from turning off both sql.stats.automatic_collection and sql.stats.flush. Looks like you've got the latter covered in #136324. #135996 is about sql.metrics.statement_details.enabled. That means we're not yet covering sql.stats.automatic_collection. Your data here was inconclusive for this setting (it's unlikely that disabling it made anything slower, so I think we truly don't know the impact).

I think that setting has to do with full table scans that recreate table statistics, so it seems likely to have an impact:

cockroach/pkg/sql/stats/automatic_stats.go

Lines 581 to 597 in 27e5b9d

	// processing the current table longer than the refresh
	// interval, look up the table descriptor to ensure we don't
	// have stale table settings.
	if elapsed > DefaultRefreshInterval || refreshingAllTables {
	desc = r.getTableDescriptor(ctx, tableID)
	if desc != nil {
	if !r.autoStatsEnabled(desc) {
	continue
	}
	if settingOverrides == nil {
	settingOverrides = make(map[descpb.ID]catpb.AutoStatsSettings)
	}
	autoStatsSettings := desc.GetAutoStatsSettings()
	if autoStatsSettings == nil {
	delete(settingOverrides, tableID)
	} else {
	settingOverrides[tableID] = *autoStatsSettings

@mgartner could you chime in?

[ajstorm]

I just kicked off a run where I'm testing this again on top of the 4 cluster settings which we know improve performance and stability. I'm hoping that this will give a more reliable result. Will report back when I have that data.

[ajstorm]

In the repeated run, I'm still seeing a ~3% degradation with sql.stats.automatic_collection disabled. Is it possible that when we disable this setting, we're running with bad/no stats, and it's resulting in sub-optimal query plans?

[tbg]

Is it possible that when we disable this setting, we're running with bad/no stats, and it's resulting in sub-optimal query plans?

That could be possible. At the end of the runs with these settings, do we have any stats for these tables at all? When I disabled this, it was on a longer-running cluster. I think we do want stats, but measure the overhead of repeatedly recreating those stats if the underlying tables don't really change.

[xinhaoz]

Can I confirm that with the creation of the issues for flush and sql exec stats recording, this issue is only tracking the impact of sql.stats.automatic_collection.enabled? If so I'm going to transfer it to queries. Mistakenly assigned myself earlier since I thought it was around the obs issues (thanks for filing, Adam).

[yuzefovich]

[queries triage] if we understand correctly, right now it's unclear whether running auto stats has detrimental effect on the sysbench numbers overall, and as such we removed this from out project board. If there is detrimental effect, and Queries needs to look closer, please add it back to our project.

[michae2]

Note that the sql.stats.automatic_collection.max_fraction_idle cluster setting can be used to tune the throttling of automatic table statistics collection. Quoting from this slack conversation:

The throttling only comes into play when CPU usage > 25%. 0 = no throttling of stats collection, 1 = max throttling of stats collection. Default is 0.9.

[ajstorm]

I still need to dig further into this one, but my theory is that auto stats is actually helpful to these runs because we're not manually collecting stats before the run. That being said, histogram collection seems to harm the runs because (again a guess) the histogram stats aren't helpful for this workload.

Will need to do more digging on this in the new year.