首页 / MYSQL / 【mysql】关于innodb_file_format

【mysql】关于innodb_file_format

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【mysql】关于innodb_file_format，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11944字，纯文字阅读大概需要18分钟。

内容图文

中指定；row_format则在创建数据表时指定

同时Barracude也支持 old redundant and compact row formats

下面是来自stackexchange上面的回答和性能测试

1、Benefits of Barracuda and Compression

Question

I‘ve been reading about MySQL‘s file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression.

My server is currently using Antelope, as it is the default of MySQL.
I‘ve had many times issues with memory due to the large database I have. My database is increasing every day.

It seems Compression is giving benefits to a few people, like:
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

I understand memory and disk space can be lower, but I‘m not sure if I understand this (quoted from the article):
"~5% CPU load according to top (from 80-100% mostly waiting for I/O)
0.01 sec average lookup time by primary key (from 1-20 sec before the conversion)"

I thought these two things would NOT improve, because if the data is compressed, the server has to uncompress in order to get the original data again, so doesn‘t that make sense that CPU usage would increase?

Does that benefit you in read/write intensive applications? Would you recommend me to change to Barracuda and Compression?

Are you aware of any issues of Barracuda?
It seems the answer of the following question points a few issues, but since it‘s from 2011, I‘d say they are fixed by now: http://serverfault.com/questions/258022/mysql-innodb-how-to-switch-to-barracuda-format

Answer

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda‘s dynamic. Remember that Barracuda also supports old redundant and compact row formats.

The article you are mentioning is probably too old (5.1) and, as Peter Z., Percona‘s CEO, mentions on the comments it may be a bit misleading. That doesn‘t mean that compression can‘t be a huge gain depending on the workloads. However, I would recommend you to try it on versions >= 5.6, as both Facebook and Oracle have done lots of improvements about it.

As more recent reference materials, I would recommend you:

Getting InnoDB Compression Ready for Facebook Scale

InnoDB Compression Present and Future

Benchmarking InnoDB page compression performance

In particular, I like Facebook materials as they are third party (no need for an agenda) and they have one of the largest MySQL deployments in the world. As you can see they have had very successful setups combining SSD technology with compression.

Will it benefit you? That will depend on your workload, working set and setup (IOPS, memory). Depending if you are IO bound, CPU bound or memory bound, compression can affect negatively in some cases, by adding extra CPU, memory requirements (both compressed and uncompressed pages are stored on the InnoDB buffer pool) or generating too many compression failures, augmenting the latency. It also depends on the type of data: compression can help a lot with large text blobs, but it may be useless with already-compressed data.

In my experience, in practice, there are people for which compression was a the holy grail of performance and are very happy with it, but on other cases, we had to revert to uncompressed data as no gain was obtained. While a very heavy writing workload may seem like a bad environment for compression, if in your particular case you are not cpu-bound and memory-bound, but iops-bound it may be none-less helpful.

In a nutshell, it is very difficult to predict results, usually you should setup a test environment for benchmarking and then discover why you get better or worse results (and that way you can play with different block sizes, etc.). Barracuda is completely safe. Compression may or not be for you. And you can always experiment with other compression methods like client-side compression of blobs (for example, if you end up CPU-bound) or other 3rd party engines like TokuDB, in which compression is a big priority, as it is focused in performance for larger datasets than InnoDB can handle.

2、Benchmarking InnoDB page compression performance

配置文件

innodb_file_format=barracuda
innodb_file_per_table = 1
innodb_strict_mode=1 #建议加上

创建数据表

CREATE TABLE name (column1 INT PRIMARY KEY) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Compact和redumdant的区别在就是在于首部的存存内容区别。

compact的存储格式为首部为一个非NULL的变长字段长度列表

redundant的存储格式为首部是一个字段长度偏移列表（每个字段占用的字节长度及其相应的位移）。

在Antelope中对于变长字段，低于768字节的，不会进行overflow page存储，某些情况下会减少结果集IO.

Sql代码

mysql> create table compressed1 ( acol int ) ENGINE=INNODB;
Query OK, 0 rows affected (0.22 sec)

mysql> show create table compressed1\G
*************************** 1. row ***************************
       Table: compressed1
