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).
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!)
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)
*************************** 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.
10 comments:
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
Ӊ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
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
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
Hello, after reading this awesome piece of writing i am too delighted to share my know-how here with colleagues.
removewat 2.2.9
very nice work i really liked it i am delight to see this
It's graceful not just engaging!
Classic work you have here.
I admire your concept =)
MySQL system information is correct. interesting post no doubt
very nice work i really liked it
Hi, Everyone. Today i saw your blog Thanks for the sharing such a wonderful post. Its very helpful for me. Keep it up admin
learning resources
Post a Comment