MyISAM Storage Engine
MySQL system tables Many use the MyISAM engine
- Transaction not supported
- Table-level lock, update data on table lock
- Reads and writes are blocked, but read is not blocked
- The cache caches only the index, through the Key_buffer_size cache index
- Reads faster, consumes less resources
- FOREIGN KEY constraints are not supported, but full-text indexing is supported
- Before MySQL5.5.5 the default engine
Production Scenario:
- Business that does not require transaction support
- Generally for reading a lot of applications, reading and writing are frequently the scene is not suitable, read more or write more suitable
- Read and write concurrent access to relatively low business, (read-only can be high concurrency) (lock mechanism problem)
- Read-oriented business, picture information database, user database
- Data modification of relatively small business;
- The requirements for data consistency are not very high business
- Machines with poor hardware resources
Engine Tuning Essentials:
- Set the appropriate index (caching mechanism)
- Adjust read and write priorities to ensure that important operations are prioritized based on real-world requirements
- Enable deferred insertion improves bulk write performance (reduces write frequency, as many data writes at once)
- Try to keep the insert data written to the tail, reducing blocking
- Separate large time-long operations to reduce blocking times for individual operations
- Reduce concurrency (reduce access to MySQL), some high concurrency scenarios through application and retrograde queueing queue mechanism Q queue
- For relatively static (infrequently changed) database data, the full use of query cache or memcached cache service can greatly improve the access efficiency
- The count of MyISAM is particularly efficient when full-table scans, and count with other conditions requires actual data access
- 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:
- Acid support, multi-version read support
- Row-level locking (update typically locks the current row), full table sweep is still a table lock
- Read-write blocking is related to transaction isolation level
- Very efficient caching features that enable caching of indexes and data
- The entire table and primary key are stored in cluster form, forming a tree of balance
- All Secondary index will hold the primary key information
- Supports partitioning, table space, similar to Oracle database
- Support for foreign key indexes, full-text indexing
- Higher requirements than MyISAM for resource requirements
Production Scenario:
- Business that requires transaction support
- Row-level locking is good for high concurrency, but needs to ensure that queries are done by indexing
- A scenario in which data is read and written and updated more frequently
- Business with high data consistency requirements
- 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:
- The primary key is as small as possible to avoid excessive space burden on secondary index
- Avoid full table scan, table lock
- Cache all indexes and data as much as possible, improve response speed, and reduce disk IO
- Do not use Autocommmit auto-commit when BULK INSERT, switch can control the way of submission
- Set Innodb_flush_log_at_trx_commit parameter value rationally, do not pursue security excessively
- 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
- 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