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

  • "the data to be stored is too large": what limit did you hit?
    – miracle173
    Commented Jul 25, 2012 at 13:05
  • 1
    How many "millions" of rows do you have, what is the composition of each row, how much RAM and CPU does your box have, how many rows are added per hour or day, how long do you need to keep the data for reporting or archiving purposes? Commented Jul 25, 2012 at 13:37
  • Number of files are not known. But, they will be in millions. So, if I could find out the limits of MySQL I can keep deleting the older files to avoid size limit problems. But I need to know how many number of devices database can support and for how many months. Also, I will be fetching the data to generate reports. So which approach is better performance wise to have multiple databases or a single database. Queries will be on individual device logs also as well as on the network as a whole Commented Jul 26, 2012 at 4:45

1 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


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


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


and so on...

UPDATE 2013-06-01 20:45 EDT

I just discovered something with reference to setting the max filesize for a system tablespace. Here is the situation:

Client has the following InnoDB files

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

and has this setting


MySQL was reporting table full errors.

I tried creating a new file ibdata3 with


That value did not work

I tried using new sizes for the max value

  • 2046G
  • 2045G
  • 2046000M

After trying for 15 min with different combinations, none of these values worked. Looks like mysqld was never going to startup again. I decided to try the exact number of bytes for the filesize of ibdata2:


When I started mysql, LO AND BEHOLD, MySQL was up.

  • ibdata3 was created at 10M
  • ibdata3 was soon growing in 8M chunks
  • Client was happy once again

Moral of the story : At times, you may need to use the exact filesize for a system tablespace in innodb_data_file_path


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:


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


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


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


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


And so on...

  • Hi Rolando Thanks for your reponse. I am stuck on so many doubts. I am working on Windows 7. I cannot see ibdata1 file that you mentioned. Also, in the data folder I am not able to see any of the databases created by me. I can only see mysql and performance_schema (default ones). There is onw my.ini file MySQL Server 5.5 folder. But, I am not able to edit and save it. It says, some other program is using that file. I have closed the server by this command "mysqladmin -u -p shutdown" and now I do not know how to start it again. net start mysql is not working :| Commented Jul 26, 2012 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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