Storage Engine (Table Types)

This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL’s storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O’Reilly Media, 2004.)

High Performance MySQLOne powerful aspect of MySQL that sets it apart from nearly every other database server is that it offers users many choices and options depending upon the user’s environment. From the server point of view, its default configuration can be changed to run well on a wide range of hardware. At the application development level, you have a variety of data types to choose from when creating tables to store records. But what’s even more unusual is that you can choose the type of table in which the records will be stored. You can even mix and match tables of different types in the same database!

Storage engines used to be called table types. From time to time we refer to them as table types when it’s less awkward to do so. In this chapter, we’ll show the major differences between the storage engines and why those differences are important. We’ll begin with a look at locking and concurrency as well as transactions—two concepts that are critical to understanding some of the major differences between the various engines. Then we’ll discuss the process of selecting the right one for your applications. Finally, we’ll look deeper into each of the storage engines and get a feel for their features, storage formats, strengths and weaknesses, limitations, and so on.

Before drilling down into the details, there are a few general concepts we need to cover because they apply across all the storage engines. Some aren’t even specific to MySQL at all; they’re classic computer science problems that just happen to occur frequently in the world of multiuser database servers.

MySQL Architecture

It will greatly aid your thinking about storage engines and the capabilities they bring to MySQL if you have a good mental picture of where they fit. Figure 2-1 provides a logical view of MySQL. It doesn’t necessarily reflect the low-level implementation, which is bound to be more complicated and less clear cut. However, it does serve as a guide that will help you understand how storage engines fit in to MySQL. (The NDB storage engine was added to MySQL just before this book was printed. Watch for it in the second edition.)

High Performance MySQL
Figure 2-1. A logical view of MySQL’s architecture.

The topmost layer is composed of the services that aren’t unique to MySQL. They’re services most network-based client/server tools or servers need: connection handling, authentication, security, etc.

The second layer is where things get interesting. Much of the brains inside MySQL live here, including query parsing, analysis, optimization, caching, and all the built-in functions (dates, times, math, encryption, etc.). Any functionality provided across storage engines lives at this level. Stored procedures, which will arrive in MySQL 5.0, also reside in this layer.

The third layer is made up of storage engines. They’re responsible for the storage and retrieval of all data stored “in” MySQL. Like the various filesystems available for Linux, each storage engine has its own benefits and drawbacks. The good news is that many of the differences are transparent at the query layer.

The interface between the second and third layers is a single API not specific to any given storage engine. This API is made up of roughly 20 low-level functions that perform operations such as “begin a transaction” or “fetch the row that has this primary key” and so on. The storage engines don’t deal with SQL or communicate with each other; they simply respond to requests from the higher levels within MySQL. 

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Locking}

Locking and Concurrency

The first of those problems is how to deal with concurrency and locking. In any data repository you have to be careful when more than one person, process, or client needs to change data at the same time. Consider, for example, a classic email box on a Unix system. The popular mbox file format is incredibly simple. Email messages are simply concatenated together, one after another. This simple format makes it very easy to read and parse mail messages. It also makes mail delivery easy: just append a new message to the end of the file.

But what happens when two processes try to deliver messages at the same time to the same mailbox? Clearly that can corrupt the mailbox, leaving two interleaved messages at the end of the mailbox file. To prevent corruption, all well-behaved mail delivery systems implement a form of locking to prevent simultaneous delivery from occurring. If a second delivery is attempted while the mailbox is locked, the second process must wait until it can acquire the lock before delivering the message.

This scheme works reasonably well in practice, but it provides rather poor concurrency. Since only a single program may make any changes to the mailbox at any given time, it becomes problematic with a high-volume mailbox, one that receives thousands of messages per minute. This exclusive locking makes it difficult for mail delivery not to become backlogged if someone attempts to read, respond to, and delete messages in that same mailbox. Luckily, few mailboxes are actually that busy.

Read/Write Locks

Reading from the mailbox isn’t as troublesome. There’s nothing wrong with multiple clients reading the same mailbox simultaneously. Since they aren’t making changes, nothing is likely to go wrong. But what happens if someone tries to delete message number 25 while programs are reading the mailbox? It depends. A reader could come away with a corrupted or inconsistent view of the mailbox. So to be safe, even reading from a mailbox requires special care.

Database tables are no different. If you think of each mail message as a record and the mailbox itself as a table, it’s easy to see that the problem is the same. In many ways, a mailbox is really just a simple database table. Modifying records in a database table is very similar to removing or changing the content of messages in a mailbox file.

The solution to this classic problem is rather simple. Systems that deal with concurrent read/write access typically implement a locking system that consists of two lock types. These locks are usually known as shared locks and exclusive locks,or read locks and write locks.

Without worrying about the actual locking technology, we can describe the concept as follows. Read locks on a resource are shared: many clients may read from the resource at the same time and not interfere with each other. Write locks, on the other hand, are exclusive, because it is safe to have only one client writing to the resource at given time and to prevent all reads when a client is writing. Why? Because the single writer is free to make any changes to the resource—even deleting it entirely.

In the database world, locking happens all the time. MySQL has to prevent one client from reading a piece of data while another is changing it. It performs this lock management internally in a way that is transparent much of the time.

Lock Granularity

One way to improve the concurrency of a shared resource is to be more selective about what is locked. Rather than locking the entire resource, lock only the part that contains the data you need to change. Better yet, lock only the exact piece of data you plan to change. By decreasing the amount of data that is locked at any one time, more changes can occur simultaneously—as long as they don’t conflict with each other.

The downside of this is that locks aren’t free. There is overhead involved in obtaining a lock, checking to see whether a lock is free, releasing a lock, and so on. All this business of lock management can really start to eat away at performance because the system is spending its time performing lock management instead of actually storing and retrieving data. (Similar things happen when too many managers get involved in a software project.)

To achieve the best performance overall, some sort of balance is needed. Most commercial database servers don’t give you much choice: you get what is known as row-level locking in your tables. MySQL, on the other hand, offers a choice in the matter. Among the storage engines you can choose from in MySQL, you’ll find three different granularities of locking. Let’s have a look at them.

Table Locks

The most basic and low-overhead locking strategy available is a table lock, which is analogous to the mailbox locks described earlier. The table as a whole is locked on an all-or-nothing basis. When a client wishes to write to a table (insert, delete, or update, etc.), it obtains a write lock that keeps all other read or write operations at bay for the duration of the operation. Once the write has completed, the table is unlocked to allow those waiting operations to continue. When nobody is writing, readers obtain read locks that allow other readers to do the same.

