Hello friends,
I want to find storage consumption on the tag history.
For example, if I log one tag in every one sec in the tag history, then that tag how much use of the storage memory of the tag history?
I want to know in detail the bifurcation of the storage consumption. (like per sec, per minute, per hour, per day, per week, per month and per year)
How to find these details in ignition?
You can refer to the following guide by IA to get your estimation per sec.
Ignition Server Sizing and Architecture Guide | Inductive Automation
1 Like
We have a weekly Ignition maintenance meeting (for our quite simple setup) and one of the things we check is the number of tags per hour being written to each of the historian tables.
SELECT a.tagid, COUNT(a.tagid) AS records, b.tagpath
FROM {schemaName}.sqlt_data_1_{tableYear}_{tableMonth} a
INNER JOIN {schemaName}.sqlth_te b ON a.tagid = b.id
WHERE a.t_stamp > (UNIX_TIMESTAMP(:dateTimeStart) * 1000)
AND a.t_stamp < (UNIX_TIMESTAMP(:dateTimeEnd) * 1000)
GROUP BY a.tagid
ORDER BY records DESC , a.tagid
LIMIT 0 , 100
The {tableYear}
, etc. are calculated from a datepicker value and the databases come via a dropdown selector.
Bind this to a Perspective table and you can quickly see anything that's recording an excessive number of readings per hour. This might be due to silly update rates or deadbands set too small on analog tags. As a result we reduced our DB requirements from 18 GB/month to < 1 GB/month.
Note that the query is using several {QueryString} variables. Make sure you understand the risks and sanitise them if you have any concerns about SQL injection. There are many posts on the topic on the forum.
Funny story ...
Using this application we found that one of the machines was switching between run and stop 1200 times per hour. Investigation revealed that instead of monitoring the machine's RUN signal the tag was looking at the RUN indicator lamp which was blinking continuously for about eight hours overnight when there was no operator available. We edited the tag path and solved the problem.
4 Likes