This is my very first question on the StackExchange/StackOverflow community. I originally am an electrical engineer so I hope you bear with me.
I need to set up a database whose purpose is to be interfaced with a software tool that will be created later on. I only have little experience in databases with MariaDB running on a NAS server and quite simple queries through C# software.
Here is my problem breakdown:
- The environment is a production line where I need to save the tests and measurements results into a database.
- The data is mostly strings, there's also bits, integers, floats and timestamps.
- Currently, the data is saved into XML files. There are between 15 to 25 test stations per workshop, each station having a separate PC. Each station creates one XML file per part, with the exact same structure (header + results, the results portion has identical fields across ~320 lines), so we would like to have one database that is common to all stations of a given workshop.
- Each station has an Intel Core i3-6100 CPU @ 3.70 GHz (2 cores, 4 threads, supports Type 1 virtualization), 8 Gb of RAM, 500 Gb of SSD storage, running Windows 10. Same PC also runs all the measurements.
- The database needs to store about 100 000 000 rows * 20 columns each year. While this doesn't sound like much, this number may evolve in the future and, the tricky part is, the 100 000 000 rows won't be inserted at a constant interval. There might be 15 minutes without anything happening and then 10 000 rows inserted in less than a second.
- The main software functionality will be a dashboard of the production line. So the database has to make the information available to the software in a very short amount of time (one hour in the worst case), to be able to take the countermeasures soon enough. Same software should be able to look for statistics on a sample of data (for example all the parts tested and measured between two dates, or taking all data from last 5000 parts, which would equal to about 1 600 000 rows in this precise case, or look for a specific test step and see what the statistics are on this precise step of the process)
- This system has to be scalable to put up with higher loads in the future, although I think this is solved by the fact that the database has to be shared across ~20 nodes.
- The software will be written in C# language, and I would probably use ODBC to link to the software, so it would be best if the database system supports it
- The data will have to be stored for years. Once we do not need the data anymore in the production stations, it will be moved to a dedicated storage solution (and I have no idea how to achieve this).
Now onto the research I made so far:
- Most database management systems I considered to be an option (MemSQL, Cassandra, Postgre-XL) appear to run on Linux so I will have to use a virtual machine, as our customer requires us to run Windows 10. Because of the performance concern, the VM either has to be running on Hyper-V or in a container like Docker (although it isn't clear to me if Docker is a suitable option to this problem). So is the hardware configuration I mentioned powerful enough ? (without regard to the fact that it already runs the measurement software)
- I found thre generations of database management systems: SQL, NoSQL, NewSQL. To me, it looks like a NewSQL database management system would be the best fit because of the scalability and ACID compliance. However, an SQL database management system that supports sharding, replication and scalability could do the trick. It would be best if any changes to the structure could be done once on a test machine and then applied easily to thousands of PCs around the world. Is one type of database management system better than the other to be able to do so ?
- Column store seems to be the best storage solution for my problem, but I don't know if I'm right or wrong about this. Any thoughts ?
- If anyone uses MemSQL or Postgres-XL, I would like to know the level of performance you achieved please.
To sum it up, I would like to know, considering the experience you may have and my description, what database management systems would best solve my problem.
Thank you for your time, patience and help. And I apologize if you found my English weird.