Server Layer

The Server layer encompasses connectors, the query cache, parser, optimizer, and executor. It includes most of MySQL's core service functionalities and all built-in functions (e.g., date, time, mathematical, encryption). Cross-storage-engine features such as stored procedures, triggers, and views are implemented here.

Connection Pool

Connector

Connections are established via client tools or commands: mysql -h$host -P$port -u$user -p. An incorrect password results in an "Access denied for user" error and client termination. Upon successful authentication, the connection's privileges are fixed for its duration. Modifying a user's permissions with an administrator account does not effect existing connections; new privileges apply only to subsequent connections.

Connection Pool Mechanics

Creating and destroying connections for each request is resource-intensive under high concurrency. A connection pool acts as an intermediary. When a connection is requested, the pool checks for idle connections. If available, one is returned. Otherwise, if the maximum pool size isn't reached, a new connection is created. If the maximum is reached, the request waits until a connection is released or an error is returned. After use, a connection is returned to the pool. If it has exceeded a maximum usage count, it is destroyed; otherwise, it becomes available for reuse.

The default maximum connections (max_connections) is 100, configurable based on max_used_connections. The theoretical limit is 16384.

Idle Connection Handling

After establishment, an inactive connection becomes idle, viewable via SHOW PROCESSLIST. By default, the connector disconnects idle connections after 8 hours (wait_timeout). A subsequent request on a disconnected connection triggers "Lost connection to MySQL server during query," requiring reconnection.

Long connections persist across multiple client requests, while short connections are closed after few operations. Exclusive use of long connections can cause rapid memory growth because temporary memory is managed per connection object and released only upon disconnection, potentially leading to Out-Of-Memory errors and MySQL restart.

Solutions:

1. Periodically disconnect long connecsions after heavy queries.
2. In MySQL 5.7+, execute mysql_reset_connection after large operations to reinitialize connection resources without re-authentication.

Query Cache

SELECT statements were cached in memory as key-value pairs (key: query, value: result). This feature is deprecated; frequent table updates invalidate all related cache entries. Note: The query cache module is removed in MySQL 8.0.

Parser

MySQL performs lexical and syntactic analysis. The parser checks SQL syntax against grammar rules. Errors yield "You have an error in your SQL syntax," with the first error location indicated near "use near." It also validates table and column existence.

The parser generates a syntax tree, which is then passed to the optimizer.

Optimizer

The optimizer decides on index usage and join order for multi-table queries, finalizing the execution plan.

Executor (SQL Interface)

The executor verifies table access permissions. If authorized, it invokes the storage engine's APIs to perform operations and returns results to the client.

Storage Engine Layer

This layer handles data storage and retrieval via a pluggable architecture supporting InnoDB, MyISAM, Memory, and others. InnoDB is the default since MySQL 5.5.5.

InnoDB vs. MyISAM

Transactions and Foreign Keys

• MyISAM: No transaction or foreign key support. Optimized for fast storage/retrieval, suitable for read-heavy workloads.
• InnoDB: Supports transactions and foreign keys. Emphasizes data integrity, ideal for insert/update-intensive operations.

Locking

• InnoDB: Row-level locking based on indexes.
• MyISAM: Table-level locking.

Index Structure

• InnoDB: Uses clustered indexes where data rows are stored with the primary key. Caches both index and data.
• MyISAM: Uses non-clustered (secondary) indexes; data and index are stored separately.

Concurrency

• MyISAM: Table locks lead to low concurrency. Reads do not block each other, but writes block reads and other writes.
• InnoDB: Row-level locking with MVCC (Multi-Version Concurrency Control) improves concurrency; blocking depends on isolation level.

Storage Files

• InnoDB: .frm (table structure) and .ibd (data and indexes). Supports up to 64TB.
• MyISAM: .frm, .MYD (data), .MYI (index). Supports up to 256TB. Index lookup in .MYI finds disk addresses, then retrieves data from .MYD.

InnoDB Storage Structure

Memory Structures

Buffer Pool (BP)

