关于mysql archive存储引擎-专门存储审计和日志数据

来源:http://60.29.242.49/?p=60

政府还有一个让数据库专家摊上更多事情的职能，就是安全控制和数据审计。 那些管理着海量数据仓库的企业官员常常得回答诸如“何人何时修改了什么”或者“何人何时查看了什么”这样的提问。那些拥有数以千计的员工，开展着不计其数 的业务的企业，每天都会产生出大量的日志记录数据，而且必须将其好好保存。为了帮助数据库专家应对数据爆炸的挑战，MySQL5.0引入了一种新的数据存 储引擎，叫做Archive。这个先进的数据管理工具，让MySQL的专家们拥有了处理和管理海量数据的新式武器。

 

 

Archive引擎作用： 为大量很少 引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案，区别于InnoDB、MyISAM提供压缩功能，没有索引。

 

 

 

 

关于Archive存储引擎的介绍和性能测试的文章：http://dev.mysql.com/tech-resources/articles/storage-engine.html

不喜欢英文的童鞋可以看这篇翻译过来的文章（推荐，翻译的不错）：http://guangxin.name/2009/04/mysql50-archive-1.html

 

 

根据英文的测试结论来看，Archive表比MyISAM表要小 大约75%，比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。

 

 

Archive表的性能是否可能超过MyISAM？答案是肯定 的。根据MySQL工程师的资料，当表内的数据达到1.5GB这个量级，CPU又比较快的时候，Archive表的执行性能就会超越MyISAM表。因为 这个时候，CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。

 

 

较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你 需要把数据从一台MySQL服务器转移到另一台的时候，Archive表可以方便地移植到新的MySQL环境，你只需将保存Archive表的底层文件复 制过去就可以了。

 

 

本着怀疑一切的精神，本人进行了如下的测试：

 

①建立一个iplog的表：

mysql> create table iplog(id int auto_increment not null primary key,userid int,ip char(15),visit_time datetime) engine=innodb;

 

②使用python脚本插入50w数据：

 

 

#!/usr/bin/mysql

import MySQLdb

 

conn = MySQLdb.connect(host=”localhost”,user=”root”,passwd=”asdf”,db=”test”,unix_socket=”/data/mysql_3306/mysql.sock”)

cursor = conn.cursor()

for i in range(0,500000):

sql = “insert into iplog(userid,ip,visit_time) values(%s,’127.0.0.1′,now())”%i

cursor.execute(sql)

cursor.close()

conn.commit()

conn.close()

 

③分别创建iplog对应的archive、 InnoDB、MyISAM对应表格并插入数据

 

 

mysql> create table iplog_archive engine=archive as select * from iplog;

Query OK, 500000 rows affected (2.73 sec)

Records: 500000  Duplicates: 0  Warnings: 0

 

mysql> create table iplog_myisam engine=myisam as select * from iplog;

Query OK, 500000 rows affected (1.39 sec)

Records: 500000  Duplicates: 0  Warnings: 0

 

mysql> create table iplog_innodb engine=innodb as select * from iplog;

Query OK, 500000 rows affected (4.78 sec)

Records: 500000  Duplicates: 0  Warnings: 0

 

 

④比较它们的大小

 

 

mysql> select table_name,engine,ROUND(data_length/1024/1024,2) total_size_mb,table_rows from information_schema.tables

-> where table_schema = ‘test’ and table_name like ‘iplog_%’;

+—————+———+—————+————+

| table_name    | engine  | total_size_mb | table_rows |

+—————+———+—————+————+

| iplog_archive | ARCHIVE |          2.10 |     500000 |

| iplog_innodb  | InnoDB  |         30.56 |     500289 |

| iplog_myisam  | MyISAM  |         29.56 |     500000 |

+—————+———+—————+————+

3 rows in set (0.01 sec)

 

⑤测试select性能：

 

 

mysql> select * from iplog_archive where userid=250000;

+——–+——–+———–+———————+

| id     | userid | ip        | visit_time          |

+——–+——–+———–+———————+

| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |

+——–+——–+———–+———————+

1 row in set (0.31 sec)

 

mysql> select * from iplog_innodb where userid=250000;

+——–+——–+———–+———————+

| id     | userid | ip        | visit_time          |

+——–+——–+———–+———————+

| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |

+——–+——–+———–+———————+

1 row in set (0.48 sec)

 

mysql> select * from iplog_myisam where userid=250000;

+——–+——–+———–+———————+

| id     | userid | ip        | visit_time          |

+——–+——–+———–+———————+

| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |

+——–+——–+———–+———————+

1 row in set (0.10 sec)

 

⑥测试insert性能（）：

使用python脚本再插入50w数据，查看插入性能，脚本如下，没有写 的很复杂，测试InnoDB或者MyISAM要修改代码

 

 

#!/usr/bin/mysql

import MySQLdb

 

conn = MySQLdb.connect(host=”localhost”,user=”root”,passwd=”asdf”,db=”test”,unix_socket=”/data/mysql_3306/mysql.sock”)

cursor = conn.cursor()

for i in range(500001,1000000):

sql = “insert into iplog_archive(userid,ip,visit_time) values(%s,’127.0.0.1′,now())”%i

cursor.execute(sql)

cursor.close()

conn.commit()

conn.close()

 

 

archive

 

real    1m30.467s

user    0m22.270s

sys     0m12.670s

 

 

InnoDB

 

real    0m48.622s

user    0m18.722s

sys     0m9.322s

 

 

MyISAM

 

real    1m32.129s

user    0m13.183s

sys     0m5.624s

 

 

 

 

测试结果是archive可以大规模的减少空间减少%93（这个 与表有关系），select性能介于MyISAM和InnoDB之间，大规模insert时效率比MyISAM和InnoDB高，至于原因“因为这个时 候，CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。”

 

欢迎大家共同探讨，包括测试用例以及任何想法。

 

关于time命令的执行结果关于real、user、sys说明

1)实际时 间(real time): 从command命令行开始执行到运行终止的消逝时间；

2)用户 CPU时间(user CPU time): 命令执行完成花费的用户CPU时间，即命令在用户态中执行时间总和；

3)系统 CPU时间(system CPU time): 命令执行完成花费的系统CPU时间，即命令在核心态中执行时间总和。