Hi, during the product launch presentation for a Ignition, I noticed a new feature where SQLTags were given a Historise function. Presumably these new tags can be added to charts and trends.
I would be interested in knowing a little more detail about this method of keeping tag history, versus the traditional method where you set them up in FSQL and they get stored in the database.
Is there any compression or other fancy tricks going on when you Historise the SQL Tags?
Thanks.
The only “compression” going on in the SQLTags Historian feature is that the tags are stored on-change, on a per-tag basis. This is significantly more efficient from a storage standpoint than a Historical Group, because even when you put a historical group in async mode, it will log all items when any one of them have changed.
You can learn more in the user manual the “How SQLTags Historian Works” section under Gateway Configuration > SQLTags would be a good place to start.
Hope this helps,
Use archive tables if you want compression, but honestly, now that MySQL is prepping to go gold, and the partitioning now works on raw dates, just partition by days or week even. If you go by day, you still get ~6 years before you hit the magical 2048 max partitions per table limit.
SQLTags Historian automatically partitions, regardless of the underlying database.
Indeed, Ignition has a partition feature that will create new data tables based on time. Not quite comparable to MySQL’s partitioning, but the rough idea is the same, and it works with any database.
Also, timestamps on historical data are stored as LONGs, since we found that that offered better index performance.
Indeed it would be possible to convert the data tables to the Archive engine in MySQL. Sometime along 7.1 Ignition will offer the ability to specify addition parameters on table creation for history, which will let you do that automatically. It should also offer some new maintenance tasks, like taking old partitions and merging them into an archive table.
Regards,
I havent tested historical sqltags yet, but good to know.
Kyle Chase or moderator,
Can you post a step by step for hooking ignition to mysql in Ubuntu 9.10?
I have installed the mysql server from the repository and mysql-admin.
I created a user and password in mysql-admin for ignition.
I tried to connect ignition to mysql, but ignition says it is faulted.
I must have skipped something.
Can you give me a step by step?
Thanks
Dennis Womack
As far as I can tell you will have to create a database schema in MySQL before Ignition can connect - Ignition doesn’t create it for you.
Yeah you have to make the schema first. Also, does the user have the required privileges for the historical sqltags. Also, because you are using Linux environment (thats all I use too, so I can help anytime, just PM me), you need to know that when you reference a table, it is case sensitive. Outside of that, Id have to take a look.