Jump to content
kostam

Automate server rebuild / performance

Recommended Posts

Posted (edited)

Hi guys,

We're looking to rebuild our Automate server, as we've been experiencing a number of performance issues in our current install, and I'm hoping for some advice.

We don't currently monitor workstations, due to this causing severe performance issues to Automate when it hit around 1,500 deployed agents. As such, we're currently just monitoring servers, around 750~. As we do not know why Automate encounters performance issues with more agents and we would like to be able to manage workstations, we've decided to rebuild our server completely. The performance issues seemed to be related to the database. As more agents were deployed, the greater the database would grow and become slow, followed by completely freezing the Automate Control Centre application. The issues are very similar to what another user encountered here: 

Our current setup is as follows:

  • Single server environment running Windows 2016 and MySQL, currently 750~ agents deployed
  • Storage / HDD: Virtualized SCSI 135GB available, 50GB used by database
  • Memory: 48GB available, 12GB in use with 8GB of that being used by MySQL
  • Database: Largest table is eventlogs at 4.5GB, followed by other tables from less than 1.3GB

I'm hoping to get some idea on the current setup other organizations have with their Automate servers and how their performance has been with an excess of 1,500 agents deployed.

Thanks :)

Edited by kostam

Share this post


Link to post
Share on other sites

When you say single server, do you use Screenconnect (Connectwise Control)? If so, that'll be the first thing to move off the server and onto a seperate VM. The rest should be fine. You should be able to double your capacity before needing to split servers so maybe something else broke your installation.

Share this post


Link to post
Share on other sites

Thanks Wesley :) We actually already have ScreenConnect on its own server / VM. So guessing it must be something else that's causing issues.

Share this post


Link to post
Share on other sites

The 8Gb in use by mysql looks to be your problem. We only have 500 agents and mysqld is using over 9Gb of memory. Logic would imply you should be seeing something more along the lines of 25Gb in use by mysql alone.

I can't supply you settings for your number of agents but there are a fair number of postings in this forum on sizing mysql, with examples. I'd recommend starting with a default my.ini, apply the changes recommended in the CW docs and then apply the changes people recommend for similarly sized installs. One change at a time so you can roll back.

I've attached my my.ini, which you should not use as-is, but it should show you which settings need to be modified. The slack channel is a good place to go to get advice as well.

Pretty much everyone makes the investment in ssd disks for the db disks as least. Its worth it.

There are people on here with 20k+ agents... so it does work, its just a matter of getting the tuning right.

my.ini

  • Like 2

Share this post


Link to post
Share on other sites
Posted (edited)

Generally speaking try to have the entire database in RAM so adjust InnoDB buffer up accordingly.  I think CWA docs say to give MySQL 50% of RAM but we've generally run closer to 80% with no issues.

Edit:"database in RAM" being better than using only 8 GB on a much larger database, which I assumed was your steady state.

Edited by SteveYates
  • Like 1

Share this post


Link to post
Share on other sites
Posted (edited)

Thanks for the replies, everyone. Apologies, I seem to have given some incorrect information. In my initial post, I stated the DB size to be 50GB, but the actual size is 11.3GB. I was looking at the total amount of disk usage on the C drive.
 

On 7/30/2020 at 9:58 PM, imurphy said:

The 8Gb in use by mysql looks to be your problem. We only have 500 agents and mysqld is using over 9Gb of memory. Logic would imply you should be seeing something more along the lines of 25Gb in use by mysql alone.

I can't supply you settings for your number of agents but there are a fair number of postings in this forum on sizing mysql, with examples. I'd recommend starting with a default my.ini, apply the changes recommended in the CW docs and then apply the changes people recommend for similarly sized installs. One change at a time so you can roll back.

I've attached my my.ini, which you should not use as-is, but it should show you which settings need to be modified. The slack channel is a good place to go to get advice as well.

Pretty much everyone makes the investment in ssd disks for the db disks as least. Its worth it.

There are people on here with 20k+ agents... so it does work, its just a matter of getting the tuning right.

my.ini 14.27 kB · 3 downloads


Interestingly, imurphy, without any changes being made, we're now seeing 24GB used by MySQL. One MySQL process in particular seems to be writing heavily to disk, which is:

C:\ProgramData\MySQL\MySQL Server 5.6\data\ibdata1

Going into C:\ProgramData\MySQL\MySQL Server 5.6\data\labtech I can see the largest files seem to be eventlog IBD files, which seem to be continuously updating (currently at around 1.8GB) - which I suppose is normal. So given that the DB size is 11.3GB and memory used by MySQL is about double that - seems to line up with what you've said. I think I'll monitor over the next few days as the memory usage was definitely not this high on Thursday.

Edited by kostam

Share this post


Link to post
Share on other sites

MySQL will start out with small buffer/cache usage and increase over time as more data is handled.

The default ibdata and one of the log files is written to on every write as that is the transaction log action. Which log file is used rotates.

Event log is about 80-90% of our database as I recall.  It will of course depend on the length of time event logs are kept...we're using 30 days I believe.

  • Like 1

Share this post


Link to post
Share on other sites
On 8/2/2020 at 7:15 PM, kostam said:

Thanks for the replies, everyone. Apologies, I seem to have given some incorrect information. In my initial post, I stated the DB size to be 50GB, but the actual size is 11.3GB. I was looking at the total amount of disk usage on the C drive.
 


Interestingly, imurphy, without any changes being made, we're now seeing 24GB used by MySQL. One MySQL process in particular seems to be writing heavily to disk, which is:

C:\ProgramData\MySQL\MySQL Server 5.6\data\ibdata1

Going into C:\ProgramData\MySQL\MySQL Server 5.6\data\labtech I can see the largest files seem to be eventlog IBD files, which seem to be continuously updating (currently at around 1.8GB) - which I suppose is normal. So given that the DB size is 11.3GB and memory used by MySQL is about double that - seems to line up with what you've said. I think I'll monitor over the next few days as the memory usage was definitely not this high on Thursday.

 

Sorry I haven't replied earlier -- you might be having MySQL issues, but buffer and size aren't among them. 

Automate servers are write-heavy into MySQL since they update inventory from all the agents. It's normal that you're seeing that behavior.

As a DBA I'm going to disagree with some of the above advice; check out my post here: https://automationtheory.org/top-5-myths-about-the-automate-database/ Chopping out data from your DB won't do a lot (especially since yours is so small; my prod with ~10k agents is ~55GB). I'd move to SSD's, and start with a buffer pool that's at least 80% of your DATA size. However, holding 100% of your DB in the buffer pool isn't going to help (see blog post).

I have a plugin (link) that does dynamic tuning of MySQL for Automate -- I'd be happy to setup a trial if you'd like to try it; especially if it would be difficult to move off of mechanical disks.

  • Like 1

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...