Maximum table size for MySQL databases

Tahukah anda seberapa besar ukuran maksimal suatu table pada database MySQL? …besar maksimal table pada MySQL bukan dari internal MySQL itu sendiri, melainkan tergantung dari OS nya (operating system file-size limits) jadi ukuran table di MySQL itu tidak dibatasi, untuk mengetahui efektiv maksimal ukuran table kita harus mengecek spesifikasi dari operating system tersebut.

Berikut contoh operating system file-size limits:

Full Table - File-size Limit


Pada MySQL anda dapat mengecek maksimal DATA dan ukuran INDEX dengan statement berikut:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

Jika pointer size pada table yang ada terlalu kecil, anda dapat merubahnya dengan statement ALTER TABLE untuk memaksimalkan ukuran table yang diinginkan.
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

Lengkapnya bisa baca-baca manualnya disini: http://dev.mysql.com/doc/refman/5.0/en/index.html
Ok sekarang langsung aja kita test…

mysql> show table status from dotproject like 'users' \G;
*************************** 1. row ***************************
           Name: users
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 128
 Avg_row_length: 82
    Data_length: 10508
Max_data_length: 4294967295
   Index_length: 28672
      Data_free: 0
 Auto_increment: 215
    Create_time: 2008-08-19 11:27:59
    Update_time: 2008-08-19 11:31:21
     Check_time: 2008-08-19 11:27:59
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

mysql> ALTER TABLE users MAX_ROWS=1000000000 AVG_ROW_LENGTH=100;
Query OK, 128 rows affected (0.16 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> show table status from dotproject like 'users' \G;
*************************** 1. row ***************************
           Name: users
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 128
 Avg_row_length: 82
    Data_length: 10508
Max_data_length: 1099511627775
   Index_length: 28672
      Data_free: 0
 Auto_increment: 215
    Create_time: 2008-09-07 00:40:49
    Update_time: 2008-09-07 00:40:49
     Check_time: 2008-09-07 00:40:49
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=1000000000 avg_row_length=100
        Comment:
1 row in set (0.00 sec)
mysql> _

sekedar info: selama maintain MySQL database ada yang sudah berjalan dengan total data sampai 600 GB pada MySQL versi 4.1.7 running on Red Hat Enterprise Linux AS
Semoga bermanfaat bro…

Referensi:
http://dev.mysql.com/doc/refman/5.0/en/full-table.html

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

5 responses to “Maximum table size for MySQL databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: