To get the most from MySQL, you need to understand its design so that you can
work with it, not against it. MySQL is flexible in many ways.
MySQL’s most unusual and important feature is its storage-engine
architecture, whose design separates query processing and other server tasks from
data storage and retrieval.
Connection Management and Security
Each client connection gets its own thread within the server process, queries execute within that single thread.
The server caches threads. Authentication is based on username, originating host, and password. X.509 certificates can also be used across an Secure Sockets Layer
Optimization and Execution
parses queries to create an internal structure (the parse tree), and then
applies a variety of optimizations
Concurrency Control
Locks Read/Write
Write locks have a higher priority than read locks.
Although storage engines can manage their own locks (ALTER TABLE)
Table locks ( basic locking)
Row locks ( InnoDB and Falcon , carries the greatest overhead)
Transactions
A transaction is a group of SQL queries that are
treated atomically, as a single unit of work. if any of them can’t be done, none of them is applied. It’s all or nothing.
sample transaction might look like this:
START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance – 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;
transactional storage engines: InnoDB, NDB Cluster, and Falcon.
Transactions aren’t enough unless the system passes the ACID test. ACID stands for
Atomicity, Consistency, Isolation, and Durability. These are tightly related criteria
that a well-behaved transaction processing system must meet:
Atomicity
A transaction must function as a single indivisible unit of work so that the entire
transaction is either applied or rolled back. When transactions are atomic, there
is no such thing as a partially completed transaction: it’s all or nothing.
Consistency
The database should always move from one consistent state to the next.
Isolation
The results of a transaction are usually invisible to other transactions until the
transaction is complete.
Durability
Once committed, a transaction’s changes are permanent. This means the
changes must be recorded such that data won’t be lost in a system crash. Dura-
bility is a slightly fuzzy concept, however, because there are actually many lev-
els. Some durability strategies provide a stronger safety guarantee than others,
and nothing is ever 100% durable.
If you don’t really need them, you might be able to get higher performance with a nontransactional storage engine for some kinds of queries.
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Deadlocks
A deadlock is when two or more transactions are mutually holding and requesting
locks on the same resources, creating a cycle of dependencies. Deadlocks occur when
transactions try to lock resources in a different order.
Deadlocks cannot be broken without rolling back one of the transactions, either par-
tially or wholly. They are a fact of life in transactional systems, and your applica-
tions should be designed to handle them.
Transaction Logging
If there’s a crash after the update is written to the transaction log but before the
changes are made to the data itself, the storage engine can still recover the changes
upon restart.
AUTOCOMMIT
This means that unless you’ve
explicitly begun a transaction, it automatically executes each query in a separate
transaction. You can enable or disable AUTOCOMMIT for the current connection by set-
ting a variable:
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| autocommit | ON |
+—————+——-+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT = 1;
If you mix transactional and nontransactional tables (for instance, InnoDB and
MyISAM tables) in a transaction, the transaction will work properly if all goes well.
MySQL will not usually warn you or raise errors if you do transactional operations
on a nontransactional table. Sometimes rolling back a transaction will generate the
warning “Some nontransactional changed tables couldn’t be rolled back,” but most
of the time, you’ll have no indication you’re working with nontransactional tables.
Implicit and explicit locking
We often see applications that have been converted from MyISAM to InnoDB but
are still using LOCK TABLES. This is no longer necessary because of row-level locking,
and it can cause severe performance problems.
The interaction between LOCK TABLES and transactions is complex, and
there are unexpected behaviors in some server versions. Therefore, we
recommend that you never use LOCK TABLES unless you are in a transac-
tion and AUTOCOMMIT is disabled, no matter what storage engine you are
using.
Multiversion Concurrency Control
MVCC works by keeping a snapshot of the data as it existed at some point in time.
This means transactions can see a consistent view of the data, no matter how long
they run. It also means different transactions can see different data in the same tables
at the same time!
Locking strategy Concurrency Overhead Engines
Table level Lowest Lowest MyISAM, Merge, Memory
Row level High High NDB Cluster
Row level with MVCC Highest Highest InnoDB, Falcon, PBXT, solidDB
MySQL’s Storage Engines
MySQL stores each database (also called a schema) as a subdirectory of its data direc-
tory in the underlying filesystem. When you create a table, MySQL stores the table
definition in a .frm file with the same name as the table.
mysql> SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)
MyISAM
As MySQL’s default storage engine, MyISAM provides a good compromise between
performance and useful features, such as full-text indexing, compression, and spatial
(GIS) functions.
a data file and an index file .MYD and .MYI extensions Format is platformneutral
MyISAM tables created in MySQL 5.0 with variable-length rows are configured by
default to handle 256 TB of data, using 6-byte pointers to the data records. Earlier
MySQL versions defaulted to 4-byte pointers, for up to 4 GB of data. All MySQL ver-
sions can handle a pointer size of up to 8 bytes. To change the pointer size on a
MyISAM table (either up or down), you must specify values for the MAX_ROWS and
AVG_ROW_LENGTH options that represent ballpark figures for the amount of space you
need:
CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
You can change the pointer
size later with the ALTER TABLE statement but that will cause the entire table and all of
its indexes to be rewritten, which may take a long time.
MyISAM features
- Locking and concurrency ( MyISAM locks entire tables)
- Automatic repair
- Manual repair
- Index features ( complex search operations)
- Delayed key writes
- Compressed MyISAM tables (Some tables—for example, in CD-ROM- or DVD-ROM-based applications)
- The MyISAM Merge Engine ( logging and data warehousing applications)
The InnoDB Engine
InnoDB was designed for transaction processing—specifically, processing of many
short-lived transactions that usually complete rather than being rolled back. It
remains the most popular storage engine for transactional storage.
InnoDB uses MVCC to achieve high concurrency, and it implements all four SQL
standard isolation levels.
You should strive for a small primary key if you’ll have many indexes on a table. InnoDB doesn’t
compress its indexes.
InnoDB is experiencing some scalability issues.
InnoDB loads data and creates indexes more slowly than MyISAM.
Any operation that changes an InnoDB table’s structure will rebuild the entire table,
including all the indexes.
Besides its high-concurrency capabilities, InnoDB’s next most popular feature is for-
eign key constraints, which the MySQL server itself doesn’t yet provide. InnoDB also
provides extremely fast lookups for queries that use a primary key.
InnoDB has a variety of internal optimizations. These include predictive read-ahead
for prefetching data from disk.
InnoDB’s behavior is very intricate, and we highly recommend reading the “InnoDB
Transaction Model and Locking” section of the MySQL manual if you’re using
InnoDB. There are many surprises and exceptions you should be aware of before
building an application with InnoDB.
The Memory Engine
Memory tables (formerly called HEAP tables) are useful when you need fast access to
data that either never changes or doesn’t need to persist after a restart. (magnitude faster than MyISAM table)
Here are some good uses for Memory tables:
- For “lookup” or “mapping” tables, such as a table that maps postal codes to state names
- For caching the results of periodically aggregated data
- For intermediate results when analyzing data
People often confuse Memory tables with temporary tables, which are
ephemeral tables created with CREATE TEMPORARY TABLE. Temporary
tables can use any storage engine; they are not the same thing as tables
that use the Memory storage engine. Temporary tables are visible only
to a single connection and disappear entirely when the connection
closes
The Archive Engine
The Archive engine supports only INSERT and SELECT queries, and it does not support indexes ( each SELECT query
requires a full table scan)
Archive tables are thus ideal for logging and data acquisition, where analysis tends to scan an entire table, or where you want fast INSERT queries on a replication master. Replication slaves can use a different storage engine for
the same table, which means the table on the slave can have indexes for faster performance on analysis
Archive is not a transactional storage engine. It is
simply a storage engine that’s optimized for high-speed inserting and compressed
storage.
The CSV Engine
The CSV engine can treat comma-separated values (CSV) files as tables, but it does
not support indexes on them.
CSV tables are especially useful as a data interchange format and for certain kinds of logging. This engine lets you copy files in and out of the database while the server is running.
The Federated Engine
The Federated engine does not store data locally. Each Federated table refers to a
table on a remote MySQL server, so it actually connects to a remote server for all
operations. It is sometimes used to enable “hacks” such as tricks with replication.
It does not perform well for aggregate queries, joins, or other basic operations.
The Blackhole Engine
The Blackhole engine has no storage mechanism at all. It discards every INSERT
instead of storing it. However, the server writes queries against Blackhole tables to its
logs as usual, so they can be replicated to slaves or simply kept in the log. That
makes the Blackhole engine useful for fancy replication setups and audit logging.
The NDB Cluster Engine
MySQL AB acquired the NDB Cluster engine from Sony Ericsson in 2003. It was
originally designed for high speed (real-time performance requirements), with redundancy and load-balancing capabilities.
One physical server is usually
dedicated to each node for redundancy and high availability. In this sense, NDB is similar to RAID at the server level.
A word of warning: NDB Cluster is very “cool” technology and definitely worth
some exploration to satisfy your curiosity, but many technical people tend to look
for excuses to use it and attempt to apply it to needs for which it’s not suitable.
This is just one of many aspects you’ll have to consider and understand thoroughly when
looking at NDB Cluster for a particular application. We will say, however, that it’s generally not what you think it is, and for most traditional
applications, it is not the answer.
The Falcon Engine
Jim Starkey, a database pioneer whose earlier inventions include Interbase, MVCC,
and the BLOB column type, designed the Falcon engine. MySQL AB acquired the Falcon technology in 2006, and Jim currently works for MySQL AB.
Falcon is designed for today’s hardware—specifically, for servers with multiple 64-bit processors and plenty of memory—but it can also operate in more modest envi-
ronments. Falcon uses MVCC and tries to keep running transactions entirely in
memory. This makes rollbacks and recovery operations extremely fast.
What is Falcon
* transactional MySQL storage engine
* based on Netfrastructure database engine
* engine has been in mission critical apps for more than 4 years
* extended and integrated into MySQL
Falcon is NOT
* an InnoDB clone
* Firebird
* a Firebird clone
* a standalone database management management system
* Netfrastructure
The solidDB Engine
The solidDB engine, developed by Solid Information Technology (http://www.
soliddb.com), is a transactional engine that uses MVCC. It supports both pessimistic
and optimistic concurrency control, which no other engine currently does.
solidDB for MySQL includes an online backup
capability at no charge.
The PBXT (Primebase XT) Engine
The PBXT engine, developed by Paul McCullagh of SNAP Innovation GmbH in
Hamburg, Germany (http://www.primebase.com), is a transactional storage engine
with a unique design. One of its distinguishing characteristics is how it uses its trans-
action logs and data files to avoid write-ahead logging. tests have already shown that it can be faster
than InnoDB for certain operations.
Selecting the Right Engine
When designing MySQL-based applications, you should decide which storage engine
to use for storing your data. If you don’t think about this during the design phase,
you will likely face complications later in the process.
It’s not necessarily a good idea to use different storage engines for dif-
ferent tables. If you can get away with it, it will usually make your life
a lot easier if you choose one storage engine for all your tables.
Here are the main elements you should take into account:
- Transactions ( InnoDB is the most stable, well-
integrated, proven choice at the time of this writing. ) - Concurrency (If you just need to insert and read concurrently, believe it or not, MyISAM is a
fine choice!
) - Backups ( If
your server can be shut down at regular intervals for backups, the storage
engines are equally easy to deal with.) - Crash recovery ( MyISAM tables generally become corrupt more easily and
take much longer to recover than InnoDB tables, for example. In fact, this is one
of the most important reasons why a lot of people use InnoDB when they don’t
need transactions.) http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/ - Special features ( or example,
a lot of applications rely on clustered index optimizations. At the moment, that
limits you to InnoDB and solidDB. On the other hand, only MyISAM supports
full-text search inside MySQL. If a storage engine meets one or more critical
requirements, but not others, you need to either compromise or find a clever
design solution.
)