Cockpit causes high CPU usage

[former_SQLA_member1694883]

We are currently looking at the new Cockpit monitoring utility in 17.0.4.2053 and it seems like it could be quite useful. The documentation says "it is designed to run continuously in the background so that you can connect to it when needed." However, we are finding that whilst it is running it is causing excessively high CPU usage, spiking at above 85% for short periods of time and triggering it's own alerts. Stopping the Cockpit, causes the processor usage to drop right back down to nearly zero. There is nothing else that can be causing this as the Cockpit is the only connection to dbsrv17.exe and it is this process that is using all the CPU time. Is there something that needs to be changed to allow this to run without maxing out the CPU, or is this just not ready for prime time yet?

[Breck_Carter]

Try the SQL Anywhere Monitor.

Normally I would say "Try Foxhound" but alas, Foxhound won't work with version 17.0.4 target databases until later this year.

Or console yourself with the thought that Cockpit is free.

[former_SQLA_member1694883]

Thanks. I am aware of both SQL Anywhere Monitor and your product, Foxhound. The reason I am interested in Cockpit, is that it is included with the standard SQLA17 license. I want to know if there is something wrong with the configuration, or if this high CPU usage is normal! If so, it rules it out for running permanently in the background in a production environment. It actually looks like it has as much, if not more, of a performance impact on the CPU as the Profiler does (which is stated in the documentation as not designed to run long-term as it can have a significant performance overhead).

[huber1]

I also tried the Cockpit monitoring but do not get more than 5 % CPU load when using it, i. e. selecting different menus. Are you doing something specifically when you get high CPU load?

[Breck_Carter]

I am seeing the same thing as robert, on 17.0.4, with the demo database and two connections (idle ISQL, and Foxhound 4 sampling every 10 seconds); i.e., low CPU.

How have you determined that Cockpit is responsible for the CPU usage? AFAIK Cockpit no longer reports its own internal connections as client connections.

Is it possible you have some runaway busy scheduled EVENT inside your database? I don't know if Cockpit shows those.

FWIW Foxhound 4 shows that on an idle database Cockpit repeatedly creates and drops 5 un-named connections that last 1 second or less... rather bizarre curious behavior IMO 🙂

[former_SQLA_member1694883]

No, nothing specific. As described in my question, there are no connections to the database other than Cockpit and Cockpit is supposedly idle, i.e. running in the background without us even being logged into it. As soon as we stop Cockpit processor usage (for dbsrv17.exe) drops back to nearly zero.

[Breck_Carter]

Question for you: Do you know how to tell Cockpit to stay connected? The GUI "timed out" after a few minutes and the connections disappeared.

FWIW Cockpit's own connections don't show up in the database-level ConnCount property, but they do show up in the connection-level property values... sigh... another violation of The Watcom Rule methinks... now I have to go change Foxhound 4, or at least document the grotesque inconsistency in the Help ( as in "if you can't FIX it, at least FAQ it" 🙂

[VolkerBarth]

Ah, I see, stealth connections in the invisible database:)

[former_SQLA_member1694883]

No, unfortunately not. I haven't worked this out yet and actually suspect there isn't a way to do this. The documentation regarding Cockpit is minimal to say the least. Regarding the connections, I think this was changed in 17.0.4.2053.

"Simplified connection counts: Previously, all connections to the database server, including those made by the Cockpit itself, were included in the connections counts for the database server. Now, connections made by the Cockpit are excluded from the connection counts."

http://dcx.sap.com/index.html#sqla170/en/html/85df02cf9625474683ac870846782ad1.html

[Breck_Carter]

> connections made by the Cockpit are excluded from the connection counts

That refers to Cockpit no longer including its own short-lived connections in its own display of connection counts, which is reasonable. That doesn't mean it is reasonable for SQL Anywhere to exclude those connections from DB_PROPERTY ( 'ConnCount' ); it isn't. AFAIK there is only one other connection (StmtPerfMngrConn introduced in 17.0.4) that is afforded this honor of not being counted in ConnCount.

FWIW I have tried and failed to duplicate your symptom. I created a scheduled event that ran a heavy-duty intra-query parallelism query, and (eventually) Cockpit showed the event connection AND all eight of its child connection in the Cockpit CONNECTIONS display. Foxhound 4 showed that the CPU time was all accounted for by event query, and the Cockpit connections were quiet as far as resource usage was concerned.

[former_SQLA_member1694883]