Create Table: CREATE TABLE `compressed1` (
  `acol` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

mysql> create table compressed2 ( acol int ) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected, 4 warnings (0.12 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4.                                    |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
+---------+------+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show create table compressed2\G
*************************** 1. row ***************************
       Table: compressed2
Create Table: CREATE TABLE `compressed2` (
  `acol` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)

mysql>  show variables like "%innodb_file%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+

//change

mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

mysql> create table compressed3 ( acol int ) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; 
Query OK, 0 rows affected (0.32 sec)

mysql> show create table compressed3 \G
*************************** 1. row ***************************
       Table: compressed3
Create Table: CREATE TABLE `compressed3` (
  `acol` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)

//数据文件

-rw-rw---- 1 mysql mysql 8.4K 12月 29 01:18 compressed1.frm
-rw-rw---- 1 mysql mysql  96K 12月 29 01:18 compressed1.ibd
-rw-rw---- 1 mysql mysql 8.4K 12月 29 01:19 compressed2.frm
-rw-rw---- 1 mysql mysql  96K 12月 29 01:19 compressed2.ibd
-rw-rw---- 1 mysql mysql 8.4K 12月 29 03:03 compressed3.frm
-rw-rw---- 1 mysql mysql  64K 12月 29 03:03 compressed3.ibd

//compressed3.ibd 是被压缩过的

使用脚本查看文件信息

[root@localhost innodb]# python  py_innodb_page_info.py  -v /data/mysql/testdb/compressed3.ibd 
page offset 00000000, page type <File Space Header>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 4:
Freshly Allocated Page: 3
File Space Header: 1
[root@localhost innodb]# python  py_innodb_page_info.py  -v /data/mysql/testdb/compressed2.ibd  
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
[root@localhost innodb]# python  py_innodb_page_info.py  -v /data/mysql/testdb/compressed1.ibd  
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1


[root@localhost innodb]# od -t x1 -j 54 -N 4 /data/mysql/testdb/compressed1.ibd 
0000066 00 00 00 00
0000072
[root@localhost innodb]# od -t x1 -j 54 -N 4 /data/mysql/testdb/compressed2.ibd 
0000066 00 00 00 00
0000072
[root@localhost innodb]# od -t x1 -j 54 -N 4 /data/mysql/testdb/compressed3.ibd  
0000066 00 00 00 27
0000072

三、文件格式兼容性检查

InnoDB Plugin引入的新的文件格式，也引入较为完整的文件兼容性检查，以防止误操作非兼容的文件格式。兼容性检查一共有三类：启动数据库时、创建数据表时、访问数据表时。

当数据库启动时候，参数innodb_file_format_check（>=5.1.38）会要求InnoDB在启动时检查当前数据表的格式。设置为ON时，如果检测到不支持的格式，那么InnoDB会启动失败；设置为OFF时，检测到不支持的仅会给出警告，并不会导致启动失败。
当创建数据表时，InnoDB会依据参数InnoDB_file_format进行检查，如果创建的数据表格式高于InnoDB_file_format，则创建会失败。
当访问某个数据表（table-access）时，InnoDB也会进行兼容性检查。只要当前运行的InnoDB版本能够支持的格式，都能够被访问，无论参数InnoDB_file_format的配置。

把innodb_file_format_check设置为OFF是很危险的。在InnoDB启动后，一般需要做一些恢复工作，例如Double write buffer/Insert buffer中的数据处理（这依赖于innodb_fast_shutdown参数），试想如果成功启动，但是某些表是不支持的格式，但是InnoDB仍然安装旧版本做恢复，这可能会毁掉相关数据。

所以，一般建议innodb_file_format_check设置为ON。如果是OFF，关闭InnoDB的innodb_fast_shutdown参数务必设置成0

参考文章

http://www.freehao123.com/vps-cpu-io-unixbench/
http://www.orczhou.com/index.php/2010/03/innodb-plugin-file-format/
http://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/
http://www.orczhou.com/index.php/2010/12/more-about-mysql-innodb-shutdown/
http://dba.stackexchange.com/questions/14246/innodb-file-format-barracuda
http://www.tuicool.com/articles/3qm2U3J
http://www.tuicool.com/articles/qQfAJfI
http://www.xuliangwei.com/xubusi/category/%E8%87%AA%E5%8A%A8%E5%8C%96%E8%BF%90%E7%BB%B4
http://www.xuliangwei.com/xubusi/203.html　　
http://www.xuliangwei.com/xubusi/175.html
http://www.cnblogs.com/billyxp/p/3342969.html?utm_source=tuicool&utm_medium=referral
http://zjadolf.blog.163.com/blog/static/775779202014111063258737/
http://imysql.cn/taxonomy/term/21
http://imysql.com/2015/07/30/mysql-faq-howto-calculate-open-files.shtml
http://imysql.com/2014/09/28/mysql-optimization-case-blob-stored-in-innodb-optimization.shtml

【mysql】关于innodb_file_format

标签：

本文系统来源：http://www.cnblogs.com/chenpingzhao/p/5079325.html

内容总结

以上是互联网集市为您收集整理的【mysql】关于innodb_file_format全部内容，希望文章能够帮你解决【mysql】关于innodb_file_format所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/459330.html

来源：【匿名】

【上一篇】mysql高可用之MHA原理【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【【mysql】关于innodb_file_format】教程文章相关的互联网学习教程文章

Mysql Row_Format 参数讲解【代码】

今天更改数据引擎的时候,突然出现了Table storage engine for ‘#sql-3e9_132‘ doesn‘t have this option这样的提示:通过搜索,发现了一些端倪,下面是对于Row_Format参数的讲解:在MYSQL中，若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表[static/fixed ]，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。那么实际开发中...

mySQL中information_schema数据库的介绍

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。...

MySQL的information_schema的介绍

MySQL中information_schema 数据库是干什么的

MySQL中information_schema是什么大家在安装或使用MYSQL时，会发现除了自己安装的数据库以外，还有一个information_schema数据库。 information_schema数据库是做什么用的呢，使用WordPress博客的朋友可能会想，是不是安装模板添加的数据库呀？看完本片文章后，你就会对information_schema数据库有所了解。information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数...

解析MySQL的information_schema数据库

information_schema数据库是在mysql的版本5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）。其中重要的表有：SCHEMATA表---SCHEMATA表提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。TABLES表---TABLES表提供了关于数据库中的表的信息（包括视图...

MySQL INFORMATION_SCHEMA 使用【代码】

-- 查看指定数据库的表信息SET@table_schema=‘employees‘; SELECT table_name,table_type,engine,table_rows,avg_row_length,data_length,index_length,table_collation,create_time FROMinformation_schema.tables WHEREtable_schema =@table_schemaORDERBY table_name; -- 查看会话连接信息SELECT THREAD_ID,name,type,PROCESSLIST_ID,PROCESSLIST_USER ASuser,PROCESSLIST_HOST AS host,PROCESSLIST_DB AS db,PROCESSLIST_CO...

Mysql 日期时间 DATE_FORMAT(date,format)【代码】

本文转自：http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-formatDATE_FORMAT(date,format) Formats the date value according to the format string. The following specifiers may be used in the format string. As of MySQL 3.23, the “%” character is required before format specifier characters. In earlier versions of MySQL, “%” was optional. SpecifierDescription%aAbbre...

pymysql连接提示format: a number is required, not str【代码】【图】

最近想随手写一个简单的员工管理系统，第一次使用python连接数据库，在这个过程中就遇到了一些问题，遂记录遇到问题习惯性百度一下，很多教程都不适合新手，有些还不知道是不是瞎写的，所以我觉得有必要自己写出来。上面的错误就是说这个port或者主机的值应该是非字符串，于是我试着在变量后面加上intport(int)=‘3309‘结果提示：也就是这个语法是错误的，所以只能换一种方法。connect = pymysql.Connect(host=‘localhost‘...

MySql用DATE_FORMAT截取DateTime字段的日期值【代码】

用 DATE_FORMAT 来格式化日期字段 SELECT DATE_FORMAT(crt_time,‘%Y-%m-%d‘) FROM ad_n_advertise_t原文：http://www.jb51.net/article/53293.htm

Mysql Date_format 及时间相关用法【图】

下面的查询选择了所有记录，其date_col的值是在最后30天以内： Sql代码 mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; DAYOFWEEK(date) 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 Sql代码 mysql> select DAYOFWEEK(‘1998-02-03‘); -> 3 WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 Sql代码...

mysql中information_schema.tables字段说明

1. 获取所有表结构(TABLES)SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA=‘数据库名‘; TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。各字段说明如下: 字段含义table_catalog数据表登记目录table_schema数据表所属的数据库名table_name表名称table_type表类型[system view|base table]engine使用的数据库引擎[MyISAM|CSV|InnoDB]ve...

mysql切换数据库提示警告：Reading table information for completion of table and column names【图】

登录数据库后，选择数据库时发现以下提示，mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 意思是预读这个库中表以及表列信息，一般原因是当库中表很多，表中数据很大时，就会出现执行use <库名>后半天没反应，连接很慢的情况，解决办法就是 -A 方式登录数据库，不会预读库中表信息。shell> mysql...

mysql - information_schema.tables视图【代码】

查看information_schema.tables：DESC information_schema.TABLES TABLE_SCHEMA ---->库名 TABLE_NAME ---->表名 ENGINE ---->引擎 TABLE_ROWS ---->表的行数 AVG_ROW_LENGTH ---->表中行的平均行（字节） INDEX_LENGTH ---->索引的占用空间大小（字节）使用案例：查询整个数据库中所有库和所对应的表信息：SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY tab...