转mysql存储引擎memory，ndb，innodb之选择

1

mysql的innodb和cluster的NDB引擎都支持事务，在有共同的特性外，也有不同之处：
以mysql cluster NDB 7.3和MySQL 5.6之InnoDB为例：
ndb7.3基于mysql5.6，包括支持innodb1.1，因此可以在cluster里使用innodb表，但这些表不是集群的。
MySQL Cluster NDB存储引擎用分布式, shared-nothing的架构实现，这使其和innodb有不少不同之处。比如事务、外键、表限制等，具体见下表：

Theseare shown in the following table:

Feature

InnoDB1.1

MySQLClusterNDB 7.3, MySQL Cluster NDB7.4

MySQLServer Version

5.6

5.6

InnoDBVersion

InnoDB5.6.23

InnoDB5.6.23

MySQLCluster Version

N/A

NDB7.3.9

StorageLimits

64TB

3TB

(Practicalupper limit based on 48 data nodes with 64GB RAM each; can beincreased with disk-based data and BLOBs)

ForeignKeys

Yes

Priorto MySQL Cluster NDB 7.3: No. (Ignored, as withMyISAM)

Availablein MySQL Cluster NDB 7.3.

Transactions

Allstandard types

READCOMMITTED

MVCC

Yes

No

DataCompression

Yes

No

(MySQLCluster checkpoint and backup files can be compressed)

LargeRow Support (> 14K)

SupportedforVARBINARY,VARCHAR,BLOB,andTEXTcolumns

SupportedforBLOBandTEXTcolumns only

(Usingthese types to store very large amounts of data can lower MySQLCluster performance)

ReplicationSupport

Asynchronousand semisynchronous replication using MySQL Replication

Automaticsynchronous replication within a MySQL Cluster.

Asynchronousreplication between MySQL Clusters, using MySQL Replication

Scaleoutfor Read Operations

Yes(MySQL Replication)

Yes(Automatic partitioning in MySQL Cluster; MySQL Replication)

Scaleoutfor Write Operations

Requiresapplication-level partitioning (sharding)

Yes(Automatic partitioning in MySQL Cluster is transparent toapplications)

HighAvailability (HA)

Requiresadditional software

Yes(Designed for 99.999% uptime)

NodeFailure Recovery and Failover

Requiresadditional software

Automatic

(Keyelement in MySQL Cluster architecture)

Timefor Node Failure Recovery

30seconds or longer

Typically< 1 second

Real-TimePerformance

No

Yes

In-MemoryTables

No

Yes

(Somedata can optionally be stored on disk; both in-memory and diskdata storage are durable)

NoSQLAccess to Storage Engine

Nativememcached interface in development (see the MySQL Dev ZonearticleMySQLCluster 7.2 (DMR2): NoSQL, Key/Value, Memcached)

Yes

MultipleAPIs, including Memcached, Node.js/JavaScript, Java, JPA, C++,and HTTP/REST

Concurrentand Parallel Writes

Notsupported

Upto 48 writers, optimized for concurrent writes

ConflictDetection and Resolution (Multiple Replication Masters)

No

Yes

HashIndexes

No

Yes

OnlineAddition of Nodes

Read-onlyreplicas using MySQL Replication

Yes(all node types)

OnlineUpgrades

No

Yes

OnlineSchema Modifications

Yes,as part of MySQL 5.6.

Yes.

 

 

数据驱动型应用负载于innodb和NDB存储引擎之主要不同：

 

Workload

InnoDB

MySQLCluster (NDB)

High-VolumeOLTP Applications

Yes

Yes

DSSApplications (data marts, analytics)

Yes

Limited(Join operations across OLTP datasets not exceeding 3TB in size)

CustomApplications

Yes

Yes

PackagedApplications

Yes

Limited(should be mostly primary key access).

MySQLCluster NDB 7.3 supports foreign keys.

In-NetworkTelecoms Applications (HLR, HSS, SDP)

No

Yes

SessionManagement and Caching

Yes

Yes

E-CommerceApplications

Yes

Yes

UserProfile Management, AAA Protocol

Yes

Yes

 

这两种存储引擎适合的应用场景

 

Preferredapplication requirements forInnoDB

Preferredapplication requirements forNDB

  • Foreignkeys

    Note

    MySQLCluster NDB 7.3 supports foreign keys.

  • Fulltable scans

  • Verylarge databases, rows, or transactions

  • Transactionsother thanREADCOMMITTED

  • Writescaling

  • 99.999%uptime

  • Onlineaddition of nodes and online schema operations

  • MultipleSQL and NoSQL APIs (seeMySQLCluster APIs: Overview and Concepts)

  • Real-timeperformance

  • Limiteduse ofBLOBcolumns

  • Foreignkeys are supported, although their use may have an impact onperformance at high throughput

 

 