Thank you Breck, that is interesting. At least this does indicate some sort of configuration issue (although there isn't that much that you can configure TBH), rather than some inherent problem with the Cockpit. We will try starting the Cockpit with a new temporary database instead of the permanent one we are using at the moment to see if the behaviour changes.

[Former Member]

Hi RobertW,

Could you tell me the configuration of the machine you are using. How many cores does it have and how much memory? Did you change any of the configuration settings in the cockpit? A clear description of exactly what you are doing to run it would be helpful. Did using a temporary database change anything?

thanks, Paul

[former_SQLA_member1694883]

I haven't checked this with a blank temporary Cockpit database yet. I will try this tomorrow and report back. The Virtual Server it is running on is Windows Server 2008 R2 with two cores, and 8GB RAM. However, our application is 32-bit, so the RAM available to it is obviously limited to 4GB. There is barely anything else running on the Virtual Server as it is dedicated to serving our application. We are running dbsrv17.exe as a service and it is using the following additional switches: -gdall -xtcpip -ti0 -c50p -gnh100 -cdb "start=yes;dbf=Cockpit.db". We changed some configuration settings inside Cockpit to setup email alerts, but that is about it. Nothing I would call particularly non-standard or out of the ordinary.

[former_SQLA_member1694883]

I started the Cockpit with a new blank database today and immediately found that the high CPU usage was no longer occurring. I then reverted to the original Cockpit database only to see the CPU spiking issue return immediately. This clearly indicates that there is something either wrong with the original Cockpit database, or there is some configuration change that we have made that causes a bug to be exposed that is not seen by default when starting the Cockpit. I will continue to investigate this to see if I can pinpoint the exact cause. However, if anyone can offer any suggestions as to what settings might potentially cause this and I should therefore be taking a closer look at within the Cockpit, it would be most appreciated.

[former_SQLA_member1694883]

I have configured the new blank database exactly the same as the original database with the problem and it does not exhibit the same behaviour. Other than the fact that one contains logging data and the other doesn't, I looked to see if I could find anything else that was different between the two databases. I found one thing, which I believe may be significant. The original database with the problem shows no connections to it, whereas the new one without the problem shows 'INT: StmtPerfMngrConn'. No matter how many times I start and stop the Cockpit database, this connection does not appear in the original database with the problem, but always appears in the new blank database without the problem. Can someone suggest why this is missing, why it is not automatically created when the Cockpit starts, or a way to reinstate it, as I suspect this may be the cause of the problem. Also any suggestions as to how this has gone missing?

[former_SQLA_member1694883]

Some additional information. I tried dropping 'INT: StmtPerfMngrConn' to try and reproduce the effect of running the Cockpit without this connection and it appeared to have no ill effect until I stopped and restarted the Cockpit. The result being after restarting that I am no longer able open the Cockpit database at all and needed to create a new one. Therefore, how the original problematic Cockpit database is functioning at all without that connection mystifies me.

[VolkerBarth]

As "INT: StmtPerfMngrConn" seems to be a v17.0.4 enhancement (cf. Breck's question ) which - according to the docs - requires a database upgrade/rebuild: Are both databases set to the same database version (see SYSHISTORY)?

[VolkerBarth]

Just to confirm: So both databases show the same version number within SYSHISTORY (for operation 'INIT' and/or 'UPGRADE')?

[former_SQLA_member1694883]

Ah, yes it would appear that one of the test databases is still 17.0.0.1358, before we went into production with 17.0.4.2053, and has not been unloaded-reloaded. In that case there is no difference that I can see between the problematic original Cockpit database and the new Cockpit database without the problem other than the original one contains about 50MB of logging data.

[former_SQLA_member1694883]

Restarting the database service brings back 'INT: StmtPerfMngrConn' on the 17.0.4.2053 database. Therefore I am no further forward, as I can find no difference between the problematic original Cockpit database and the new one that does not have the problem. Can anyone suggest what line of investigation to go down next? Short of simply dumping the problematic Cockpit database and using a new one, which we could do, I don't know what else to try. However, this doesn't get to the cause of the problem or give me confidence that we won't see this again at some point.

[former_SQLA_member1694883]

I am still looking into this and have yet to figure out what has caused this odd behaviour with the one affected database, which I have yet to reproduce with another database. I did have a thought and this troublesome Cockpit database was in use and running whilst also running the Profiler. I am therefore wondering if this could have impacted it in any way? Is it OK to run the Cockpit and Profiler at the same time?