Most Stable Ignition & SQL DB InfrastructureSetup

Ignition
1

Hello, I am about to start a project to upgrade Ignition 7.9 to 8.1 (Redundant System), that used MySQL replication. The replication always caused issues over the years, and I now think the architecture would be better with 2 dedicated servers for Ignition Primary & Backup with a 3rd server for MySQL DB (with periodic data backups).

Has anyone experience to confirm/contradict the thinking above?

Also, has anyone ever used MySQL where is has stored a huge text file in addition to the actual DB Table data? For example, the DB Tables data is approx 60GB, however the text file is >400GB, but the text file hasn't been updated in over 2 years.

2

Are you saying that you want to store a 400 gigabyte text file as a blob in a database? I store a lot of reference materials as blobs that are sometimes accessed via Ignition, but I have to split the files up into chunks to avoid java heap errors. Even using long blobs without buffer restrictions, you would have to split a 400 gig file into 100 pieces to store it. When retrieving and reassembling large files, I've found that anything over 50 megabytes is impractical for daily use. and it is preferable to simply store a path on the database that can be used to link to the file on a network server.

3

What sort of data is in the file? How is the text file used?
It seems to me that you could probably convert the text file to a database table using paragraph breaks as end of record markers.

| id | paragraph                |
|  1 | Sentence 1. Sentence 2   |
|  2 | Sentence 3.              |

It would still be searchable, editable and the original can be regenerated|. The only problem I can think of is if you need to insert a paragraph in the middle.

Addition of a second table to keep track of backups with, say,

| Timestamp | StartRowId | EndRowId |

would enable tracking of incremental backups and minimise disc storage.

4

The file is so big currently I can’t open it on the server. The fact it hadn’t been updated in over two years though suggests it’s not an active resource. I’m going to copy the DB table and set up the system in a new environment and see does everything work ok.

Can these text files be used by Ignition to read data from, and why would you use them, how are they generated?

From what I can tell, all data since system stated is still in the DB tables.

5

Use tools that don't read the whole thing. head -n 100 /path/to/my/huge/file perhaps. (That shows the first 100 lines.

6

Has anyone experience to confirm/contradict the thinking above?

Yes, I'm trying to understand what redundancy you're trying to achieve; and as importantly what redundancy the original deployment was trying to achieve.

Then: what does this have to do with the SQL DB, for which replication was allegedly used, but which isn't discussed in your post?

Also, has anyone ever used MySQL where is has stored a huge text file in addition to the actual DB Table data?

I have to admit, I'm unduly exercised to know what's in that file. But just like I don't have a mustache, it's not important. What is probably important is what process created that file. The claim is made that it's a text file; in my world no claim like that would be entertained without some explanation; if it was a textfile then it's puzzling why examination of its contents hasn't yielded clues as to its origin. I don't care how big it is: are you going to keep it around? If so how are you going to justify that decision to privacy and security stakeholders without understanding what's in it? Why do you claim it was created by MySQL?

Where is the MySQL replication instance? Is that file, mayhaps, a replication log that got so out of control it can't be replayed to bring the new repl into sync? If so, that was a mistake. But was it the cause of the complaint that "replication always caused issues"? Or is that a network or hardware reliability issue?

In general, replication (of the contents of a DB) involves replaying, in order, the commits which altered the contents.

In my world ((service + backing store) * 2) where the service and the backing store are cross mirrored so that the service is primary on one instance while the backing store is primary on the other suggests that there has to be a plan for load shedding if they both need to fail over to one or the other instance; a third node suggests tie breaking for a cluster.

By the way, for single writer or (mostly) read only loads, MyISAM gets an undeserved bad reputation. I say this as somebody who worked with RDB before Oracle bought the Koda engine, and long before anyone ever thought of MySQL, and they in turn thought of InnoDB.