For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance.

The Synology is great for managing things like RAID arrays and automated backups, but it doen't have a particularly impressive CPU. I also use it for all sorts of other purposes, so I don't want to burden the CPU too much.

I am also building a web server (actually a Shiny Server) on an Ubuntu machine to serve a web page of some analytics on top of the data. I want part of these analytics to be calculated on demand by a stored proc. However, I want the stored proc to use the CPU resources of the Ubuntu machine. But I want the data to be managed and backup up by the Synology.

So my plan is to have a DB in the Synology for storing the data, and have another DB with federated tables in the Ubuntu. The stored proc runs on the Ubuntu. The stored proc only reads data from the Synology, transforms it, and returns it to the caller. It doesn't write anything back to the Synology.

Does this seems like a good idea? How would you do it?

  • For the last 20 years, CPUs have only slightly improved in performance.
  • MySQL rarely tops out a CPU these days. When it does, there is usually an index that can be added or some SQL changed to make it quite down.
  • Hence, I suggest that the CPU you have is not that bad.

"Calculating analytics on demand" is a common problem in Data Warehousing. There is a common solution: build and maintain Summary table(s) You can still use a Stored Proc to interface with those table(s) to provide the analytics, but many times as fast (elapsed time).

Federated? Ouch. That can be terribly slow. There are cases where fetching each row is a roundtrip to the other machine. Consider connecting to the other machine(s) and querying them directly.

Once you have gotten further in your design, start another Question for further advice. Please provide data sizes, RAM and disk sizes, what the analytics are, schema, etc.

In particular, be sure to minimize disk footprint from the start. Don't use BIGINT except in rare cases. Don't use DOUBLE; pick something smaller, normalize 'names', etc.

Now you have me worried that you are at the mercy of whether Synology has the smarts to follow the above hints??

  • FEDERATED connected table concept will CRUCIFY any machine when you have more than a FEW rows of data. EVERY access requires moving ACROSS the interface EVERY ROW every contact. Your SLOW QUERY LOG will confirm this attribute of FEDERATED connected tables. So BOTH CPU's and the NETWORK interface have to move EVERY ROW through the hardware for EVERY QUERY. Not something you EVER want to DO in production. – Wilson Hauck Feb 18 '19 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.