2

如何选择memory存储引擎或mysql cluster：
When to Use MEMORY or MySQL Cluster.
Developers looking to deploy applications that use the MEMORY storage engine for important, highly available, or frequently updated data should consider whether MySQL Cluster is a better choice. A typical use case for the MEMORY engine involves these
characteristics:
• Operations involving transient, non-critical data such as session management or caching. When the MySQL server halts or restarts, the data in MEMORY tables is lost.
• In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
• A read-only or read-mostly data access pattern (limited updates).

MySQL Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:
• Row-level locking and multiple-thread operation for low contention between clients.
• Scalability even with statement mixes that include writes.
• Optional disk-backed operation for data durability.
• Shared-nothing architecture and multiple-host operation with no single point of failure, enabling 99.999% availability.
• Automatic data distribution across nodes; application developers need not craft custom sharding or partitioning solutions.
• Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY.

MEMORY存储引擎和MySQL Cluster的更多细节对比参见白皮书《Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine》

Table 15.4 MEMORYStorage Engine Features

Storagelimits

RAM

Transactions

No

Lockinggranularity

Table

MVCC

No

Geospatialdata type support

No

Geospatialindexing support

No

B-treeindexes

Yes

T-treeindexes

No

Hashindexes

Yes

Full-textsearch indexes

No

Clusteredindexes

No

Datacaches

N/A

Indexcaches

N/A

Compresseddata

No

Encrypteddata[a]

Yes

Clusterdatabase support

No

Replicationsupport[b]

Yes

Foreignkey support

No

Backup/ point-in-time recovery[c]

Yes

Querycache support

Yes

Updatestatistics for data dictionary

Yes

[a]Implemented in the server (via encryption functions), ratherthan in the storage engine.

[b]Implemented in the server, rather than in the storage engine.

[c]Implemented in the server, rather than in the storage engine.

 

 

3

myisam, memory, ndb, archive, innodb存储引擎功能汇总：

Table 15.1 StorageEngines Feature Summary

Feature

MyISAM

Memory

InnoDB

Archive

NDB

Storagelimits

256TB

RAM

64TB

None

384EB

Transactions

No

No

Yes

No

Yes

Lockinggranularity

Table

Table

Row

Table

Row

MVCC

No

No

Yes

No

No

Geospatialdata type support

Yes

No

Yes

Yes

Yes

Geospatialindexing support

Yes

No

Yes[a]

No

No

B-treeindexes

Yes

Yes

Yes

No

No

T-treeindexes

No

No

No

No

Yes

Hashindexes

No

Yes

No[b]

No

Yes

Full-textsearch indexes

Yes

No

Yes[c]

No

No

Clusteredindexes

No

No

Yes

No

No

Datacaches

No

N/A

Yes

No

Yes

Indexcaches

Yes

N/A

Yes

No

Yes

Compresseddata

Yes[d]

No

Yes[e]

Yes

No

Encrypteddata[f]

Yes

Yes

Yes

Yes

Yes

Clusterdatabase support

No

No

No

No

Yes

Replicationsupport[g]

Yes

Yes

Yes

Yes

Yes

Foreignkey support

No

No

Yes

No

No

Backup/ point-in-time recovery[h]

Yes

Yes

Yes

Yes

Yes

Querycache support

Yes

Yes

Yes

Yes

Yes

Updatestatistics for data dictionary

Yes

Yes

Yes

Yes

Yes

[a]InnoDB support for geospatial indexing is available in MySQL5.7.5 and higher.

[b]InnoDB utilizes hash indexes internally for its AdaptiveHash Index feature.

[c]InnoDB support for FULLTEXT indexes is available in MySQL5.6.4 and higher.

[d]Compressed MyISAM tables are supported only when using thecompressed row format. Tables using the compressed row formatwith MyISAM are read only.

[e]Compressed InnoDB tables require the InnoDB Barracuda fileformat.

[f]Implemented in the server (via encryption functions), ratherthan in the storage engine.

[g]Implemented in the server, rather than in the storageengine.

[h]Implemented in the server, rather than in the storageengine.

 

要选mysql cluster要根据ndb存储引擎的特征和应用场景做详细测试，安装简测见我的博文《centos65安装简测mysql cluster 7.3.7》http://blog.csdn.net/beiigang/article/details/43485585

 

参考

http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html

 

 

 

----------------- 

转载请著明出处：
blog.csdn.net/beiigang

posted @ 2015-09-16 08:53  雍洲无名  阅读(1640)  评论(0)  编辑  收藏  举报