Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Converting to MySQL pros/cons?
-
Hi all, am heavily considering switching to an external Mysql DB instance due to problems with the H2 database datapoints getting corrupted (causes http 500 errors just from trying to load datasources) from time to time. That and the fact I want some means of redundancy for datapoint values being stored - would rather not keep all my eggs in one basket and backing up files won't be enough for me.
As I'm doing my research however, I'm a bit conflicted due to the possible large database size in the mid to long term. Not so much in the order of TB's (more likely several hundred GB's) but how the size will impact speed in mangoUI also.
IMO having mysql provides a bit more freedom in replicating data because of the structured format - it's consistent. And that I could utilise external MySQL servers for redundancy.
I'd like to know what others have found to ensure data is consistent and up to date should they have a server fault or anything unexpected comes up and throws everything out of kilter.Thanks!
Fox
-
Hi Fox,
I would say MySQL has a fair collection of advantages, and few drawbacks.
Pros:
- The "missing lob" issue doesn't exist.
- There're tons of tools, and I'm sure more integrations.
- You can specifically control resources available to the db
Cons:
- MySQL could crash independently of Java, so you have to watch for that (but errors will abound).
Mango can still do MySQL backups (mysqldump and zip it), so you can keep that to ensure if everything gets out of kilter somehow it's recoverable.
If you're using the NoSQL module, only the events, userEvents, and audit tables are likely to cause database expansion in your SQL database. Your JSON store / data points / data sources / users all take some effort to get gigantic. So, I wouldn't worry too much about the database getting large. Even if it does, the same procedure can work in MySQL as H2 to shrink the database: purge the large tables, dump the SQL, stop the database, move it's data file, start the database, restore the dump.
That and the fact I want some means of redundancy for datapoint values being stored - would rather not keep all my eggs in one basket and backing up files won't be enough for me.
I would suspect you are using the NoSQL module. If so, your point values are stored in Mango/databases/mangoTSDB by default, and your SQL's pointValues table should be empty (unless you've started without NoSQL). Generally this means the SQL database isn't under a lot of pressure from acquiring data, as writes go to NoSQL and the points only need to be loaded when they or their data source is enabled.
I have not had experience running MySQL with a redundancy, as explained the SQL table isn't generally in great flux when NoSQL is used, nor is it the provider of what is often of the most interest: the data. I've found file backups to be sufficient.
-
I have not had experience running MySQL with a redundancy, as explained the SQL table isn't generally in great flux when NoSQL is used, nor is it the provider of what is often of the most interest: the data. I've found file backups to be sufficient.
So are you saying that MySQL is not used to hold point data full stop? Or is this in the instance that the NoSQL module is used?
-
The latter. If the NoSQL module is installed, MySQL is not used for pointValues
-
Finally, (figured it would be quicker to ask direct)
Are there any items/settings/configs that are not stored/migrated when converting to a MySQL DB? -
No. The only other thing that you need to keep in mind is you can't convert and upgrade databases in the same launch. If you are going to update the core, do that before you convert the db (separate Mango startups).
-
Noted!
Thank you very much for your help Phil!
-
This video is worth watching to better understand the NoSQL Database https://help.infiniteautomation.com/enterprise-nosql-database/
For clarification, Mango uses both the SQL (H2 or MySQL) and the NoSQL at the same time. You can, however, remove the NoSQL module and store all your history in MySQL or H2 but you will suffer very larger performance hits as MySQL just can't come close to comparing with the speed on our NoSQL.
This is another interesting article comparing the Mango NoSQL database to a couple other popular ones: https://infiniteautomation.com/large-scale-mango-installation/
As already discussed for larger systems using MySQL over H2 is a good option.
-
Thanks @JoelHaggar, I knew I'd take a bit of a performance hit but seeing as a lot of our live values are reporting every few minutes to half an hour, I want to believe speed won't be too much of an issue. Thank you all for your input and advice. This forum really is an excellent source of information.
