This Blog will focus on MySQL logical and physical architecture overview. MySQL operates in networked environment using client/server architecture. A MySQL installation has the following major components.

  • MySQL Server
    • is also referred to as mysqld
    • Manage access to the actual database on disk or memory.
    • Is multithreaded
    • supports many simultaneous client connections
    • Supports multiple storage engines – both transactional or non-transactional
    • A single host can have multiple mysqld instances running.
  • Client programs
    • Utility to connect MySQL server.
    • programs are mysql, mysqladmin, mysqlcheck, mysqldump,mysqlimport, mysqlpump, mysqlsh, mysqlshow,mysqlslap
  • MySQL non-client programs.
    • Programs that act independently of the server.
    • myisamchk (table checks and repair operation) and myisampack (compressed read only version of MyISAM table)

MySQL Logical Architecture

MySQL’s architecture is a web of task-related functions that work together to completed the responsiblity of database server. The diagram as below is an overview of the subsystems that interact with each other through a well-defined function interface. Each subsystem has its own responsibilities and independent from each other.

mysql

It’s very difficult to explain each and every component in one blog, but I’ll concentrate on main core parts. The brain of the MySQL server:

  • Parsing:  Check for SQL syntax by checking every character in SQL query and generate SQL_ID for each SQL query.
  • Optimizing: Creates efficient query execution plan as per the storage engine. It will rewrite a query.
  • Executing: Executes the optimized path for the SQL command passed through the parser and optimizer.

Query Cache: is a fast-in-memory store to quickly look up the result set of a particular SELECT statement already executed and cached in the memory. Query cache based on SQL_ID.SELECT data into view is the best example of pre-cache data using query cache.

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,

  • InnoDB
    • Fully Transactional ACID and offers REDO/UNDO for transactions.
    • Data Storage in tablespaces and multiple data files.
    • Row Level locking
    • Logical object structure using InnoDB data and log buffer
  • NDB (For MySQL cluster)
    • Fully Transactional ACID and offers REDO/UNDO for transactions.
    • Distribution execution of data and using multiple mysqld.
    • Logical data with own buffer for each NDB engine
    • Row level locking
  • MyISAM
    • Non-transactional with data storage in files
    • Speed for read and table level locking
    • MYI for table index and MYD for table read
  • Memory
    • Non-transactional data stored in memory other than metadata and structure
    • Table level locking

The Base Function Library is a set of common function shared among all of MySQL subsystems.

Process, Thread and resource Management: MySQL utilizes a thread-based server architecture.

Cache and Buffer management enables  caching and retrieval of various types of data used by all threads executing in the server process. Caching and buffer reduce the number of requests to expensive disk based I/O’s.

Memory Allocation: Two type of Memory allocation.

  • Per session: Session specific, Dynamic allocated/de-allocated, for query results.
  • Per instance: Allocated only once and per server instance, shared by all server processes and all of its threads.

MySQL Physical Architecture

Here is an overview of MySQL base and data directory. Location depends of installation, OS used and data storage.

mysql_phy

I installed MySQL on windows environment and here is the some basic command to start interacting with MySQL.

Data directory and Sub directory:

Configuration files:

Thanks
Mandy

Reference:

https://dev.mysql.com/doc/

Leave a Reply