Tuan Tran

Posted on Aug 11

Research for MySQL architecture and Optimize performance

#mysql #performance #database #sql

Summary

MySQL is a relationship database management system developed on C and C++ programming languages, created by MySQL AB, which was founded by David Axmark, Allan Larsson, and Michael Widenius. The newest version is MySQL 9.0.

MySQL is an open source project with a GNU license.

MySQL is the most popular and useful database management system now because it's open source, has full features, and has high performance.

Architecture

MySQL architecture diagram: Source

Client Layer

The first layer of MySQL system architecture.
The important services of this layer are Connection Handling, Authentication and Security.
- Connection Handling: Manages connections from clients. When a client wants to connect to the MySQL server, a new thread is created for the connection. The server caches threads when new connections are created.
- Authentication: When a client connects to a server, it must provide authentication information to establish a connection. Typically, clients authenticate using a username and password.
- Security: MySQL provides many features to protect the database and verify user permissions after authentication.

Server Layer

The next layer of MySQL is responsible for processing query statements and providing many utilities.
The main features:
- Parser: When a client requests a query statement, the server analyzes it to create a query tree, rewrites the query, and define the order and indexes to be used to execute the query statement.
- Optimizer: It optimizes the query statement before execution. The optimizer doesn't care what engine is used, but the storage engine can impact optimization for the specific query.
- Query cache: The query cache stores the result of a query statement. If a client requests the same query again, the server returns the cached result, bypassing the parsing and optimization steps. This feature was deprecated in version 8.0 or higher due to its bottleneck problem.
- Services & Utitlity:
  - Backup & Restore
  - Security: provider user and permissions system
  - Replication: This is a process to copy and sync data from the main server to many child servers.
  - Cluster
  - Partitioning: Split the table into many parts using a specific logic.
  - Workbench: It is a visual tool designed for interacting with and utilizing MySQL.

Storage Layer

It's a layer of responsibility for the way we store data in the database.
By default, MySQL uses the InnoDB storage engine.
MySQL supports many different storage engines:
- InnoDB (default)
- MyISAM
- MEMORY
- CSV
- ARCHIVE
- BLACKHOLE
- MERGE
- FEDERATED

Optimize Performance for MySQL

Optimize SQL statement

Explain Statement

Query prediction without execution

explain sql_statememnt

Explain after execution query

explain analyze sql_statement

Indexing Technique

It upgrades performance when querying and searching.
When creating an index for multiple columns, the first column is very important. It decides the performance of a query statement. For example, if you create an index on (name, birthday), a query using the 'name' column will utilize this index, but a query using the 'birthday' column will not.
Syntax for creating new index:

create index idx_birthday on customers(birthday);

The command will render the specified index inaccessible to the query optimizer, thus preventing it from being considered for query execution plans:

alter table customers alter index idx_birthday invisible;

Partition

When the size of a database is larger than 2GB (or higher than 10 million records), you should use partitioning for that database.
When using partitioning, performance will improve.
The partition plan should be based on the column that is frequently used in WHERE clauses.

Optimize database parameters

Buffer Cache Hit

It's the percentage of data that the system loads from the buffer cache instead of from the physical storage.
If the percentage is lower than 90%, then you must optimize it.
Depend on 2 parameters:
- Innodb_buffer_pool_read_requests: Total request sent to Buffer Pool. Command: show global status like '%Innodb_buffer_pool_read_requests%
- Innodb_buffer_pool_reads: Total requests that are not found in the buffer pool in memory must be read from disk. Command: show global status like '%Innodb_buffer_pool_reads%
Calculation formula:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

Table Cache Hit

MySQL will cache the table to query it fast in memory.
If the percentage is lower than 80%, then you must optimize it.
Depend on 2 parameters:
- Open_tables: Total tables are opened in cache. Command: show global status like 'Open_tables
- Opened_tables: Total tables are opened. Command: show global status like 'Opened_tables
Calculation formula: Open_tables/Opened_tables

Table Definition Cache Hit

When executing a query, MySQL requires information about the table, such as its name, number of columns, and number of rows. This information is referred to as the table definition.
If the percentage is lower than 80%, then you must optimize it.
Depend on 2 parameters:
- Open_table_definitions: Total definition tables are in cache . Command: show global status like 'Open_table_definitions'
- Opened_tables: Total definition tables are requested. Command: show global status like 'Opened_tables'
Calculation formula: Open_table_definitions/Opened_table_definitions

Temporary Table in memory

Many SQL statements like Order, Group By, ... must use a temporary table.
If the percentage is lower than 80%, then you must optimize it.
Depend on 2 parameters:
- Created_tmp_disk_tables: Total temp tables are created on disk. Command: show global status like '%Created_tmp_disk_tables%
- Created_tmp_tables: Total temp tables are created. Command: show global status like '%Created_tmp_tables%
Calculation formula: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

Conclusion

This blog offers research-based knowledge about MySQL. MySQL is a relational database management system known for its low cost, extensive utilities, and high performance, making it suitable for a wide range of projects. While this blog covers many features and aspects of MySQL, there is more to explore. I will delve deeper into additional features and knowledge in future blogs.

Thank you for reading, and see you in my next blog.

DEV Community