Caches data and index pages (default 16KB per page). Managed via linked lists. Reduces disk I/O by caching frequently accessed pages.

Page Management:

• Free Page: Unused.
• Clean Page: Used, unmodified.
• Dirty Page: Used, modified (differs from disk).

Managed by three lists:

• Free List: Tracks free pages.
• Flush List: Manages dirty pages for disk write-back, ordered by modification time (oldest first).
• LRU List: Manages clean and dirty pages in use. Divided into new (63% frequently accessed) and old (37% less accessed) sublists, split at a midpoint.

Enhanced LRU Algorithm: New pages are inserted at the midpoint. Frequently accessed pages move toward the new sublist head; unused pages drift toward the old sublist tail for eviction. When BP lacks free pages, LRU evicts pages from the old sublist.

Configuration:

SHOW VARIABLES LIKE 'innodb_page_size'; -- Page size
SHOW VARIABLES LIKE 'innodb_old%'; -- LRU parameters
SHOW VARIABLES LIKE 'innodb_buffer%'; -- Buffer Pool settings

Recommendation: Set innodb_buffer_pool_size to 60-80% of total RAM. Use multiple instances (innodb_buffer_pool_instances) to reduce contention.

Change Buffer (CB)

Caches changes to secondary index pages when the page is not in BP. Changes are merged into BP when the page is later read, avoiding immediate disk I/O.

Default size is 25% of BP, configurable via innodb_change_buffer_max_size (max 50%). For updates, if the page is in BP, modification occurs directly. If not, the change is buffered in CB. Upon subsequent read, disk data and CB changes are merged into BP.

Limitations: Only for non-unique secondary indexes. Unique indexes require immediate uniqueness checks, necessitating disk reads.

Adaptive Hash Index (AHI)

InnoDB automatically builds hash indexes on frequently accessed BP pages to speed up queries.

Log Buffer

Holds redo/undo log data before writing to disk. Default size 16MB. Large transactions benefit from increased size to reduce I/O.

Log Buffer writes to OS cache, then to disk (unlike BP, which can write directly). Flush behavior is controlled by innodb_flush_log_at_trx_commit:

• 0: Write to OS cache every second; may lose up to 1 second of data.
• 1 (default): Write and flush to disk on commit; safest but more I/O.
• 2: Write to OS cache on commit, flush every second; balances safety and performance.

Disk Structures

System Tablespace

Contains InnoDB data dictionary, doublewrite buffer, change buffer, and undo logs. Can store user tables and indexes. Configurable via innodb_data_home_dir and innodb_data_file_path. Default file: ibdata1.

File-Per-Table Tablespaces

Each table has its own .ibd data file when innodb_file_per_table is enabled. Supports dynamic and compressed row formats.

General Tablespaces

Shared tablespaces created via CREATE TABLESPACE. Can store multiple tables outside the MySQL data directory.

Undo Tablespaces

Store undo logs. Number controlled by innodb_undo_tablespaces. Location set via innodb_undo_directory.

Temporary Tablespaces

Store on-disk internal temporary tables and user-created temporary tables. File configured via innodb_temp_data_file_path. Removed and recreated on server restart.

InnoDB File Storage Hierarchy

Tablespace → Segment → Extent → Page → Row.

• Tablespace: Contains multiple segments.
• Segment: Manages extents (data, index, rollback segments).
• Extent: 64 contiguous pages (1MB).
• Page: 16KB unit storing rows.
• Row: Contains field values, transaction ID, roll pointer, field pointers.

Background Threads

I/O Threads

Use Async I/O for performance. Includes read, write, log, and insert buffer threads. Read/write threads increased to 4 each (total 10).

Purge Thread

Reclaims undo pages after transaction commit.

Page Cleaner Thread

Flushes dirty pages to disk, allowing redo log reuse. Invokes write threads.

Master Thread

Highest priority; schedules other threads. Asynchronously flushes BP data to disk.

