Date: Fri, 16 Sep 2005 01:18:46 -0700 From: Justin Bastedo <justin.bastedo@gmail.com> To: Greg 'groggy' Lehey <grog@mysql.com> Cc: freebsd-database@freebsd.org, Alex Dupre <ale@freebsd.org> Subject: Re: MySQL 5 Message-ID: <8a52552405091601183622ea23@mail.gmail.com> In-Reply-To: <20050916073816.GB41235@wantadilla.lemis.com> 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>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?8a52552405091601183622ea23>