MySQL Storage engine

MyISAM Storage Engine

Advantages:

1. Supports up to 256TB of storage space
2. Read data faster and consume less resources

MyISAM Engine Storage File:

• TBL_NAME.FRM: Table-style definition
• Tbl_name. MYD: Data files
• Tbl_name. MYI: Index File

Disadvantages:

1. Transaction not supported
2. Minimum granularity Lock: Table level
3. Read/write blocking, write unreadable, cannot be written at read time
4. MVCC not supported (multi-version concurrency control mechanism supported)
5. Clustered index not supported
6. Data caching not supported
7. Foreign keys are not supported
8. Poor recovery of crashes

Scenario: The default database engine before MySQL5.5.5, for scenarios where read-only (or less write), smaller tables (which can accept long-time repair operations)

InnoDB Storage Engine

Characteristics:

1. 64TB
2. Support Transactions
3. Row-level Locks
4. Support for multi-version concurrency control mechanism (MVCC)
5. Support for clustered Indexes
6. Support for data caching
7. Support for foreign keys

InnoDB Database files:

• TB_NAME.FRM: Table-style definition
• TB_NAME.IBD: Data files

  Note: The default all InnoDB table data files are stored in the database directory under Ibddata1, Ibddata2, ..., so extremely inconvenient to manage enable Innodb_file_per_table=on, Each table uses a single table space to store the data and indexes of the table

Enabled: innodb_file_per_table

Edit/etc/my.cnf in [mysqld] Add innodb_file_per_table Restart Server # service mysqld restartmariadb [(none)]'  Innodb_file_per_table'; +-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | On    |+-----------------------+-------+

1. Managing the Storage Engine

To view the storage engines supported by MySQL:MariaDB [(none)]> SHOW ENGINES\G

To view the current default storage engine:

' Storage_engine ' ; +----------------+--------+| Variable_name  | Value  |+----------------+--------+| storage_engine | InnoDB |+----------------+--------+

To set the default storage engine:

编辑/etc/my.conf在[mysqld]下添加default_storage_engine = InnoDB

2. Cache of InnoDB Storage engine

Buffer pool for InnoDB storage engine typically the buffer pool hit should not be less than 99%

Related state variables:

MariaDB [(None)]> SHOW GLOBAL STATUS like ' innodb%read% ' \g

• Innodb_buffer_pool_reads: Indicates the number of times a page was read from a physical disk
• Innodb_buffer_pool_read_ahead: Number of read-ahead
• Innodb_buffer_pool_read_ahead_evicted: Read-ahead page, but no read on the number of pages replaced from the buffer pool, generally used to determine the efficiency of the read-ahead
• Innodb_buffer_pool_read_requests: Number of pages read from buffer pool
• Innodb_data_read: Number of bytes read in total
• Innodb_data_reads: Number of Read requests initiated, each read may require multiple pages to be read

INNODB buffer pool Hit rate = innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)

Average number of bytes read per reading = Innodb_data_read/innodb_data_reads

MySQL Storage engine