What is the maximum SQL table size

Question

Hello,

I am wondering at which point would my MySQL table be considered too big.

The table is this:

• id
• customer_id (int)
• value (float)
• timestamp_1 (datetime)
• tmestampt_2 (datetime)

so the row size is not too great, but would be constantly being added. In my estimation I am looking at around 17000 new rows a day, so about 500,000 a month. The data is likely to be polled quite constantly in large quantities.

Should I be looking at ways to split this or am I still OK at this point?

Thanks,

Answer 1

From http://dev.mysql.com/doc/refman/5.0/en/full-table.html:

The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

From the table in the linked article, on FAT/FAT32 systems and Linux pre 2.4, the maximum file size is 2-4 GB, on all other systems listed, the max file size is at least 2TB.

So long as you index your table correctly, there shouldn't be too much slowdown as your table grows. However, if your table grows to the extent that you do notice any slowdown, it might be an option for you to archive off old records periodically.

Answer 2

It would be too big when your query starts to slow down.

Do you need to keep the entire history in this table or are you only ever looking for the latest values? You could optimise things by archiving off records you don't need onto an archive table.

Other than that, be careful how you plan your indexes. If you put indexes all over the place, inserts may take longer. If you don't have any indexes but need to sort and filter, the retrieval may be too slow.

Answer 3

What is "Too big" is really going to depend on how big your hardware is. MySQL itself should have no problem managing millions of rows in a table.

Still, I would think about splitting it up to get the best possible performance. Exactly how you do that would depend on how the data is used. Is more recent data used much more frequently? If so, create an archive table with the same structure to store the old data and periodically move data from your main table to the archive table. This would increase the complexity of your application, but could give you better performance in the long run.

Answer 4

In MyISAM, the theoretical table size is constrained by the size of data pointer, set by myisam_data_pointer_size.

It can be from 2 to 7 bytes, making the possible table size to be from 2 ^ (8 * 2) = 65k to 2 ^ (8 * 7) = 64P bytes long.

By default, it's 6 bytes (256T).

Of course, since MyISAM tables are held in one file, the maximum size of the file is subject to constraint by the OS and the filesystem.

An InnoDB tablespace can consist of as many as 2^32 pages, which is 4G pages of 16K bytes each, or 64T bytes at most.