MyISAM Storage Engine
Advantages:
- Supports up to 256TB of storage space
- 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:
- Transaction not supported
- Minimum granularity Lock: Table level
- Read/write blocking, write unreadable, cannot be written at read time
- MVCC not supported (multi-version concurrency control mechanism supported)
- Clustered index not supported
- Data caching not supported
- Foreign keys are not supported
- 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:
- 64TB
- Support Transactions
- Row-level Locks
- Support for multi-version concurrency control mechanism (MVCC)
- Support for clustered Indexes
- Support for data caching
- 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