MYSQL Row Count Limits

google-app-engineMySQLmysql-5.5

What is the maximum row count for a table in MYSQL?

I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 bytes. Upon reaching how many rows should there be an alarm? They are intermittently used so archiving them will involve some additional work.

Which of the is advisable?
1. Archiving
2. Moving to big data
3. Leaving them as it is.

Best Answer

I have compile a summary of MySQL limits. Some, related to your question:

  • An InnoDB partition is limited to 64TB (2**46 bytes).
  • A MyISAM table is limited to 2** 56 bytes, but you need to change a setting to get beyond 2**48.
  • A table can have 1024 partitions until some recent release, then 8192.
  • (BIGINT is not relevant in these limits.)

Is there alarm?

  • Do you have an AUTO_INCREMENT? If so, you need to watch out for it overflowing. 600M is 28% of the way toward INT SIGNED and 14% of the way to INT UNSIGNED.
  • 58GB is big, but certainly not the biggest ever.
  • You did mention performance issues, so we probably don't need to discuss things that can go wrong with tables that big
  • Archiving -- Based on how fast the data is accumulating, when will you run out of disk space? It would be better to be concerned when the disk is 50% full -- this would leave room for an ALTER.

Is this a Data Warehouse application?

Related Question