skip to Main Content
Oversized Crestron Fusion Databases And Restarting Services

Oversized Crestron Fusion databases and restarting services

We recently had a case with a customer of ours where the Crestron Fusion database grew to rather large proportions in a short time. On top of that, apparently the Fusion room booking panels became unresponsive at regular intervals which caused a lot of grief for the users trying to book a room. We discovered rather quickly that the latter problem was caused by the Fusion services restarting (and recovering) nearly every minute.

With information we got from the customer’s IT department, it became aparrent that the size of the database was primarily dictated by two tables: CRV_AttributeLog and CRV_SignalLog. On our own Fusion install, we prepared a few SQL queries to investigate what caused the database to grow to that extend and then went on site, well prepared to further debug on the customer’s installation.

NOTE: Logging into the Crestron Fusion database should be done with the greatest caution. We don’t support altering any of the records in te database, since the smallest change could seriously affect your installation.

Also note that in this particular case, we provided our services in installing, configuring and maintaining Fusion, but were not tasked with programming the rooms. So, although, similar problems might better be solved by debugging from within the Crestron program, this article explains our approach from the Fusion side.

 

To determine which attributes in the AttributeLog were recurring most frequently we issued the following query against the customer’s Fusion database:

SELECT [AttributeID] ,count(*) as Counted
  FROM [database].[dbo].[CRV_AttributeLog]
  GROUP BY AttributeID
  ORDER BY Counted DESC  
GO

which provided us with the following result

ROOM_OCCUPIED 25978823
DISPLAY_POWER 6867
SYSTEM_POWER 6470
ONLINE_STATUS 5354
DOCKING STATION 2954
HDMI 1038
BROADCAST_MSG_TYPE 198
SYSTEM STARTUP VGA 79
SYSTEM STARTUP HDMI 59
SYSTEM STARTUP DOCKING STATION 23
DISPLAY_USAGE 15

You can easily tell the “Room_Occupied” attribute was being logged at extreme rates. FYI, this log was taken approximately 6 months after install.

 

To determine which rooms this attribute was being logged for we used:

SELECT RoomName, COUNT(*) as Counted
  FROM [database].[dbo].[CRV_AttributeLog]
  LEFT JOIN [database].[dbo].[CRV_Rooms]
  ON [database].[dbo].[CRV_AttributeLog].RoomID=[database].[dbo].[CRV_Rooms].RoomID
  WHERE AttributeID='ROOM_OCCUPIED'
  group by RoomName
  order by Counted DESC
GO

There was no apparent difference between the amount of times each room logged this attribute, but a handy query nonetheless for future use.

We discussed our findings with the customer and agreed on the fact that we don’t need logging of the occupancy sensor itself and simply disabled logging for that attribute to prevent the database from further exessive growth.

Problem 1 solved!

Still, however, the Fusion services restarted at regular intervals. We then decided to delete the exessive records from the AttributeLog and SignalLog tables to erase the approximately 26 million “Room_Occupied” records we didn’t need in the first place.

Immediately we saw this resulting in the Fusion service being stable and the installation runs fine ever since.

 

Another job well done!

Let us know if you have any problem regarding such problems or Fusion in general. We’re happy to support you with your Fusion installs and Crestron programming.

 

 

EXTRA, EXTRA!

To get similar information from the SignalLog table with an overview per Attribute, Symbol and Room, you can use the following query

SELECT [database].[dbo].[CRV_SignalLog].[SignalID], [AttributeID], [SymbolName], [RoomName], COUNT(*) as counted 
  FROM [database].[dbo].[CRV_SignalLog]
  LEFT JOIN [database].[dbo].[CRV_SymbolSignals]
  ON [database].[dbo].[CRV_SignalLog].SignalID=[database].[dbo].[CRV_SymbolSignals].SignalID
  LEFT JOIN [database].[dbo].[CRV_Symbols]
  ON [database].[dbo].[CRV_SymbolSignals].SymbolID=[database].[dbo].[CRV_Symbols].SymbolID
  LEFT JOIN [database].[dbo].[CRV_Rooms]
  ON [database].[dbo].[CRV_Symbols].RoomID=[database].[dbo].[CRV_Rooms].RoomID  
  GROUP BY [database].[dbo].[CRV_SignalLog].[SignalID], AttributeID, SymbolName, RoomName
  ORDER BY counted DESC
GO
This Post Has 4 Comments
  1. Hello!
    Thanks for the post.
    a) The anrealistic amount of Occupancy changes is a BUG currently under investigation. So, at least for this, dont blame the programmer! 😉
    26 Mio occ-changes within 200 days = 130.000 changes within a single day! Even with 1000 rooms that would be more than 100 changes any given day, right?!

    This bug happens on some but not all installations. Annoying like hell!

    b) The restart of the services is most likely due to running out of memory. This is highly likely when the SQL server is colocated on the same server as the Fusion services. Do yourself a favour and set the max memory
    See here: https://technet.microsoft.com/en-us/library/ms191144%28v=sql.105%29.aspx

    People tend to forget that by default SQL Server is allowed to grab all memory.

    Now add a) and b) together and SQL server WILL grab all memory. As the occupancy table gets rapidly bigger SQL thinks it must ramp up its memory usage.

  2. Pls do note, that disabling the logging of the Occupancy changes does also disable the most valueable report of all there are.
    So dont consider this a solution but rather a short term medicine.
    🙂

  3. Hey Harald,

    Thanks for the insights!

    a) Calculated from the time Fusion had been installed and the amount of changes. We see every motion sensor in this installation being logged approx. once evey minute. With 60 rooms, that’s every single second.

    b) The services run on the server as SQL indeed. Against recommendations… I know. The installation will soon (hopefully) be added to the companies production SQL environment. We’ll advice to use 2 server then.

    Regarding the reports being disabled. That had me worrying indeed. We wanted to give it some time first to then see how the reports are affected. To your findings, does it affect “Room Occupancy” and/or “Room Utilization”?

  4. ad b)
    Although Support strongly recommends 2 servers I personally dont agree at all! If there is already a SQL at the customer and we are allowed to use it, then go for it pls, BUT 2 different servers just for Fusion?? No way in my world.
    Given the power of current hardware there is simply no need to have multiple servers. Nothing within Fusion is CPU intense (besides Capture transcoding if used!), so simply adding some cheap RAM will get you many happy faces. Setting the SQLmaxMemory to around 8 GB in a 32GB server should leave plenty of RAM available to the Fusion processes.
    This is also within the best practice of Crestron itself!

    Tip: There is NO reason for SQL server having access to more than 1.5 times the memory size of the Fusion db itself. You will find hardly a Fusion DB being bigger than a few GB (for typical installs!) so 8GB should do it easily in almost all installs!

    VERY good real life info here:
    http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

    Room Occupancy simply looks into the occupancy signals, so you will see SQL look through millions of buggy table entries.

    Room Utilization
    has to do with the SCHEDULE of the room. Not quite sure how the “need to CECK IN in order to use the room” feature has some influence.
    Based on the speed of the report it does NOT use the occupancy table at at all.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top