MySQL 5

[Justin Bastedo]

Well you'll all be glad to know the tests ran fine on my system I
moved the data over from my production server using mysqlhotcopy
script. stopped my mysql, moved over the databases, started it back
up, renamed my MyISAM table and recreated it as an ARCHIVE table.

Here is some out put with some times:
mysql> SHOW TABLE STATUS LIKE 'campaign_16_long_myisam';
+-------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name                    | Engine | Version | Row_format | Rows     |
Avg_row_length | Data_length | Max_data_length | Index_length |
Data_free | Auto_increment | Create_time         | Update_time        
| Check_time | Collation         | Checksum | Create_options | Comment
|
+-------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| campaign_16_long_myisam | MyISAM |       9 | Dynamic    | 28224724 |
            88 |  2500641604 |      4294967295 |    261594112 |       
 0 |       28621173 | 2005-07-07 13:57:00 | 2005-09-15 19:00:09 | NULL
      | latin1_swedish_ci |     NULL |                |         |
+-------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `campaign_16_long`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `campaign_16_long` (
    ->   `id` int(11) NOT NULL,
    ->   `timestamp` bigint(20) NOT NULL default '0',
    ->   `ad_name` varchar(25) NOT NULL default '',
    ->   `type` varchar(25) NOT NULL default '',
    ->   `referer` varchar(100) NOT NULL default '',
    ->   `path` varchar(255) NOT NULL default '',
    ->   `client_ip` varchar(15) NOT NULL default ''
    -> ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `ad_tracking`.`campaign_16_long` SELECT * FROM
`ad_tracking`.`campaign_16_long_myisam`;

...
# top
last pid: 85206;  load averages:  0.99,  0.74,  0.37                  
                                                                      
                               up 8+12:37:29  03:02:10
66 processes:  2 running, 64 sleeping
CPU states: 25.0% user,  0.0% nice,  1.1% system,  0.0% interrupt, 73.9% idle
Mem: 113M Active, 3212M Inact, 184M Wired, 158M Cache, 214M Buf, 5368K Free
Swap: 2048M Total, 2048M Free

  PID USERNAME  PRI NICE   SIZE    RES STATE  C   TIME   WCPU    CPU COMMAND
85044 mysql      20    0 59756K 34092K kserel 1   6:52 98.10% 98.10% mysqld
...

Query OK, 28224724 rows affected (8 min 42.90 sec)
Records: 28224724  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'campaign_16_long';
+------------------+---------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name             | Engine  | Version | Row_format | Rows     |
Avg_row_length | Data_length | Max_data_length | Index_length |
Data_free | Auto_increment | Create_time         | Update_time        
| Check_time | Collation         | Checksum | Create_options | Comment
|
+------------------+---------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| campaign_16_long | ARCHIVE |      10 | Compressed | 28224724 |      
    4533 |   533048587 |    127942673892 |            0 |         0 | 
         NULL | 2005-09-16 03:04:20 | 2005-09-16 03:04:20 | NULL      
| latin1_swedish_ci |     NULL |                |         |
+------------------+---------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

# ls -alh | grep campaign_16_long
-rw-rw----  1 mysql  mysql    19B Sep 16 02:54 campaign_16_long.ARM
-rw-rw----  1 mysql  mysql   508M Sep 16 03:04 campaign_16_long.ARZ
-rw-rw----  1 mysql  mysql   8.6K Sep 16 02:54 campaign_16_long.frm
-rw-r-----  1 mysql  mysql   2.3G Sep 15 19:00 campaign_16_long_myisam.MYD
-rw-r-----  1 mysql  mysql   249M Sep 15 21:35 campaign_16_long_myisam.MYI
-rw-r-----  1 mysql  mysql   8.6K Jul  7 13:57 campaign_16_long_myisam.frm

The server moved  28,224,724 in 8 min 42.9 seconds. It compressed the
data from 2.3G to 508M AMAZING storage saver! I am truely happy,
running selects off it were just as fast as the MyISAM:
mysql> SELECT * FROM `campaign_16_long_myisam` LIMIT 0, 30;
...
30 rows in set (0.00 sec)

mysql> SELECT * FROM `campaign_16_long` LIMIT 0, 30;
...
30 rows in set (0.00 sec)

System Specs:
FreeBSD 5.4-RELEASE-p7 #0: Wed Sep  7 14:12:34 CDT 2005
CPU: Dual Core AMD Opteron(tm) Processor 265 (1792.85-MHz K8-class CPU)
FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs
 cpu0 (BSP): APIC ID:  0
 cpu1 (AP): APIC ID:  1
 cpu2 (AP): APIC ID:  2
 cpu3 (AP): APIC ID:  3

4096MB Ram
2 x 146GB SCSI/ RAID 1

I don't know if this helps much more than to confirm that the ARCHIVE
Table Engine for MySQL 5 works fine in FreeBSD 5.4 on AMD64. But let
me know if you have any more questions I would be more than happy to
try and answer any.

Justin