• Every 1 second: Flush log buffer, optionally merge change buffer, flush dirty pages if ratio exceeds 75% (innodb_max_dirty_pages_pct).
• Every 10 seconds: Unconditionally flush dirty pages, merge change buffer, flush log buffer, purge unused undo pages.

System Files Layer

Manages physical storage of data and logs, interacting with storage engines.

Log Files

• Error Log: Enabled by default. SHOW VARIABLES LIKE 'log_error';
• General Query Log: Records all queries. SHOW VARIABLES LIKE 'general%';
• Binary Log: Records data changes for recovery and replication. SHOW VARIABLES LIKE 'log_bin%';
• Slow Query Log: Logs queries exceeding long_query_time (default 10s). SHOW VARIABLES LIKE 'slow_query%';

Configuration Files

my.cnf, my.ini.

Data Files

• db.opt: Data base default character set and collation.
• .frm: Table structure metadata.
• .MYD / .MYI: MyISAM data and index files.
• .ibd / ibdata: InnoDB data files (private/shared tablespace).
• ib_logfile0, ib_logfile1: Redo log files.

PID File

Contains the MySQL server process ID on Unix/Linux.

Socket File

Unix/Linux-specific for local client connections without TCP/IP.

Key Logging Systems

Undo Log

Records pre-modification data before transaction start. Supports rollback and MVCC. Stored in rollback segments (128 segments * 1024 slots = 131072 concurrent transactions max). Logical log.

Redo Log

InnoDB-specific physical log for crash recovery. Uses WAL (Write-Ahead Logging). Circular write to fixed-size files.

Key Parameters:

SHOW VARIABLES LIKE 'innodb_log_buffer_size%'; -- Buffer size
SHOW VARIABLES LIKE 'innodb_log_group_home_dir%'; -- File location
SHOW VARIABLES LIKE 'innodb_log_files_in_group%'; -- File count
SHOW VARIABLES LIKE 'innodb_log_file_size%'; -- File size

Persistence Policy (innodb_flush_log_at_trx_commit):

• 0: Write to OS cache every second.
• 1: Write and flush on commit.
• 2: Write to OS cache on commit, flush every second.

Recovery uses LSN (Log Sequence Number) comparison between data pages and redo log.

Binary Log (Binlog)

Server-layer logical log for replication and point-in-time recovery. Append-written.

Configuration Example:

log-bin=/path/to/binlog
binlog-format=ROW
sync-binlog=1
server-id=1
expire-logs-days=15
max_binlog_size=200M

Modes:

• STATEMENT: Logs SQL statements; minimal log size but non-deterministic functions may cause inconsistencies.
• ROW: Logs row changes; precise but verbose.
• MIXED: Hybrid approach.

Write Mechanism: Log events are buffered per transaction and written sequentially at commit. sync_binlog controls disk flush:

• 0: OS decides flush.
• 1: Flush on commit (safe).
• N: Flush after N commits.

Data Recovery: Use mysqlbinlog tool with position or timestamp filters to replay events.

Error Log

Mandatory log for startup/shutdown and critical errors.

General Query Log

Logs all operations; disabled by default due to performance overhead.

Log Comparison

Redo Log vs. Undo Log

• Redo Log: Physical, for crash recovery.
• Undo Log: Logical, for rollback and MVCC.

Redo Log vs. Binlog

• Scope: Redo log is storage engine (InnoDB); binlog is server-layer.
• Content: Redo log is physical page changes; binlog is logical statements/row events.
• Write Pattern: Redo log circular; binlog append.
• Purpose: Redo log enables crash-safe recovery; binlog enables replication and data recovery.

UPDATE Execution Flow

1. Parse SQL and generate plan.
2. Begin transaction (implicitly if autocommit=1).
3. Locate row via index; load page to BP if not present.
4. Write old data to undo log.
5. Modify row in BP (page becomes dirty).
6. Write change to redo log buffer.
7. On commit: redo log prepare → write binlog → redo log commit (two-phase commit).
8. Return success to client.

Two-Phase Commit Importance: Ensures consistency between redo log and binlog. Disorder in writing these logs can cause data inconsistency during recovery.