Mysql InnoDB note memory

Mysql InnoDB note memory InnoDB status SQL code show engine innodb Status \ G; execution result Type: InnoDBName: status: Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD --------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 17509 srv_idlesrv_master_thread log flush and writes: 17509--SEMAPHORES--OS WAIT ARRAY INFO: reservation count 2OS wait array info: sign Al count 2 Mutex spin waits 5, rounds 12, OS waits 0RW-shared spins 2, rounds 60, OS waits 2RW-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 idleHistory list length 588 list of transactions for each session: --- TRANSACTION 0, not s TartedMySQL thread id 1, OS thread handle 0x8ac, query id 15 localhost 127.0.0.1 rootinitshow 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; B Uffer pool: 0312 OS file reads, 5 OS file writes, 5 OS fsyncs0.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 merge S segSize: The current 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 2122042519log flushed up to 2122026419 Pages flushed up to 2122042519las T checkpoint at 21220264190 pending log writes, 0 pending chkp writes8 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 299Old 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 00.00 youngs/s, 0.00 non-youngs/sPages read 299, created 0, written 10.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/s LRU len: 299, 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 id 2708, 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 =================== InnoDB memory by buffer pool, redo the log buffer pool and extra memory. SQL code show variables like 'innodb _ buffer_pool_siz E '\ G; 134217728 show variables like 'innodb _ log_buffer_size' \ G; 8388608 show variables like 'innodb _ additional_mem_pool_size '\ G; 8388608 the largest part of the buffer pool, 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.