MySQL Table Types Ubuntuland

01/08/2008 GMT 1

MySQL Table Types

ubuntuland @ 21:03

As of MySQL Version 3.23.6, you can choose between three basic table formats (ISAM, HEAP and MyISAM. Newer MySQL may support additional table type (InnoDB, or BDB), depending on how you compile it.
When you create a new table, you can tell MySQL which table type it should use for the table.

MySQL will always create a .frm file to hold the table and column definitions.
Depending on the table type, the index and data will be stored in other files.
Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option.

The default table type in MySQL is MyISAM. If you are trying to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This is a very useful feature when you want to copy tables between di erent SQL servers that supports di erent table types (like copying tables to a slave that is optimised for speed by not having transactional tables). This automatic table changing can however also be very confusing for new MySQL users. We plan to fix this by introducing warnings in MySQL 4.0 and giving a warning when a table type is automatically changed.
Note that MySQL supports two di erent kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).

Advantages of transaction-safe tables (TST):
• Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
• You can combine many statements and accept these all in one go with the COMMIT command.
• You can execute ROLLBACK to ignore your changes (if you are not running in auto commit mode).
• If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
Advantages of not transaction-safe tables (NTST):
• Much faster as there is no transaction overhead.
• Will use less disk space as there is no overhead of transactions.
• Will use less memory to do updates.
You can combine TST and NTST tables in the same statements to get the best of both worlds.

MyISAM Tables
MyISAM is the default table type in MySQL Version 3.23. It’s based on the ISAM code and has a lot of useful extensions.
The index is stored in a file with the .MYI (MYIndex) extension, and the data is stored in a file with the .MYD (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility.
The following is new in MyISAM:
• There is a ag in the MyISAM file that indicates whether or not the table was closed correctly. If mysqld is started with --myisam-recover, MyISAM tables will automatically be checked and/or repaired on open if the table wasn’t closed properly.
• You can INSERT new rows in a table that doesn’t have free blocks in the middle of the data file, at the same time other threads are reading from the table (concurrent insert).
An free block can come from an update of a dynamic length row with much data to a row with less data or when deleting rows. When all free blocks are used up, all future inserts will be concurrent again.
• Support for big files (63-bit) on filesystems/operating systems that support big files.
• All data is stored with the low byte first. This makes the data machine and OS independent. The only requirement is that the machine uses two’s-complement signed integers (as every machine for the last 20 years has) and IEEE oating-point format (also totally dominant among mainstream machines). The only area of machines that
may not support binary compatibility are embedded systems (because they sometimes have peculiar processors).
There is no big speed penalty in storing data low byte first; The bytes in a table row is normally unaligned and it doesn’t take that much more power to read an unaligned byte in order than in reverse order. The actual fetch-column-value code is also not time critical compared to other code.

• All number keys are stored with high byte first to give better index compression.
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk.
This will make AUTO_INCREMENT columns faster (at least 10 %) and old numbers will not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is defined on the end of a multi-part-key the old behavior is still present.
• When inserted in sorted order (as when you are using an AUTO_INCREMENT column) the key tree will be split so that the high node only contains one key. This will improve the space utilisation in the key tree.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
• Maximum key length is 500 bytes by default (can be changed by recompiling). In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table is 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• myisamchk will mark tables as checked if one runs it with --update-state. myisamchk --fast will only check those tables that don’t have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• myisampack can pack BLOB and VARCHAR columns.
• You can use put the datafile and index file on di erent directories to get more speed (with the DATA/INDEX DIRECTORY="path" option to CREATE TABLE).
MyISAM also supports the following things, which MySQL will be able to use in the near future:
• Support for a true VARCHAR type; A VARCHAR column starts with a length stored in 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have di erent language definitions per column.
• A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can’t search on a UNIQUE computed index, however.)

