InnoDB for Mysql 5.6

Mysql architecture

InnoDB High Level Overview

• what is in the buffer pool
  • index page
  • data page
  • redo page
  • insert buffer
  • adaptive hash index
  • lock info
  • data dictionary
    
• the default page size is 16KB
• innodb_buffer_pool_size and innodb_buffer_pool_instances
  • select pool_id, pool_size, free_buffers, database_pages from information_schema.innodb_buffer_pool_stats; #show buffer pool stats.
  • innodb_buffer_pool_instances divides the buffer pool into specified number of separate regions. each with its own LRU list and data structures. The advantage is to reduce contention during concurrent memeory.
  • buffer instance param will only take effect when innodb_buffer_pool_size is larger than 1GB, and recommend use multiple Gigebytes
• from memory point of view, innodb has three types of page: clean page, dirty page and free page.
  • clean page, data in memory and in disk is identical
  • dirty page, not identical
  • free page, not used page
• innodb logically has data page type:

select distinct page_type from information_schema.innodb_buffer_page_lru where;
-------page_type-------------
SYSTEM
INODE
IBUF_INDEX
INDEX
IBUF_BITMAP
TRX_SYSTEM
UNDO_LOG
FILE_SPACE_HEADER
BLOB

• LRU list, Free list and Flush list**

  • innodb use list to manage different pages.
  • LRU list saves already read page. Use midpoint tech to save lastest read page into LRU list.
  • free list has all not used free page, when server starts, all the page are in free list, and move to LRU.
  • flush list contains all the dirty page. Use checkpoint to flush dirty data into disk. Dirty page may exist both in flush list and LRU list.
  • innodb_old_blocks_pct and innodb_old_blocks_time

  variables like '%innodb_old_block%'``` # innodb_old_blocks_pct sets percertage of old list, innodb_old_blocks_time sets the midpoint page will be in the hot part of LRU list after certain time.

  - ```show engine innodb status;``` #show the page info 
  ``` bash
  ----------------------BUFFER POOL AND MEMORY----------------------
  Total large memory allocated 2198863872
  Dictionary memory allocated 776332
  Buffer pool size   131072
  Free buffers       124908
  Database pages     5720
  Old database pages 2071
  Modified db pages  910
  Pending reads 0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 4, not young 0
  0.10 youngs/s, 0.00 non-youngs/s
  Pages read 197, created 5523, written 5060
  0.00 reads/s, 190.89 creates/s, 244.94 writes/s
  Buffer pool hit rate 1000 / 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: 5720, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]

  

  • select * from information_schema.innodb_buffer_pool_stats # can also show the buffer pool stats.
  • buffer poll hit rate is the rate hit in memory not in disk, the higher the better
  • select * from information_schema.innodb_buffer_page_lru where compressed_size <> 0 # compressed lru page

• redo log buffer

  show variables like 'innodb_log_buffer_size';
  
  innodb_log_buffer_size	16777216

• three conditions that flush data into disk

  • every 1s master thread trigger
  • every transaction
  • redo buffer free page less than 1/2

• Dirty page checkout mechanization

• write-ahead-log write to log before write page

• the problem to solve:

  1. reduce database recover time
  2. flush data when buffer pool is full
  3. refresh dirty page when redo log is out of work

• Insert buffer

• primary key is the unique identification of the row, usually application insert rows based on auto-increased primary key.
  if we insert primary key that is random, we may not get sequence data in disk