Category: MySQL Architecture
-
Physical Database Architecture
Database Files SQL Server maps a DB to a set of OS files visible to SQL Server. We can create 32767 files in a DB. First page (Page 0) of every DB file is FHP file header page. Second page (Page 1) of every DB file PFS (page free space) page. PFS uses 1 byte for each…
//
-
InnoDB Architecture
Buffer Pool In MySQL, buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. For efficiency…
//
-
Serialized Dictionary Information (SDI)
In addition to storing metadata about database objects in the data dictionary, MySQL stores it in serialized form. This data is referred to as serialized dictionary information (SDI). InnoDB stores SDI data within its tablespace files. NDBCLUSTER stores SDI data in the NDB dictionary. Other storage engines store SDI data in .sdi files that are created for a given table in the table’s…
//
-
Removal of File-based Metadata Storage
In previous MySQL releases, dictionary data was partially stored in metadata files. Issues with file-based metadata storage included expensive file scans, susceptibility to file system-related bugs, complex code for handling of replication and crash recovery failure states, and a lack of extensibility that made it difficult to add metadata for new features and relational objects.…
//
-
Transactional Storage of Dictionary Data
The data dictionary schema stores dictionary data in transactional (InnoDB) tables. Data dictionary tables are located in the mysql database together with non-data dictionary system tables. Data dictionary tables are created in a single InnoDB tablespace named mysql.ibd, which resides in the MySQL data directory. The mysql.ibd tablespace file must reside in the MySQL data directory and its name cannot be modified…
//
-
InnoDB Engine
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Key Advantages of InnoDB
//
-
InnoDB Multi-Versioning
InnoDB is a multi-versioned storage engine: it keeps information about old versions of changed rows, to support transactional features such as concurrency and rollback. This information is stored in the tablespace in a data structure called a rollback segment (after an analogous data structure in Oracle). InnoDB uses the information in the rollback segment to perform the undo operations needed in a…
//
-
MySQL Physical (Raw) Vs Logical Backups
Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur. Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of…
//
Search
Categories
- Azure (7)
- About Us (1)
- Azure SQL Database (6)
- My SQL (53)
- About Us (1)
- DBMS Concepts (20)
- Handbook (1)
- MySQL Architecture (19)
- MySQL Basics (29)
- MySQL L1 (14)
- Oracle (34)
- PostgreSQL (37)
- About Us (1)
- DBMS Concepts (21)
- PG Hand Book (1)
- pg_ High Availability (4)
- pg_Backup (4)
- pg_basic (11)
- SQL Server (132)
- About Us (1)
- Azure SQL Database (4)
- Backup and Recovery (17)
- Backup Tools for SQL Server (3)
- DBA Worries Continued (27)
- DBMS Concepts (28)
- High Availability (12)
- Installation and Configuration (23)
- L1 DBA (59)
- Monitoring (17)
- Performance Tuning (29)
- Power Shell Script (6)
- Security (12)
- SQL DBA Hand Book (2)
- SQL Scripting (16)
- SQL Server Reporting Services (2)
- Uncategorized (3)