Home arrow MySQL arrow Page 13 - Storage Engine (Table Types)

Compressed MyISAM Tables - MySQL

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.)

TABLE OF CONTENTS:
  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: