Jump to content
Automation Theory

Beta testers wanted: Database performance optimization plugin

Recommended Posts

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. 
 
Feel free to reach out with any questions or comments.
 

Share this post


Link to post
Share on other sites

Update: The first patch has been released for the plugin. It includes a new tuning optimization (reducing overhead from row locking). This new feature brings the benchmarks to 21% faster than the stock config. The ZIP file in the KB article has been updated with the latest version of the plugin (version number should read 2020.0309).

Share this post


Link to post
Share on other sites

Just got this installed and running.

I didn't actually 'enable' the plugin before running the script so it failed to run the second query. I figured it out and got it sorted and running but just thought I'd mention it.

Share this post


Link to post
Share on other sites

I have the plugin installed, but when I try to run the script to create a stored procedure it fails:

The Script(601) failed in the Then section at step 1. The reason: SQL Execute failed processing query

Any suggestions? This is a cloud-hosted instance if that makes a difference.

Share this post


Link to post
Share on other sites

@chris_bb I upgraded my test server to 2020.3 and I wasn't able to duplicate your error message. The first thing I'd check is permissions (must be a Super Admin). Otherwise it might be worth while to copy/paste the commands into a new script; it might be import strangeness between Automate versions. Let me know how it goes.

Share this post


Link to post
Share on other sites

I actually had the same problem. It was weird. It apparently ran the first time successfully enough to create the stored procedure. But subsequent runs errored out and when I ran the query manually in sqlyog it said it failed since the SP already existed. I kinda assumed it was just my older version of MariaDB. So I just manually ran the second query and things seem to be working well for me. Definitely noticed a performance boost for thick client operations. Not really anything for web client. 

Share this post


Link to post
Share on other sites

That definitely sheds some light on the situation. Let me explain some order-of-operations items here:

  1. When the plugin initializes it checks if it's table plugin_databasetuner_general exists, and creates it if needed.
  2. The first line of the script creates the stored procedure. If the procedure already exists step one of the script will fail.
  3. The second line of the script sets a value of '1' to the column sp_created in the database table. If the plugin has not created the table this step will fail, and without this value the plugin assumes that the stored procedure is missing and won't run the tune.

@chris_bb run the following script to see if the stored procedure has been created (the log should say "labtech"). If so just disable the first line of the creation script, and re-run it so line 2 will set the value in the plugin's table. [For on-premise servers this'd be a single query, but since we can only hit hosted DB's through the app, this is the clunky work around.]

Show Database Tuner SP.xml

Share this post


Link to post
Share on other sites

Thanks! Looks like that did it. Got "labtech" as expected, then re-ran the second line of the first script.

Share this post


Link to post
Share on other sites

How have the tests been going?

How can I test the plugin for an 1hr or a day? 

If there is major issue how to I roll back or remove the plugin?

Share this post


Link to post
Share on other sites

@DNA3e8 No issues have been reported, and most users have reported noticeable improvements.

The plugin doesn't make any persistent changes. Restarting the LabMySQL service or using the UI button to revert the changes will flush the changes. You can use that functionally to do short term testing (but please note it might take some time for the changes to show).

Removing the license key will prevent the plugin from performing the initial tune when the server starts. Let me know if you have any other questions!

  • Like 1

Share this post


Link to post
Share on other sites

@tlphipps I don't want to hijack this thread for IIS tuning, but I'd guess your server might benefit from that too. I normally advise increasing the threads in the application pool (CPU core count - 1 is the traditional wisdom) and setting the app pool to recycle regularly (I recycle every 90 minutes, but every 8 hours is where I'd start).

Share this post


Link to post
Share on other sites
1 hour ago, Automation Theory said:

@tlphipps I don't want to hijack this thread for IIS tuning, but I'd guess your server might benefit from that too. I normally advise increasing the threads in the application pool (CPU core count - 1 is the traditional wisdom) and setting the app pool to recycle regularly (I recycle every 90 minutes, but every 8 hours is where I'd start).

Thanks. We actually have done that and have very decent performance from our /automate site. I just mentioned that we didn't notice it being any better/different really with the changes from this plugin as a point of reference.

  • Like 1

Share this post


Link to post
Share on other sites
Quote

so I created a plugin that does all the dirty work

Quote

The plugin doesn't make any persistent changes. Restarting the LabMySQL service or using the UI button to revert the changes will flush the changes.

What exactly does your plugin do to MySQL?

Share this post


Link to post
Share on other sites
On 3/27/2020 at 11:28 AM, Automation Theory said:

@tlphipps I don't want to hijack this thread for IIS tuning, but I'd guess your server might benefit from that too. I normally advise increasing the threads in the application pool (CPU core count - 1 is the traditional wisdom) and setting the app pool to recycle regularly (I recycle every 90 minutes, but every 8 hours is where I'd start).

For which Application Pool are you recommending this change? There are a few: CwaRac, CwaRestApi, LabTech, LabTech WebCC

Thanks in advance.

Share this post


Link to post
Share on other sites

@jbbehar I bump up the worker processes on the LabTech pool, and I recycle LabTech and CwaRestApi. I found that each worker process in those pools would eventually use 2GB+ of RAM (and hold open a bunch of idle MySQL connections). This is a textbook example of a memory leak, and recycling is Microsoft's answer (and works well). Please note that your end users will see ~30 second lag when that recycle job runs. However, since the thick client freezes during it's normal operation the end users normally don't notice.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...