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