For a long time, MySQL provided only table locks, and this caused a great deal of concern among database geeks. They warned that MySQL would never scale up beyond toy projects and work in the real world. However, MySQL is so much faster than most commercial databases that table locking doesn’t get in the way nearly as much as the naysayers predicted it would.

Part of the reason MySQL doesn’t suffer as much as expected is because the majority of applications for which it is used consist primarily of read queries. In fact, the MyISAM engine (MySQL’s default) was built assuming that 90% of all queries run against it will be reads. As it turns out, MyISAM tables perform very well as long as the ratio of reads to writes is very high or very low.

Page Locks

A slightly more expensive form of locking that offers greater concurrency than table locking, a page lock is a lock applied to a portion of a table known as a page. All the records that reside on the same page in the table are affected by the lock. Using this scheme, the main factor influencing concurrency is the page size; if the pages in the table are large, concurrency will be worse than with smaller pages. MySQL’s BDB (Berkeley DB) tables use page-level locking on 8-KB pages.

The only hot spot in page locking is the last page in the table. If records are inserted there at regular intervals, the last page will be locked frequently.

Row Locks

The locking style that offers the greatest concurrency (and carries the greatest overhead) is the row lock. In most applications, it’s relatively rare for several clients to need to update the exact same row at the same time. Row-level locking, as it’s commonly known, is available in MySQL’s InnoDB tables. InnoDB doesn’t use a simple row locking mechanism, however. Instead it uses row-level locking in conjunction with a multiversioning scheme, so let’s have a look at that.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Multi-Version Concurrency Control}

There is a final technique for increasing concurrency: Multi-Version Concurrency Control (MVCC). Often referred to simply as versioning, MVCC is used by Oracle, by PostgreSQL, and by MySQL’s InnoDB storage engine. MVCC can be thought of as a new twist on row-level locking. It has the added benefit of allowing nonlocking reads while still locking the necessary records only during write operations. Some of MVCC’s other properties will be of particular interest when we look at transactions in the next section.

So how does this scheme work? Conceptually, any query against a table will actually see a snapshot of the data as it existed at the time the query began—no matter how long it takes to execute. If you’ve never experienced this before, it may sound a little crazy. But give it a chance.

In a versioning system, each row has two additional, hidden values associated with it. These values represent when the row was created and when it was expired (or deleted). Rather than storing the actual time at which these events occur, the database stores the version number at the time each event occurred. The database version (or system version) is a number that increments each time a query* begins. We’ll call these two values the creation id and the deletion id.

* That’s not quite true. As you’ll see when we start talking about transactions later, the version number is incremented for each transaction rather than each query.

Under MVCC, a final duty of the database server is to keep track of all the running queries (with their associated version numbers). Let’s see how this applies to particular operations:

SELECT

When records are selected from a table, the server must examine each row to ensure that it meets several criteria:

  • Its creation id must be less than or equal to the system version number. This ensures that the row was created before the current query began.

  • Its deletion id, if not null, must be greater than the current system version. This ensures that the row wasn’t deleted before the current query began.

  • Its creation id can’t be in the list of running queries. This ensures that the row wasn’t added or changed by a query that is still running.

Rows that pass all of these tests may be returned as the result of the query.

INSERT

When a row is added to a table, the database server records the current version number along with the new row, using it as the row’s creation id.

DELETE

To delete a row, the database server records the current version number as the row’s deletion id.

UPDATE

When a row is modified, the database server writes a new copy of the row, using the version number as the new row’s creation id. It also writes the version number as the old row’s deletion id.

The result of all this extra record keeping is that read queries never lock tables, pages, or rows. They simply read data as fast as they can, making sure to select only rows that meet the criteria laid out earlier. The drawbacks are that the server has to store a bit more data with each row and do a bit more work when examining rows. Table 2-1 summarizes the various locking models and concurrency in MySQL.

Locking strategy

Concurrency

Overhead

Engines

Table locks

Lowest

Lowest

MyISAM, Heap, Merge

Page locks

Modest

Modest

BDB

Multiversioning

Highest

High

InnoDB


Table 2-1. Locking models and concurrency in MySQL

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Transactions}

You can’t examine the more advanced features of a database system for very long before transactions enter the mix. A transaction is a group of SQL queries that are treated atomically, as a single unit of work. Either the entire group of queries is applied to a database, or none of them are. Little of this section is specific to MySQL. If you’re already familiar with ACID transactions, feel free to skip ahead to the section “Transactions in MySQL.”

A banking application is the classic example of why transactions are necessary. Imagine a bank’s database with a two tables: checking and savings. To move $200 from Jane’s checking account to her savings account, you need to perform at least three steps:

  1. Make sure her checking account balance is greater than $200.

  2. Subtract $200 from her checking account balance.

  3. Add $200 to her savings account balance.

The entire operation should be wrapped in a transaction so that if any one of the steps fails, they can all be rolled back.

A transaction is initiated (or opened) with the BEGIN statement and applied with COMMIT or rolled back (undone) with ROLLBACK. So the SQL for the transaction might look like this:

        BEGIN;
[step 1] SELECT balance FROM checking WHERE customer_id = 10233276;
[step 2] UPDATE checking SET balance = balance – 200.00 WHERE customer_id = 10233276;
[step 3] UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
        COMMIT;

But transactions alone aren’t the whole story. What happens if the database server crashes while performing step 3? Who knows? The customer probably just lost $200. What if another process comes along between Steps 2 and 3 and removes the entire checking account balance? The bank has given the customer a $200 credit without even knowing it.

Simply having transactions isn’t sufficient unless the database server passes what is known as the ACID test. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability—four tightly related criteria that are required in a well-behaved transaction processing system. Transactions that meet those four criteria are often referred to as ACID transactions.

Atomicity

Transactions must function as a single indivisible unit of work. 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. Consistency ensures that a crash between Steps 2 and 3 doesn’t result in $200 missing from the checking account. Because the transaction is never committed, none of the transaction’s changes are ever reflected in the database.

Isolation

The results of a transaction are usually invisible to other transactions until the transaction is complete. This ensures that if a bank account summary runs after Step 2, but before Step 3, it still sees the $200 in the checking account. When we discuss isolation levels, you’ll understand why we said usually invisible.

