RFC: use PGO to optimize release builds

[derekperkins]

Feature Description

There's a good write up of using PGO in a database, TL;DR: ~5% improvements with minimal effort https://www.dolthub.com/blog/2024-02-02-profile-guided-optimization/

While not a perfect candidate for representing production workloads, if we could collect profiles from benchmarking once per major release, it seems likely we could get decent gains almost for free. Alternatively, if Planetscale or someone else collected profiles in production, that might even be better.

I think we'd want to collect separate profiles per component, mainly vttablet and vtgate.

Use Case(s)

General performance improvements

[frouioui]

It does sound promising indeed, I wonder what the numbers would be with Vitess. We would have to try this out before implementing it in our release builds.

Regarding the collection of profiles, to keep the entire build secure and "inside a box" we could automatically generate the profiles in our release build workflow that way we avoid having a dynamic parameter in our build.

[derekperkins]

More details about their implementation. TL;DR: collect profiles during benchmarking. https://dolthub.com/blog/2024-04-19-golang-pgo-builds-using-github-actions/

This should fit pretty nicely into our existing infrastructure, given all the work that has gone into benchmark tooling.

[derekperkins]

This was just discussed in the monthly meeting. The question was asked if arewefastyet benchmarks would be representative enough, and if we risk making things slower

• paraphrased from @systay, "query planning is just string parsing, so I expect it to be beneficial"
• Should be close to risk-free, per the PGO docs “While a profile that is not representative of production behavior will result in optimizations in cold parts of the application, it should not make hot parts of the application slower” https://go.dev/doc/pgo

The first steps will be to collect any profile information and submit a PR with a default.pgo files next to vtgate and/or vttablet main.go, which should automatically be incorporated into the build.

The standard approach to building is to store a pprof CPU profile with filename default.pgo in the main package directory of the profiled binary. By default, go build will detect default.pgo files automatically and enable PGO.

Committing profiles directly in the source repository is recommended as profiles are an input to the build important for reproducible (and performant!) builds. Storing alongside the source simplifies the build experience as there are no additional steps to get the profile beyond fetching the source.

[systay]

@derekperkins I'm sorry if I left you with the impression that query planning is just string parsing.

What I meant was more that PGO could probably help a lot of things that are going to be common across most use cases - such as string parsing that will not much be different between query sets.

query planning is a super difficult and very hard, rocket-science level task 😅 just so my boss knows how necessary and complex it really is! ;)

FWIW - I think we should do this. It would surprise me very much if our performance didn't improve noticeably.

[derekperkins]

@systay I wasn't implying anything about the complexity of query planning, just agreeing with your comment that to PGO, it's lots of string parsing ;)

[derekperkins]

Cloudflare is reporting ~3.5% improvements across their fleet https://blog.cloudflare.com/reclaiming-cpu-for-free-with-pgo