Bug #112808 Please build MySQL with the hypergraph optimizer enabled but configured off
Submitted: 24 Oct 2023 6:44 Modified: 4 Nov 2023 9:44
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: flexibility, hypergraph, improvements, new features, optimiser

[24 Oct 2023 6:44] Simon Mudd
Description:
I notice from https://github.com/mysql/mysql-server/blob/trunk/CMakeLists.txt#L2119-L2128 that there is a new hypervisor optimizer and yet it's not configured to be built into the server or available by default.

I believe this optimizer is partly being used and it may not be good at solving all queries but may be better at handling more complex analytical queries so it would be interesting to try it out and see how it behaves and if there are use cases where it is worth using now.

Right now this is turned off on normal builds provided by Oracle and debug builds are much slower so there's no room for experimentation or for us to see how well this works and where it may be an improvement on the current optimiser.

I would like to be able to enable it on certain queries and compare behaviour so that where it works better I can use it instead of the current optimiser.  If there are cases where performance is better I can then adjust my workloads to use the better optimiser for that.

I assume that the optimiser team is aware of the good/bad parts of the code and maybe it's working on trying to make the optimiser better for all code paths but even before that's possible it may be interesting for us to use it in its current state.

While it's possible to modify the build setup and compile MySQL to do this now it's also much easier to grab an existing packaged binary and just change the configuration at run time.  Allowing for this would be much more convenient.

How to repeat:
Check the source code (in 8.0.34) as indicated above. I believe 8.1 is built the same way.

Suggested fix:
1. Modify the build configuration to build MySQL with the hypergraph optimizer enabled, but not used by default and provide information on how to enable it.

2. Upgrade documentation to talk about more openly about the new optimiser. It's fine to say this is work in progress which is the reason it's not enabled by default but providing some examples of queries it handles better would show that this is interesting.

3. Clarify the best way to use the different optimisers. I believe this can be done via changing optimizer_switch settings, e.g hypergraph_optimizer=off/on but if used more dynamically I imagine a query hint would also be very useful to allow it to be used for specific queries known to work better.
[24 Oct 2023 7:06] Simon Mudd
To be clear I'd like to see the build options have:

SET(WITH_HYPERGRAPH_OPTIMIZER_DEFAULT ON)

but maintain (the default usage) so

optimizer_switch='….hypergraph_optimizer=off,….' is maintained for now.

e.g. MySQL behaviour does not change by default, but I can experiment with the optimizer.
[24 Oct 2023 9:44] MySQL Verification Team
Hi Mr. Mudd,

Thank you for your feature request report.

We have analysed both your report, source code and building system.

We agree that this is a perfect feature request.

Verified as reported for 8.0 and 8.1.
[4 Nov 2023 9:44] Simon Mudd
Until there is a patch to do this you can now do it indirectly rebuilding RHEL8/9 rpms with this built in using this:

https://github.com/sjmudd/mysql-rpm-builder

and using docker

$ git clone https://github.com/sjmudd/mysql-rpm-builder.git
$ cd mysql-rpm-rebuilder
$ docker run --rm -it --network=host --hostname=mysql-builder -v $PWD:/data oraclelinux:8 /data/build -a 8.2.0.hyp

Which will:
1. setup the container for building
2. pull down the 8.2.0 src rpm from Oracle
3. rebuild it with the hypergraph functionality enabled.

I've not actually done this for 8.0 but the same patch or a very similar one would be expected to work.

However, this is a "hack", having the option built-in by default (but disable by default is fine) would be really good.
[6 Nov 2023 11:13] MySQL Verification Team
Thank you, Mr. Mudd,

Your suggestion has been forwarded to the relevant Development team.