Durability

Once committed, the results of a transaction are permanent. This means that the changes must be recorded in such a way that system crashes won’t lose the data. Of course, if the database server’s disks fail, all bets are off. That’s a hardware problem. We’ll talk more about how you can minimize the effects of hardware failures in Chapter 6.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Benefits and Drawbacks}

ACID transactions ensure that banks don’t lose your money. By wrapping arbitrarily complex logic into single units of work, the database server takes some of the burden off application developers. The database server’s ACID properties offer guarantees that reduce the need for code guarding against race conditions and handling crash recovery.

The downside of this extra security is that the database server has to do more work. It also means that a database server with ACID transactions will generally require more CPU power, memory, and disk space than one without them. As mentioned earlier, this is where MySQL’s modularity comes into play. Because you can decide on a per-table basis if you need ACID transactions or not, you don’t need to pay the performance penalty on a table that really won’t benefit from transactions.

Isolation Levels

The previous description of isolation was a bit simplistic. Isolation is more complex than it might first appear because of some peculiar cases that can occur. The SQL standard defines four isolation levels with specific rules for which changes are and aren’t visible inside and outside a transaction. Let’s look at each isolation level and the type of problems that can occur.

Read uncommitted

In the read uncommitted isolation level, transactions can view the results of uncommitted transactions. At this level, many problems can occur unless you really, really know what you are doing and have a good reason for doing it. Read uncommitted is rarely used in practice. Reading uncommitted data is also known as a dirty read.

Read committed

The default isolation level for most database systems is read committed. It satisfies the simple definition of isolation used earlier. A transaction will see the results only of transactions that were already committed when it began, and its changes won’t be visible to others until it’s committed.

However, there are problems that can occur using that definition. To visualize the problems, refer to the sample data for the Stock and StockPrice tables as shown in Tables 2-2 and 2-3.

id

Ticker

Name

1

MSFT

Microsoft

2

EBAY

eBay

3

YHOO

Yahoo!

4

AMZN

Amazon

Table 2-2. The Stock table.

stock_id

date

open

high

low

close

1

2002-05-01

21.25

22.30

20.18

21.30

2

2002-05-01

10.01

10.20

10.01

10.18

3

2002-05-01

18.23

19.12

18.10

19.00

4

2002-05-01

45.55

46.99

44.87

45.71

1

2002-05-02

21.30

21.45

20.02

20.21

2

2002-05-02

10.18

10.55

10.10

10.35

3

2002-05-02

19.01

19.88

19.01

19.22

4

2002-05-02

45.69

45.69

44.03

44.30

Table 2-3. The StockPrice table

Imagine you have a Perl script that runs nightly to fetch price data about your favorite stocks. For each stock, it fetches the data and adds a record to the StockPrice

table with the day’s numbers. So to update the information for Amazon.com, the transaction might look like this:

BEGIN;
SELECT @id := id FROM Stock WHERE ticker = ‘AMZN’;
INSERT INTO StockPrice VALUES (@id, ’2002-05-03′, 20.50, 21.10, 20.08, 21.02);
COMMIT;

But what if, between the select and insert, Amazon’s id changes from 4 to 17 and a new stock is added with id 4? Or what if Amazon is removed entirely? You’ll end up inserting a record with the wrong id in the first case. And in the second case, you’ve inserted a record for which there is no longer a corresponding row in the Stock table. Neither of these is what you intended.

The problem is that you have a nonrepeatable read in the query. That is, the data you read in the SELECT becomes invalid by the time you execute the INSERT. The repeatable read isolation level exists to solve this problem.

Repeatable read

At the repeatable read isolation level, any rows that are read during a transaction are locked so that they can’t be changed until the transaction finishes. This provides the perfect solution to the problem mentioned in the previous section, in which Ama-zon’s id can change or vanish entirely. However, this isolation level still leaves the door open to another tricky problem: phantom reads.

Using the same data, imagine that you have a script that performs some analysis based on the data in the StockPrice table. And let’s assume it does this while the nightly update is also running.

The analysis script does something like this:

BEGIN;
SELECT * FROM StockPrice WHERE close BETWEEN 10 and 20;
// think for a bit
SELECT * FROM StockPrice WHERE close BETWEEN 10 and 20; COMMIT;

But the nightly update script inserts between those two queries new rows that happen to match the close BETWEEN 10 and 20 condition. The second query will find more rows that the first one! These additional rows are known as phantom rows (or simply phantoms). They weren’t locked the first time because they didn’t exist when the query ran.

Having said all that, we need to point out that this is a bit more academic than you might think. Phantom rows are such a common problem that InnoDB’s locking (known as next-key locking) prevents this from happening. Rather than locking only the rows you’ve touched in a query, InnoDB actually locks the slot following them in the index structure as well.

Serializable

The highest level of isolation, serializable, solves the phantom read problem by ordering transactions so that they can’t conflict. At this level, a lot of timeouts and lock contention may occur, but the needs of your application may bring you to accept the decreased performance in favor of the data stability that results.

Table 2-2 summarizes the various isolation levels and the drawbacks associated with each one. Keep in mind that as you move down the list, you’re sacrificing concurrency and performance for increased safety.

Isolation level

Dirty reads possible

Non-repeatable reads possible

Phantom reads possible

Read uncommitted

Yes

Yes

Yes

Read committed

No

Yes

Yes

Repeatable read

No

No

Yes

Serializable

No

No

No

Table 2-4. ANSI SQL isolation levels

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Deadlocks}

Whenever multiple transactions obtain locks, there is the danger of encountering a deadlock condition. Deadlocks occur when two transactions attempt to obtain conflicting locks in a different order.

For example, consider these two transactions running against the StockPrice table:

Transaction #1:

BEGIN;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = ’2002-05-01′;
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = ’2002-05-02′;
COMMIT;

Transaction #2:

BEGIN;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = ’2002-05-02′;
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = ’2002-05-01′;
COMMIT;

If you’re unlucky, each transaction will execute its first query and update a row of data, locking it in the process. Each transaction will then attempt to update its second row only to find that it is already locked. Left unchecked, the two transactions will wait for each other to complete—forever.

