Re: Alter MyISAM table to adjust max_rows and Avg_row_length

[Michael Stassen]

Jeff wrote:

I've got a table that just hit the 4gig limit for MyISAM tables in a 4.x
ver db.  I need to alter the table structure and set the max_rows and
Avg_row_length to override the default of 4 gig.  Problem is I can't
find any reference in the mysql docs that indicates how to decide a
setting for Avg_row_length or even what the measurement is.  Bytes?
Columns? 


Any help is much appreciated.

Jeff


MAX_ROWS and AVG_ROW_LENGTH are defined in the manual page for CREATE TABLE 
http://dev.mysql.com/doc/refman/4.1/en/create-table.html:


MAX_ROWS: The maximum number of rows you plan to store in the table.  This is 
not a hard limit, but rather an indicator that the table must be able to store 
at least this many rows.


AVG_ROW_LENGTH: An approximation of the average row length for your table.  You 
need to set this only for large tables with variable-size records.


The current value of AVG_ROW_LENGTH can be seen in the output of SHOW TABLE 
STATUS http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html, along 
with the maximum_data_length.  They are in bytes.


As for MAX_ROWS, you really just need a value large enough to require a larger 
pointer.  For example, the manual suggests


  ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn;

It goes on to say that you don't need to set AVG_ROW_LENGTH unless your table 
has BLOB or TEXT values http://dev.mysql.com/doc/refman/4.1/en/full-table.html.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Alter MyISAM table to adjust max_rows and Avg_row_length

[Jeff]

 -Original Message-
 From: Michael Stassen [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, December 08, 2005 14:34
 To: Jeff
 Cc: mysql@lists.mysql.com
 Subject: Re: Alter MyISAM table to adjust max_rows and Avg_row_length
 
 
 Jeff wrote:
  I've got a table that just hit the 4gig limit for MyISAM 
 tables in a 
  4.x ver db.  I need to alter the table structure and set 
 the max_rows 
  and Avg_row_length to override the default of 4 gig.  Problem is I 
  can't find any reference in the mysql docs that indicates how to 
  decide a setting for Avg_row_length or even what the 
 measurement is.  
  Bytes? Columns?
  
  Any help is much appreciated.
  
  Jeff
 
 MAX_ROWS and AVG_ROW_LENGTH are defined in the manual page 
 for CREATE TABLE 
 http://dev.mysql.com/doc/refman/4.1/en/create-table.html:
 
 MAX_ROWS: The maximum number of rows you plan to store in 
 the table.  This is 
 not a hard limit, but rather an indicator that the table must 
 be able to store 
 at least this many rows.
 
 AVG_ROW_LENGTH: An approximation of the average row length 
 for your table.  You 
 need to set this only for large tables with variable-size records.
 
 The current value of AVG_ROW_LENGTH can be seen in the output 
 of SHOW TABLE 
 STATUS 
 http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html
, along 
with the maximum_data_length.  They are in bytes.

As for MAX_ROWS, you really just need a value large enough to require a
larger 
pointer.  For example, the manual suggests

   ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn;

It goes on to say that you don't need to set AVG_ROW_LENGTH unless your
table 
has BLOB or TEXT values
http://dev.mysql.com/doc/refman/4.1/en/full-table.html.

Michael


Thanks Michael, the show table status was what I needed.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]