|< Day Day Up >|
Prior to exploring how you can best configure MyISAM's data storage features, you can look at several key administrative reports and utilities at your disposal.
Table Reporting and Control
Recall that to help get a better understanding of the information MySQL tracks about MyISAM tables, the first part of Chapter 7, "Indexing Strategies," examined the SHOW INDEX output for a sample table, filled with 1.25 million rows, and then updated MySQL's internal statistics via OPTIMIZE TABLE. Of course, this command does much more than simply analyze and update statistics, including repairing any damage to the table as well as sorting index pages. To simply refresh statistics, you can use ANALYZE TABLE instead. In either case, here is the structure for this table:
CREATE TABLE demo_show_index ( col1 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, col2 VARCHAR(30) NOT NULL, col3 DATE NOT NULL, col4 ENUM ('Mercury', 'Venus', 'Mars', 'Earth', 'Jupiter') NOT NULL, col5 TEXT NOT NULL, col6 DATETIME NOT NULL, col7 BLOB NOT NULL, INDEX (col2), INDEX (col3), INDEX (col4), FULLTEXT (col5), INDEX (col6), INDEX (col7(150)), INDEX (col3,col2) ) ENGINE = MYISAM;
The following is a portion of the output from the SHOW INDEX command for this table:
mysql> SHOW INDEX FROM demo_show_index\ G *************************** 1. row *************************** Table: demo_show_index Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: col1 Collation: A Cardinality: 1250000 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:
Although this is helpful, you can get a much more informative report by running the myisamchk utility. Remember that myisamchk is designed to work only when the mysqld process is not running; otherwise you may corrupt your tables:
myisamchk -i demo_show_index Checking MyISAM file: demo_show_index Data records: 1250000 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 54% Packed: 84% Max levels: 4 - check data record references index: 3 Key: 3: Keyblocks used: 81% Packed: 0% Max levels: 4 - check data record references index: 4 Key: 4: Keyblocks used: 87% Packed: 0% Max levels: 3 - check data record references index: 5 Key: 5: Keyblocks used: 83% Packed: 0% Max levels: 4 - check data record references index: 6 Key: 6: Keyblocks used: 62% Packed: 6% Max levels: 9 - check data record references index: 7 Key: 7: Keyblocks used: 68% Packed: 70% Max levels: 4 - check data record references index: 8 Key: 8: Keyblocks used: 50% Packed: 96% Max levels: 4 Total: Keyblocks used: 56% Packed: 93% - check record links Records: 1250000 M.recordlength: 885 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1250000 Delete blocks: 0 Record data: 1106732346 Deleted data: 0 Lost space: 937638 Linkdata: 4687784 myisamchk -verbose -description demo_show_index MyISAM file: demo_show_index Record format: Packed Character set: latin1_general_ci (8) File-version: 1 Creation time: 2004-11-26 19:28:59 Recover time: 2005-05-27 5:40:42 Status: checked,analyzed,sorted index pages Auto increment key: 1 Last value: 1250000 Data records: 1250000 Deleted blocks: 0 Datafile parts: 1250000 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 1112357768 Keyfile length: 950180864 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 67 table description: Key Start Len Index Type Rec/key Root Blocksize 1 1 4 unique unsigned long 1 1024 1024 2 5 30 multip. char packed stripped 625 10260480 1024 3 35 3 multip. uint24 579 22009856 1024 4 38 1 multip. binary 250000 32770048 1024 5 49 8 multip. ulonglong 1 39958528 1024 6 57 150 multip. varbin prefix BLOB 1 57952256 1024 7 35 3 multip. uint24 prefix 579 344858624 1024 5 30 char stripped 1 8 5 254 fulltext varchar packed 8621 364686336 2048 1 4 float 0
As you can see, these reports provide more extensive details about the table and its indexes than the SHOW INDEX command reviewed earlier. In fact, you'll get even more information if you pass in added "v" characters along with the dv directive (such as dvv). Additional items of interest from a performance point of view include the following:
Storing a Table in Index Order
If you frequently retrieve large ranges of indexed data from a table or consistently sort results on the same index key, you might want to consider running myisamchk with the -sort-records option. Doing so tells MySQL to store the table's data in the same physical order as the index, and can help speed these kinds of operations. Alternatively, you can combine the ALTER TABLE statement with an ORDER BY a particular column option to achieve the same results.
For example, imagine that you have a table containing millions of rows of frequent flyer detail information:
CREATE TABLE customer_mileage_details ( customer_id INT NOT NULL, ff_number CHAR(10) NOT NULL, transaction_date DATE NOT NULL, mileage SMALLINT NOT NULL, INDEX (customer_id), INDEX (ff_number, transaction_date) ) ENGINE = MYISAM;
As you analyze usage patterns for the table, you realize that users typically employ the second index when running queries that locate and retrieve large blocks of consecutive rows. To speed access, you choose to have the table sorted by the second index:
myisamchk v R 2
From this point forward, all rows in the table will be stored in ff_number, transaction_date order until new rows are added. If you want to make this sequencing more permanent, consider running this command periodically.
This is just a fraction of the information that myisamchk provides. Although a full exploration of this vital utility is beyond the scope of this book, it's a good investment for any MySQL administrator to spend the time to learn more about myisamchk.
Table Maintenance and Repair
Table and index maintenance and repair are vital functions. The following sections look at a few examples of how to improve the speed of these necessary operations.
MySQL features a number of internal compression algorithms that can shrink the amount of space required by certain fields within indexes. However, administrators can also elect to compress an entire table by using the myisampack utility. Note that this option is only available for read-only tables, so a heavily updated transactional table is not a good candidate for compression.
Compression provides two main benefits. First, if your query uses either a primary key or unique index to locate a row, MySQL only decompresses the specific row in question. This happens very quickly and efficiently. Secondly, a compressed table consumes less disk space, freeing up resources for other uses.
If you still want to realize the benefits of compression from a frequently modified table, one idea is to occasionally create an empty MyISAM table, insert relevant records from your transactional table, and then compress the newly filled table. You can also elect to decompress a MyISAM table, fill it with additional data, and then rerun myisampack to recompress the table. However, these steps may be too time consuming if the table is very large or very frequently updated.
Parallel Index Creation
Although currently considered an alpha-quality feature, you may elect to have myisamchk build indexes in parallel. To do so, pass in the p or --parallel-recover parameters. Another option would be to boost the value of the myisam_repair_threads server setting, which will affect the behavior of the REPAIR TABLE statement, forcing it to attempt index rebuilding in parallel.
In terms of choosing a value, don't raise this value to more than the number of CPUs found on your database server.
Over time, tables that feature variable length rows (such as those containing TEXT, VARCHAR, or BLOB columns) can become fragmented. This happens because these variable-length rows often span noncontiguous pages if there isn't available space on consecutive pages. Locating and retrieving this dispersed information taxes the database server. Fortunately, administrators have at least options to defragment these types of tables:
Controlling Sorting Resources
MyISAM lets administrators dictate the amount of resources available for a number of important database operations, including sorting. The following list describes two ways to affect this behavior:
|< Day Day Up >|