MySQL Architecture and Components

MySQL Architecture and Components

This blog post is all about new MySQL 5.7 physical, logical architecture and it’s components.In this blog post, I will try to explain things in flow including data processing and SQL execution in MySQL with the help of diagrams.

Unlike the other databases, MySQL is a very flexible and offers different kinds of storage engines as a plugin for different kinds of needs. Because of this, MySQL architecture and behavior will also change as per the use of storage engines, for example transactional [InnoDB] and non-transactional [MyISAM] engines data storage and SQL execution methods will be different and within the server it will use engine specific components like memory and buffers depending on type storage engine will get used for the SQL operation.
Will discuss more InnoDB, since it’s default and main storage engine for MySQL.

MySQL Physical Architecture:

Configuration files:

auto.cnf :  Contains server_uuid
my.cnf :     MySQL Configuration file.

Miscellaneous files:

–basedir=dir_name
The path to the MySQL binaries installation directory.
–datadir=dir_name
The path to the MySQL data directory contains data, status and log files.
–pid-file=file_name
The path name of the file in which the server should write its process ID.
–socket=file_name, -S file_name
On Unix, the name of the Unix socket file to use, for connections made using a named pipe to a local server.
–log-error=file_name
Log errors and startup messages to this file.

MySQL Logical Architecture:

Client :
Utility to connect MySQL server.

Server :
MySQL instance where actual data getting stored and data processing is happening.

mysqld:
MySQL Server daemon program which runs in the background and manages database related incoming and outgoing requests from clients. mysqld is a multi-threaded process which allows connection to multiple sessions listen for all connections and manages MySQL instance.

MySQL memory allocation:
Main MySQL memory is dynamic, examples innodb_buffer_pool_size (from 5.7.5), key_buffer_size etc.Working on shared nothing principal which means, every session has unique execution plan and we can share data sets only for the same session.

GLOBAL:

• Allocated once
• Shared by the server process and its threads

SESSION:

• Allocated for each mysql client session
• Dynamically allocated and deallocated
• Used for handling query result
• Buffer size per session

Connection/Thread Handling:
Manages client connections/sessions [mysql threads]

Parser:
Check for SQL syntax by checking every character in SQL query and generate SQL_ID for each SQL query.

Optimizer :
Created efficient query execution plan as per the storage engine.It will rewrite a query.Example: InnoDB has shared buffer so optimizer will get pre-cached data from it. Using table statistics optimizer will generate an execution plan for a SQL query.

Metadata cache:
Cache for object metadata information and statistics.

Query cache:
Shared identical queries from memory.If an identical query from client found in query cache then, the MySQL server retrieves the results from the query cache rather than parsing and executing that query again. It’s a shared cache for sessions, so a result set generated by one client can be sent in response to the same query issued by another client. Query cache based on SQL_ID.SELECT data into view is the best example to pre-cache data using query cache

key cache:
Cache table indexes.In MySQL keys are indexes(In oracle keys are constraints) if index size is small then it will cache index structure and data leaf.If an index is large then it will only cache index structure.Used by MyISAM storage engine.

Storage engine:
MySQL component that manages physical data (file management) and locations. Storage engine responsible for SQL statement execution and fetching data from data files. Use as a plugin and can load/unload from running MySQL server.Few of them as following,

1. InnoDB :
   • Fully transactional ACID.
   • Offers REDO and UNDO for transactions.
   • Data storage in tablespace:

   – Multiple data files
   – Logical object structure using innodb data and log buffer

   • Row level locking.
2. NDB (For MySQL Cluster):
   • Fully Transactional and ACID Storage engine.
   • Distribution execution of data and using multiple mysqld.
   • NDB use logical data with own buffer for each NDB engine.
   • Offers REDO and UNDO for transactions.
   • Row level locking.
3. MyISAM:
   • Non-transactional storage engine
   • Speed for read
   • Data storage in files and use key, metadata and query cache

   – FRM  for table structure
   – MYI for table index
   – MYD for table data

   • Table level locking.
4. MEMORY:
   • Non-transactional storage engine
   • All data stored in memory other than table metadata and structure.
   • Table level locking.
5. ARCHIVE:
   • Non-transactional storage engine,
   • Store large amounts of compressed and unindexed data.
   • Allow INSERT, REPLACE, and SELECT, but not DELETE or UPDATE sql operations.
   • Table level locking.
6. CSV:
   • Stores data in flat files using comma-separated values format.
   • Table structure need be created within MySQL server (.frm)

SQL execution

Other storage engines  like InnoDB,NDB are having logical structure for data and they have their own data buffer. This buffer define on storage engine level.

About InnoDB Storage Engine:

• Fully transactional ACID.
• Row level locking.
• Offers REDO and UNDO for transactions.
• Data storage in tablespace:
  – Multiple data files
  – Logical object structure using InnoDB data and log buffer

• Use shared file to store objects [Data and Index in the same file]
• InnoDB data is 100% of a logical structure, data stored physically.
• InnoDB Read physical data and build logical structure[Blocks and Rows]
• Logical storage called as TABLESPACE.

InnoDB storage engine architecture:

Tablespace:

Storage for InnoDB is divided into tablespaces. A tablespace is a logical structure associated with multiple data files(objects). Each tablespace contains pages(blocks),extents and segments.

Pages: a Smallest piece of data for InnoDB also called as blocks. Default page size is 16kb and page can hold one or more rows depending on row size.