To combat this problem, database systems implement various forms of deadlock detection and timeouts. The more sophisticated systems, such as InnoDB, will notice circular dependencies like the previous example and return an error. Others will give up after the query exceeds a timeout while waiting for a lock. InnoDB’s default timeout is 50 seconds. In either case, applications that use transactions need to be able to handle deadlocks and possibly retry transactions.

Transaction Logging

Some of the overhead involved with transactions can be mitigated through the use of a transaction log. Rather than directly updating the tables on disk each time a change occurs, the system can update the in-memory copy of the data (which is very fast) and write a record of the change to a transaction log on disk. Then, at some later time, a process (or thread) can actually apply the changes that the transaction log recorded. The serial disk I/O required to append events to the log is much faster than the random seeks required to update data in various places on disk.

As long as events are written to the transaction log before a transaction is considered committed, having the changes in a log will not affect the durability of the system. If the database server crashes before all changes have been applied from the transaction log, the database will continue applying changes from the transaction log when it is restarted and before it accepts new connections.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Transactions in MySQL}

MySQL provides two transaction-safe storage engines: Berkeley DB (BDB) and InnoDB. Their specific properties are discussed in next section. Each one offers the basic BEGIN/COMMIT/ROLLBACK functionality. They differ in their supported isolation levels, locking characteristics, deadlock detection, and other features.

AUTOCOMMIT

By default MySQL operates in AUTOCOMMIT mode. This means that unless you’ve explicitly begun a transaction, it automatically executes each query in a separate transaction. You can enable AUTOCOMMIT for the current connection by running:

SET AUTOCOMMIT = 1;

Disable it by executing:

SET AUTOCOMMIT = 0;

Changing the value of AUTOCOMMIT has no effect on non-transaction-safe tables such as MyISAM or HEAP.

Implicit commits

Certain commands, when issued during an open transaction, cause MySQL to commit the transaction before they execute. Typically these are commands that make significant changes, such as removing or renaming a table.

Here is the list of commands for which MySQL implicitly commits a transaction:

  • ALTER TABLE

  • BEGIN

  • CREATE INDEX

  • DROP DATABASE

  • DROP TABLE

  • RENAME TABLE

  • TRUNCATE

  • LOCK TABLES

  • UNLOCK TABLES

As additional features are added to MySQL, it is possible that other commands will be added to the list, so be sure to check the latest available documentation.

Isolation levels

MySQL allows you to set the isolation level using the SET TRANSACTION ISOLATION LEVEL command. Unless otherwise specified, the isolation level is changed beginning with the next transaction.

To set the level for the whole session (connection), use:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Here’s how to set the global level:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE

MySQL recognizes all four ANSI standard isolation levels, and as of Version 4.0.5 of MySQL, InnoDB supports all of them:

  • READ UNCOMMITTED

  • READ COMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

The default isolation level can also be set using the –transaction-isolation command-line option when starting the server or set via my.cnf.

Mixing storage engines in transactions

Transaction management in MySQL is currently handled by the underlying storage engines, not at a higher level. Thus, you can’t reliably mix tables stored in transactional engines (such as InnoDB and BDB) in a single transaction. A higher-level transaction management service may someday be added to MySQL, making it safe to mix and match transaction-safe tables in a transaction. Until then, don’t expect it to work.

If you mix transaction-safe and non-transaction-safe tables (such as InnoDB and MyISAM) in a transaction, the transaction will work properly if all goes well. However, if a rollback is required, the changes to the non-transaction-safe table won’t be undone. This leaves the database in an inconsistent state that may be difficult to recover from (and renders the entire point of transactions moot).

Simulating transactions

At times you may need the behavior of transactions when you aren’t using a transac-tion-safe table. You can achieve something like transactions using MySQL’s LOCK TABLES and UNLOCK TABLES commands. If you lock the tables that will be involved in the transaction and keep track of any changes that you make (in case you need to simulate a rollback), you’ll have something equivalent to running at the serializable isolation level. But the process is kludgy and error prone, so if you really need transactions, we recommend using a transactional storage engine.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Selecting the Right Engine}

When designing MySQL-based applications, you should decide which engine to use for storing your data. If you don’t think about it during the design phase, you will likely face complications later in the process. You might find that the default engine doesn’t provide a feature you need, such as transactions. Or maybe the mix of read and write queries your application generates will require more granular locking than MyISAM’s table locks.

Because you can make the choice on a table-by-table basis, you’ll need a clear idea of how each table is used and the data it stores. Of course, it also helps to have a good understanding of the application as a whole and its potential for growth. Armed with this information, you can begin to make good choices about which table engines can do the job.

Considerations

While there are many factors that can affect your decision, it usually boils down to just a few considerations: transactions and concurrency, backups, and special features.

Transactions and concurrency

When it comes to transactions and concurrency, consider the following guidelines:

  • If your application requires transactions and high read/write concurrency, InnoDB is probably your best bet.

  • If your application requires transactions but only moderate read/write concurrency, either BDB or InnoDB tables should work fine.

  • If your application doesn’t require transactions and issues primarily SELECT or primarily INSERT/UPDATE queries, MyISAM is a good choice. Many web applications fall into this category.

Backups

The need to perform regular backups may also influence your table choices. If your server can be shut down at regular intervals for backups, the storage engines are equally easy to deal with. However, if you need to perform online backups in one form or another, the choices become less clear. Chapter 9 deals with this topic in more detail.

Another way of looking at this is simplicity. As you’ll see in Chapter 9, using multiple storage engines increases the complexity of backups and server tuning. You may decide that it’s just easier to use a single storage engine rather than those that are theoretically best.

Special features

Finally, you sometimes find that an application relies on particular features or optimizations that are provided by only some of MySQL’s storage engines. For example, not all tables provide a quick answer to queries like the following:

SELECT COUNT(*) FROM mytable

If your application depends on accurate and fast row counts, MyISAM is the answer. InnoDB must actually count up all the rows, but the MyISAM storage engine always knows the exact row count of a table without the need to do any work.

If your application requires referential integrity with foreign keys, you’re limited to just InnoDB tables. Do you need full-text search capabilities? Only MyISAM tables provide it.

Keep this in mind as you read the more detailed information about each table’s features. There will come a time when you find that the feature you really, really need is available only in one table engine. When that happens, you need to either compromise or break a table into multiple tables of different types.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Practical Examples}

These issues may seem rather abstract without some sort of real-world context. So let’s consider some common uses for tables in various database applications. For each table, we’ll look at which engine best matches with the table’s needs. The details of each engine are covered in the next section.

