mysql amount of data per table

Question

Hi:

I'm designing a system, and by going deep into numbers, I realize that it could reach a point where there could be a table with 54,240,211,584 records/year (approximately). WOW!!!!

So, I brook it down & down to 73,271,952 records/year (approximately).

I got the numbers by making some excel running on what would happen if:
a) no success = 87 users,
b) low moderated success = 4300 users,
c) high moderated success = 13199 users,
d) success = 55100 users
e) incredible success = nah

Taking into account that the table is used for SELECT, INSERT, UPDATE & JOIN statements and that these statements would be executed by any user logged into the system hourly/daily/weekly (historical data is not an option):

Question 1: is 2nd quantity suitable/handy for the MySQL engine, such that performance would suffer little impact???

Question 2: I set the table as InnoDB but, given the fact that I handle all of the statements with JOINS & that I'm willing to run into the 4GB limit problem, is InnoDB useful???

Quick overview of the tables:
table #1: user/event purchase. Up to 15 columns, some of them VARCHAR.
table #2: tickets by purchase. Up to 8 columns, only TINYINT. Primary Key INT. From 4 to 15 rows inserted by each table #1 insertion.
table #3: items by ticket. 4 columns, only TINYINT. Primary Key INT. 3 rows inserted by each table #2 insertion. I want to keep it as a separated table, but if someone has to die...

table #3 is the target of the question. The way I reduced to 2nd quantity was by making each table #3's row be a table #2's column.

Something that I dont want to do, but I would if necessary, is to partition the tables by week and add more logic to application.

Every answer helps, but it would be more helpful something like:
i) 33,754,240,211,584: No, so lets drop the last number.
ii) 3,375,424,021,158: No, so lets drop the last number.
iii) 337,542,402,115: No, so lets drop the last number. And so on until we get something like "well, it depends on many factors..."

What would I consider "little performance impact"??? Up to 1,000,000 records, it takes no more than 3 seconds to exec the queries. If 33,754,240,211,584 records will take around 10 seconds, that's excellent to me.

Why don't I just test it by myself??? I think I'm not capable of doing such a test. The stuff I would do is just to insert that quantity of rows and see what happens. I prefer FIRST the point of view of someone who has already known of something similar. Remember, I'm still in design stage

Thanks in advance.

Answer 1

Start at the level you're at. Build from there.

There are plenty of people out there who will sell you services you don't need right now.

If $10/month shared hosting isn't working anymore, then upgrade, and eventually hire someone to help you get around the record limitations of your DB.

Answer 2

54,240,211,584 is a lot. I only have experience with mysql tables up to 300 million rows, and it handles that with little problem. I'm not sure what you're actually asking, but here's some notes:

• Use InnoDB if you need transaction support, or are doing a lot of inserts/updates. MyISAM tables are bad for transactional data, but ok if you're very read heavy and only do bulk inserts/updates every now and then.

• There's no 4Gb limit with mysql if you're using recent releases/recen't operating systems. My biggest table is 211Gb now.

• Purging data in large tables is very slow. e.g. deleting all records for a month takes me a few hours. (Deleting single records is fast though).

• Don't use int/tinyint if you're expecting many billions of records, they'll wrap around.

• Get something working, fix the scaling after the first release. An unrealized idea is pretty much useless, something that works(for now) might be very useful.

• Test. There's no real substitute - your app and db usage might be wildely different from someone elses huge database.

• Look into partitioned tables, a recent feature in MySQL that can help you scale in may ways.

Answer 3

There is no 4Gb limit, but of course there are limits. Don't plan too far ahead. If you're just starting up and you plan to be the next Facebook, that's great but you have no resources.

Get something working so you can show your investors :)