Forum / Remote Desktop Manager - Feature Request

reducing reliance on the database

  • Create an Issue
  • Cancel

So try as we might we just can't get the performance of RDM to a level that we would like.
We have even moved the enterprise database to a local SQL express instance here in our office. Even after doing that we still notice the following.

Our office is located in Arizona
Database located in New Jersey
-> average launch time = 13.65 seconds
-> average refresh time = 6.49 seconds

Database located in California
-> average launch time = 9.9 seconds
-> average refresh time = 4.23 seconds

Database located in Arizona
-> average launch time = 7.17 seconds
-> average refresh time = 2.35 seconds

I have a few threads in this forum speaking to the performance of RDM and ways to speed it up. We've determined that the database reliance is the single biggest contributing factor to slowness experiences within RDM. We'd like to find ways to reduce our reliance on the database constantly.
We would like to explore the following set of feature requests. To start, we already know RDM can be set to launch in offline/cached mode. So let's start there.

1) launch RDM in offline/cache mode

2) ability to trickle update the cache from the database on a schedule (once per hour or something configurable)

3) when reconciling the trickle update do a quick refresh of the cache, but this time its done locally so it won't freeze RDM for a few seconds while it updates since the data is already local.

4) ability to read/see properties of entries when in offline mode. We notice that when going to the properties of an entry in offline mode we do not see any of the properties. We understand we can't edit things when in offline mode, but we should still at least see what the properties are. It seems as though RDM does not cache everything about the entries in offline mode. Only the folder structure and names/IP's of the entries.

5) ability to quickly go back into online mode when changing a property of an entry and then back to offline mode after the change has been confirmed. Rather than go to RDM options, switch to online mode, change the entries of the property, then go back to options and go back into offline mode. Perhaps the ability to 'go into online mode' when looking at properties of an entry if it detects you're in offline mode?

6) Even in online mode, when doing manual refreshes from the database we see RDM freeze for a number of seconds while this refresh occurs. (The average refresh numbers from above) Is there a way to not make RDM completely freeze/unusable while this refresh occurs?

7) Our database engineers have determined that RDM is rather inefficient in its usage of SQL database connections. Here is a quote from one of our database admin's about RDM's SQL usage. We would like to get addressed the inefficiencies of RDM's connections to SQL to increase performance.

I did notice that each RDM session appears to hold an open database connection for each target being accessed. For example, three sessions from Scott's RDM client will result in three separate database connections. Connections appear to remain open until the associated RDM session has ended. I would have expected to see one database connection per RDM client, rather than per session. Establishing and opening a new database connection can be a rather expensive process and can take some time to complete. Most applications get around this by maintaining an open connection pool from an application server. If every RDM client session requires a new database connection, this is likely contributing to the delay.

8) We would love the ability to have a complete and 100% cache of the RDM database on each client machine so we're always working with the fastest available storage (our local hard drive). Then only things that NEED to be synchronized to the database can be trickle updated back to SQL. Thinks like audit logs, if properties change it there can be a scheduled background refresh take place every few minutes, etc. We would be fine with this being something that isn't turned on by default. Our database is roughly 300mb on the SQL server. And we would be fine with a local 300mb cache of everything on each client station (though I understand other customers might not want that but since it's not turned on by default they wouldn't be affected)
If this last feature request could be implemented, it would negate the need for features 4 through 7.

Clock3 mths

I will create a Epic in our task system. I think it could be good idea but it's an architecture redesign. This will not be possible in a minor update.

Regards

David Hervieux

signaturesignature

Clock3 mths

Just checking in. Has any progress been made. There was a 'fix' posted in the release notes for version 14.0.11.0 that states "

  • Fixed cache-only issue with ways to go offline on startup
was this to address any of the issues I've mentioned above? Could more details be provided about what this fix addresses?

Clock3 mths

Sorry this is a very long post. So let us start with some high-level definitions/explanations:

What does RDM do when you refresh?
Answer: RDM "loads the data" then displays it in the UI (in the tree view).

What does "loads the data" mean?
Answer: It depends. With no caching enabled, we must read all the data from the database. When caching is enabled we load from local files (if not already in memory) then fetch a delta of the changes from the server (added/edited/deleted sessions). Those changes are then merged into memory and the tree view is refreshed. The changes are in parallel saved back to the local cache (using a background thread).