Note that index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU when inserting data into a compressed index.
The following options to mysqld can be used to change the behavior of MyISAM tables. .
Meaning
--myisam-recover=# Automatic recover of crashed tables.
-O myisam_sort_buffer_size=# Bu er used when recovering tables.
--delay-key-write-for-all- tables
Don’t ush key bu ers between writes for any My- ISAM table
-O myisam_max_extra_sort_file_
size=#
Used to help MySQL to decide when to use the slow but safe key cache index create method. Note that
this parameter is given in megabytes!
-O myisam_max_sort_file_size=# Don’t use the fast sort index method to created index if the temporary file would get bigger than this. Note that this paramter is given in megabytes!
-O myisam_bulk_insert_tree_
size=#
Size of tree cache used in bulk insert optimisation.
Note that this is a limit per thread!
The automatic recovery is activated if you start mysqld with --myisam-recover=#.
On open, the table is checked if it’s marked as crashed or if the open count variable for the table is not 0 and you are running with --skip-locking. If either of the above is true the following happens.

• The table is checked for errors.
• If we found an error, try to do a fast repair (with sorting and without re-creating the data file) of the table.
• If the repair fails because of an error in the data file (for example a duplicate key error), we try again, but this time we re-create the data file.
• If the repair fails, retry once more with the old repair option method (write row by row without sorting) which should be able to repair any type of error with little disk requirements..
If the recover wouldn’t be able to recover all rows from a previous completed statement and you didn’t specify FORCE as an option to myisam-recover, then the automatic repair will abort with an error message in the error file:
Error: Couldn’t repair table: test.g00pages
If you in this case had used the FORCE option you would instead have got a warning in the error file:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if you run automatic recover with the BACKUP option, you should have a cron script that automatically moves file with names like ‘tablename-datetime.BAK’ from the database directories to a backup media.

Space Needed for Keys
MySQL can support di erent index types, but the normal type is ISAM or MyISAM.
These use a B-tree index, and you can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order and we don’t have any compressed keys.)
String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the above figures if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.
In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.

MyISAM Table Formats
MyISAM supports 3 di erent table types. Two of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.
When you CREATE or ALTER a table you can for tables that doesn’t have BLOB’s force the table format to DYNAMIC or FIXED with the ROW_FORMAT=# table option. In the future you will be able to compress/decompress tables by specifying ROW_FORMAT=compressed | default to ALTER TABLE.

Static (Fixed-length) Table Characteristics
This is the default format. It’s used when the table contains no VARCHAR, BLOB, or TEXT columns.
This format is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with an index and static format it is very simple. Just multiply the row number by the row length.
Also, when scanning a table it is very easy to read a constant number of records with each disk read.
The security is evidenced if your computer crashes when writing to a fixed-size MyISAM file, in which case myisamchk can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in MySQL all indexes can always be reconstructed:
• All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because records are located in fixed positions.
• Doesn’t have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
• Usually requires more disk space than dynamic tables.

Dynamic Table Characteristics
This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic.
This format is a little more complex because each row has to have a header that says how long it is. One record can also end up at more than one location when it is made longer at an update.
You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation:
• All string columns are dynamic (except those with a length less than 4).
• Each record is preceded by a bitmap indicating which columns are empty (’’) for string columns, or zero for numeric columns. (This isn’t the same as columns containing NULL values.) If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk.
Non-empty strings are saved as a length byte plus the string contents.
• Usually takes much less disk space than fixed-length tables.
• Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as are required. This results in record fragmentation.
• If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run myisamchk -r from time to time to get better performance. Use myisamchk -ei tbl_name for some statistics.
• Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
• The expected row length for dynamic sized records is:

+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.

Compressed Table Characteristics
This is a read-only type that is generated with the optional myisampack tool (pack_isam for ISAM tables):
• All MySQL distributions, even those that existed before MySQL went GPL, can read tables that were compressed with myisampack.
• Compressed tables take very little disk space. This minimises disk usage, which is very nice when using slow disks (like CD-ROMs).
• Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed di erently. Some of the compression types are:
- There is usually a di erent Hu man table for each column.
- Su x space compression.
- Prefix space compression.
- Numbers with value 0 are stored using 1 bit.
- If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
- If a column has only a small set of possible values, the column type is converted to ENUM.
- A column may use a combination of the above compressions.
• Can handle fixed- or dynamic-length records, but not BLOB or TEXT columns.
• Can be uncompressed with myisamchk.