Logging

Suppose you want to use MySQL to log a record of every telephone call from a central telephone switch in real time. Or maybe you’ve installed mod_log_sql for Apache so you can log all visits to your web site directly in a table. In such an application, speed is probably the most important goal; you don’t want the database to be the bottleneck. Using MyISAM tables works very well because they have very low overhead and can handle inserting thousands of records per second.

Things will get interesting if you decide it’s time to start running reports to summarize the data you’ve logged. Depending on the queries you use, there’s a good chance you will significantly slow the process of inserting records while gathering data for the report. What can you do?

You can use MySQL’s built-in replication (Chapter 7) to clone the data onto a second (slave) server. You can then run your time-and CPU-intensive queries against the data on the slave. This keeps the master free to insert records as fast as it possibly can while also giving you the freedom to run any query you want without worrying about how it could affect the real-time logging.

Another option is to use a MyISAM Merge table. Rather than always logging to the same table, adjust the application to log to a table that contains the name or number of the month in its name, such as web_logs_2004_01 or web_logs_2004_jan. Then define a Merge table that contains the data you’d like to summarize and use it in your queries. If you need to summarize data daily or weekly, the same strategy works; you just need to create tables with more specific names, such as web_logs_ 2004_01_01. While you’re busy running queries against tables that are no longer being written to, your application can log records to its current table uninterrupted. Merge tables are discussed in the later section “MyISAM Merge tables.”

A final possibility is simply to switch to using a table that has more granular locking than MyISAM does. Either BDB or InnoDB works well in this case. Non-MyISAM tables will generally use more CPU and disk space, but that may be a reasonable tradeoff in this case. Also, in the event of a crash, MyISAM tables may take quite a long time to check and repair while InnoDB tables should recover quickly.

Read-only or read-mostly tables

Tables that contain the data used to construct a catalog or listing of some sort (jobs, auctions, real estate, etc.) are usually read from far more often than they are written to. This makes them great candidates for MyISAM.

Order processing

When you deal with any sort of order processing, transactions are a requirement. Half-completed orders aren’t going to endear customers to your service. Using trans-action-safe table types (InnoDB or BDB), these unfortunate “data surprises” can be avoided. Considering that BDB tables use—at best—locking at the page level, applications with high transaction volumes should consider InnoDB tables.

In the case of order processing, InnoDB has a distinct advantage because it supports referential integrity through the use of foreign keys. These keys allow a field in one table to have an enforced relationship to the key of another table (e.g., an Order record contains a CustomerID field that “points” to the primary key of the Customer table). Foreign keys effectively point to those other tables and indicate that data is maintained in them, and they help you keep data consistent across your tables. (Keep in mind that a foreign key in an InnoDB table must reference another InnoDB table. Currently they can’t cross storage engines.)

You might want to design your tables so that customers can’t be removed without also removing all their orders. Or maybe you’d like to ensure that products aren’t deleted from the catalog table before the orders that reference those products are archived. With InnoDB’s foreign keys, you can.

Stock quotes

If you’re collecting stock quotes for your own analysis, MyISAM tables work great. However, if you’re running a high-traffic web service that has a real-time quote feed and thousands of users, a query should never have to wait. At any time, there could be many clients attempting to read and write to the table, so the row-level locking provided by InnoDB tables is the way to go.

If you have sufficient memory, MySQL’s in-memory Heap tables might be an option, too. However, their indexes have some interesting restrictions you need to investigate first. See the “Heap Tables” section in Chapter 4 for more details.

Bulletin boards and threaded discussion forums

Threaded discussions are an interesting problem for MySQL users. There are hundreds of freely available PHP and Perl-based systems available that provide threaded discussions. Many of them aren’t written with database efficiency in mind, so they tend to perform a large number of queries for each request they serve, as well as updates to counters and usage statistics about the various discussions. Many of the systems also use a small number of monolithic tables to store all their data. As a result, a few central tables become the focus of heavy read and write activity, and the locks required to enforce concurrency become a substantial source of contention.

Despite their design shortcomings, most of the systems work well for small and medium loads. However, if a web site grows large enough and generates a significant amount of traffic, it may begin to get very slow. The obvious solution is to switch to a different table type that can handle the heavy read/write volume. Users who have attempted this are sometimes surprised to find that the system runs even more slowly than it did before!

What they don’t realize is that the system is using a particular query, normally something like:

SELECT COUNT(*) FROM table WHERE …

The problem is that not all engines can run that query quickly. MyISAM tables keep accurate row counts available, so they can. But BDB and InnoDB must actually scan the data to count all the rows. The developers of the popular web site Slashdot (http://slashdot.org/) ran into this problem when they moved their system from MyISAM to InnoDB tables. They spent time going through their code to eliminate all those queries.

MySQL’s query cache, which we’ll cover in more detail in Chapter 5, can often be a big help in situations in which an application issues the same query over and over with the same parameters.

CD-ROM applications

If you ever need to distribute a CD-ROM-or DVD-ROM-based application that uses MySQL data files, consider using MyISAM or Compressed MyISAM tables. They can be easily isolated and copied to other media. Compressed MyISAM tables take far less space than uncompressed ones, but they are read-only. Since the data is going to be on read-only media anyway, there’s little reason not to use compressed tables.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Table Conversions}

Several techniques are available to convert one table type to another, each with advantages and disadvantages. In the following sections, we cover three of the most common.

ALTER TABLE

The easiest way to move a table from one engine to another is by using an ALTER TABLE statement. The following command converts mytable to BDB:

ALTER TABLE mytable TYPE = BDB;

Note: As of MySQL Versions 4.0.18 and 4.1.2, you may use ENGINE instead of TYPE. In a later version of MySQL (probably in the 5.x series), support for TYPE will be removed entirely.

The previous syntax works for all storage engines, but there’s a catch: it can take a lot of time. MySQL will perform a row-by-row copy of your old table into your new table. During that time, you’ll probably be using all the server’s disk I/O capacity, and the original table will be locked while the conversion runs. So take care before trying this technique on a busy table. Instead, you can use one of the following methods, which involve making a copy of the table first.

Dump and reimport

To gain more control over the process, you might choose to dump the table to a text file using the mysqldump utility. Once the table is dumped, simply edit the dump file to adjust the CREATE TABLE statement it contains. Be sure to change the table name as well as its type because you can’t have two tables with the same name in the same database even if they are of different types.

