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