MySQL Forums
Forum List  »  Partitioning

Re: Really Fixed?
Posted by: Mikael Ronström
Date: May 22, 2006 01:25PM

Hi,
Thx for spotting this bug. A patch for this has been pushed now to the
5.1 tree and should appear in 5.1.11

Rgrds Mikael


Mikael Ronstrom wrote:
> Hi,
>
> marc steikert wrote:
> > I thought about Fixed Tables that "Each row
> is
> > stored using a fixed number of bytes" but
> ...
> > mysql> create table t (id int) engine =
> myisam
> > partition by hash(id) partitions 2;
> > Query OK, 0 rows affected (0.04 sec)
> >
> > mysql> insert into `t` values
> > (1),(2),(3),(4),(5),(6),(7),(8),(9);
> > Query OK, 9 rows affected (0.00 sec)
> > Datensätze: 9 Duplikate: 0 Warnungen: 0
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 9
> > Avg_row_length: 7
> > Data_length: 63
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: NULL
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So 9 entries are allocated in file with 7 bytes
> per record
>
> > mysql> delete from t where id > 5;
> > Query OK, 4 rows affected (0.00 sec)
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 5
> > Avg_row_length: 12
> > Data_length: 63
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: NULL
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So the delete removes 4 entries, these entries do
> however still remain in
> the MyISAM data file, thus the file remains 63
> byte long.
>
> > mysql> alter table t optimize partition
> p0,p1;
> > Query OK, 0 rows affected (0.01 sec)
> > Datensätze: 0 Duplikate: 0 Warnungen: 0
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 5
> > Avg_row_length: 7
> > Data_length: 35
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: 2006-04-30 00:13:23
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So after optimize partitions there is only 5
> entries left of 7 bytes each.
>
> >
> > ... it look's like dynamic
> > ... and thus is Data_free = 0 ???
>
> After checking things I noticed that you spotted a
> bug. The variable
> delete_length which is used to calculate Data_free
> wasn't handled in the
> ::info call in the partition handler. I'll file a
> bug and fix it.

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3059
April 29, 2006 04:54PM
2128
May 03, 2006 12:27AM
Re: Really Fixed?
2131
May 22, 2006 01:25PM
2141
June 09, 2006 08:29AM
2105
June 09, 2006 12:03PM
2144
June 09, 2006 03:48PM
2141
June 10, 2006 01:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.