[mysqld]
big-tables
Max_data_length -- 최대 data 파일 bytes 크기(index 제외)
|-- Fixed-row: 고정적인 row 포맷, 최대 rows 수
`-- Dynamic-row: 유동적인 row 포맷(BLOB,TEXT 자료형)
총 data 파일 bytes 수
(mysql 4.1.2, myisam_data_pointer_size, 기본값 4 bytes, 2<PS<8)
[AVG_ROW_LENGTH] -- http://dev.mysql.com/doc/mysql/en/create-table.html
An approximation of the average row length for your table.
You need to set this only for large tables with variable-size records.
When you create a MyISAM table, MySQL uses the product of the `MAX_ROWS'
and `AVG_ROW_LENGTH' options to decide how big the resulting table will be.
If you don't specify either option, the maximum size for a table will be
4GB (or 2GB if your operating system only supports 2GB tables).
The reason for this is just to keep down the pointer sizes to make the
index smaller and faster if you don't really need big files.
If you want all your tables to be able to grow above the 4GB limit and
are willing to have your smaller tables slightly slower and larger than
necessary, you may increase the default pointer size by setting th
`myisam_data_pointer_size' system variable, which was added in MySQL 4.1.2.
[Max_data_length] -- http://dev.mysql.com/doc/mysql/en/show-table-status.html
The maximum length of the data file. For fixed-row formats,
this is the maximum number of rows in the table.
For dynamic-row formats, this is the total number of data bytes that
can be stored in the table, given the data pointer size used.
[The table is full] -- http://dev.mysql.com/doc/mysql/en/full-table.html
You are using a MyISAM table and the space required for the table exceeds
what is allowed by the internal pointer size.(If you don't specify the
`MAX_ROWS' table option when you create a table, MySQL uses the
`myisam_data_pointer_size' system variable. Its default value of 4 bytes is
enough to allow only 4GB of data.)
MAX_ROWS 계산)
+--------+-----------------------------------+-------------------------------------+
| | MAX_ROWS(Number) | Max Total Size(bytes) |
|--------+-----------------------------------+-------------------------------------|
| Fixed | Max_data_length | Max_data_length / Avg_row_length |
|--------+-----------------------------------+-------------------------------------|
| Dynamic| Max_data_length / Avg_row_length | Max_data_length |
+--------+-----------------------------------+-------------------------------------+
ex1) Fixed-row format
mysql> SHOW TABLE STATUS LIKE 'foobar' \G
Name: foobar
Type: MyISAM
Row_format: Fixed
Rows: 14
Avg_row_length: 13
Data_length: 182
Max_data_length: 55834574847
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2004-10-05 15:30:10
Update_time: 2005-01-25 12:47:51
Check_time: 2005-01-25 06:30:01
Create_options:
Comment:
- 최대 크기 : 5583457484 / 13 = 4G
- MAX_ROWS : 5583457484 = 5,583,457,484 (약 55억 ROWS)
ex2) Dynamic-row format
mysql> SHOW TABLE STATUS LIKE 'qna' \G
Name: qna
Type: MyISAM
Row_format: Dynamic
Rows: 5381
Avg_row_length: 1725
Data_length: 9286156
Max_data_length: 4294967295
Index_length: 279552
Data_free: 1140
Auto_increment: 5779
Create_time: 2002-12-16 03:16:59
Update_time: 2005-01-25 14:16:07
Check_time: 2002-12-16 03:17:00
Create_options:
Comment:
- 최대 크기 : 4294967295 = 4G
- MAX_ROWS : 4294967295 / 1725 = 2,489,836 (약 2백 5십만 ROWS)
예) 천만 ROWS, 16G
ALTER TABLE table MAX_ROWS=17250000000 AVG_ROW_LENGTH=1725; |
sun's longitude:37 39 26
/board/delete.php:소스보기
