Project - Running MemSQL on Windows in Docker

Help
1

Hello,

Here is my problem breakdown:
Environment:
I have a production environment where I have measurements and tests results. Currently, this data is stored in XML files and I would like to switch to a database.

  • The production environment has about 20 to 25 Windows computers all doing the same thing.

Project:

  • I would like to create an application to be able to have a dashboard of what’s going on in real time, thanks to a distributed database.
  • Another part of the software will generate statistics, less often.

Constraints:

  • I am required to use Windows 10 as a base OS.
  • Database has to be on-premises.
  • The computers are below MemSQL hardware requirements (2*3.70GHz, 4vCPU, 8 Gb RAM, 512 Gb SSD)

Misc:

  • Workload is quite low (about 100 000 000 rows * 20 columns per year)
  • There may be 15 minutes of downtime and then 10 000 rows to be inserted in less than a second.

Now onto my questions:

  • Given the workload and the fact that I need to run a distributed database, is MemSQL a good fit for this purpose ?
  • I am currently trying to run MemSQL in a Docker image with CentOS as a base image. I need that for the containerized property but also to be able to export and update the system across all computers quite easily. Does this sound doable or is this just asking to run into problems ?
  • What mechanism would you recommend to make sure that the data from over a year ago doesn’t impact the database performance ? (the query that will be run the most is about 300 000 rows)

Thank you for your answers !

2

Hi Francis

Thanks for coming to the MemSQL forums!

Reading your question, it appears real-time analytics is your main priority, with generating statistics as a secondary use case.

MemSQL is very strong in what’s known as operational analytics – the combination of doing real-time analytic processing (using our in-memory rowstore) and day-later analytics (using our disk-based columnstore). So yes, MemSQL will be a great database for your use case.

MemSQL requires at-minimum of 8 GB of RAM to run effectively. Judging by your system’s specs, you JUST meet the requirements. And since you’re on Windows, we actually don’t recommend running a production system on Windows via Docker. It’s more advised to run it on Linux. Also, with your data volume, MemSQL will fully handle your workload. However, you can still give MemSQL a try to see if it can handle your workload.

While you’re doing this, feel free to come back to the forums to ask your questions!

4

Thank you for your answer Jacky.

Does any documentation exist on how to perform the transfer between a “temporary” rowstore to a consistent columnstore in an automated manner ? Or maybe could you please give me some insight about that ?
How does the rowstore handle power outages if it is in-memory ?

Unfortunately, running Windows 10 is not a choice but a constraint in my case, so I have to keep testing in that direction. It could be an interesting experience I guess.