MySQL Storage Engines(转)
来源:互联网 发布:华为进入一组端口 编辑:程序博客网 时间:2024/05/07 02:51
http://www.softwareprojects.com/resources/programming/t-mysql-storage-engines-1470.html
One of the greatest things about MySQL, other than being free, widely supported and fast, is the flexibility of choosing different storage engines for different tables.
Out of the box, MySQL comes with 7 storage engines, including an "example" stub storage engine that allows you to implement your own storage library.
What's the big deal about having all these options?
Every storage engine is completely different, designed to address a unique application need.
Not being locked down to a single storage engine (like Oracle), means you can optimize and choose the best tool for the job.
Tip: A well designed MySQL-powered application will use different storage engines for different tables. If you're still stuck with all MyISAM tables, now may be a good time to revisit.
MySQL Storage Engines Overview
MyISAM: The default engine. No transactions support, average data reliability. Offers great performance for read heavy applications. Most web services and data warehousing applications use MyISAM heavily.
HEAP: All in-memory. Very fast for data retrieval, however due to being stored only in memory - all data is lost on shutdown. Great for temporary tables.
Archive: Used for storing large amounts of data without indexes in a small footprint.
Merge: Collection of MyISAM tables logically merged together to provide a single view.
InnoDB: Transaction-safe storage engine, best suited for write heavy environments thanks to row-level locking. Offers good built-in recovery and solid data reliability. InnoDB engine was acquired by Oracle on 2005.
NDB: A clustered engine - data is automatically split and replicated across several machines, a.k.a data nodes. Best suited for applications that require high performance lookups with the highest possible degree of uptime and availability. Originally designed by Ericsson for the Telco market, NDB offers the highest levels of data reliability (99.999%). NDB works well in read heavy environments. For write heavy environments with multiple concurrent writes, consider InnoDB.
The biggest disadvantadge of NDB, is that by design your entire database must fit in memory. If your database size times 2 is too big to fit in memory, NDBCluster is not for you.
-
To make it easier to follow the unique characteristics of each storage engine, I created this magic quadrant diagram:
Examples:
Below are some examples of using the best storage engine for different tasks:
Search Engine - NDBCluster
Web stats logging - Flat file for the logging with an offline processing demon processing and writing all stats into InnoDB tables.
Financial Transactions - InnoDB
Session data - MyISAM or NDBCluster
Localized calculations - HEAP
Dictionary - MyISAM
Important notes about MyISAM tables:
1. Your tables will get corrupted eventually! Plan accordingly.
Tar the entire database directory daily and setup MySQL replication to a slave for an up-to-the-minute live backup.
2. Turn on auto-repair by adding this flag to your my.cnf file:
myisam-recover=backup,force
Or consider running a check-all-tables-and-email-me cronjob daily: See our MySQL Table Maintenance automation.
3. Super fast for read (select) operations.
4. Concurrent writes lock the entire table. Switch everything to offline processing where you can, to serialize writes without taking the database down. (Offline processing is golden and applies to all table types)
Important notes about HEAP/Memory tables:
While this type of table offers super fast retrieval, it only works well for small temporary tables.
If you try to load too much data into a Memory table, MySQL will start swapping information to disk and then you lose the benefits of an all-memory storage.
Important notes about InnoDB tables:
1. ACID transactions support. Row-level locking (compared to table level locking with MyISAM) means faster concurrent writes.
2. Doing a "SELECT Count(*) FROM table" without specifying any indexes is very slow on InnoDB and requires a full table scan. (With MyIsam this operation doesn't cost anything because MyIsam stores an internal record counter with each table).
If you need to "SELECT COUNT(*)" often on InnoDB tables, create MySQL insert/delete triggers that will increment/decrement a counter whenever records are added or deleted from the table.
3. Backup:
Doing a tar/rsync backup where you simply copy all files is not possible with InnoDB.
MySQLDump backup is too slow with InnoDB. (If you insist on using it, turn on these flags: --opt --compress)
The only viable fast backup option, which can also be used to populate new slave machines, is InnoDB Hot Backup.
4. Recovery:
InnoDB has built-in recovery that works 99% of the times automatically. Never try to move .frm or .ibd files around as a way of "helping" the database to recover. If the built-in recovery doesn't work, switch to your slave server and restore the primary from backup.
5. LOAD DATA INFILE is too slow with InnoDB. Consider using MyIsam tables for LOAD DATA operations.
6. InnoDB is less forgiving than MyIsam when it comes to queries on non indexes. InnoDB is going to "School" you into ensuring every single query and update statement runs on an index. Issue no index queries and you'll pay dearly in execution time.
7. Never ever change my.cnf INnoDB log file size while the database is running. You'll corrupt the log sequence number beyond repair.
8. To maximize InnoDB MySQL database performance, start with these my.cnf settings:
innodb_open_files = 500
innodb_file_per_table
innodb_buffer_pool_size = 250M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency =8
innodb_lock_wait_timeout = 500
interactive_timeout = 20
back_log = 75
table_cache = 300
thread_cache = 32
thread_concurrency = 8
wait_timeout = 30
connect_timeout = 10
9. If InnoDB crashes and the built-in recovery mechanism is unable to roll-back transactions, your database will not start. This is very important to understand. With MyISAM, even if a table gets corrupted, you can still start the database and everything will work normally. InnoDB will simply refuse to start until you restore the entire database from backup. Make sure you understand this principle and backup religiously.
Scalability:
Every successful web application eventually outgrows the capacity and throughput of a single database machine.
At that point you typically have two options - Replication or NDBCluster.
As always the choice depends on the needs of your application.
For read-heavy environments, use an NDBCluster or setup replication for n MyISAM slave read-only machines.
For write-heavy environments, InnoDB on an active/passive replication setup is typically the best choice. You may also want to experiment with an NDBCluster. An NDBCluster is generally going to be slower than InnoDB in write-heavy environments, but it offers a higher level of availability.
Helpful Scripts:
* Convert all tables to InnoDB
* MySQLDump one database to another (designed for a case where you cannot use InnoDB Hot backup and you have a lot of tables/databases to import. Script automatically retries, reopens connection and imports one table at a time)
* Add triggers to all tables for fast InnoDB count(*)
You can only have one delete/insert trigger per each table so if you're already using triggers, modify the code accordingly.
Out of the box, MySQL comes with 7 storage engines, including an "example" stub storage engine that allows you to implement your own storage library.
What's the big deal about having all these options?
Every storage engine is completely different, designed to address a unique application need.
Not being locked down to a single storage engine (like Oracle), means you can optimize and choose the best tool for the job.
Tip: A well designed MySQL-powered application will use different storage engines for different tables. If you're still stuck with all MyISAM tables, now may be a good time to revisit.
MySQL Storage Engines Overview
MyISAM: The default engine. No transactions support, average data reliability. Offers great performance for read heavy applications. Most web services and data warehousing applications use MyISAM heavily.
HEAP: All in-memory. Very fast for data retrieval, however due to being stored only in memory - all data is lost on shutdown. Great for temporary tables.
Archive: Used for storing large amounts of data without indexes in a small footprint.
Merge: Collection of MyISAM tables logically merged together to provide a single view.
InnoDB: Transaction-safe storage engine, best suited for write heavy environments thanks to row-level locking. Offers good built-in recovery and solid data reliability. InnoDB engine was acquired by Oracle on 2005.
NDB: A clustered engine - data is automatically split and replicated across several machines, a.k.a data nodes. Best suited for applications that require high performance lookups with the highest possible degree of uptime and availability. Originally designed by Ericsson for the Telco market, NDB offers the highest levels of data reliability (99.999%). NDB works well in read heavy environments. For write heavy environments with multiple concurrent writes, consider InnoDB.
The biggest disadvantadge of NDB, is that by design your entire database must fit in memory. If your database size times 2 is too big to fit in memory, NDBCluster is not for you.
-
To make it easier to follow the unique characteristics of each storage engine, I created this magic quadrant diagram:
Examples:
Below are some examples of using the best storage engine for different tasks:
Search Engine - NDBCluster
Web stats logging - Flat file for the logging with an offline processing demon processing and writing all stats into InnoDB tables.
Financial Transactions - InnoDB
Session data - MyISAM or NDBCluster
Localized calculations - HEAP
Dictionary - MyISAM
Important notes about MyISAM tables:
1. Your tables will get corrupted eventually! Plan accordingly.
Tar the entire database directory daily and setup MySQL replication to a slave for an up-to-the-minute live backup.
2. Turn on auto-repair by adding this flag to your my.cnf file:
myisam-recover=backup,force
Or consider running a check-all-tables-and-email-me cronjob daily: See our MySQL Table Maintenance automation.
3. Super fast for read (select) operations.
4. Concurrent writes lock the entire table. Switch everything to offline processing where you can, to serialize writes without taking the database down. (Offline processing is golden and applies to all table types)
Important notes about HEAP/Memory tables:
While this type of table offers super fast retrieval, it only works well for small temporary tables.
If you try to load too much data into a Memory table, MySQL will start swapping information to disk and then you lose the benefits of an all-memory storage.
Important notes about InnoDB tables:
1. ACID transactions support. Row-level locking (compared to table level locking with MyISAM) means faster concurrent writes.
2. Doing a "SELECT Count(*) FROM table" without specifying any indexes is very slow on InnoDB and requires a full table scan. (With MyIsam this operation doesn't cost anything because MyIsam stores an internal record counter with each table).
If you need to "SELECT COUNT(*)" often on InnoDB tables, create MySQL insert/delete triggers that will increment/decrement a counter whenever records are added or deleted from the table.
3. Backup:
Doing a tar/rsync backup where you simply copy all files is not possible with InnoDB.
MySQLDump backup is too slow with InnoDB. (If you insist on using it, turn on these flags: --opt --compress)
The only viable fast backup option, which can also be used to populate new slave machines, is InnoDB Hot Backup.
4. Recovery:
InnoDB has built-in recovery that works 99% of the times automatically. Never try to move .frm or .ibd files around as a way of "helping" the database to recover. If the built-in recovery doesn't work, switch to your slave server and restore the primary from backup.
5. LOAD DATA INFILE is too slow with InnoDB. Consider using MyIsam tables for LOAD DATA operations.
6. InnoDB is less forgiving than MyIsam when it comes to queries on non indexes. InnoDB is going to "School" you into ensuring every single query and update statement runs on an index. Issue no index queries and you'll pay dearly in execution time.
7. Never ever change my.cnf INnoDB log file size while the database is running. You'll corrupt the log sequence number beyond repair.
8. To maximize InnoDB MySQL database performance, start with these my.cnf settings:
innodb_open_files = 500
innodb_file_per_table
innodb_buffer_pool_size = 250M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency =8
innodb_lock_wait_timeout = 500
interactive_timeout = 20
back_log = 75
table_cache = 300
thread_cache = 32
thread_concurrency = 8
wait_timeout = 30
connect_timeout = 10
9. If InnoDB crashes and the built-in recovery mechanism is unable to roll-back transactions, your database will not start. This is very important to understand. With MyISAM, even if a table gets corrupted, you can still start the database and everything will work normally. InnoDB will simply refuse to start until you restore the entire database from backup. Make sure you understand this principle and backup religiously.
Scalability:
Every successful web application eventually outgrows the capacity and throughput of a single database machine.
At that point you typically have two options - Replication or NDBCluster.
As always the choice depends on the needs of your application.
For read-heavy environments, use an NDBCluster or setup replication for n MyISAM slave read-only machines.
For write-heavy environments, InnoDB on an active/passive replication setup is typically the best choice. You may also want to experiment with an NDBCluster. An NDBCluster is generally going to be slower than InnoDB in write-heavy environments, but it offers a higher level of availability.
Helpful Scripts:
* Convert all tables to InnoDB
* MySQLDump one database to another (designed for a case where you cannot use InnoDB Hot backup and you have a lot of tables/databases to import. Script automatically retries, reopens connection and imports one table at a time)
* Add triggers to all tables for fast InnoDB count(*)
You can only have one delete/insert trigger per each table so if you're already using triggers, modify the code accordingly.
- MySQL Storage Engines(转)
- MySQL 5.5 Storage Engines
- Storage Engines
- MySQL database engines
- MySQL引擎(Engines)
- MySQL engines MyISAM and InnoDB
- Chapter 15 Alternative Storage Engines 替代存储引擎
- Differences Between the NDB and InnoDB Storage Engines
- Mongo3.4 Storage Engines存储引擎(简介)
- Mongo3.4 Storage Engines存储引擎(MMAPv1)
- Mongo3.4 Storage Engines存储引擎(内存存储引擎)
- 【知识库】--mysql engines MyISAM vs InnoDB(146)
- 17.3.2 Using Replication with Different Master and Slave Storage Engines
- Mongo3.4 Storage Engines存储引擎(将MongoDB实例更改为WiredTiger存储引擎)
- Mongo3.4 Storage Engines存储引擎(将Replica Set更改为WiredTiger存储引擎)
- MySQL Storage Engine
- MySQL Archive Storage Engine
- mysql storage procedure
- Linux下获得系统时间的C语言的实现方法[转]
- 广东河源周末游
- Linux必学的60个命令(6)-其他
- 开始记录了~~~
- strust2实践总结
- MySQL Storage Engines(转)
- 如何解决Datastage端口不能释放的问题
- 上帝,赐予我一个女人吧。
- 女婴哦。。。
- 删除后缀名为".torrent."的文件
- C++模板技术实例(2) 静态多态
- X Window 和GNOME、KDE
- MSCOMM
- 影像自动配准 之 特征(角点)提取 之 matlab