When users of your ClearSCADA system search the Events List, ClearSCADA loads the event data files required for the searches into the Event Journal Cache. On an efficient system, the event data files are loaded into the Event Journal Cache with no effect on system performance. However, if a system’s Event Journal Cache is unable to contain the event data files required for current searches, the performance of the Events List and Queries Lists can be affected and they will run noticeably slower.
If the Event Journal Cache becomes full, ClearSCADA has to remove some event data files from the cache to make space for the next files to be searched. The earliest event data files that were loaded into the cache are removed to make way for the latest event data files.
There are several potential causes for the Event Journal Cache to become full:
- The size of the Event Journal Cache is insufficient for the type of searches being performed In ClearSCADA
- The searches being performed in ClearSCADA are covering an inappropriate time span (and so require a large number of event data files or very large event data files to be loaded into the Event Journal Cache)
- An excessive amount of events are being generated due to inappropriate or incorrect configuration
If your Events List is affected by slow performance, you need to determine the reason for the Event Journal Cache becoming full:
- Run an SQL query on the event data files to calculate the total number of event data files being generated each day:
- In ViewX, display any Queries List (see Display a Queries List in the ClearSCADA Guide to Lists).
- Right-click on the List.
A context-sensitive menu is displayed. - Select the Edit Query option.
The SQL window displays the List's SQL Query. - Replace the existing SQL Query with the following SQL:
SELECT {FN TIMESTAMPDIFF( SQL_TSI_DAY, StartTime, CURRENT_DATE) }, SUM(RecordCount)
FROM CDBEventFile
GROUP BY {FN TIMESTAMPDIFF( SQL_TSI_DAY, StartTime, CURRENT_DATE ) }
ORDER BY 1
- Select the OK button to run the new SQL Query.
The Queries List displays a list containing 2 columns.
Each row represents a single day and the number shown indicates the actual day with 0 being today, 1 being yesterday (1 day in the past), 2 being 2 days in the past and so on. There are only rows for those days on which events were recorded.
The right-hand column contains the sum total of events that were generated on each of the days. For example, if the 0 row has a Sum of 54, it means that 54 events have been reported during the present day.
- Use the Sum totals to estimate the amount of events that are recorded in a 'normal' day. We will refer to this amount the 'average daily events total' (although it may not be a calculated average).
- Use the 'average daily events total' to calculate the required cache size:
- Multiply the 'average total' by 768 (the size of an individual event record in bytes). The result of this calculation is the amount of space needed in the Event Journal cache when a user searches 1 day’s worth of events. We will refer to this amount as the '1 day cache size'.
- Now that you know the size of cache that is required for a search of 1 day’s worth of events, consider the size of the Event Journal searches being performed by users on your system. If they are mostly performing searches on 1 day’s worth of data, you already know the required cache size; 2 day’s worth of data and you need to double the required cache size, a week’s data and you need to multiple the cache size by 7 (and so on).
- Compare the calculated required cache size to the actual cache size that is defined in the Server Configuration settings (see Define the Historic Data Cache Size).
- You will probably see that the configured Cache Size is insufficient and needs to be increased.
- On 32 bit systems, the total size of the Event Journal and Historic caches combined cannot exceed 400MB and so there is a limit to the amount by which you can increase the cache size (see Define the Historic Data Cache Size).
- On 64 bit systems, you can increase the Event Journal cache to any size as long as the server has the appropriate amount of RAM available. If there is insufficient RAM, you can upgrade the server to have increased RAM or proceed to step 5.
If increasing the Cache Size for the Event Journal does not improve performance, or you are unable to increase the Cache Size to the required amount, proceed to step 5.
- Check that your server hardware meets the system requirements (see ClearSCADA Server Requirements in the ClearSCADA Installation Guide). You may need to upgrade your server or look at ways of reducing the size of your database.
If your server hardware meets the system requirements, proceed to step 6.
- If your system has a large database of over 100,000 database items or is recording a high volume of events, you may be able to improve performance by adjusting the Stream Size setting for the Event Journal (see Define the Stream Size for Maximum Performance).
If changing the Stream Size does not improve performance, proceed to step 7.
- You need to reduce the number of events being recorded or reduce the size of the searches being performed (see Excessive Number of Events).