Database Design - Creating Multiple databases to avoid the headache of limit on table size

Question

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

My first approach was storing all the information in tables contained in a single database. But this approach seems to fail as the data to be stored is too large. So, I thought of creating separate database for each device and storing each device log files in separate database. My question is if this approach can spare me of the headache involved with table size limit. Below are given my platform specification

• Operating System - Windows 7
• File System - NTFS
• Storage Engine - InnoDB
• Table Type - InnoDB

I want to build my database design in a way so that I need not to apply shrink of tables procedures in future

Answer 1

In my answer to your previous question, I mentioned how the size of an individual table is 2TB when you use innodb_file_per_table. There may be a way to surpass the table limit by doing just the the opposite, leaving innodb_file_per_table disabled.

With innodb_file_per_table disabled, there are three ways you can go about this:

TECHNIQUE #1 : Use multiple system tablespaces in ext3

You could chain InnoDB tablespaces 2TB at a time. Start off with this

innodb_data_file_path=ibdata1:10M:autoextend:max:2048G

When ibdata1 is about to hit 2TB, add another ibdata file

innodb_data_file_path=ibdata1:2048G:ibdata2:10M:autoextend:max:2048G

When ibdata2 is about to hit 2TB, add another ibdata file

innodb_data_file_path=ibdata:2048G:ibdata2:2048G:ibdata3:10M:autoextend:max:2048G

On so on...

TECHNIQUE #2 : RAW DISK PARTITION

According to MySQL 5.0 Certification Study Guide, Page 428

Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

After changing the configuration, restart the server.

Just create a RAID10 Disk with whatever size you need. Just don't put any filesystem on it.

TECHNIQUE #3 : Use ext4 with TECHNIQUE #1

You could chain InnoDB tablespaces 16TB at a time. Start off with this

innodb_data_file_path=ibdata1:10M:autoextend:max:16384G

When ibdata1 is about to hit 16TB, add another ibdata file

innodb_data_file_path=ibdata1:16384G:ibdata2:10M:autoextend:max:16384G

When ibdata2 is about to hit 16TB, add another ibdata file

innodb_data_file_path=ibdata:16384G:ibdata2:16384G:ibdata3:10M:autoextend:max:16384G

And so on...