半瓶子酱油

身是菩提树 心如明镜台 时时勤拂拭 莫使惹尘埃

  • 博客访问: 368836
  • 博文数量: 67
  • 用 户 组: 普通用户
  • 注册时间: 2014-07-02 09:20
个人简介

不忘初心,方得始终

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(67)

文章存档

2016年(9)

2015年(45)

2014年(13)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
友情链接
BLACKHOLE存储引擎总结 2015-04-27 00:30:48

分类: MySQL

BLACKHOLE安装

使用cmake源码安装时指定-DWITH_BLACKHOLE_STORAGE_ENGINE 选项 可以启用 BLACKHOLE 存储引擎,使用默认编译项可编译BLACKHOLE,但是没有安装插件,需要手工安装。

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

mysql> show variables like 'plugin_dir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| plugin_dir    | /db/mysql/lib/plugin/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> system ls /db/mysql/lib/plugin/
adt_null.so    auth_test_plugin.so  ha_archive.so    ha_federated.so        qa_auth_client.so      semisync_master.so
auth.so        daemon_example.ini   ha_blackhole.so  libdaemon_example.so  qa_auth_interface.so  semisync_slave.so
auth_socket.so    debug             ha_example.so    mypluglib.so        qa_auth_server.so

mysql> INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

使用BLACKHOLE

创建BLACKHOLE表只会在数据库生成.frm表定义结构。

mysql> use test;
Database changed

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
Empty set (0.00 sec)

mysql> system ls /db/mysql/data/test
db.opt    test.frm

BLACKHOLE总结

  • BLACKHOLE支持所有类型的索引
  • BLACKHOLE 表不存储数据,如果复制基于SBR,语句可以记录并在从库执行;如果复制为RBR、MBR,UPDATE及DELETE操作将会跳过,不会记录也从库不执行。
  • Insert触发器可以正常使用,Update、Delete触发器因为不存储数据不能触发,FOR EACH ROW 也不能触发。
  • BLACKHOLE 表Auto Increment字段不会自动递增,也不保留自增字段的状态
  • 结合复制replicate-do和replicate-ignore规则,可使用BLACKHOLE当做一个分发主服务器
  • 可用来验证转储文件语法
  • 测试binlog的开销量,通过对比 BLACKHOLE 与 不启动 binlog的性能
  • 可能被用来查找与存储引擎自身不相关的性能瓶颈

参考:
http://dev.mysql.com/doc/refman/5.5/en/blackhole-storage-engine.html

整理自网络

Svoid
2015-04-24
阅读(2452) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册