MysqlInnoDB note memory _ MySQL

MysqlInnoDB note memory bitsCN.com

Mysql InnoDB note memory

InnoDB status

SQL code

Show engine innodb status/G;

Execution result

Type: InnoDB

Name:

Status:

Per second averages calculated from the last 6 seconds

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

BACKGROUND THREAD

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

Srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 17509 srv_idle.

Srv_master_thread log flush and writes: 17509

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

Mutex spin waits 5, rounds 12, 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: 2.40 mutex, 30.00 RW-shared, 0.00 RW-excl

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

TRANSACTIONS

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

Trx id counter 17154

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

History list length 588

List of transactions for each session:

--- TRANSACTION 0, not started

MySQL thread id 1, OS thread handle 0x8ac, query id 15 localhost 127.0.0.1 root

Init

Show engine innodb status

--------

File I/O

--------

I/O thread 0 state: wait Windows aio (insert buffer thread)

I/O thread 1 state: wait Windows aio (log thread)

I/O thread 2 state: wait Windows aio (read thread)

I/O thread 3 state: wait Windows aio (read thread)

I/O thread 4 state: wait Windows aio (read thread)

I/O thread 5 state: wait Windows aio (read thread)

I/O thread 6 state: wait Windows aio (write thread)

I/O thread 7 state: wait Windows aio (write thread)

I/O thread 8 state: wait Windows aio (write thread)

I/O thread 9 state: wait Windows aio (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

312 OS file reads, 5 OS file writes, 5 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

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

Insert buffer and adaptive hash index performance insert buffer and adaptive hash insert buffer are not part of the BUFFER pool. it is used for secondary indexes, and the indexes are not unique. if the primary key is auto-incrementing, the clustered index will be inserted in order quickly. However, if the table is not a unique non-clustered index, you need to insert the discrete storage B + tree to the leaf nodes of non-clustered indexes.

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

Ibuf: size 1, free list len 0, seg size 2, 0 merges segSize: The current inserted buffer size is 2*16 kB. free list is the length of the idle list. size is the number of merged records. insert is the number of inserted records, the number of pages merged by merged, and the number of merged merges.

Merged operations:

Insert 0, delete mark 0, delete 0

Discarded operations:

Insert 0, delete mark 0, delete 0

Hash table size 553253, node heap has 1 buffer (s)

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

---

LOG

---

Log sequence number 2122026419

Logflushed up to 2122026419

Pages flushed up to 2122026419

Last checkpoint at 2122026419

0 pending log writes, 0 pending chkp writes

8 log I/o's done, 0.00 log I/o's/second

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

BUFFER POOL AND MEMORY

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

Total memory allocated 135987200; in additional pool allocated 0

Dictionary memory allocated 32514

Buffer pool size 8192

Free buffers 7892

Database pages 299

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 299, created 0, written 1

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: 299, 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 id 2708, 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

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

InnoDB memory

Consists of the buffer pool, redo log buffer pool, and additional memory.

SQL code

Show variables like 'innodb _ buffer_pool_size '/G; 134217728

Show variables like 'innodb _ log_buffer_size '/G; 8388608

Show variables like 'innodb _ additional_mem_pool_size '/G; 8388608

The buffer pool is the largest part used to store various data caches. the engine reads data from the buffer pool by page 16KB/page, and then retains data in the buffer pool by LRU. if the database file needs to be modified, first modify the pages in the buffer pool. after modification, it is a dirty page, and then refresh the dirty pages to the file at a certain frequency.

The Buffer pool size is 8192*16 (KB/page)/8192 =?

The cache data types in the buffer pool include: index page, data page, UNDO page, insert buffer, adaptive hash index, InnoDB lock information, and data dictionary.

After AWE is enabled, the engine automatically disables adaptive hash indexes.

The log buffer puts the redo log information into this buffer first, and then refreshes it to the redo log file at a certain frequency.

BitsCN.com