mysql additional pool 指的是什么？

最新推荐文章于 2023-05-21 16:01:33 发布

cuiyangshua0551

最新推荐文章于 2023-05-21 16:01:33 发布

阅读量147

点赞数

文章标签：数据库 c/c++ 数据结构与算法

刚开始认为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