MYSQL Row Count Limits?

Question

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.

Answer 1

I have compiled 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?

• Michael Massey: 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.

Answer 2

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

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

• Rick James: BIGINT is not the first limit to hit.
• Michael Massey: @Kondybas The primary index is a combination of few columns (int, int, date, time). So, will it still depend on their combination.