Bear in mind when Percona considerably improved question processing time by fixing the optimizer bug? I’ve described all the small print in Extra Performant Question Processing in Percona Server for MySQL weblog submit. This time, we dug deeper into all of the concepts from Enhanced for MySQL and primarily based on our evaluation, we proposed a number of new enhancements. All of the adjustments can be found in Percona Server 8.0.43 and eight.4.6, and newer variations
Earlier than discussing particular enhancements, let me describe how we measured the sort of efficiency achieve we achieved. The highest-level method is widespread for benchmarks: you want a baseline model after which evaluate your modified model to that baseline. At Percona, we now have a devoted surroundings for efficiency testing. For detailed details about our testing methodology, the sysbench workloads used, and the {hardware} configuration, please see our earlier weblog submit: Percona Server for MySQL Efficiency Enhancements.
Let’s get to enhancements
One of many concepts was to implement mem_root_deque class utilizing std::listing as a substitute of a customized implementation. This class is used all through the SQL engine to carry lists of expressions, fields, and desk references throughout question parsing, optimization, and execution, making reminiscence administration extra environment friendly and predictable throughout the core of MySQL’s question processing pipeline. Implementation of the change was fairly straightforward, due to mem_root_dequeue well-defined interface that matches virtually completely with std::listing interface.
Listed here are the outcomes of benchmarking. Every cell shows the development share in comparison with the baseline model. In different phrases, it says how a lot sooner or slower is the modified model compared to the baseline model.
We will observe a noticeable enchancment in almost all circumstances. As soon as we filter out these affected by jitter, solely a single case stays, exhibiting a slight lower in efficiency. Nevertheless, this minor exception doesn’t detract from the general development, as the overall efficiency positive factors are clear and vital. The proof strongly helps that the enhancements result in a constant and measurable enchancment, demonstrating the effectiveness of the adjustments applied.
One other enchancment we determined to embrace is the thought of inlining a number of capabilities concerned in scorching execution paths. One case was particularly attention-grabbing. In commit WL#13899 : INSTANT DROP (and ADD) COLUMN, Oracle refactored the rec_init_offsets() operate. A portion of the code was extracted right into a separate operate, and the if-else sequence was refactored right into a switch-case. Even after inlining the brand new operate, we haven’t noticed the anticipated enchancment. Deeper evaluation revealed that refactoring the if-else to a switch-case launched a further conditional soar on the assembler stage. Reverting this half to the unique model confirmed a efficiency increase.
Abstract
Sure, it’s true that these enhancements aren’t dramatic or game-changing within the sense of manufacturing a ten% increase for each single case. They’re, as a substitute, comparatively modest positive factors – delicate refinements that accumulate over time. Traditionally, we now have seen comparable patterns in techniques like MySQL, the place main bottlenecks have been progressively recognized and eradicated, resulting in vital total efficiency enhancements. But even after the obvious inefficiencies have been addressed, there remained – and certain nonetheless stay – quite a few smaller alternatives for optimization. These incremental positive factors, often on the order of some tenths of a p.c, could appear insignificant on their very own, however in combination they meaningfully improve total system efficiency. The pursuit of those small enhancements is a steady course of, highlighting the continued potential for fine-tuning and optimization even in mature, well-optimized software program.
