innodb_file_format梭鱼

Question

我有几个问题要问那些更熟悉的人。我的大多数例子都是运行羚羊，尽管有支持梭鱼。

我想玩些压缩的桌子。我的理解是，这只能在梭子鱼的格式下使用。

1. 我看到innodb_file_format是动态的，所以我可以在没有跳转的情况下切换。我应该知道这样做有什么意义吗？我所能知道的是，这意味着新表或随后修改的表将使用该格式创建。这些都是对的吗？
2. 我希望不要把我所有的桌子都换掉。是犹太有羚羊和芭蕾桌共存在同一个表空间吗？即使成功了，你还有什么要注意的吗？

根据我从测试中读到的和收集到的，答案是:是的。是。我没有把握。

更新

自从这篇文章发表以来，我一直在运行一些动态表和一些压缩表。此外，当时我忘了读http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format-identifying.html。

启用给定innodb_file_format后，此更改只适用于新创建的表，而不适用于现有表。如果确实创建了一个新表，则包含该表的表空间将被标记为“最早”或“最简单”的文件格式，这是表的功能所必需的。例如，如果启用文件格式Barracuda，并创建未压缩且不使用ROW_FORMAT=DYNAMIC的新表，则包含该表的新表空间被标记为使用文件格式羚羊。

因此，即使您允许梭鱼，表也将作为羚羊创建。除非将每个表指定为row_format动态表或压缩表，否则混合是不可避免的。

在引入第一个梭鱼表时，没有迹象表明您应该做一个完整的转储和重新加载(如升级mysql的主要版本时推荐的那样)。

Answer 1

所以我回答这个问题已经晚了4年了：

• InnoDB文件格式是在InnoDB独立于MySQL服务器(例如: MySQL 5.1可以运行两个不同版本的InnoDB)时构思的。
• 您不想运行Barracuda (在2012年)的原因是，它可能降低MySQL降级的灵活性(即升级失败后，您希望回到无法读取更新格式的版本)。也就是说，不应该有技术上的原因，为什么羚羊更好。
• 在MySQL 5.7中，innodb_file_format选项被否决了。因为MySQL和InnoDB不再是独立的，因此InnoDB可以使用升级的MySQL规则，以及需要什么向后兼容性。不需要设置混乱！
• 在MySQL 5.7中，默认切换到Barracuda/DYNAMIC。由于所有当前支持的MySQL版本都可以读取这种格式，因此可以安全地离开羚羊，仍然提供降级。
• 在MySQL 5.7服务器上，将在下一个表重建(优化表等)中将羚羊表升级到Barracuda/DYNAMIC。这是除非它们是用ROW_FORMAT=oldrowformat专门创建的。
• 在MySQL 8.0中，innodb_file_format选项被删除。

MySQL 5.7还引入了默认为DYNAMIC的选择innodb_default_row_format。这解决了更新中的要点。

Answer 2

Just give a try!!!

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.21-log |
+------------+
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.00 sec)

I had observed a single line logged in Error Log file :

[root@dhcppc0 Desktop]# tail -1 /usr/local/mysql/data/dhcppc0.err
120402 11:26:52 [Info] InnoDB: the file format in the system tablespace is
now set to Barracuda.

After switching to barracuda file format, I could also access my Database
and tables without any error :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opentaps1          |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use opentaps1;
Database changed
mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|     3244 |
+----------+
1 row in set (0.42 sec)

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

mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status: 
=====================================
120402 11:36:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18446744073709534037 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 1_second, 12 sleeps, 1 10_second, 2 background,
2 flush
srv_master_thread log flush and writes: 12
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter F01
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION F00, not started
MySQL thread id 1, OS thread handle 0x7f38309f9710, query id 28 localhost
root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer
thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (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
554 OS file reads, 7 OS file writes, 7 OS fsyncs
-0.01 reads/s, 16384 avg bytes/read, -0.00 writes/s, -0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 15 buffer(s)
-0.15 hash searches/s, -0.12 non-hash searches/s
---
LOG
---
Log sequence number 221536390
Log flushed up to   221536390
Last checkpoint at  221536390
0 pending log writes, 0 pending chkp writes
10 log i/o's done, -0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 3476070
Buffer pool size   8192
Free buffers       7635
Database pages     542
Old database pages 220
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 542, created 0, written 1
-0.01 reads/s, -0.00 creates/s, -0.00 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead -0.00/s, evicted without access -0.00/s, Random read ahead
-0.00/s
LRU len: 542, unzip_LRU len: 0
I/O sum[0]:cur[238], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2937, id 139879303665424, state: waiting for server
activity
Number of rows inserted 0, updated 0, deleted 0, read 3244
-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.18 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)