1

I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a definitive answer on what specs I should be aiming for.

I'm building an application which collates data from ~8 vendors and brings all the sales data into one location for reporting purposes. As a base line, each user will end up with about 2.5million rows in my sales_report table over the next 6 years. I'm calculating based on around 3000 users using the service.

I'm in the dev stage so only have access to 200k rows for now but it's a relatively solid base to work from as it's live data. 200k rows in this table is around 40mb which gives me ~1.5TB table size over the next ~6 years. The rest of the data in the DB is more trivial so we're probably looking at about 2-3GB total.

The sales report table looks like this in terms of data (column headings missing): example row For the sake of indexing, lets assume I will need to query all these columns at some point but I will never need to look up more than 1 users rows in the DB so it'll be around the 2.5million rows for that user.

In terms of reads / writes for the data. The data, for the most part, is inserted via bulk inserts (with the exception being todays sales, as they update the row in the DB every 10 mins). A user will sign up, import historical data (~1million rows) and then mostly read that data via reports. A cron will update the sales data every 10 mins for each user logged in (let's assume 100 at any given time).

I'm using MySQL 8, InnoDB.

A DB of this size isn't uncommon as far as I can tell but it is outside of my experience so I'm trying to get clued up as much as possible. At this point, I'm trying to ascertain a few things:

  1. Advantages of nvme over SSD for this size of data - I'm assuming nvme because faster is better when it comes to writes to the DB but I'm trying to work out how much better it is vs cost.
  2. CPU requirements?
  3. RAM requirements?
  4. Do I need replication?
  5. Am I just overthinking this and the size really isn't that much of a worry?

Most of the questions I find on trying to work out this stuff are based on live projects with stats to show on current usage. I don't have that so am trying my best to just get a good base line system in place. Money isn't the biggest concern so if we end up having spare resources, it's not a huge problem as we will grow into them.

I hope this question isn't too vague to garner some feedback. Let me know if I need to provide any information I've not already provided.

1 Answer 1

1

2.5M rows is only medium-sized, however...

1.5T / 2.5M ==> Rather large rows?? Is there some bulky column that we should consider moving elsewhere? Or am I missing something in the math?

The main configurable is to set innodb_buffer_pool_size to about 70% of available RAM. After that, have the slowlog turned on so you can catch naughty queries for further analysis.

3K users is probably not an issue. Presumably, they will come and there won't be more than a few actively querying the database.

Sales report -- Oh? is there an image of such in your Question? Please avoid using images. UI quibbles: Do you really need "0" to take up about 20 characters of width? Do you really need datetime shown to the millisecond?

2.5M rows for one user? Do you try to show all 2.5M rows to the user? Or do you have Summary Tables ?

Bulk INSERT -- Good. UPDATE one(?) row every 10 minutes -- let's discuss that.

Summarizing 2.5M rows every 10 minutes -- No. Summary tables eliminate that.

Your numbered 'questions':

  1. SSD, NVMe -- Let's improve the queries and the schema first. Then, I suspect you could get away with just an HDD.

  2. CPU -- Ignore. It is almost never an issue.

  3. RAM -- Proper use of Summary tables can lead to needing much less RAM than data.

  4. Replication -- Summary tables can avoid the need. However, you may want it as a backup and/or failover.

  5. Overthinking -- Yes and no. As stated, you are in trouble. But there are solutions.

You should strive to never need to read all 2.5M rows to deliver something to the user. In achieving that, most hardware needs are greatly diminished.

Stats on current usage -- need more details.

Loading a million rows and initially building the summary tables? That is a one time task, so the minutes (or maybe an hour) is acceptable. Maintaining the summary tables -- this is simply part of subsequent actions. "Reports" are always available almost "instantly"; no 10-minute delay.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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