author：skate
time:2013/08/21
mysql archive存储引擎安装/使用
语法如下： INSTALL PLUGIN plugin_name SONAME 'plugin_library'
plugin_name：就是plugin的名称，也就是字段：PLUGINS.PLUGIN_NAME
plugin_library：是共享库的名字，可能是静态或者动态库，静态的需要重启服务加载，动态的可以在线加载。 共享库在plugin的目录下(show variables like 'plugin_dir';)
环境说明
mysql version:5.5.28-log Source distribution
os:centos6.3
dell420
安装步骤
1. 首先查看共享库是否存在
mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)
mysql>
[root@db01 ~]# ll /usr/local/mysql/lib/plugin/
total 1456
-rwxr-xr-x 1 mysql mysql 13054 Jun 20 13:17 adt_null.so
-rwxr-xr-x 1 mysql mysql 24699 Jun 20 13:17 auth.so
-rwxr-xr-x 1 mysql mysql 12588 Jun 20 13:17 auth_socket.so
-rwxr-xr-x 1 mysql mysql 22993 Jun 20 13:17 auth_test_plugin.so
-rw-r--r-- 1 mysql mysql 227 Aug 29 2012 daemon_example.ini
drwxr-xr-x 2 mysql mysql 4096 Jan 6 2013 debug
-rwxr-xr-x 1 mysql mysql 267399 Jun 20 13:17 ha_archive.so
-rwxr-xr-x 1 mysql mysql 218437 Jun 20 13:17 ha_blackhole.so
-rwxr-xr-x 1 mysql mysql 174841 Jun 20 13:17 ha_example.so
-rwxr-xr-x 1 mysql mysql 322294 Jun 20 13:15 ha_federated.so
-rwxr-xr-x 1 mysql mysql 28372 Jun 20 13:17 libdaemon_example.so
-rwxr-xr-x 1 mysql mysql 17803 Jun 20 13:17 mypluglib.so
-rwxr-xr-x 1 mysql mysql 17583 Jun 20 13:17 qa_auth_client.so
-rwxr-xr-x 1 mysql mysql 23294 Jun 20 13:17 qa_auth_interface.so
-rwxr-xr-x 1 mysql mysql 12918 Jun 20 13:17 qa_auth_server.so
-rwxr-xr-x 1 mysql mysql 174839 Jun 20 13:17 semisync_master.so
-rwxr-xr-x 1 mysql mysql 93642 Jun 20 13:17 semisync_slave.so
[root@db01 ~]#
2.安装
mysql> install plugin archive soname 'ha_archive.so';
Query OK, 0 rows affected (0.05 sec)
mysql> show plugins;
+--------------------------+--------+--------------------+---------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+---------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
....
....
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | ha_archive.so | GPL |
+--------------------------+--------+--------------------+---------------+---------+
21 rows in set (0.00 sec)
mysql>
注意：如果是主备环境，一定要现在slave环境先安装，然后在master在安装插件，否则同步会受影响
3. 卸载
mysql> uninstall plugin ARCHIVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看plugin的状态是如果出现“deleted”时，说明可能有表还在使用这个plugin，如下
mysql> show plugins;
+--------------------------+---------+--------------------+---------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+---------+--------------------+---------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
....
....
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | DELETED | STORAGE ENGINE | ha_archive.so | GPL |
+--------------------------+---------+--------------------+---------------+---------+
21 rows in set (0.00 sec)
mysql>
直接把表删除，发现plugin就被uninstall了，如下
mysql> drop table archtab;
Query OK, 0 rows affected (0.00 sec)
mysql> show plugins;
+--------------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
....
....
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------+--------+--------------------+---------+---------+
20 rows in set (0.00 sec)
mysql>
相关命令：
mysql> show variables like 'plugin_dir'
mysql> install plugin archive soname 'ha_archive.so';
mysql> show plugins;
mysql> uninstall plugin ARCHIVE;
archive表特点
1.支持insert和select,drop
2.不支持DML操作，如delete，update，truncate
3.只支持在auto_increment的列上创建索引，其他列不支持索引
archive的读取、dml性能对比
mysql> create table tmp_myisam engine=myisam select * from tmp_archive where id <=1000000;
Query OK, 1000000 rows affected (5.91 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> create table tmp_innodb engine=innodb select * from tmp_archive where id <=1000000;
Query OK, 1000000 rows affected (41.09 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> create table tmp_arch engine=archive select * from tmp_archive where id <=1000000;
Query OK, 1000000 rows affected (10.61 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into tmp_myisam(operator,operator1 ) select operator,operator1 from tmp_archive;
Query OK, 3853565 rows affected (53.41 sec)
Records: 3853565 Duplicates: 0 Warnings: 0
mysql> insert into tmp_innodb(operator,operator1 ) select operator,operator1 from tmp_archive;
Query OK, 3853565 rows affected (2 min 12.44 sec)
Records: 3853565 Duplicates: 0 Warnings: 0
mysql> insert into tmp_arch(operator,operator1 ) select operator,operator1 from tmp_archive;
Query OK, 3853565 rows affected (29.94 sec)
Records: 3853565 Duplicates: 0 Warnings: 0
通过上面的create和insert可以看到archive表要比myisam慢一点，这是因为数据量比较少和cpu不是很强劲（dell420）
mysql> select count(*) from tmp_myisam where id=3;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (1.48 sec)
mysql> select count(*) from tmp_innodb where id=3;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (1.98 sec)
mysql> select count(*) from tmp_arch where id=3;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (3.54 sec)
上面是读操作的对比，发现archive表也不快啊，这样因为cpu比较弱，使用率一直在100%不下，cpu成为瓶颈导致的。
当cpu比较强悍，数据量比较大，如表在3G以上，这样让cpu资源换取io资源，archive表的性能才发挥出来。后面会找一个cpu强一点的机器来测试
续......
参考：http://dev.mysql.com/doc/refman/5.5/en/archive-storage-engine.html
8061
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