hazen wrote:

1) launch RDM in offline/cache mode
You can already accomplish this a few different ways:
File > Data Sources > Edit (button) > Advanced (tab) > "Prompt for offline mode on startup"
File > Options > General > Application Start > Startup online state > "Automatically go offfline"
and others

hazen wrote:

2) ability to trickle update the cache from the database on a schedule (once per hour or something configurable)
Updates are done on refresh (F5) but also when edit an entry. You can disable this behavior.
File > Options > Advanced > "Force refresh before edit entry". We will look at adding an auto-refresh with the SQL Server data source.

hazen wrote:

3) when reconciling the trickle update do a quick refresh of the cache, but this time its done locally so it won't freeze RDM for a few seconds while it updates since the data is already local.
If caching is enabled it is already done this way.

hazen wrote:

4) ability to read/see properties of entries when in offline mode. We notice that when going to the properties of an entry in offline mode we do not see any of the properties. We understand we can't edit things when in offline mode, but we should still at least see what the properties are. It seems as though RDM does not cache everything about the entries in offline mode. Only the folder structure and names/IP's of the entries.
You can see the properties when in read/write offline mode. RDM does cache the entire entry (not task/attachments). It is just not available when in read-only offline mode.

hazen wrote:

5) ability to quickly go back into online mode when changing a property of an entry and then back to offline mode after the change has been confirmed. Rather than go to RDM options, switch to online mode, change the entries of the property, then go back to options and go back into offline mode. Perhaps the ability to 'go into online mode' when looking at properties of an entry if it detects you're in offline mode?
When in read-write offline mode you can perform the changes locally then once you connect back online RDM will prompt you to synchronize your changes and notify of any possible modification collision with another user.

hazen wrote:

6) Even in online mode, when doing manual refreshes from the database we see RDM freeze for a number of seconds while this refresh occurs. (The average refresh numbers from above) Is there a way to not make RDM completely freeze/unusable while this refresh occurs?
The "freeze" you experience should be very fast. What offline mode are you running with? File > My Data Source Information > Offline Mode

hazen wrote:

7) Our database engineers have determined that RDM is rather inefficient in its usage of SQL database connections. Here is a quote from one of our database admin's about RDM's SQL usage. We would like to get addressed the inefficiencies of RDM's connections to SQL to increase performance.

I did notice that each RDM session appears to hold an open database connection for each target being accessed. For example, three sessions from Scott's RDM client will result in three separate database connections. Connections appear to remain open until the associated RDM session has ended. I would have expected to see one database connection per RDM client, rather than per session. Establishing and opening a new database connection can be a rather expensive process and can take some time to complete. Most applications get around this by maintaining an open connection pool from an application server. If every RDM client session requires a new database connection, this is likely contributing to the delay.

We use Microsoft .NET and hence connection pooling is enabled by default. Connections from the client will stay open and be reused on a subsequent call to the database. This is not based on the number of open sessions but rather the number of concurrent database sessions needs on the client at a given time. So if we open connection A then close connection A, will would have a single session in the client pool. If we then open session A, open session B, open session C then closes A, B & C then all 3 database sessions will stay opened. The client cache could eventually decide to close a session at any given time if it hasn't been used in a while. Like I said this is built in with the .NET framework and is a recommended approach.

hazen wrote:

8) We would love the ability to have a complete and 100% cache of the RDM database on each client machine so we're always working with the fastest available storage (our local hard drive). Then only things that NEED to be synchronized to the database can be trickle updated back to SQL. Thinks like audit logs, if properties change it there can be a scheduled background refresh take place every few minutes, etc. We would be fine with this being something that isn't turned on by default. Our database is roughly 300mb on the SQL server. And we would be fine with a local 300mb cache of everything on each client station (though I understand other customers might not want that but since it's not turned on by default they wouldn't be affected)
If this last feature request could be implemented, it would negate the need for features 4 through 7.
Your database might be 300MB but that includes a lot of history records for auditing purposes. To find out how much data is actually transferred from server to clients you can query, in SSMS, "select data from dbo.Connections" with right click "Include Client Statistics" (SHIFT + ALT +S) and have a look at the "Bytes received from server".

