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.
9 comments:
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
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
Free Software Download
Free Software Download
Free Software Download
Internet Download Manager (IDM) is a powerful software application that enhances the downloading process from the internet. Internet Download Manager Cracked offers accelerated download speeds, resume and schedule features, and comprehensive error recovery to ensure uninterrupted downloads.
Post a Comment