MariaDB MariaDB Profile-Guided Optimization?

On the topic of PGO and MySQL/MariaDB, take a look at this talk by two Dropbox SREs:
Optimizing MySQL without SQL or touching my.cnf | Percona Live - Open Source Database Conference 2017

Some takeaways: Apparently Clang/LLVM can produce better results than GCC, with different GCC versions also making a big difference in performance. Using GCC with -march=native -mtune=native and -flto (with PGO) all helped.

The main challenges using PGO this way are:

1) The binary is fixed to your training run and can actually be slower if your workload changes.

2) You need to go through the work of building and training yourself. It can all be very labour intensive.

3) There is a bit of risk involved when building MySQL yourself. Did you set the right source config options such that MySQL will function exactly as before?

To overcome this I would recommend trying Dynimize. It performs PGO on live running processes by using in-memory machine code plus live profiling data at runtime, so your profiling data is as relevant as possible. It's a machine code to machine code JIT compiler that runs as a separate background process and interfaces with target processes through various system calls. The advantages are:

1) Trivial setup (as easy as tweaking your my.cnf).

2) You don't need to build MariaDB from source as it only uses live in memory machine code. That also means target program functionality will not change.

3) Zero downtime. Application restart not required.

4) It can automatically adapt to changes in workload profile if you start it with the -reoptimize flag. You can also tell it to redo the PGO at any point, or undo the PGO and revert back to the original machine code, all without mysqld restart.

5) It optimizes across all user space machine code in a target process, including shared libraries. It also adds memory address dependant optimizations that can only be done with run-time information.

6) No changes to object code on disk.

7) It has been thoroughly stress tested across a large range of MariaDB and MySQL versions/distributions.

The PGO phase for Dynimize is quite short and only takes one to a few minutes, depending on how CPU intensive your workload is and therefor how quickly it can collect profiling data. You will need a relatively CPU intensive mysqld process. It does temporarily consume 4 GB of memory during the PGO phase, but you can just increase swap instead of RAM for that if you are short on RAM. The perf improvements are very similar to what you get with GCC PGO: https://dynimize.com/performanceSpeedup

Keep in mind that when you improve the CPU performance of MySQL or any other program, the gains may manifest as a reduction in CPU usage rather than a performance boost (or a bit of both), depending on where your bottlenecks are.

Disclosure: I'm the founder of Dynimize and I actually discovered Centmin Mod from a Dynimize user using it with the Centmin Mod stack

 

One could always give it a try with memcached/redis, however Dynimize only optimizes user mode machine code at the moment. The redis and memcached workloads I've benchmarked spend most of their time in kernel mode. Dido for NGINX. There are plans to support kernel mode in the future, and those workloads would be an excellent match for PGO since they spend most their time in icache misses. Compression algorithms typically don't, so it would depend on other opportunities that may exist in the specific compression algorithm.

Good point. There's also something to be said for sticking with the official project compiler.

.

 

From the Dynimize User Guide:

@tininho your dyni -status means the shared libraries or mysqld on disk has been updated since you started that mysqld process, so they are out of sync. Dynimize won't optimize it until you restart MariaDB.

Note that if the performance of the stack is dependant on several components and not completely limited by mysqld, then expect some component of the benefit to be a mysqld CPU usage reduction. That is often the case with WordPress.

 

That's good to know. Thanks for the feedback!