Available page sizes: 4kb,8kb,16kb,32kb,64kb
Variable name           : innodb_page_size
Need  to configure before initializing mysqld server.

Extents:  It’s a group of pages.For better I/O throughput innodb read or write a collection of pages i.e one extent at a time.
For a group of pages with default page size 16kb, extent size up to 1mb.
Doublewrite buffer read/write/allocate or free data as one extent at a time.

Segments:  Collection of files in InnoDB tablespace.Use up to 4 extents in a segment.

Innodb components:

In Memory:

InnodDB buffer pool:
Central buffer for InnoDB storage engine.In this data buffer, we load blocks and cache table and index data.
– The main memory where InnoDB cache table data and indexes.
–  Size up to 80% of physical memory on dedicated DB server.
– Shared buffer across all sessions.
– InnoDB use LRU (Least Recently Used ) page replacement algorithm.
– Data that is being reused is always in the same memory.
– Data that does not used get phased out eventually.

Change buffer:
In a memory change buffer is a part of InnoDB buffer pool and on disk, it is part of system tablespace, so even after database restart index changes remain buffered.Change buffer is a special data structure that caches changes to secondary index pages when affected pages not in the buffer pool.

Redo log buffer:
Buffer for redo logs, hold data to written to the redo log.Periodically data getting flushed from redo log buffer to redo logs. Flushing data from memory to disk managed by innodb_log_at_trx_commit and innodb_log_at_timeout configuration option.

– A large size of redo log buffer enables a large transaction to run without writing redo logs to disk before the transaction commit.
– Variable:
innodb_log_buffer_size (default 16M)

On Disk:

System tablespace:  
Apart from the table data storage, InnoDB’s functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation. It contains several types of information for InnoDB objects.

• Contains:
  Table Data Pages
  Table Index Pages
  Data Dictionary
  MVCC Control Data
  Undo Space
  Rollback Segments
  Double Write Buffer (Pages Written in the Background to avoid OS caching)
  Insert Buffer (Changes to Secondary Indexes)
• Variables:
  innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

  By enabling innodb_file_per_table (the default) option, we can store each newly created table (data and index) in a separate tablespace. Advantage for this storage method is less fragmentation within disk data file.

General tablespace:
Shared tablespace to store multiple table data. Introduce in MySQL 5.7.6. A user has to create this using CREATE TABLESPACE syntax. TABLESPACE option can be used with CREATE TABLE to create a table and ALTER TABLE to move a table in general table.

– Memory advantage over innodb_file_per_table  storage method.
– Support both Antelope and Barracuda file formats.
–  Supports all row formats and associated features.
–  Possible to create outside data directory.

InnoDB data dictionary:
Storage area in system tablespace made up of internal system tables with metadata information for objets[tables,index,columns etc.]

Double write buffer:
Storage area in system tablespace where innodb writes pages from innodb buffer pool, before writing to their proper location in the data files.
In case mysqld process crash in the middle of a page writes, at the time of crash recovery InnoDB can find a good copy of the page from doublewrite buffer.

Variable: inndb_doublewrite (default enable)

REDO logs:
Use for crash recovery. At the time of mysqld startup, InnoDB perform auto recovery to correct data written by incomplete transactions. Transactions that not finish updating data files before an unexpected mysqld shutdown are replayed automatically at the time of mysqld startup even before taking any connection. It uses LSN(Log Sequence Number) value.
Plenty of data changes can not get written to disk quickly, so it will go under redo and then to the disk.

Why we need a redo for recovery?
Let’s take an example, User changing data in innodb buffer and commit, somewhere it needs to go before writing into a disk. Because in the case of crash buffer data will lost, that’s why we need redo logs.

– In redo, all changes will go with info like row_id, old column value, new column value, session_id and time.
– One commit complete data will under disk in a data file.
– Variables:
Innodb_log_file_in_group= [# of redo file groups]
Innodb_log_file_size= [Size for each redo file]

UNDO tablespace and logs:
UNDO tablespace contains one or more undo logs files.
UNDO manages consistent reads by keeping modified uncommitted data for active transaction [MVCC]. Unmodified data is retrieved from this storage area.Undo logs also called as rollback segments
By default, UNDO logs are part of system tablespace, MySQL allows to store undo logs in separate UNDO tablespace/s [Introduce in MySQL 5.6]. Need to configure before initializing mysqld server.

– When we configure separate undo tablespace, the undo logs in the system tablespace become inactive.
– Need to configure before initializing mysqld server and can not change after that.
– We truncate undo logs, but can not drop.
– Variables :
innodb_undo_tablespace : # of undo tablespaces, default 0
innodb_undo_directory:Location for undo tablespace,default is data_dir with 10MB size.
innodb_undo_logs : # of undo logs, default and max value is ‘128’

Temporary tablespace:
Storage to keep and retrieve modified uncommitted data for temporary tables and related objects.Introduce in MySQL 5.7.2 and use for rollback temp table changes while a server is running.

– Undo logs for temporary tables reside in the temp tablespace.
– Default tablespace file ibtmp1 getting recreated on server startup.
–  Not getting used for crash recovery.
– Advantage: Performance gain by avoiding redo logging IO for temp tables and related objects.
– Variable:
innodb_temp_data_file_path = ibtmp1:12M:autoextend (default)

And All SET !!