MyISAM table problems.
The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.

Corrupted MyISAM tables.
Even if the MyISAM table format is very reliable (all changes to a table is written before the SQL statements returns) , you can still get corrupted tables if some of the following things happens:
• The mysqld process being killed in the middle of a write.
• Unexpected shutdown of the computer (for example, if the computer is turned o ).
• A hardware error.
• You are using an external program (like myisamchk) on a live table.
• A software bug in the MySQL or MyISAM code.
Typial typical symptoms for a corrupt table is:
• You get the error Incorrect key file for table: ’...’. Try to repair it while selecting data from the table.
• Queries doesn’t find rows in the table or returns incomplete data.
You can check if a table is ok with the command CHECK TABLE.
You can repair a corrupted table with REPAIR TABLE.
If your tables get corrupted a lot you should try to find the reason for this!
In this case the most important thing to know is if the table got corrupted if the mysqld died (one can easily verify this by checking if there is a recent row restarted mysqld in the mysqld error file). If this isn’t the case, then you should try to make a test case of this.

Clients is using or hasn’t closed the table properly
Each MyISAM .MYI file has in the header a counter that can be used to check if a table has been closed properly.
If you get the following warning from CHECK TABLE or myisamchk:
# clients is using or hasn’t closed the table properly
this means that this counter has come out of sync. This doesn’t mean that the table is corrupted, but means that you should at least do a check on the table to verify that it’s okay.
The counter works as follows:

• The first time a table is updated in MySQL, a counter in the header of the index files is incremented.
• The counter is not changed during further updates.
• When the last instance of a table is closed (because of a FLUSH or because there isn’t room in the table cache) the counter is decremented if the table has been updated at any point.
• When you repair the table or check the table and it was okay, the counter is reset to 0.
• To avoid problems with interaction with other processes that may do a check on the table, the counter is not decremented on close if it was 0.
In other words, the only ways this can go out of sync are:
• The MyISAM tables are copied without a LOCK and FLUSH TABLES.
• MySQL has crashed between an update and the final close. (Note that the table may still be okay, as MySQL always issues writes for everything between each statement.)
• Someone has done a myisamchk --repair or myisamchk --update-stateon a table that was in use by mysqld.
• Many mysqld servers are using the table and one has done a REPAIR or CHECK of the table while it was in use by another server. In this setup the CHECK is safe to do (even if you will get the warning from other servers), but REPAIR should be avoided as it currently replaces the data file with a new one, which is not signaled to the other
servers.

MERGE Tables
MERGE tables are new in MySQL Version 3.23.25. The code is still in gamma, but should be resonable stable.
A MERGE table (also known as a MRG_MyISAM table) is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.
Note that DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables. (We plan to fix this in 4.1).
With identical tables we mean that all tables are created with identical column and key information. You can’t put a MERGE over tables where the columns are packed di erently, doesn’t have exactly the same columns or have the keys in di erent order. Some of the tables can however be compressed with myisampack.
When you create a MERGE table, you will get a .frm table definition file and a .MRG table list file. The .MRG just contains a list of the index files (.MYI files) that should be used as one. All used tables must be in the same database as the MERGE table itself.

For the moment you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table.
MERGE tables can help you solve the following problems:
• Easily manage a set of log tables. For example, you can put data from di erent months into separate files, compress some of them with myisampack, and then create a MERGE
to use these as one.

