MySQL Storage engine

MyISAM Storage Engine
MySQL system tables Many use the MyISAM engine

1. Transaction not supported
2. Table-level lock, update data on table lock
3. Reads and writes are blocked, but read is not blocked
4. The cache caches only the index, through the Key_buffer_size cache index
5. Reads faster, consumes less resources
6. FOREIGN KEY constraints are not supported, but full-text indexing is supported
7. Before MySQL5.5.5 the default engine

Production Scenario:

1. Business that does not require transaction support
2. Generally for reading a lot of applications, reading and writing are frequently the scene is not suitable, read more or write more suitable
3. Read and write concurrent access to relatively low business, (read-only can be high concurrency) (lock mechanism problem)
4. Read-oriented business, picture information database, user database
5. Data modification of relatively small business;
6. The requirements for data consistency are not very high business
7. Machines with poor hardware resources

Engine Tuning Essentials:

1. Set the appropriate index (caching mechanism)
2. Adjust read and write priorities to ensure that important operations are prioritized based on real-world requirements
3. Enable deferred insertion improves bulk write performance (reduces write frequency, as many data writes at once)
4. Try to keep the insert data written to the tail, reducing blocking
5. Separate large time-long operations to reduce blocking times for individual operations
6. Reduce concurrency (reduce access to MySQL), some high concurrency scenarios through application and retrograde queueing queue mechanism Q queue
7. For relatively static (infrequently changed) database data, the full use of query cache or memcached cache service can greatly improve the access efficiency
8. The count of MyISAM is particularly efficient when full-table scans, and count with other conditions requires actual data access
9. The main library can be used InnoDB, from the library with MyISAM (not recommended)

query_cache_size=256m
query_cache_limit=1m
query_cache_min_res_unit=2k

InnoDB Engine:

1. Acid support, multi-version read support
2. Row-level locking (update typically locks the current row), full table sweep is still a table lock
3. Read-write blocking is related to transaction isolation level
4. Very efficient caching features that enable caching of indexes and data
5. The entire table and primary key are stored in cluster form, forming a tree of balance
6. All Secondary index will hold the primary key information
7. Supports partitioning, table space, similar to Oracle database
8. Support for foreign key indexes, full-text indexing
9. Higher requirements than MyISAM for resource requirements

Production Scenario:

1. Business that requires transaction support
2. Row-level locking is good for high concurrency, but needs to ensure that queries are done by indexing
3. A scenario in which data is read and written and updated more frequently
4. Business with high data consistency requirements
5. Hardware device memory is large, can use InnoDB, cache data to memory

innodb_additional_mem_pool_size=16m
innodb_buffer_pool_size=2048m
Innodb_data_file_path=ibdata1:1024m:autoextend
Innodb_file_io_threads = 4
Innodb_thread_concurrency = 8
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 16M
innodb_log_file_size=128m
Innodb_log_files_in_group=3
Innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=120
Innodb_file_per_table=0
Innodb_file_per_table #每张表一个文件
Innodb_data_home_dir=/data/xxx
Innodb_log_group_home_dir=/data/xxx

InnoDB Engine Tuning Essentials:

1. The primary key is as small as possible to avoid excessive space burden on secondary index
2. Avoid full table scan, table lock
3. Cache all indexes and data as much as possible, improve response speed, and reduce disk IO
4. Do not use Autocommmit auto-commit when BULK INSERT, switch can control the way of submission
5. Set Innodb_flush_log_at_trx_commit parameter value rationally, do not pursue security excessively
6. If the value of Innodb_flush_log_at_trx_commit is 0,log buffer, the log file is written to disk every second, and no action is taken when committing the transaction
7. Avoid primary key updates because there is a lot of data movement

Mysql>show engines;+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|Engine|Support|Comment|Transactions|Xa|Savepoints|+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|MyISAM|YES|MyISAM Storage Engine|NO|NO|NO||Csv|YES|CSV Storage Engine|NO|NO|NO||Performance_schema|YES|PerformanceSchema                                             |NO|NO|NO||Blackhole|YES| /Dev/NULLStorage engine (anything you write toIt disappears)|NO|NO|NO||Mrg_myisam|YES|Collection ofIdentical MyISAM tables|NO|NO|NO||InnoDB| DEFAULT |Supports transactions, row- LevelLocking and ForeignKeys|YES|YES|YES||ARCHIVE|YES|Archive Storage Engine|NO|NO|NO||MEMORY|YES|Hash based, storedinchMemory, useful for TemporaryTables|NO|NO|NO||Federated|NO|Federated MySQL Storage Engine| NULL         | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+------------ --+------+------------+9Rowsinch Set(0.00Sec

How to batch modify MySQL engine in production environment
1, ALTER TABLE account Engine=myisam;

MySQL> show Create table account ; +---------+-----------------------------------+| Table   | Create Table                      |+---------+-----------------------------------+|Account| CREATE TABLE' account ' (' ID ')int( One)DEFAULT NULL, ' name 'varchar(Ten)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=Utf8mb4|+---------+-----------------------------------+1Rowinch Set(0.00sec) MySQL  > alter table account ENGINE=  MyISAM; Query OK,2Rows Affected (0.06sec) Records:2Duplicates:0Warnings:0 mysql> show Create table account ; +---------+-----------------------------------+| Table   | Create Table |+---------+-----------------------------------+|Account| CREATE TABLE' account ' (' ID ')int( One)DEFAULT NULL, ' name 'varchar(Ten)DEFAULT NULL) ENGINE=MyISAMDEFAULTCHARSET=Utf8mb4|+---------+-----------------------------------+1Rowinch Set(0.00Sec

2. Use SED to convert backup content to engine (small data volume)

>-'s/myisam/innodb/g'> &  < Abbott.sql

3, the following script

SET @DATABASE_NAME = 'Abbott';SELECTCONCAT ('ALTER TABLE '', TABLE_NAME,'' Engine=innodb;') assql_statements fromInformation_schema.tables asTBWHERETable_schema= @DATABASE_NAME and' ENGINE '= 'MyISAM' and' Table_type '= 'BASE TABLE'ORDER  bytable_nameDESC;

MySQL Storage engine