3

Use case: A measurement creates a given number of images. For each image we need to store a small set of quality indicators (floats, doubles) along with an image integer [1 ...N], a timestamp and one or two foreign key values. This should then be plotted in "real time" in a web application (PHP) for users to evaluate.

Each web client polls the database every 5s. Storage + retrieval of each set of quality indicators should ideally take < 2s (approximately). In a worst case scenario there can be ~30 simultaneous web clients polling and around 10 measurements could be writing simultaneously, leading to write bursts of approx. 1000 sets of quality indicators per second.

In a programming language, this sort of data would probably be stored in arrays or lists. As I'm not aware of anything similar in the MariaDB / MySQL world I'm just using a regular InnoDB table with a column for each of the values mentioned above. This already has 90+ million rows and is expected to grow faster in the coming months.

Is InnoDB overall the best storage engine for this, or should I consider others? Is it best practice to archive data after a while, perhaps once all the measurements' images have been processed? Would it help to enable compression, or would that have very negative impacts on performance?

4

With just MySQL, I would employ:

Your last paragraph throws in the kitchen sink of questions (Toku, MyRocks, archive, compression, history table). I'm surprised the posting has not be killed for being "too broad". Please elaborate on what your data and queries are like; otherwise, all we can do is throw a kitchen sink full of solutions.

You say "realtime", yet you need "thousands/sec". Can you allow 1 minute delay in realtime? 1 sec? You cannot get 1ms; 1s will be hard to achieve. How long does a burst last? What is a per-minute burst? 1K/sec will probably spill into the next couple of seconds. 6K/minute is not much trouble.

How many clients are storing data? Some solutions work well with a single client; different solutions are needed for multiple clients.

Keep in mind that benchmarks are tuned to show off one thing, and rarely match real life.

  • Thank you very for your answer, much appreciated. I've edited the question to make it a little bit less of a kitchen sink, and a bit more specific in terms of requirements. At the same time I don't want to be too specific, as I'm more interested in a general answer, and we can to some extent modify the table(s) and queries to fit with best practices. – dbdemon Nov 15 '17 at 10:23
  • A thousand rows per second, each separately INSERTed gets dicey unless you have an SSD drive. 30 SELECTs per 5 seconds should not be a problem if suitably indexed. – Rick James Nov 15 '17 at 17:52
3

There are some big questions there that probably need closer examination than can be achieved here, as there are so many dependencies (realize that you know this!). There are a number of slide sets from presentations on the Percona Live and Percona Live Europe pages about time series that might help you get further down the track. For example, about using ClickHouse from Yandex

https://www.percona.com/live/17/program/schedule/time-series

https://www.percona.com/live/e17/program-open-source-databases

You might also find some of the blog posts interesting. This one looks at TokuDB versus InnoDB for a time series benchmark.

https://www.percona.com/blog/2013/09/05/tokudb-vs-innodb-timeseries-insert-benchmark/

Whereas this one looks at MongoDB and TokuMX https://www.percona.com/blog/2015/05/26/storing-time-series-data-with-mongodb-and-tokumx/

Hope these help.

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.