MySQL Bugs: #27533: myisam table converted to archive is 20% bigger

Bug #27533	myisam table converted to archive is 20% bigger
Submitted:	29 Mar 2007 18:23	Modified:	19 Feb 2009 19:48
Reporter:	Alex Burgel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S2 (Serious)
Version:	5.0.37/5.0BK	OS:	Linux (linux 2.4.21)
Assigned to:	Brian Aker	CPU Architecture:	Any

Description:
converting the table below from myisam to archive results in a 20% increase in size. i'm not sure if this is related to bug 21461. it might be, cuz this table has quite a lot of large varchars.

i have tested this on 5.0.18, 5.0.33, and 5.0.37. this is all on redhat es 3 on x86. the resulting archive table is always bigger, though not necessarily by 20%.

using the mysql binaries: mysql-5.0.37-linux-i686-glibc23

How to repeat:
run this:

drop table if exists requests;
drop table if exists requests_arch;
drop procedure if exists test_insert;

create table requests (
  id int not null auto_increment,
  time datetime not null,
  host varchar(20) not null,
  method varchar(10) not null,
  server varchar(50) not null,
  req_time int unsigned not null,
  status smallint unsigned not null,
  bytes int unsigned not null,
  path varchar(8192) not null,
  query varchar(8192),
  referer varchar(8192),
  uagent varchar(8192),
  cookies varchar(8192),
  primary key (id),
  key (path(100))
) engine=myisam;

delimiter //
create procedure test_insert()
begin
declare v_ctr int default 0;
while v_ctr < 5000 do
  INSERT INTO requests VALUES (NULL,'2007-03-28 00:00:00','127.0.0.1','GET','www.example.com',1000,200,5000,'/index.html','name=value','referer','user agent','cookie1=value; cookie2=value;');
  set v_ctr = v_ctr + 1;
end while;
end
//
delimiter ;

call test_insert();
create table requests_arch engine=archive select * from requests;

the resulting file sizes:
-rw-rw----    1 mysql    mysql          65 Mar 29 11:33 db.opt
-rw-rw----    1 mysql    mysql          19 Mar 29 14:01 requests_arch.ARM
-rw-rw----    1 mysql    mysql      793037 Mar 29 14:01 requests_arch.ARZ
-rw-rw----    1 mysql    mysql       49916 Mar 29 14:01 requests_arch.frm
-rw-rw----    1 mysql    mysql       49916 Mar 29 14:01 requests.frm
-rw-rw----    1 mysql    mysql      660000 Mar 29 14:01 requests.MYD
-rw-rw----    1 mysql    mysql       67584 Mar 29 14:01 requests.MYI

notice that the archive data file is 20% bigger than the myisam data file.

if i gzip the myisam file, i get this:
-rw-rw----    1 mysql    mysql       14069 Mar 29 14:01 requests.MYD.gz

i would expect that the archive data file size should be a bit closer to that.

Thank you for the bug report.

[miguel@light 5.0]$ ls var/db7/ -la
total 1652
drwx------ 2 miguel miguel   4096 Mar 29 17:57 .
drwx------ 8 miguel miguel   4096 Mar 29 17:56 ..
-rw-rw---- 1 miguel miguel     65 Mar 29 17:56 db.opt
-rw-rw---- 1 miguel miguel     19 Mar 29 17:57 requests_arch.ARM
-rw-rw---- 1 miguel miguel 808042 Mar 29 17:57 requests_arch.ARZ
-rw-rw---- 1 miguel miguel  49916 Mar 29 17:57 requests_arch.frm
-rw-rw---- 1 miguel miguel  49916 Mar 29 17:56 requests.frm
-rw-rw---- 1 miguel miguel 660000 Mar 29 17:56 requests.MYD
-rw-rw---- 1 miguel miguel  67584 Mar 29 17:56 requests.MYI

This was fixed with change to 5.1 to pack varchars (the hidden comment shows this).

In which 5.1 release did the referenced fix become available?

Noted in 5.1.17 changelog.

MyISAM tables converted to ARCHIVE were excessively large.