If you import into InnoDB or BDB, be sure to use the –no-autocommit option to disable AUTOCOMMIT mode. Otherwise each individual insert will be performed in its own transaction.

The downside of using mysqldump is that it isn’t terribly fast and uses far more disk space. Not only will the dump file contain all the data from the table, it will also contain all the SQL necessary to repopulate the table. Also, you won’t be able to delete the dump file until the new table has been created.

Furthermore, if the dump file happens to be quite large, editing it can be a challenge. You can’t simply load a 6-GB file into vi or emacs on most systems.* Instead, you’ll need to craft a Perl or sed script to do the job.

* Maybe you can, but it’ll be pretty painful.

CREATE and SELECT

The third technique is a compromise between the speed of the first mechanism and the safety of the second. Rather than dumping the entire table or converting it all at once, you create the new table and use MySQL’s INSERT INTO … SELECT syntax to populate it incrementally. If, for example, you have a MyISAM table called myisam_ table that you’d like to convert to an InnoDB table named innodb_table, you need to run queries like this:

BEGIN;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;

Assuming that id is the primary key, you run that query using larger values of x and y each time until all the data has been copied to the new table. After doing so, you are left with the original table, which you can drop after you’re done with it, and the new table, which is now fully populated.

Alternatively, if you use MySQL 4.1 or newer, you can create the new table and copy the table in two steps:

CREATE TABLE newtable LIKE mytable;
INSERT INTO newtable SELECT * FROM mytable;

Whichever method you use, if you’re dealing with a large volume of data, it’s often more efficient to copy the data before adding indexes to the new table.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=The Storage Engines}

Now it’s time to look at each of MySQL’s storage engines in more detail. Table 2-3 summarizes some of the high-level characteristics of the handlers. The following sections provide some basic highlights and background about each table handler as well as any unusual characteristics and interesting features.

Before going further, it’s worth noting that this isn’t an exhaustive discussion of MySQL’s storage engines. We assume that you’ve read (or at least know where to find) the information in the MySQL Reference Manual.

Table 2-5: Storage Engine features in MySQL

Attribute

MyISAM

Heap

BDB

InnoDB

Transactions

No

No

Yes

Yes

Lock granularity

Table

Table

Page (8 KB)

Row

Storage

Split files

In-memory

Single file per table

Tablespace(s)

Isolation levels

None

None

Read committed

All

Portable format

Yes

N/A

No

Yes

Referential integrity

No

No

No

Yes

Primary key with data

No

No

Yes

Yes

MySQL caches data records

No

Yes

Yes

Yes

Availability

All versions

All versions

MySQL-Max

All Versions*

*Prior to MySQL 4.0, InnoDB was available in MySQL-Max only.

Most of MySQL’s disk-based tables have some basic things in common. Each database in MySQL is simply a subdirectory of MySQL’s data directory in the underlying filesystem.* Whenever you create a table, MySQL stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named MyTable, MySQL stores the table definition in MyTable.frm.

* In MySQL 5.0, the term “database” will likely morph into “schema.”

To determine the type of a table, use the SHOW TABLE STATUS command. For example, to examine the user table in the mysql database, you execute the following:

mysql> SHOW TABLE STATUS LIKE ‘user’ G

****************** 1. row ***************************
Name: user
Type: 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
Create_options:
Comment: Users and global privileges
1 row in set (0.06 sec)

Notice that it’s a MyISAM table. You might also notice a lot of other information and statistics in the output. Let’s briefly look at what each line means:

Name

The table’s name.

Type

The table’s type. Again, in some versions of MySQL, this may say “Engine” rather than “Type.”

Row_format

Dynamic, Fixed, or Compressed. Dynamic rows vary in length because they contain variable-length fields such as VARCHAR or BLOB. Fixed rows, which are always the same size, are made up of fields that don’t vary in length, such as CHAR and INTEGER. Compressed rows exist only in compressed tables (see the later section “Compressed MyISAM”).

Rows

The number of rows in the table. For non-transactional tables, this number is always accurate. For transactional tables, it is usually an estimate.

Avg_row_length

How many bytes the average row contains.

Data_length

How much data (in bytes) the entire table contains.

Max_data_length

The maximum amount of data this table can hold. In a MyISAM table with dynamic (variable length) rows, the index file for a table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only up to 4 GB of space by default. See the next section, “MyISAM Tables” for more details. For MyISAM tables with fixed-length rows, the limit is just under 4.3 billion rows.

Index_length

How much space is consumed by index data.

Data_free

The amount of space that has been allocated but is currently unused.

Auto_increment

The next AUTO_INCREMENT value.

Create_time

When the table was first created.

Update_time

When data in the table last changed.

Check_time

When the table was last checked using CHECK TABLE or myisamchk.

Create_options

Any other options that were specified when the table was created.

Comment

The comments, if any, that were set when the table was created.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=MyISAM Tables}

As MySQL’s default storage engine, MyISAM provides a good compromise between performance and useful features. Versions of MySQL prior to 3.23 used the Index Sequential Access Method (ISAM) table format. In Version 3.23, ISAM tables were deprecated in favor of MyISAM, an enhanced ISAM format.* MyISAM tables don’t provide transactions or a very granular locking model, but they do have full-text indexing (see Chapter 4), compression, and more.

* ISAM tables may be used in MySQL 4.0 and 4.1. Presumably they’ll vanish sometime in the 5.x release cycle. If you’re still using ISAM tables, it’s time to upgrade to MyISAM!

Storage

In MyISAM storage, there are typically two files: a data file and an index file. The two files bear .MYD and .MYI extensions, respectively. The MyISAM format is plat-form-neutral, meaning you can copy the data and index files from an Intel-based server to a Macintosh PowerBook or Sun SPARC without any trouble.

MyISAM tables can contain either dynamic or static (fixed-length) rows. MySQL decides which format to use based on the table definition. The number of rows a MyISAM table can hold is limited primarily by the available disk space on your database server and the largest file your operating system will let you create. Some (mostly older) operating systems have been known to cut you off at 2 GB, so check your local documentation.

However, MyISAM files with variable-length rows, are set up by default to handle only 4 GB of data, mainly for efficiency. The index uses 32-bit pointers to the data records. To create a MyISAM table that can hold more than 4 GB, 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;

