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>
[[email protected] ~]# 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
[[email protected] ~]#
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
![揭秘Mysql 5.6 Index Condition Pushdown(ICP)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)