• Give you more speed. You can split a big read-only table based on some criteria and then put the di erent table part on di erent disks. A MERGE table on this could be much faster than using the big table. (You can, of course, also use a RAID to get the same kind of benefits.)
• Do more e cient searches. If you know exactly what you are looking after, you can search in just one of the split tables for some queries and use MERGE table for others.
You can even have many di erent MERGE tables active, with possible overlapping files.
• More e cient repairs. It’s easier to repair the individual files that are mapped to a MERGE file than trying to repair a real big file.
• Instant mapping of many files as one. A MERGE table uses the index of the individual tables. It doesn’t need to maintain an index of its one. This makes MERGE table collections VERY fast to make or remap. Note that you must specify the key definitions when you create a MERGE table!.
• If you have a set of tables that you join to a big table on demand or batch, you should instead create a MERGE table on them on demand. This is much faster and will save a lot of disk space.
• Go around the file size limit for the operating system.
• You can create an alias/synonym for a table by just using MERGE over one table.
There shouldn’t be any really notable performance impacts of doing this (only a couple of indirect calls and memcpy’s for each read).

The disadvantages with MERGE tables are:
• You can only use identical MyISAM tables for a MERGE table.
• AUTO_INCREMENT columns are not automatically updated on INSERT.
• REPLACE doesn’t work.
• MERGE tables uses more file descriptors. If you are using a MERGE that maps over 10 tables and 10 users are using this, you are using 10*10 + 10 file descriptors. (10 data files for 10 users and 10 shared index files.)
• Key reads are slower. When you do a read on a key, the MERGE handler will need to issue a read on all underlying tables to check which one most closely matches the given key. If you then do a ’read-next’ then the merge table handler will need to search the read bu ers to find the next key. Only when one key bu er is used up, the handler will need to read the next key block. This makes MERGE keys much slower on eq_ref searches, but not much slower on ref searches.
• You can’t do DROP TABLE, ALTER TABLE or DELETE FROM table_name without a WHERE clause on any of the table that is mapped by a MERGE table that is ’open’. If you do this, the MERGE table may still refer to the original table and you will get unexpected results.

When you create a MERGE table you have to specify with UNION(list-of-tables) which tables you want to use as one. Optionally you can specify with INSERT_METHOD if you want insert for the MERGE table to happen in the first or last table in the UNION list. If you don’t specify INSERT_METHOD or specify NO, then all INSERT commands on the MERGE table will return an error.
The following example shows you how to use MERGE tables:
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Note that we didn’t create a UNIQUE or PRIMARY KEY in the total table as the key isn’t going to be unique in the total table.

Note that you can also manipulate the .MRG file directly from the outside of the MySQL server:
shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables
Now you can do things like:
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+

To remap a MERGE table you can do one of the following:
• DROP the table and re-create it
• Use ALTER TABLE table_name UNION(...)
• Change the .MRG file and issue a FLUSH TABLE on the MERGE table and all underlying tables to force the handler to read the new definition file.

You can also use the deprecated ISAM table type. This will disappear rather soon because
MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index
is stored in a file with the .ISM extension, and the data is stored in a file with the .ISD
extension. You can check/repair ISAM tables with the isamchk utility.
ISAM has the following features/properties:
• Compressed and fixed-length keys
• Fixed and dynamic record length
• 16 keys with 16 key parts/key
• Max key length 256 (default)
• Data is stored in machine format; this is fast, but is machine/OS dependent.

Most of the things true for MyISAM tables are also true for ISAM tables. The major di erences compared to MyISAM tables are:
• ISAM tables are not binary portable across OS/Platforms.
• Can’t handle tables > 4G.
• Only support prefix compression on strings.
• Smaller key limits.
• Dynamic tables get more fragmented.
• Tables are compressed with pack_isam rather than with myisampack.
If you want to convert an ISAM table to a MyISAM table so that you can use utilities such as
mysqlcheck, use an ALTER TABLE statement:
mysql> ALTER TABLE tbl_name TYPE = MYISAM

Latest Posts

Linux Links

digg it |

del.icio.us

Comments

Ubuntuland

01/08/2008 GMT 1

MySQL Table Types

No Comments »

Post a Comment