In the example, we’ve told MySQL to be prepared to store at least 32 GB of data in the table. To find out what MySQL decided to do, simply ask for the table status:

mysql> SHOW TABLE STATUS LIKE ‘mytable’ G

******************* 1. row ***************************
Name: mytable
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 98784247807
Index_length: 1024 Data_free: 0
Auto_increment: NULL
Create_time: 2002-02-24 17:36:57
Update_time: 2002-02-24 17:36:57
Check_time: NULL
Create_options: max_rows=1000000000 avg_row_length=32
Comment:
1 row in set (0.05 sec)

As you can see, MySQL remembers the create options exactly as specified. And it chose a representation capable of holding 91 GB of data!

Other stuff

As one of the oldest storage engines included in MySQL, MyISAM tables have a number of features that have been developed over time specifically to fill niche needs uncovered through years of use:

Locking and concurrency

Locking in MyISAM tables is performed at the table level. Readers obtain shared (read) locks on all tables they need to read. Writers obtain exclusive (write) locks.

Automatic repair

If MySQL is started with the –myisam-recover option, the first time it opens a MyISAM table, it examines the table to determine whether it was closed properly. If it was not (probably because of a hardware problem or power outage), MySQL scans the table for problems and repairs them. The downside, of course, is that your application must wait while a table it needs is being repaired.

Manual repair

You can use the CHECK TABLE mytable and REPAIR TABLE mytable commands to check a table for errors and repair them. The myisamchk command-line tool can also be used to check and repair tables when the server is offline.

Concurrency improvements

If a MyISAM table has no deleted rows, you can insert rows into the table while select queries are running against it.

Index features

BLOB and TEXT columns in a MyISAM table can be indexed. MyISAM tables have a limit of 500 bytes on each key, however, so the index uses only the first few hundred bytes of a BLOB or TEXT field. MyISAM tables also allow you to index columns that may contain NULL values. You can find more information on MyISAM indexes in Chapter 4.

Delayed key writes

MyISAM tables marked with the DELAY_KEY_WRITE create option don’t have index changes written to disk as they are made. Instead, the changes are made to the in-memory key buffer only and flushed to disk when the associated blocks are pruned from the key buffer or when the table is closed. This can yield quite a performance boost on heavily used tables that change frequently.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Compressed MyISAM Tables}

For circumstances in which the data never changes, such as CD-ROM-or DVD-ROM-based applications, or in some embedded environments, MyISAM tables can be compressed (or packed) using the myisampack utility. Compressed tables can’t be modified, but they generally take far less space and are faster as a result. Having smaller tables means fewer disk seeks are required to find records.

On relatively modern hardware, the overhead involved in decompressing the data is insignificant for most applications. The individual rows are compressed, so MySQL doesn’t need to unpack an entire table (or even a page) just to fetch a single row.

RAID MyISAM Tables

While they’re not really a separate table type, MyISAM RAID tables do serve a particular niche. To use them, you need to compile your own copy of MySQL from source or use the MySQL-Max package. RAID tables are just like MyISAM tables except that the data file is split into several data files. Despite the reference to RAID in the name, these data files don’t have to be stored on separate disks, although it is easy to do so. Writes to the table are striped across the data files, much like RAID-0 would do across physical disks. This can be helpful in two circumstances. If you have an operating system that limits file sizes to 2 or 4 GB but you need larger tables, using RAID will get you past the limit. If you’re have an I/O bound table that is read from and written to very frequently, you might achieve better performance by storing each of the RAID files on a separate physical disk.

To create a RAID table, you must supply some additional options at table-creation time:

CREATE TABLE mytable (

a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

) RAID_TYPE = STRIPED RAID_CHUNKS = 4 RAID_CHUNKSIZE = 16;

The RAID_TYPE option, while required, must be STRIPED or RAID0, which are synonymous. No other RAID algorithms are available. The RAID_CHUNKS parameter tells MySQL how many data files to break the table into. The RAID_CHUNKSIZE option specifies how many kilobytes of data MySQL will write in each file before moving to the next.

In the previous example, MySQL would create four subdirectories named 00, 01, 02, and 03 in which it would store a file named mytable.MYD. When writing data to the table, it would write 16 KB of data to one file and then move to the next one. Once created, RAID tables are transparent. You can use them just as you would normal MyISAM tables.

With the availability of inexpensive RAID controllers and the software RAID features of some operating systems, there isn’t much need for using RAID tables in MySQL. Also, it’s important to realize that RAID tables split only the data file, not the indexes. If you’re trying to overcome file size limits, keep an eye on the size of your index files.

MyISAM Merge Tables

Merge tables are the final variation of MyISAM tables that MySQL provides. Where a RAID table is a single table split into smaller pieces, a Merge table is the combination of several similar tables into one virtual table.

This is particularly useful when MySQL is used in logging applications. Imagine you store web server logs in MySQL. For ease of management, you might create a table for each month. However, when it comes time to generate annual statistics, it would be easier if all the records were in a single table. Using Merge tables, that’s possible. You can create 12 normal MyISAM tables, log_2004_01, log_2004_02, … log_2004_12, and then a Merge table named log_2004.

Queries for a particular month can be run against the specific table that holds the data. But queries that may need to cross month boundaries can be run against the Merge table log_2004 as if it was a table that contained all the data in the underlying twelve tables.

The requirements for a Merge table are that the underlying tables must:

  • Have exactly the same definition

  • Be MyISAM tables

  • Exist in the same database (this limitation is removed in MySQL Versions 4.1.1 and higher, however)

Interestingly, it’s possible for some underlying tables to be compressed MyISAM tables. That means you can compress tables as they get old (since they’re no longer being written to anyway), but still use them as part of a Merge table. Just make sure to remove the table from the Merge table before compressing it, then re-add it after it has been compressed.

Using the example table from earlier, let’s create several identical tables and a Merge table that aggregates them:

CREATE TABLE mytable0 (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
);

CREATE TABLE mytable1 (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
);

CREATE TABLE mytable2 (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
);

CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) TYPE = MERGE UNION = (mytable0, mytable1, mytable2) INSERT_METHOD = LAST;

