通過以下命令看一下mysql中 innodb_file_format的配置
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | 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 | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.25-log | +------------+ 1 row in set (0.00 sec) mysql> select @@innodb_version; +------------------+ | @@innodb_version | +------------------+ | 5.6.25 | +------------------+ 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 | +--------------------------+----------+ 4 rows in set (0.00 sec) mysql> SET GLOBAL innodb_file_format = barracuda; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "%innodb_file%"; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | +--------------------------+-----------+ 4 rows in set (0.00 sec) mysql> SET GLOBAL innodb_file_format_max = barracuda; Query OK, 0 rows affected (0.00 sec) 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.01 sec) mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2015-12-29 01:31:16 a3731b70 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 16015 srv_idle srv_master_thread log flush and writes: 16017 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5 OS WAIT ARRAY INFO: signal count 5 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 5, rounds 150, OS waits 5 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 125966 Purge done for trx's n:o < 124524 undo n:o < 0 state: running but idle History list length 484 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 41, OS thread handle 0xa3731b70, query id 3846 localhost root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 548 OS file reads, 43 OS file writes, 33 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 1256, seg size 1258, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 553193, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1121969516 Log flushed up to 1121969516 Pages flushed up to 1121969516 Last checkpoint at 1121969516 0 pending log writes, 0 pending chkp writes 18 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 136019968; in additional pool allocated 0 Dictionary memory allocated 47602 Buffer pool size 8191 Free buffers 7823 Database pages 366 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 356, created 10, written 38 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 366, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 2255, id 2837244784, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.01 sec)
Antelope（羚羊）是Built-in-InnoDB（MySQL內置的InnoDB）支持文件格式的代號，有兩種“數據表格式”（row_format）：Redundant（冗余）、Compact（緊湊）
Barracuda（梭子魚）是InnoDB Plugin支持的文件格式，在原來的基礎上新增了兩種數據表格式的支持：Dynamic 和 Compressed
innodb_file_format在配置文件中指定；row_format則在創建數據表時指定
同時Barracude也支持 old redundant and compact row formats
下面是來自stackexchange上面的回答 和 性能測試
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
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:
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.
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.
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
