【mysql】关于innodb_file_format

一、几条mysql命令

通过以下命令看一下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_idlesrv_master_thread log flush and writes: 16017----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 5OS WAIT ARRAY INFO: signal count 5Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 5, rounds 150, OS waits 5RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 125966Purge done for trx's n:o < 124524 undo n:o < 0 state: running but idleHistory list length 484LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 41, OS thread handle 0xa3731b70, query id 3846 localhost root initshow 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: 0Pending flushes (fsync) log: 0; buffer pool: 0548 OS file reads, 43 OS file writes, 33 OS fsyncs0.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 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 553193, node heap has 2 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1121969516Log flushed up to  1121969516Pages flushed up to 1121969516Last checkpoint at 11219695160 pending log writes, 0 pending chkp writes18 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 136019968; in additional pool allocated 0Dictionary memory allocated 47602Buffer pool size  8191Free buffers    7823Database pages   366Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 356, created 10, written 380.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 366, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 2255, id 2837244784, state: sleepingNumber of rows inserted 0, updated 0, deleted 0, read 00.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)

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：【mysql】关于innodb_file_format

关键词：MYSQL