There is a lot of information here. First step is to double check that offline/cache is enabled. You have a three options (Read/Write, Read-only & Cache-only). From the questions above, I believe that Read/Write would to the one best suited for you.

Note: There are times and things that can happen in RDM that will invalidate the client cache, forcing all clients to perform a full refresh. I will go through those in another reply.

Best regards,

Stefane Lavergne

signaturesignature

Clock3 mths

Stefane, this is perhaps one of the best posts I have ever seen on this forum! Thank you for walking through everything. Here are my replies.

Let's go through things in order. I should point out from the get go that I am currently running RDM 14.1.0 (the latest as of 12/19). So any issues I talk about are present up to this release.

Issue 1, launch RDM in offline mode.
This was something I discovered was possible only after my original post. We can ignore this.

Issue 2, ability to trickle update the cache.
This is also something that I discovered was already available after my original post.
File -> Data Sources -> Settings (tab) -> "Auto Refresh"
Based on conversations with support we determined that this is the better way to auto update our cache since it is indeed a trickle and non-impactfull (non-freeze) updated to RDM. Hitting the File -> Refresh option destroys the local cache and rebuilds it. We were told this is why we see RDM freeze for a few seconds when using that method. We have set all our clients to do the auto refresh and have been very happy with the results.

Issue 3, trickle update reconciliation
issue related to number 2 and can be ignored. We consider issue 2 and 3 resolved.

Issue 4, ability to read/view properties of entries while in offline mode.
We are unfamiliar with what you mean by "read/write offline mode". We don't know how to get to this state. When having RDM start in offline mode we are not able to see properties of entries. However, with the trickle update of cache from issue 2 resolved, we no longer start RDM in offline mode. So we would consider issue 4 a moot point.

Issue 5, ability to go back into online mode when being in offline mode.
Your explanation doesn't make sense. We don't know how to get into the read-write offline mode that allows for local changes which then trickle/push updates back to SQL. Please explain more.

Issue 6, online mode refresh freeze.
Still an issue. When click 'refresh' we see RDM become unresponsive for several seconds. We wouldn't really call that 'very fast'. Looking at the my data source information is seems we are in read-only offline mode. I have no idea how to configure for a 'read-write' offline mode.

Issue 7, SQL database connections.
thank you for the explanation. I don't understand it, but it sounds good.

Issue 8, complete cache of RDM locally.

Let us know how to get into read-write offline mode to get the full cache of the database locally and I think we will be set. We would hope that being in that state would also make the RDM startup process faster as well.

Clock3 mths

Issue 2: Small clarification, File -> Refresh will not destroys the local cache, CTRL + Refresh (F5) will.

Issue 4: There are many ways to control the behavior of the offline. You can have a look at this help topic for more information: https://help.remotedesktopmanager.com/index.html?datasource_offline.htm
Please let me know if you need clarification on this topic.

Topic 30935

Issue 5: Made a small video have a look. At how you can perform changes offline and then sync the to the server.

Issue 6: Will need to investigate further, will get back to you with this one.

As mentioned in the last post, a few things will invalidate ALL client caches and force the RDM client to perform a full reload. Examples:
- Adding/changing/deleting users/roles
- Changing "Data Source Settings"
In other words, almost anything you perform here:
Topic 30935 2

Stefane Lavergne

signaturesignature

Topic-30935.png
Topic30935.mp4
Topic-30935-2.png
Clock2 mths

As for slow load times. Can you please perform the following steps (see video)?

- Open the data source
- Open the Profiler window Help > Profiler
- CTR+Refresh button
- Refresh button
- Copy the output and past it in a text editor
- please send me the output


In this video I have an RDM attached to an SQL Azure database located in the East US 2 (Virginia) data center and I'm located in Montreal, Quebec, Canada.

With almost 6000 sessions, you can see that it took just under 4 seconds total time. Of that time, 2.5 seconds was database/network related and the remainder was manipulating the data locally.

On a refresh with changes (1 edit, 1 delete) 539 ms total, 65 ms fetch

To this time you must add (150 ms 'ish) to display it all in the tree view.

How does this compare to your setup?

Best regards,

Stefane Lavergne

signaturesignature

Topic-30935-2.mp4
Clock2 mths