Mysql-Max_rows

Reff : http://davidaank.wordpress.com/2008/09/06/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…

Advertisements
  1. Leave a comment

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: