MYSQL Storage Engines
FEDERATED InnoDB MyISAM ARCHIVE
BLACKHOLE CSV MEMORY PERFORMANCE_SCHEMA
➔ Pluggable storage engine architecture - load and unload on a running
MySQL Server
➔ show engines - which storage engine your server support
➔ Shared library location for all the plugins - plugin_dir variable
➔ Can install & uninstall more storage engines
➔ mysql>install plugin engine SONAME ‘[Link]’ - install
➔ mysql>uninstall plugin engine - uninstall
MYSQL Storage Engines
FEDERATED STORAGE ENGINE:
❖ Disabled by default
❖ Table created with FEDERATED Storage Engine, normally points to a table in another MySQL
instance installed on a separate server.
❖ Linked Server - Microsoft SQL Server
❖ Database Link - Oracle
❖ Both tables should have the same name and definition
❖ The table in requester acts like a view
❖ Target table can have different storage engine but requester table should be created with
FEDERATED
Syntax:
Create table employee_salaries (
Employee_id int,
Employee_salary int
) ENGINE = FEDERATED
CONNECTION = ‘mysql://db_user@target-server:3306/employees/employee_salaries’;
MYSQL Storage Engines
MEMORY STORAGE ENGINE:
❖ Called HEAP in older versions
❖ Very useful for temporary tables
❖ MEMORY will write table data in memory
❖ Not Persistent - Data lost on server crash
❖ Very fast data retrieval but memory is volatile so use only for read-only cache data or temp
tables
USE CASE:
❖ Static Tables - lookup
❖ Temporary Tables
Caveats:
❖ No Transactions support
❖ No Referential Integrity support - No FK
❖ NO TEXT data type support - No BLOB column
MySQL Storage Engines
ASSIGNMENT: Create continents table in MEMORY - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=MEMORY
❖ Insert data
❖ Verify data and also table definition from
information_schema.tables
❖ Restart MySQL Service and observe table is there but data is
gone
MYSQL Storage Engines
BLACKHOLE STORAGE ENGINE:
❖ Acts as a black hole, whatever goes into it, never comes back
❖ You can store as much data as you want, when you retrieve it, it returns
empty result set
❖ Anything you write to it, disappears
❖ Does not support transactions
Syntax:
Create table employee_salaries (
Employee_id int,
Employee_salary int
) ENGINE = BLACKHOLE;
MySQL Storage Engines
ASSIGNMENT: Create continents table in BLACKHOLE - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=BLACKHOLE
❖ Insert data
❖ Verify empty result set will return
MYSQL Storage Engines
CSV STORAGE ENGINE:
❖ Stores table in text files using comma-separated values format
❖ MySQL creates a .csv file in the $DATA_DIR - plain text file
❖ CSV format can be read, written by spreadsheet applications like Excel
❖ Does not support transactions
❖ CSV files are not indexed
USE CASE:
❖ When data need to be shared with other applications that also use CSV
format
Syntax:
Create table continents (
cid int NOT NULL,
cname VARCHAR(25) NOT NULL
MySQL Storage Engines
ASSIGNMENT: Create continents table in CSV - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=CSV
❖ Insert data
❖ Search for [Link] file under $DATA_DIR/world
MYSQL Storage Engines
MyISAM STORAGE ENGINE:
❖ MyISAM = My + ISAM = Indexed Sequential Access Method
❖ Indexing algorithm developed by IBM that allows retrieving information from large sets
of data in a fast way
❖ MyISAM was default storage engine up until MySQL 5.5 - around 2009-2010
❖ Good speed advantages especially useful in Data warehouse scenario
❖ Replaced by InnoDB
❖ Does not support transactions - ACID Model
USE CASE:
❖ Data Warehouse - a lot of reads
Syntax:
Create table continents (
cid int NOT NULL,
cname VARCHAR(25) NOT NULL
) ENGINE = MyISAM;
MySQL Storage Engines
ASSIGNMENT: Create continents table in MyISAM - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=MyISAM
❖ Insert data
❖ Start Transaction, Commit, Rollback
MYSQL Storage Engines
ARCHIVE STORAGE ENGINE:
❖ Produces special-purpose tables that store large amounts of un-indexed data in very
small footprint
❖ Creates .ARZ files with same name as table name
❖ ARZ files are binary data files and are called MySQL Archive Storage Engine Data File
❖ Uses gzip to compress rows
CAVEAT:
❖ No DELETE or UPDATE operation
❖ No Partitioning
Syntax:
Create table continents (
cid int NOT NULL,
cname VARCHAR(25) NOT NULL
) ENGINE = ARCHIVE;
MySQL Storage Engines
ASSIGNMENT: Create continents table in ARCHIVE - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=ARCHIVE
❖ Insert data
❖ Look for .ARZ file
MYSQL Storage Engines
InnoDB STORAGE ENGINE:
❖ ACID compliant storage engine that support all types of transactions
❖ A - Atomicity, involves transactions COMMIT & ROLLBACK
❖ C - Consistency, mechanism for crash recovery
❖ I - Isolation, different isolation levels that applies at each transaction level
❖ D - Durability, storage engine interacts with underlying hardware to provide best
performance
❖ Default storage engine, robust, fast, heart of MySQL
❖ Best for OLTP - Online Transaction Processing
❖ Row-level locking, indexing
❖ InnoDB maintains its own buffer pool (memory area where InnoDB cache table and
indexed data)
Syntax:
Create table continents (
cid int NOT NULL,
cname VARCHAR(25) NOT NULL
) ENGINE = InnoDB;
MySQL Storage Engines
ASSIGNMENT: Create continents table in InnoDB - world database
ASSIGNEE: Bob, The Junior DBA
Steps:
❖ Create table specifying ENGINE=InnoDB or skip
❖ Insert data
❖ Test all operations
BOB PROGRESS
CHECKPOINT
Bob’s Knowledge Gain:
❖ Storage Engines
❖ InnoDB - Default Storage Engine
❖ Storage Engine Status
❖ Install new storage engine
❖ Migrate table from one storage engine to other
❖ Disable storage engine