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
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
464 OS file reads, 5 OS file writes, 5 OS fsyncs
0.37 reads/s, 16384 avg bytes/read, 0.08 writes/s, 0.08 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 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 7.98 non-hash searches/s
---
LOG
---
Log sequence number 1912018
Log flushed up to   1912018
Pages flushed up to 1912018
Last checkpoint at  1912018
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 165306202; in additional pool allocated 748032
Dictionary memory allocated 96418
Buffer pool size   8191
Free buffers       7894
Database pages     297
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 0, written 1
0.31 reads/s, 0.00 creates/s, 0.02 writes/s
Buffer pool hit rate 991 / 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: 297, 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. 3848, id 139878732592896, 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
============================

紅色部分有值了， 並且 比資料字典的要大，大出的那部分可能是 文件中說的別的  內部資料結構的吧！

文件中說這個引數是：
Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF).
innodb是否使用OS記憶體，
並且 還說
As of MySQL 5.6.3, innodb_use_sys_malloc is deprecated and will be removed in a future MySQL
release.
還說了要 棄用。


 innodb_additional_mem_pool_size 要棄用 ，innodb_use_sys_malloc也要棄用， 也沒有看到要用那個引數去替換。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1585242/，如需轉載，請註明出處，否則將追究法律責任。

相關文章