> -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'
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 t
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 refernce in the mysql docs that indicates how to decide a
s
On Tue, Dec 23, 2003 at 07:07:57PM -0600, Matt W wrote:
> Hi Jeremy,
>
> - Original Message -
> From: "Jeremy Zawodny"
> Sent: Monday, December 22, 2003 2:20 PM
> Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
>
>
> > On Fri, Dec 19
ng on very well. :-)
More below...
- Original Message -
From: "Mark Hawkes"
Sent: Saturday, December 20, 2003 3:50 PM
Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
> Hi Matt,
>
> Thanks very much for your thoughts and advice. I was going to ignore
using
>
Hi Jeremy,
- Original Message -
From: "Jeremy Zawodny"
Sent: Monday, December 22, 2003 2:20 PM
Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
> On Fri, Dec 19, 2003 at 06:40:17PM -0600, Matt W wrote:
> > Hi Mark,
> >
> > I'll tell you what I kno
On Fri, Dec 19, 2003 at 06:40:17PM -0600, Matt W wrote:
> Hi Mark,
>
> I'll tell you what I know. :-)
>
> First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's
> ignored with fixed-length rows) -- more specifically, those with
> TEXT/BLOB
Hi Mark,
I'll tell you what I know. :-)
First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's
ignored with fixed-length rows) -- more specifically, those with
TEXT/BLOB columns. Otherwise, if MAX_ROWS is used, MySQL will assume
that each TEXT/BLOB column will
Hi all,
At table creation time I can use MAX_ROWS and AVG_ROW_LENGTH to
(a) limit the size of a HEAP table
(b) overcome MyISAM's default 4GB limit
But are they used in any other ways? Does MySQL use them to improve
performance by sizing buffers appropriately or to prevent fragmentati
byte is
used for the bitmap when you use NULLs, allowing up to 15 NULL fields to be
stored with only one additional byte per record: 15 char(10) fields would
give a Avg_row_length of 152, while 16 fields would give a Avg_row_length of
163. If there are fewer than 8 NULL fields, no additio
PROTECTED]>
To: "Stefan Hinz" <[EMAIL PROTECTED]>
Cc: "Sergei Golubchik" <[EMAIL PROTECTED]>; "Keith C. Ivey"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, January 17, 2003 3:48 PM
Subject: Re: Avg_row_length
>
>
> Stefa
col9 | char(10) | | | | |
> +---+--+--+-+-+---+
> MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
> +---+++--++
> | Name | Type | Row_format | Rows | Avg_row_length |
> +---+++--+---
| foo | foo | foo | foo |
+--+--+--+--+--+--+--+--+--+
MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name | Type
oo | foo | foo | foo | foo | foo | foo |
+--+--+--+--+--+--+--+--+--+
MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name | Type | Row_format | Rows | Avg_row_length |
+---+++--++
On Thu, Jan 16, 2003 at 06:47:03PM +0100, Stefan Hinz wrote:
> Keith,
>
> thanks once again! Well, anyone here from the MySQL developer team? Or
> any other guru who can explain why 10 = 11?
Hi Stefan,
Here is my guess. ;)
CHAR columns are fixed width. A CHAR column with a width of 10 wil
---+
> mysql> SHOW TABLE STATUS LIKE 'myrowisam';
> +---+++--+----+
> | Name | Type | Row_format | Rows | Avg_row_length |
> +---+++--++
> | myrowisam | MyISAM | Fixed | 109 | 11 |
> +---+---
-+
mysql> SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name | Type | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed |
| Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed | 109 | 11 |
+---+++--++
Regards,
--
Stefan Hinz <[EMAIL PROTECTED]>
Geschäftsführer / CEO iConne
On 16 Jan 2003, at 0:37, Stefan Hinz wrote:
> > There's a formula to calculate the row length for dynamic MyISAM
> > tables here:
> >http://www.mysql.com/doc/en/Dynamic_format.html
>
> Thanks for the hint, but this doesn't explain fixed length row
> calculations where char(10) will give an av
D]>
To: <[EMAIL PROTECTED]>
Cc: "Stefan Hinz" <[EMAIL PROTECTED]>
Sent: Wednesday, January 15, 2003 4:39 PM
Subject: Re: Avg_row_length
> On 15 Jan 2003, at 13:03, Stefan Hinz wrote:
>
> > > I believe there is an extra byte for the 'null' flag; if
On 15 Jan 2003, at 13:03, Stefan Hinz wrote:
> > I believe there is an extra byte for the 'null' flag; if you declare
> > this column to be NOT NULL, I'll bet the avg length will be 10.
>
> nice bet, but you lose :
In addition to the bitmap for which columns have null values (if
there are any c
Row_format: Dynamic
> Rows: 1
> Avg_row_length: 20
Well, isn't the row length supposed to be 2 (not 20)? I mean 1 byte for
the character 's', plus 1 byte to store the length.
BTW, the manual doesn't say anything about "internal uses" of add
mysql> insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)
And this gave table status of
Name: t2
Type: MyISAM
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Data_length: 20
Max_data_length: 4294967295
Index_length: 1024
-++
> | Name| Type | Row_format | Rows | Avg_row_length |
> +-+++--++
> | myrowisam | MyISAM | Fixed | 68 | 11 | <---
* Each `NULL' column takes one bit extra, rounded up
---+---+
mysql> SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name | Type | Row_format | Rows | Avg_row_length |
+-+++--++
| myrowisam | MyISAM | Fixed
YES | | NULL| |
+---+--+--+-+-+---+
mysql> SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name| Type | Row_format | Rows | Avg_row_length |
+-+++-
| Extra |
+---+--+--+-+-+---+
| col | char(10) | YES | | NULL| |
+---+--+--+-+-+---+
mysql> SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name| Type | Row_format |
If I may, I'd like to have a brief explanation of how to use max_rows and
avg_row_length.
Of course I have seen them in the manual, but after trying them I just
haven't found the explanations very clear.
Thank you.
--
28 matches
Mail list logo
