0

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.

1

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?

  • 1.There are no alarms. 2. There is no auto_increment as well. 3. Around 35% of the allotted space is used. 4. Mysql is on Google Cloud. It is not a Data Warehouse application. – Michael Massey May 13 '15 at 10:14
0

Maximum row count is depend on type of field used for primary index.

In case of unsigned bigint table can be 18446744073709551615 rows long.

  • BIGINT is not the first limit to hit. – Rick James May 11 '15 at 16:12
  • @Kondybas The primary index is a combination of few columns (int, int, date, time). So, will it still depend on their combination. – Michael Massey May 13 '15 at 10:06

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.