Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

Question

I need to design database to store information contained in millions of log files generated by devices on trial on network.

I have designed 5 different tables to store log file information. But, as the files are very large in number I may fall short of memory on maximum table size. Does anyone know maximum table size limit. I am using MYSQL database on Windows 7 (NTFS) All tables are of type InnoDB.

Thanks for your time

Answer 1

If you enable MySQL with innodb_file_per_table, each InnoDB table will have its own dedicated tablespace. The system tablespace file, bwetter known as ibdata1, will grow very slowly.

If innodb_file_per_table is disable, everything and its grandmother goes into ibdata1. That will grow quickly.

If you are not using innodb_file_per_table, you need to rearchitect InnoDB's infrastructure. This will give you a limit of 2TB per table instead of 2TB for all InnoDB. This is the case for ext3. On ext4, the table size can be up to 16TB.

Please see my past posts on how to do that

• https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 (Oct 29, 2010)
• https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306 (Mar 09, 2011)
• https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111 (Mar 11, 2011)
• https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400 (Feb 04, 2011)
• https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233 (Mar 29, 2011)

Answer 2

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

Have a look at the link for your reference Limits on Table Size.