Agenda

• MySQL Introduction
• MySQL Uses and Market share
• MySQL Enterprise Edition
• Client/Server Model
• General RDBMS and MySQL Architecture
• MySQL’s Physical and Logical Architecture
• MySQL Storage Engines
• Communication Protocols
• MySQL Architecture – Big Picture

Introduction

• MySQL is the world's most popular open source database software.
• MySQL is a relational database management system (RDBMS) and which runs a server, providing multi-user access to a number of databases.
• With its superior speed, reliability, and ease of use, MySQL has become the preferred choice for IT in all sectors or domains.

Uses

• Many web applications use MySQL as the database component of a LAMP, Apache HTTP Server, MySQL, and PHP, Perl or Python software stack.
• Its popularity for use with web applications is closely tied to the popularity of PHP , which is often combined with MySQL.
• Several high-traffic web sites includes:
• Flickr, Facebook, Wikipedia, Google, Nokia and YouTube etc.

MySQL Market Share: DB-Engines 2021

• MySQL database is the second most popular database according to DB-Engines Ranking in Aug 2021.
• MySQL has consistently been in the top three in the last five years.

MySQL Enterprise Edition

Advanced Features

• High Availability
• Authentication
• Audit
• Encryption and TDE
• Firewall
• Masking

Management Tools

• Monitoring
• Backup
• Development
• Administration
• Migration

Support

• Technical Support
• Consultative Support
• Oracle Certifications

MySQL Enterprise Edition

Client/Server

• The MySQL database system operates using a client/server architecture.
• The server is the central program that manages database contents, and client programs connect to the server to retrieve or modify data.
• MySQL also includes non-client utility programs and scripts.

General RDBMS Architecture

• All database systems can be viewed as a Garlan & Shaw layered architecture at the highest level of abstraction.
• It has three main components:
  • Application Layer
  • Logical Layer
  • Physical Layer

MySQL Architecture Overview

APPLICATION LAYER

• Users and clients interacts with the MySQL RDBMS.
• Three components:
  • Administrators
  • Clients
  • Query Users
• Query users interact with MySQL RDBMS using “mysql”.
• Administrators use various administrative interface and utilities like mysqladmin, myisamchk etc.
• Clients communicate with the MySQL RDBMS through various interfaces and utilities like the MySQL APIs.
• “mysql”is actually a query interface. It’s a monitor that allows users to issue SQL statements and view the results.

LOGICAL LAYER

• The logical layer of MySQL architecture is divided into various subsystems.
  • Query Processor.
  • Transaction Management.
  • Recovery Management.
  • Storage Management.
• These sub systems work together to process the requests issued to the MySQL database server.

Physical Layer

Deals with storage of information kept in secondary storage and accessed via storage manager. The types of data kept in the system:

• Data files - store the user data in the database (Applicable for MyISAM)
• Data dictionary - to stores metadata about the structure of the database
• Indexes - provide fast access to data items that hold particular values
• Statistical Data - store statistical information about the data in the database; it is used by the query processor to select efficient ways to execute a query.
• Serialized Dictionary Information (SDI): In addition to storing metadata in the data dictionary, MySQL stores it in serialized form.
• This data is referred to as serialized dictionary information (SDI).
• Log Information - to keep track of executed queries such that the recovery manager can use the information to successfully recover the database in the case of a system crash.
• MySQL Server uses disk space for directories and files that are found under /var/lib/mysql to store all the following:
  • Database directories. Each database corresponds to a single directory.
  • Data and index files are created for each table by some storage engines and placed in the appropriate database directory.
  • The InnoDB storage engine has its own tablespace and log files.
    • The tablespace contains data and index information for all InnoDB tables, as well as the undo logs that are needed if a transaction must be rolled back.
    • The log files record information about committed transactions and are used to ensure that no data loss occurs.
    • The default tablespace file is named ibdata1 and the default log files are named ib_logfile0 and ib_logfile1.
  • Server log files and status files.
  • Default/autogenerated SSL and RSA certificate and key files.
  • The mysqld-auto.cnf file that stores persisted global system variable settings.

• A storage engine has a particular set of operational characteristics.
• Storage engines differ in the way that they use locking to manage query contention, or in whether the tables that they provide are transactional or non-transactional.
• These engine properties have implications for query processing performance, concurrency, and deadlock prevention.
• When you create a table, you can choose what storage engine to use.

MySQL’s Logical Architecture

Query Processor

• Consists of:
  • Embedded DML Pre-compiler.
  • DDL Compiler.
  • Query Parser.
  • Query Preprocessor.
  • Security/Integration Manager.
  • Query Optimizer.
  • Execution Engine.
• The output of one of the above component becomes the input for another.
• Query processor layer is scalable and evolvable.

Transaction Management

• It facilitates concurrent data access.
• Provides locking facility.
• Ensures multiple users/sessions access data simultaneously in a consistent way.
• Prevents data corruption or data damage.
• Lock Manager is the sub component name that handles locking.

Recovery Management

• Log Manager
  • Logs every operation executed in the database.
  • Stores the operations logs as MySQL Commands.
  • In case of SYSTEM crash executing these commands will bring back the database to its last stable state.
• Recovery Manager
  • Responsible for recovering the database to its last stable state.
  • Uses the logs created by the log manager.

Memory Management

• Buffer Manager
  • It allocated memory resources.
• Resource Manager
  • Accepts the requests from execution engine.
  • Requests the details from buffer manager.
  • It actually receives references of data with memory from buffer manager.
  • Returns this data to the upper layer.

MySQL Concepts: InnoDB

• InnoDB tables are transaction-safe storage engine that has commit, rollback, and crash recovery capabilities for data protection.
• It supports row-level locking.
• Foreign key referential-integrity constraint can be defined.
• Table can extend to any size even beyond 2 GB and power loss recovery is fast.
• The InnoDB stores user data in clustered indexes
• This reduces I/O for common queries based on primary keys
• InnoDB should be used for applications requiring the data integrity.

MySQL Concepts: MyISAM

• MyISAM is the improved version of the original storage engine of MySQL, ISAM
• After MySQL 3.23, MyISAM replaced ISAM as the default storage engine.
• The MyISAM engine is fast and thus, preferred for web and other application environments.
• It is also used for data warehousing
• MyISAM is not transaction-safe and supports 64 keys per table with maximum key length of 1024 bytes
• The size of MyISAM table depends on the host operating system
• MyISAM table allows table level locking only.
• There are no limitations on data file transfer and the data files can be ported from system to system
• The foreign key constraint cannot be defined
• MyISAM is the only storage engine that supports Full-text search
• It also supports one auto increment column per table
• A high-byte-first pattern for saving numeric key values ensures faster indexing
• It can be used where fulltext indexing is needed

Communication Protocols

• A MySQL client program can connect to a server running on the same machine or another machine.
• MySQL supports connections between clients and the server using several networking protocols.
• Some protocols are applicable for connecting to either local or remote servers. Others can be used only for local servers.