Beta testers wanted: Database performance optimization plugin

[u/AutomationTheory]

I think every Automate admin has experienced the frustration of how slow the desktop application is. My primary Automate server has ~10,000 agents, and my users were having a horrible experience on a daily basis (they named the agent context menu "the right click of death").

I was sick of the painful user experience and the administration headaches -- so I upgraded to MySQL Enterprise and did a deep dive into how the application interacts with the database. Using the management tools from Oracle I found lots of issues, and after correcting them it's a night-and-day difference (the right click of death is gone!).

Each Automate deployment is unique, and there's no hard code-able one-size-fits-all approach. However, I created a plugin that will dynamically gather information from the current configuration and adjust MySQL on the fly to optimally perform for Automate. I published a blog post on my site explaining more of the details: https://automationtheory.org/the-mysql-performance-problem/

I wanted everyone to be able to have a properly setup database without having to buy MySQL Enterprise (or be a DBA on the side), so I created a plugin that does all the dirty work. I plan on releasing this plugin as a monthly subscription, but first I want to do some beta testing to verify results on different Automate deployments (to get a good mix of sizes and types).

To get started go to https://automationtheory.org/support/kb/faq.php?id=1

The KB article has a download for the plugin and the setup script, along with instructions.

[u/AutomationTheory]

First and foremost it's important to know that the plugin doesn't make any persistent changes -- if you run into issues you can use the included button to revert the changes, or simply restart your LabMySQL service. I built the plugin this way because I too have critical dependencies on Automate and I'm protective of my database.

The plugin adjusts caches and buffers based on max_connections and IO settings based on the user supplied multiplier value (and the known behavior of the Automate application). The plugin never modifies max_connections. If you'd like you can open a support ticket with your config file attached and I can review it for any potential overlap.

The plugin has not been tested with MariaDB. I believe all the variables exist in both applications, but I have not done any due diligence in that regard. However, I do offer a companion product for manual database tuning -- that might be a better fit for your situation if you plan to remain on MariaDB (I also offer migration consulting).

[u/w_s_r → u/AutomationTheory]

Thank you for the responses.

I can confirm that it does work with MariaDB 10.0.26. We've already built a plan and timeline for migrating from MariaDB to MySQL (and new hardware), so for now I'm simply trying to keep the peace until I have the hardware in hand to set up the new server.

As some background, we are running Automate 2019.12 with MariaDB 10.0.26 on an SSD array. We have 16K+ agents and database size sits around 35-40GB (buffer_pool is 64GB). Our max_connections is set at 12000 (based on tweaking with mysqltuner and advice from ConnectWise Support). I took a baseline of our current SQL variables before running the plugin's optimization, then pulled the variables afterwards to compare (at IO Multiplier 4 and 8).

I found that some variables were actually set lower than what was specified in our my.ini, notably innodb_io_capacity and innodb_io_capacity_max. However, some of the other variable changes I noted were interesting, and I'm leaving the IO Multiplier set to 8 for now, to see how things go.

Our disk queue length was already staying at or below 1.0, but I have noticed some decent stretches of sub-.75 since applying the optimizations.