The only difference between the Merge table and the underlying tables is that it has a few extra options set at creation time. The type, of course, is MERGE. The UNION option specifies the tables that make up the Merge table. Order is important if you plan to insert into the Merge table rather than the underlying tables. The INSERT_METHOD option, which can be NO, FIRST, or LAST, tells MySQL how to handle inserts to the Merge table. If the method is NO, inserts aren’t allowed. Otherwise, inserts will always go to either the first or last of the underlying tables based on the value of INSERT_METHOD.

The order of the tables is also important for unique-key lookups because as soon as the record is found, MySQL stops looking. Thus, the earlier in the list the table is, the better. In most logging applications where you’ll be doing searches on the Merge table, it might make sense to put the tables in reverse chronological order. The order is also important for making ORDER BY as fast as possible because the required merge-sort will be faster when the rows are nearly in order already. If you don’t specify INSERT_METHOD, the default is NO.

As with other tables, you can use SHOW TABLE STATUS to get information about a Merge table:

mysql> SHOW TABLE STATUS LIKE ‘mytable’ G

******************* 1. row ***************************
Name: mytable
Type: MRG_MyISAM
Row_format: Fixed
Rows: 2 Avg_row_length: 23
Data_length: 46
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

Not all of the data is available. MySQL doesn’t keep track of the creation, update, and check times for merge tables. It also doesn’t store the create options that you might expect. However, you can retrieve that information using SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE mytable G
******************** 1. row **************************
*
Table: mytable
Create Table: CREATE TABLE `mytable` (
`a` int(11) NOT NULL default ’0′,
`b` char(18) NOT NULL default ”,
PRIMARY KEY (`a`)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(mytable0,mytable1,mytable2)
1 row in set (0.00 sec)

This demonstrates that Merge tables really aren’t full-fledged tables. In fact, Merge tables have some important limitations and surprising behavior:

  • REPLACE queries don’t work on them.

  • AUTO_INCREMENT columns aren’t updated on insert. They are updated if you insert directly into one of the underlying tables.

  • DROP TABLE mytable will drop only the virtual table, not the underlying tables. This may or may not be what you’d expect.

 

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=InnoDB Tables}

The InnoDB table handler is the newest addition to the MySQL family. Developed by Heikki Tuuri of Innobase Oy in Helsinki, Finland, InnoDB was designed with transaction processing in mind and modeled largely after Oracle.

Storage

The InnoDB table handler breaks from MySQL tradition and stores all its data in a series of one or more data files that are collectively known as a tablespace.A tablespace is essentially a black box that is completely managed by InnoDB. If a tablespace if composed of several underlying files, you can’t choose or influence which of the underlying files will contain the data for any particular database or table.

InnoDB can also use raw disk partitions in building its tablespace, but that’s not very common. Using disk partitions makes it more difficult to back up InnoDB’s data, and the resulting performance boost is on the order of a few percent on most operating systems.

As of MySQL 4.1, you have the option of slightly more MyISAM-like storage for InnoDB. You can enable multiple tablespace support by adding innodb_file_per_ table to my.cnf; this makes InnoDB create one tablespace file per newly created InnoDB table. The filename will be of the form tablename.ibd. In all other respects, they’re simply dynamically sized InnoDB tablespace files. Each one just happens to contain data for only one specific table.

Locking and concurrency

InnoDB uses MVCC to achieve very high concurrency. InnoDB defaults to the repeatable read isolation level, and as of MySQL Version 4.0.5, it implements all four levels: read uncommitted, read committed, repeatable read, and serializable.

In an InnoDB transaction, You may explicitly obtain either exclusive or shared locks on rows using the MySQL statements: SELECT…FOR UPDATE and SELECT…LOCK IN SHARE MODE.

Special features

Besides its excellent concurrency, InnoDB’s next most popular feature is referential integrity in the form of foreign key constraints. This means that given the following schema:

CREATE TABLE master (
 id INTEGER NOT NULL PRIMARY KEY,
 stuff TEXT NOT NULL
) TYPE = InnoDB;

CREATE TABLE detail (
 master_id INTEGER NOT NULL,
 detail1 VARCHAR(80) NOT NULL,
 detail2 VARCHAR(20) NOT NULL,
 INDEX master_idx (master_id),
 FOREIGN KEY (master_id) REFERENCES master(id)
) TYPE = InnoDB;

InnoDB doesn’t allow you to insert add records to the detail table until there is a corresponding record in the master table. Attempting to do so yields an error:

mysql> INSERT INTO detail VALUES (10, ‘blah’, ‘blah’);
ERROR 1216: Cannot add a child row: a foreign key constraint fails

InnoDB also provides lightning fast record lookups for queries that use a primary key. Its clustered index system (described in more detail in Chapter 4) explains how it works.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

{mospagebreak title=Heap (In-Memory) Tables}

MySQL provides in-memory Heap tables for applications in which you need incredibly fast access to data that either never changes or doesn’t need to persist after a restart. Using a Heap table means that a query can complete without even waiting for disk I/O. This makes sense for lookup or mapping tables, such as area code to city/state name, or for caching the results of periodically aggregated data.

Limitations

While Heap tables are very fast, they often don’t work well as replacements for disk-based tables. Until MySQL Version 4.1, Heap tables used only hash-based indexes rather than B-tree indexes (which MyISAM uses). Hash indexes are suited to only a subset of queries. The section “Heap Tables” in Chapter 4 covers this in more detail.

Berkeley DB (BDB) Tables

MySQL’s first transaction-safe storage engine, BDB is built on top of the Berkeley DB database library, which is now maintained and developed by Sleepycat Software. In fact, the original work to integrate the Berkeley DB technology with MySQL was performed jointly by MySQL AB and Sleepycat Software. Other than transactions, the BDB table handler’s other main feature is that it uses page-level locking to achieve higher concurrency than MyISAM tables.

Though BDB tables have been available in MySQL since Version 3.23, they haven’t proven very popular among users. Many users looking for transactions in MySQL were also looking for row-level locking or MVCC. Further dampening interest in BDB, by the time the BDB code had stabilized, word of InnoDB began to circulate. This prompted many users to hold out for the real thing and use MyISAM tables a bit longer.

If nothing else, the inclusion of BDB tables in MySQL served as a stepping stone in many ways. It prompted the MySQL developers to put the transaction-handling infrastructure into MySQL, while at the same time proving to the skeptics that MySQL wasn’t a toy.

Buy the book! If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

Visit the O’Reilly Network http://www.oreillynet.com for more online content.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan