STORAGE ENGINES IN MySQL
We covered MyISAM & InnoDB in previous article.
In this article we will cover one more important storage engine called MEMORY STORAGE.ENGINE.
MEMORY STORAGE ENGINE :
Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.
Characteristics of Memory Storage Engines :
- Represented by .frm file.
- Table data and indexes stored in memory
- Storage very fast
- fixed-lengths rows
- Table contents do not servive restart
- Can limit file size using –max-heap-table-size
- Table level locking
- Cannot obtain TEXT or BLOB
- Memory storage engine formally called Heap engine
Let’s create database db3 and some objects into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> create database db3; Query OK, 1 row affected (0.00 sec) mysql> use db3; Database changed mysql> select database (); +-------------+ | database () | +-------------+ | db3 | +-------------+ 1 row in set (0.01 sec) mysql> create table t1(ID INT) ENGINE=MEMORY; Query OK, 0 rows affected (0.00 sec) |
Check at physical layer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
root@ip-172-31-14-134:/usr/local/mysql/data# ll total 111120 drwxr-xr-x 7 mysql mysql 4096 Oct 27 22:35 ./ drwxr-xr-x 13 root root 4096 Oct 27 14:01 ../ -rw-rw---- 1 mysql mysql 56 Oct 27 06:50 auto.cnf drwx------ 2 mysql mysql 4096 Oct 27 21:56 db1/ drwx------ 2 mysql mysql 4096 Oct 27 22:36 db3/ -rw-rw---- 1 mysql mysql 12582912 Oct 27 22:15 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Oct 27 22:15 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 27 22:15 ib_logfile1 -rw-rw---- 1 mysql mysql 5488 Oct 27 07:13 ip-172-31-14-134.err -rw-rw---- 1 mysql mysql 5 Oct 27 22:15 ip-172-31-14-134.pid drwx------ 2 mysql mysql 4096 Oct 27 06:49 mysql/ drwx------ 2 mysql mysql 4096 Oct 27 06:49 performance_schema/ drwx------ 2 mysql mysql 4096 Oct 27 21:34 world/ -rw-r--r-- 1 root root 397334 Oct 27 15:26 world.sql -rw-r--r-- 1 root root 92244 Oct 27 14:01 world.sql.zip root@ip-172-31-14-134:/usr/local/mysql/data# cd db3/ root@ip-172-31-14-134:/usr/local/mysql/data/db3# ll total 24 drwx------ 2 mysql mysql 4096 Oct 27 22:36 ./ drwxr-xr-x 7 mysql mysql 4096 Oct 27 22:35 ../ -rw-rw---- 1 mysql mysql 65 Oct 27 22:35 db.opt -rw-rw---- 1 mysql mysql 8556 Oct 27 22:36 t1.frm root@ip-172-31-14-134:/usr/local/mysql/data/db3# |
Only t1.frm file will created .
The data will be stored in memory only
Will try to insert data into t1 table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> insert into t1 values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) |
Here in t3 table we have 3 records but when we restart the service this table data will flushed out from memory.
|
1 2 |
root@ip-172-31-14-134:/usr/local/mysql/data/db3# /etc/init.d/mysql.server restart [ ok ] Restarting mysql.server (via systemctl): mysql.server.service. |
Now check the records in t1 table.
Data will be flushed out .But structure remains same because of .frm file
|
1 2 3 4 5 6 7 |
mysql> select * from t1; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: db3 Empty set (0.00 sec) |
What size will be stored in memory ?
The table data will be stored in temp table in the database.
To check the size of temp table follow below command.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show variables like '%tmp%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | default_tmp_storage_engine | InnoDB | | innodb_tmpdir | | | max_tmp_tables | 32 | | slave_load_tmpdir | /tmp | | tmp_table_size | 16777216 | | tmpdir | /tmp | +----------------------------+----------+ 6 rows in set (0.00 sec) |
The temp table size is 16MB and maximum size will be 32 MB
When to Use MEMORY storage engine:
- Operations involving transient, non-critical data such as session management or caching.
- In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
- By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
- The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
- The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
- Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
- The maximum tablespace size is four billion database pages (64TB) and the minimum tablespace size is slightly larger than 10MB.
ARCHIVE Storage Engine
The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.
Features of ARCHIVE storage engine:
| Storage limits | None | Transactions | No | Locking granularity | Table |
| MVCC | No | Geospatial data type support | Yes | Geospatial indexing support | No |
| B-tree indexes | No | T-tree indexes | No | Hash indexes | No |
| Full-text search indexes | No | Clustered indexes | No | Data caches | No |
| Index caches | No | Compressed data | Yes | Encrypted data | Yes |
| Cluster database support | No | Replication support | Yes | Foreign key support | No |
| Backup / point-in-time recovery | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes |
ARCHIVE storage engine supports
- INSERT and SELECT.
- ORDER BY operations
- BLOB columns
- AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index.
- AUTO_INCREMENT table option in CREATE TABLE statements
ARCHIVE storage engine does not support
- DELETE, REPLACE, or UPDATE
- Inserting a value into an AUTO_INCREMENT column less than the current maximum column value.
ARCHIVE storage engine: Storage & Retrieval
- The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/).
- Rows are compressed as they are inserted.
- On retrieval, rows are uncompressed on demand; there is no row cache.
MySQL: EXAMPLE Storage Engine
The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :
- The server creates a table format file (.frm extension) in the database directory.
- No other files are created
- No data can be stored into the table.
- Retrievals return an empty result.
- Does not support indexing.
To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
