Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Do you have innodb_file_per_table enabled ??? –  RolandoMySQLDBA Jul 24 '12 at 22:31

3 Answers 3

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

share|improve this answer
    
Thanks for your time and help. Your answer gave me some pointers to move forward in my quest to design a data storage system :) –  mani sharma Jul 25 '12 at 10:37
    
Updated my answer to reflect ext3/ext4 –  RolandoMySQLDBA Jul 25 '12 at 21:03

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.

share|improve this answer
    
I had also gone through the same link. Infact, this was the link which confused me. According to the table listed in that link Maximum table size should be 2 TB for Windows NTFS. Just below that table there is one more line which says "The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This enables a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which permits extremely large tables. The maximum tablespace size is 64TB" Is tablespace and table one and the same thing ? –  mani sharma Jul 24 '12 at 11:40
    
The manual dev.mysql.com/doc/refman/5.5/en/create-tablespace.html says that tablespaces can only be used for storage engine NDB and NDBCLUSTER –  miracle173 Jul 25 '12 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.