From owner-freebsd-database@FreeBSD.ORG Fri Sep 16 08:18:48 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9060816A41F for ; Fri, 16 Sep 2005 08:18:48 +0000 (GMT) (envelope-from justin.bastedo@gmail.com) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.199]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2B50F43D46 for ; Fri, 16 Sep 2005 08:18:47 +0000 (GMT) (envelope-from justin.bastedo@gmail.com) Received: by xproxy.gmail.com with SMTP id i27so1274wxd for ; Fri, 16 Sep 2005 01:18:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ETfYzedS1lJyHxZjPlGGxOztQba3gJGWeGrJcSQjEjG/6LbNlsZ+2DmWuxjV3h79irn4g9zjWsh6/REgu1vojg2s0jnhp0sML9qiGb5JRMvKQ9bQLCHrGayMtIvfYd9CxN/L6fnOxUvzghT1rS6/QwbuEamtF9r07Hm6McWsbOI= Received: by 10.70.49.12 with SMTP id w12mr27160wxw; Fri, 16 Sep 2005 01:18:46 -0700 (PDT) Received: by 10.70.35.7 with HTTP; Fri, 16 Sep 2005 01:18:46 -0700 (PDT) Message-ID: <8a52552405091601183622ea23@mail.gmail.com> Date: Fri, 16 Sep 2005 01:18:46 -0700 From: Justin Bastedo To: Greg 'groggy' Lehey In-Reply-To: <20050916073816.GB41235@wantadilla.lemis.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <8a52552405091521103933833d@mail.gmail.com> <20050916041339.GN86168@wantadilla.lemis.com> <8a52552405091521592405d814@mail.gmail.com> <20050916051600.GP86168@wantadilla.lemis.com> <8a5255240509152356516b639d@mail.gmail.com> <20050916073816.GB41235@wantadilla.lemis.com> Cc: freebsd-database@freebsd.org, Alex Dupre Subject: Re: MySQL 5 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: justin.bastedo@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 16 Sep 2005 08:18:48 -0000 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 =20 | Check_time | Collation | Checksum | Create_options | Comment | +-------------------------+--------+---------+------------+----------+-----= -----------+-------------+-----------------+--------------+-----------+----= ------------+---------------------+---------------------+------------+-----= --------------+----------+----------------+---------+ | campaign_16_long_myisam | MyISAM | 9 | Dynamic | 28224724 | 88 | 2500641604 | 4294967295 | 261594112 | =20 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=3DARCHIVE DEFAULT CHARSET=3Dlatin1; 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 =20 =20 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% id= le 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 COMMAN= D 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 =20 | Check_time | Collation | Checksum | Create_options | Comment | +------------------+---------+---------+------------+----------+-----------= -----+-------------+-----------------+--------------+-----------+----------= ------+---------------------+---------------------+------------+-----------= --------+----------+----------------+---------+ | campaign_16_long | ARCHIVE | 10 | Compressed | 28224724 | =20 4533 | 533048587 | 127942673892 | 0 | 0 |=20 NULL | 2005-09-16 03:04:20 | 2005-09-16 03:04:20 | NULL =20 | 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