How to benchmark Switchable Optimizations in MySQL

Question

I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches.

I have already tried to use sysbench and MySQL official test-suite. But I fond that they are too general and mainly for functional testing, i.e., runing these tests with/without the optimization switches, the performance are almost identical. What I want is sth like a performance test suite.

Could you please suggest me some good tools/suites to fulfill my demands? Thanks!

Answer 1

(I realize that this does not "answer" the Question. But it is too long for a Comment.)

Good luck. re "Almost identical" -- Probably because they were "identical" except for random noise in the timing. The switchable optimizations are, in general, "binary" and "optional".

Consider an obvious situation: You have an index that could be used in a query. Still, the Optimizer will deliberately consider whether to ignore the index and simply scan the table. It may be more costly to bounce between the index and the table, hence using the index would slow down execution. This is a "binary" decision -- use vs ignore index. The Optimizer cannot know the exact cutoff between the choices. It is influenced by HDD vs SDD, buffer_pool_size, other activity, statistics, etc, etc.

That is, a benchmark would be complicated by having more than 2 choices (an index exists or not):

• FORCE INDEX vs IGNORE INDEX vs 'allowed' the Optimizer to choose
• for "allowed" the Optimizer picks the better choice vs fails to

Any of these could change whether the Optimizer would pick a particular optimization. Also, the timings would change by a relatively unpredictable amount in cases such as these:

• Adding more rows to a table
• Changing the size of a 'range'
• ANALYZE TABLE will sometimes lead to changing things significantly.
• OPTIMIZE TABLE -- ditto.

Over the years, I occasionally see "index merge intersect". (INDEX(a), INDEX(b) with WHERE a=1 AND b=2) In every case, a composite index would be better (INDEX(a,b)).

Change that AND to OR and no index is useful -- except when "index merge union" is used. This almost never happens. It takes two index range scans, a merge, and then something like a "join".

EXPLAIN does not show much info. EXPLAIN FORMAT=JSON is better. The Optimizer Trace shows a different set of info.

I don't know of a test set like what you seek. I see the writing of even one test to be quite a challenge (see above). Furthermore, interpreting the results would be challenging.

If you do succeed with your goal, please blog about it.

• Tim He: Thank you for the reply @James, it is very helpful! I think I shouldn't have used the word benchmark. Actually what I want to do is to test the performance of these swtichable optimizations and find BUGs, i.e., the cases when Optimizer fails to picks the best choice (e.g., MySQL67432). I do agree that the table size, query, ANALYZE, OPTIMIZE may influence the dicision of Optimizer, but no matter what decision made by the optimizer, if turning on a switch results in longer query latency, there may be a potential performance bug.
• Rick James: @haochenhe - Everyone appreciates your help in looking for bugs. You will find some. But be aware that not all will be "fixed". When you do find one, run some other experiments to help pin down what things "cause the bug" (bigger data, stale statistics, unbalanced data (eg, % of rows that have NULL), etc). The goal would be to able to say "in this circumstance, turn off that flag". That might lead to the Optimizer developers automatically using/ignoring the flag.
• Rick James: my mention of force index was an example along that vein. The use has a 'flag' (force/ignore index), but the optimizer usually picks the right choice.
• Tim He: @James, thank you for the thoughtful suggestions! That will surely help my work. I will have a try.