mysql additional pool 指的是什麼？

psufnxk2000發表於2015-04-18

MySql

剛開始認為additional pool指的是 innodb_additional_mem_pool_size。

但是看到下面的情況，讓我有點疑惑：

mysql> show variables like '%additional%';

+---------------------------------+---------+

| Variable_name | Value |

+---------------------------------+---------+

| innodb_additional_mem_pool_size | 8388608 |

+---------------------------------+---------+

mysql> show engine innodb status;

| InnoDB | |

=====================================

2015-04-18 22:49:34 7fafbf1f9700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 20 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 53165 srv_idle

srv_master_thread log flush and writes: 53166

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 4

OS WAIT ARRAY INFO: signal count 4

Mutex spin waits 1, rounds 30, OS waits 1

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 7969

Purge done for trx's n:o < 7617 undo n:o < 0 state: running but idle

History list length 75

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 2, OS thread handle 0x7fafbf1f9700, query id 41 localhost root init

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

313 OS file reads, 92 OS file writes, 50 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 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 0 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 1912008

Log flushed up to 1912008

Pages flushed up to 1912008

Last checkpoint at 1912008

0 pending log writes, 0 pending chkp writes

24 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 135498

Buffer pool size 8192

Free buffers 7870

Database pages 322

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 297, created 25, written 51

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: 322, 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. 2741, id 140392755988224, 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

============================

看狀態的時候卻是 0，

檢視這個引數的文件：

The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal

data structures.

資料字典的資訊和別的內部資料結構

但是上面蘭色部分有一個資料字典的大小。

接著看文件

This variable relates to the InnoDB internal memory allocator, which is unused if

innodb_use_sys_malloc is enabled. As of MySQL 5.6.3, innodb_additional_mem_pool_size

is deprecated and will be removed in a future MySQL release.

檢視

mysql> show variables like '%innodb_use%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_use_native_aio | ON |

| innodb_use_sys_malloc | ON |

+-----------------------+-------+

2 rows in set (0.00 sec)

這個是啟用的，關閉掉試試：

關閉重啟之後：

mysql> show variables like '%innodb_use%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 1

Current database: test

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_use_native_aio | ON |

| innodb_use_sys_malloc | OFF |

+-----------------------+-------+

2 rows in set (0.01 sec)

mysql> show variables like '%addition%';

+---------------------------------+---------+

| Variable_name | Value |

+---------------------------------+---------+

| innodb_additional_mem_pool_size | 8388608 |

+---------------------------------+---------+

1 row in set (0.00 sec)

| InnoDB | |

=====================================

2015-04-18 23:17:39 7f380e683700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 52 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 52 srv_idle

srv_master_thread log flush and writes: 52

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

Mutex spin waits 0, rounds 0, OS waits 0

RW-shared spins 2, rounds 60, OS waits 2

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 8466

Purge done for trx's n:o < 7617 undo n:o < 0 state: running but idle

History list length 75

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

0 lock struct(s), heap size 488, 0 row lock(s)

MySQL thread id 1, OS thread handle 0x7f380e683700, query id 11 localhost root init