Wednesday, May 30, 2012

Maximum MySQL Database Size

While working as DBA , we think what is the maximum size of database/file MySQL/File system can support.  While looking answer for same I found some interesting details and thought to share.

Below are the estimated maximum file sizes per operating system:

Operating System
File-size Limit
Win32 w/ FAT/FAT32
2GB/4GB
Win32 w/ NTFS
2TB (possibly larger)
Linux 2.2-Intel 32-bit
2GB (LFS: 4GB)
Linux 2.4+
(using ext3 file system) 4TB
Solaris 9/10
16TB
MacOS X w/ HFS+
2TB
NetWare w/NSS file system
8TB

This information was taken directly off MySQL.com but this is not necessarily the maximum limitations of your database. A number of methods can help to increase your max file size:

LFS (Large File Support) in Linux
To support files larger than 2GiB on 32-bit Linux systems you would have to use LFS. The standard max file size limitations without LFS enabled are 2^31 bytes(2GiB), but enabling LFS can enable your maximum file size to reach 2^63 bytes (9 223 372 036 854 775 808 bytes).

Using the “Alter Table” command
This will come in handy when using the MyISAM storage engine. The simple “Alter Table” in the mysql prompt command can extend your database capacity dramatically.
Example: “alter table ‘weather’ max_rows = 200000000000″
Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

Most of this researched information is very old so I decided to run a little check (which I should have done right in the beginning), and by doing so on my local machine, I literally nearly fell of my chair:

mysql> show table status like ‘blog_hits’ \G
*************************** 1. row ***************************
Name: blog_hits
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 15497
Avg_row_length: 247
Data_length: 3835852
Max_data_length: 281474976710655
Index_length: 366592
Data_free: 0
Auto_increment: 15509
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

This is telling me my max database size can be as much as 281474976710655 bytes which If I’m not mistaken, equates to 256 terabytes. With this being said, I think we are going to run into system limitations rather than MySQL limitations, so therefore, revert back to the table at the top of this post.

I have taken reference from below blog posts.


8 comments:

Anonymous said...

Thanks , I've just been looking for information approximately this subject for a long
time and yours is the greatest I've discovered so far.
However, what in regards to the conclusion? Are you sure about the source?


Take a look at my blog :: Skyla

Anonymous said...

Ӊi, just wanted to tell үou, I lovеd this article.
It աas funny. Kеep on posting!

my site - regarder des films en streaming

Anonymous said...

I am 26 and have by no means been in a position to wear underwear
or bras.

My website :: underwear size compared to pants size

Anonymous said...

Its like you read my mind! You appear to know so much about this, like you
wrote the book in it or something. I think that you can do
with a few pics to drive the message home a bit, but other
than that, this is great blog. An excellent read.

I will certainly be back.

Look at my web site: apartment near ucla for rent vp

Kristina Ismailovna said...

Hello, after reading this awesome piece of writing i am too delighted to share my know-how here with colleagues.
removewat 2.2.9

maliha6143 said...

very nice work i really liked it i am delight to see this

Shareit Download said...

It's graceful not just engaging!
Classic work you have here.
I admire your concept =)

cleanmymac 3 activation code said...

MySQL system information is correct. interesting post no doubt