[MySQL]: Storage Engines

6.1  Comparing Transaction and Nontransaction Engines 

• Transaction-safe tables (TSTs) have several advantages over nontransaction-safe tables (NTSTs):
• They are safer. Even if MySQL crashes or you get hardware problems, the database will be in a consistency form.
• You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
• You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
• If an update fails, all of your changes are reverted. (With nontransaction-safe tables, all changes that have taken place are permanent.)
• Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.
• You can combine transaction-safe and nontransaction-safe tables in the same statements to get the best result. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to nontransaction-safe tables still are committed immediately and cannot be rolled back.

Nontransaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:

• Much faster
• Lower disk space requirements
• Less memory required to perform updates

6.2  Setting the Storage Engine 

When you create a new table, you can specify which storage engine to use by adding an ENGINE table option to the CREATE TABLE statement:

mysql> CREATE TABLE t (i INT) ENGINE = INNODB;

If you omit the ENGINE option, the default       engine is used. Normally, this is MyISAM, but you can change it by using the –default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.

You can set the default storage engine to be used during the current session by setting the storage_engine variable:

mysql> SET storage_engine=MYISAM;

           To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

mysql> ALTER TABLE t ENGINE = MYISAM;

          If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table using the default storage engine. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)

          A warning is generated whenever a storage engine is automatically changed. To prevent this from happening if the desired engine is unavailable, enable the NO_ENGINE_SUBSTITUTION SQL mode. In this case, an error occurs instead of a warning and the table is not created or altered if the desired engine is unavailable.

A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

6.3.  The MyISAM Storage Engine 

          MyISAM is the default storage engine still 5.1.X version, from 5.5.X InnoDB is the default storage engine. It is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE table option:

mysql> CREATE TABLE t (i INT) ENGINE = MYISAM;

 You can check or repair MyISAM tables with the mysqlcheck client or myisamchk utility. You can also compress MyISAM tables with myisampack to take up much less space.

MyISAM tables have the following characteristics:

• There is a limit of 232 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the –with-big-tables option, the row limitation is increased to (232)2 (1.844E+19) rows.
• The maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. Beginning with MySQL 5.1.4, you can configure the build by invoking configure with the –with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less than or equal to 128.

                The maximum number of columns per index is 16.

• The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling.
• There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the –myisam-recover option, MyISAM tables are automatically checked when opened, and are repaired if the table wasn’t closed properly.
• Tables can be repaired.
• You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE.

 

6.3.1.  MyISAM Startup Options

 

The following options to mysqld can be used to change the behavior of MyISAM tables.

 

bulk_insert_buffer_size

 

          MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

 

          Allocated per each session.

 

concurrent_insert

 

          If 1 (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with –skip-new, this variable is set to 0.

This variable can take three integer values.

Value	Description
0	Disables concurrent inserts
1	(Default) Enables concurrent insert for MyISAM tables that do not have holes
2	Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

 

delay_key_write

 

This option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.

Option	Description
OFF	DELAY_KEY_WRITE is ignored.
ON	MySQL honors any DELAY_KEY_WRITE option specified in CREATE TABLE statements. This is the default value.
ALL	All new opened tables are treated as if they were created with the DELAY_KEY_WRITE option enabled.

If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the –myisam-recover

key_buffer_size

 

          Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

          The maximum permissible setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.1.23, values larger than 4GB are permitted for 64-bit platforms, except 64-bit Windows prior to MySQL 5.1.31, for which large values are truncated to 4GB with a warning. As of MySQL 5.1.31, values larger than 4GB are also permitted for 64-bit Windows. The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform

 

          25% of the machine’s total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine’s total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

 

It is possible to create multiple MyISAM key caches. The size limit of 4GB applies to each cache individually, not as a group.

 

key_cache_block_size

 

          The size in bytes of blocks in the key cache. The default value is 1024.

          (Range 512-16384)

 

myisam_max_sort_file_size

 

          The maximum size of the temporary file that MySQL is permitted to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

The default value is 2GB (Max valve depends on ur disk size). If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. The space must be available in the file system containing the directory where the original index file is located.

–myisam-recover

 

          Set the MyISAM storage engine recovery mode. The option value is any combination of the values of DEFAULT, BACKUP, FORCE, or QUICK. If you specify multiple values, separate them by commas. Specifying the option with no argument is the same as specifying DEFAULT, and specifying with an explicit value of “” disables recovery (same as not giving the option). If recovery is enabled, each time mysqld opens a MyISAM table, it checks whether the table is marked as crashed or was not closed properly. (The last option works only if you are running with external locking disabled.) If this is the case, mysqld runs a check on the table. If the table was corrupted, mysqld attempts to repair it.

The following options affect how the repair works.

Option	Description
DEFAULT	Recovery without backup, forcing, or quick checking.
BACKUP	If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
FORCE	Run recovery even if we would lose more than one row from the .MYD file.
QUICK	Do not check the rows in the table if there are not any delete blocks.

 

          Before the server automatically repairs a table, it writes a note about the repair to the error log. If you want to be able to recover from most problems without user intervention, you should use the options BACKUP,FORCE. This forces a repair of a table even if some rows would be deleted, but it keeps the old data file as a backup so that you can later examine what happened.

          If the recovery wouldn’t be able to recover all rows from previously completed statements and you didn’t specify FORCE in the value of the –myisam-recover option, automatic repair aborts with an error message in the error log:

Error: Couldn’t repair table: db.table

 

If you specify FORCE, a warning like this is written instead:

Warning: Found 344 of 354 rows when repairing ./db/table

 

myisam_repair_threads

If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1.

Note: Multi-threaded repair is still beta-quality code.

tmp_table_size

 

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

Space Needed for Keys:

 

MyISAM tables use B-tree indexes. 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 the table doesn’t have any compressed keys.

 

6.3.2.  MyISAM Table Storage Formats:

 

          MyISAM supports three different storage formats. Two of them, fixed and dynamic format, are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the myisampack utility

 

When you use CREATE TABLE or ALTER TABLE for a table that has no BLOB or TEXT columns, you can force the table format to FIXED or DYNAMIC with the ROW_FORMAT table option.

 

You can decompress (unpack) compressed MyISAM tables using myisamchk –unpack

 

6.3.2.1.  Static (Fixed-Length) Table Characteristics:

 

          Static format is the default for MyISAM tables. It is used when the table contains no variable-length columns (VARCHAR, VARBINARY, BLOB, or TEXT). Each row is stored using a fixed number of bytes.

Of the three MyISAM storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats due to the ease with which rows in the data file can be found on disk: To look up a row based on a row number in the index, multiply the row number by the row length to calculate the row position. Also, when scanning a table, it is very easy to read a constant number of rows with each disk read operation.

 

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format MyISAM file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all rows except the partially written one. Note that MyISAM table indexes can always be reconstructed based on the data rows.

 

Note: Fixed-length row format is only available for tables without BLOB or TEXT columns. Creating a table with these columns with an explicit ROW_FORMAT clause will not raise an error or warning; the format specification will be ignored.

 

Static-format tables have these characteristics:

• CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because rows are located in fixed positions.
• Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
• Usually require more disk space than dynamic-format tables.

 

6.3.2.2.  Dynamic Table Characteristics:

 

          Dynamic storage format is used if a MyISAM table contains any variable-length columns (VARCHAR, VARBINARY, BLOB, or TEXT), or if the table was created with the ROW_FORMAT=DYNAMIC table option.

 

     Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in noncontiguous pieces) when it is made longer as a result of an update.

 

          You can use OPTIMIZE TABLE or myisamchk -r to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.

 

Dynamic-format tables have these characteristics:

 

          –     All string columns are dynamic except those with a length less than four.

• Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). Note that this does not include columns that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Nonempty strings are saved as a length byte plus the string contents.
• Much less disk space usually is required than for fixed-length tables.
• Each row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run OPTIMIZE TABLE or myisamchk -r from time to time to improve performance. Use myisamchk -ei to obtain table statistics.
• More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.
• The expected row length for dynamic-sized rows is calculated using the following expression:

 

                   3+ (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 row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk -ed. All links may be removed with OPTIMIZE TABLE or myisamchk -r.

 

6.3.3.  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. The following discussion describes how this can happen and how to handle it.

• The mysqld process is killed in the middle of a write.
• An unexpected computer shutdown occurs (for example, the computer is turned off).
• Hardware failures.
• You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time.
• A software bug in the MySQL or MyISAM code.

 

Typical symptoms of a corrupt table are:

 

          You get the following error while selecting data from the table:

 

          Incorrect key file for table: ‘…’. Try to repair it

 

          Queries don’t find rows in the table or return incomplete results.

 

          You can check the health of a MyISAM table using the CHECK TABLE statement, and repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command.

 

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation. This is a bug. You should try to create a reproducible test case that demonstrates the problem.

 

Problems from Tables Not Being Closed Properly:

 

Each MyISAM index file (.MYI file) has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from CHECK TABLE or myisamchk, it means that this counter has gone out of sync:

 

          clients are using or haven’t closed the table properly

 

This warning doesn’t necessarily mean that the table is corrupted, but you should at least check the table.

 

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 a FLUSH TABLES operation was performed or because there is no 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 is found to be okay, the counter is reset to zero.
• To avoid problems with interaction with other processes that might check the table, the counter is not decremented on close if it was zero.

 

          In other words, the counter can become incorrect only under these conditions:

• A MyISAM table is copied without first issuing LOCK TABLES and FLUSH TABLES.
• MySQL has crashed between an update and the final close. (Note that the table may still be okay, because MySQL always issues writes for everything between each statement.)
• A table was modified by myisamchk –recover or myisamchk –update-state at the same time that it was in use by mysqld.
• Multiple mysqld servers are using the table and one server performed a REPAIR TABLE or CHECK TABLE on the table while it was in use by another server. In this setup, it is safe to use CHECK TABLE, although you might get the warning from other servers. However, REPAIR TABLE should be avoided because when one server replaces the data file with a new one, this is not known to the other servers.

 

          In general, it is a bad idea to share a data directory among multiple servers.

 

6.4.  The InnoDB Storage Engine

 

          InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement.

 

          To determine whether your server supports InnoDB use the SHOW ENGINES statement.

 

          The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be very large even on operating systems where file size is limited to 2GB.

 

6.4.1.  Configuring InnoDB

 

          The first decisions to make about InnoDB configuration involve how to lay out InnoDB data files, and how much memory to allocate for the InnoDB storage engine. You record these choices either by recording them in a configuration file that MySQL reads at startup, or by specifying them as command-line options in a startup script.

 

          Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. To get good performance, explicitly provide InnoDB parameters.

 

Considerations for Storage Devices:

 

          Database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. The example illustrates how to do this. It places the two data files on different disks and places the log files on the third disk. InnoDB fills the tablespace beginning with the first data file. You can also use raw disk partitions (raw devices) as InnoDB data files, which may speed up I/O.

 

6.4.2.  InnoDB Table and Index Structures

 

MySQL stores its data dictionary information for tables in .frm files in database directories. This is true for all MySQL storage engines, but every InnoDB table also has its own entry in the InnoDB internal data dictionary inside the tablespace. When MySQL drops a table or a database, it has to delete one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. Consequently, you cannot move InnoDB tables between databases simply by moving the .frm files.

 

6.4.3.  Adding, Removing, or Resizing InnoDB Data and Log Files

 

          To set up the InnoDB tablespace files, use the innodb_data_file_path option in the [mysqld] section of the my.cnf

option file. On Windows, you can use my.ini instead. The value of innodb_data_file_path should be a list of one or more data file specifications. If you name more than one data file, separate them by semicolon (“;”) characters:

 

          innodb_data_file_path=datafile_spec1[;datafile_spec2]…

 

          For example, the following setting explicitly creates a tablespace having the same characteristics as the default:

 

          [mysqld]

          innodb_data_file_path=ibdata1:10M:autoextend

 

          This setting configures a single 10MB data file named ibdata1 that is auto-extending. No location for the file is given, so by default, InnoDB creates it in the MySQL data directory.  Sizes are specified using K, M, or G suffix letters to indicate units of KB, MB, or GB.

 

          A tablespace containing a fixed-size 50MB data file named ibdata1 and a 50MB auto-extending file named ibdata2 in the data directory can be configured like this:

 

          [mysqld]

          innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

          The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line. If you specify the autoextend option for the last data file, InnoDB extends the data file if it runs out of free space in the tablespace.

 

The increment is 8 MB at a time by default, To modify the increment, change the innodb_autoextend_increment system variable.

 

          InnoDB is not aware of the file system maximum file size, so be cautious on file systems where the maximum file size is a small value such as 2GB. To specify a maximum size for an auto-extending data file, use the max attribute following the autoextend attribute. Use the max attribute only in cases where constraining disk usage is of critical importance, because exceeding the maximum size causes a fatal error, possibly including a crash. The following configuration permits ibdata1 to grow up to a limit of 500MB.

 

          [mysqld]

          innodb_data_file_path=ibdata1:10M:autoextend:max:500M

 

          InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the innodb_data_home_dir option. For example, to use two files named ibdata1 and ibdata2 but create them in the /ibdata directory, configure InnoDB like this:

 

          [mysqld]

          innodb_data_home_dir = /ibdata

          innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

Note: InnoDB does not create directories, so make sure that the /ibdata directory exists before you start the server. This is also true of any log file directories that you configure. Use the Unix or DOS mkdir command to create any neces-sary directories. Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files or log files.

 

          InnoDB forms the directory path for each data file by textually concatenating the value of innodb_data_home_dir to the data file name, adding a path name separator (slash or backslash) between values if necessary. If the innodb_data_home_dir option is not mentioned in my.cnf at all, the default value is the “dot” directory ./, which means the MySQL data directory. (The MySQL server changes its current working directory to its data directory when it begins executing.)

 

          If you specify innodb_data_home_dir as an empty string, you can specify absolute paths for the data files listed in the innodb_data_file_path value. The following example is equivalent to the preceding one:

 

          [mysqld]

          innodb_data_home_dir =

          innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

          When you add a new file to the tablespace configuration, make sure that it does not exist. InnoDB will create and initialize the file when you restart the server.

 

          Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

 

1. Use mysqldump to dump all your InnoDB tables.
2. Stop the server.
3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
4. Remove any .frm files for InnoDB tables.
5. Configure a new tablespace.
6. Restart the server.
7. Import the dump files.

 

If you want to change the number or the size of your InnoDB log files, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown:

 

If innodb_fast_shutdown is not set to 2: Stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

 

If innodb_fast_shutdown is set to 2: Set innodb_fast_shutdown to 1:

 

          mysql> SET GLOBAL innodb_fast_shutdown = 1;

 

Then follow the instructions in the previous item.

 

Determining the Maximum Memory Allocation for InnoDB:

 

          On 32-bit GNU/Linux x86, be careful not to set memory usage too high. glibc may permit the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB.

 

          innodb_buffer_pool_size

          + key_buffer_size

          + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)

          + max_connections*2MB

 

Turning Off InnoDB:

 

If you do not want to use InnoDB tables, start the server with the –innodb=OFF or –skip-innodb option to disable the InnoDB storage engine. In this case, the server will not start if the default storage engine is set to InnoDB. Use –default-storage-engine to set the default to some other engine if necessary.

 

Using Per-Table Tablespaces:

 

          You can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

 

          To enable multiple tablespaces, start the server with the –innodb_file_per_table option. For example, add a line to the [mysqld] section of my.cnf.

 

          [mysqld]

          innodb_file_per_table

 

          With multiple tablespaces enabled, InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. This is similar to what the MyISAM storage engine does, but MyISAM divides the table into a tbl_name.MYD data file and an tbl_name.MYI index file. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

 

          You cannot freely move .ibd files between database directories as you can with MyISAM table files. This is because the table definition that is stored in the InnoDB shared tablespace includes the database name, and because InnoDB must preserve the consistency of transaction IDs and log sequence numbers.

 

          If you remove the innodb_file_per_table line from my.cnf and restart the server, InnoDB creates tables inside the shared tablespace files again.

 

          The –innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

 

Note: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

 

          To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

 

          mysql> RENAME TABLE db1.tbl_name TO db2.tbl_name;

 

Using Raw Devices for the Shared Tablespace:

 

          You can use raw disk partitions as data files in the shared tablespace. By using a raw disk, you can perform nonbuffered I/O on Windows and on some Unix systems without file system overhead. This may improve performance, but you are advised to perform tests with and without raw partitions to verify whether this is actually so on your system.

 

          When you create a new data file, put the keyword newraw immediately after the data file size in innodb_data_file_path. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

 

          [mysqld]

          innodb_data_home_dir=

          innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

 

          The next time you start the server, InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost. (As a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

 

After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

 

          [mysqld]

          innodb_data_home_dir=

          innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

 

          Then restart the server and InnoDB permits changes to be made.

 

          When you use a raw disk partition, be sure that it has permissions that enable read and write access by the account used for running the MySQL server. For example, if you run the server as the mysql user, the partition must permit read and write access to mysql. If you run the server with the –memlock option, the server must be run as root, so the partition must permit access to root.

 

Dealing with InnoDB Initialization Problems:

 

If InnoDB prints an operating system error during a file operation, usually the problem has one of the following causes:

• You did not create the InnoDB data file directory or the InnoDB log directory.
• mysqld does not have access rights to create files in those directories.
• mysqld cannot read the proper my.cnf or my.ini option file, and consequently does not see the options that you specified.
• The disk is full or a disk quota is exceeded.
• You have created a subdirectory whose name is equal to a data file that you specified, so the name cannot be used as a file name.
• There is a syntax error in the innodb_data_home_dir or innodb_data_file_path value.

 

          If something goes wrong when InnoDB attempts to initialize its tablespace or its log files, delete all files created by InnoDB. This means all ibdata files and all ib_logfile files. In case you have already created some InnoDB tables, delete the corresponding .frm files for these tables (and any .ibd files if you are using multiple tablespaces) from the MySQL database directories as well. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening.

 

6.4.4.  InnoDB Startup Options and System Variables

 

innodb

          Enables the InnoDB storage engine, if the server was compiled with InnoDB support.

innodb-status-file

          Controls whether InnoDB creates a file named innodb_status.<pid> in the MySQL data directory. If enabled, InnoDB periodically writes the output of SHOW ENGINE INNODB STATUS to this file.

By default, the file is not created. To create it, start mysqld with the –innodb-status-file=1 option. The file is deleted during normal shutdown.

skip-innodb

          Disable the InnoDB storage engine.

innodb_additional_mem_pool_size

          The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system, and writes warning messages to the MySQL error log. The default value is 1MB.

innodb_autoextend_increment

          The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full. The default value is 8. This variable does not affect the per-table tablespace files that are created if you use innodb_file_per_table=1.

innodb_buffer_pool_size

          The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size.

innodb_data_file_path

          The paths to individual data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified in KB, MB, or GB (1024MB) by appending K, M, or G to the size value. The sum of the sizes of the files must be at least 10MB. If you do not specify On some operating systems, files must be less than 2GB. If you do not specify innodb_data_file_path, the default behavior starting from 4.0 is to create a single 10MB auto-extending data file named ibdata1. Starting from 3.23.44, you can set the file size larger than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files.

innodb_data_home_dir

          The common part of the directory path for all InnoDB data files in the shared tablespace. This setting does not affect the location of per-file tablespaces when innodb_file_per_table is enabled. The default value is the MySQL data directory. If you specify the value as an empty string, in which case you can use absolute file paths in innodb_data_file_path.

innodb_file_per_table

          If innodb_file_per_table is disabled (the default), InnoDB creates tables in the shared tablespace. If innodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace.

innodb_flush_log_at_trx_commit

          If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

          The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).

          A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and thus crash recovery does work regardless of the value.

          For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.

sync_binlog

          If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk  after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice

innodb_fast_shutdown

          The InnoDB shutdown mode. By default, the value is 1, which causes a “fast” shutdown (the normal type of shutdown). If the value is 0, InnoDB does a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or even hours in extreme cases. If the value is 1, InnoDB skips these operations at shutdown. If the value is 2, InnoDB will just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but crash recovery will be done at the next startup.

innodb_lock_wait_timeout

          The timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. The default is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

          ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

          When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15.

innodb_rollback_on_timeout

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction. This variable was added in MySQL 5.1.15.

innodb_log_buffer_size

          The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 1MB. Sensible values range from 1MB to 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

innodb_log_file_size

          The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

innodb_log_files_in_group

          The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.

innodb_log_group_home_dir

          The directory path to the InnoDB log files. If you do not specify any InnoDB log variables, the default is to create two 5MB files names ib_logfile0 and ib_logfile1 in the MySQL data directory.

innodb_open_files

          This variable is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default value is 300. This variable is available as of MySQL 4.1.1.

          The file descriptors used for .ibd files are for InnoDB only. They are independent of those specified by the –open-files-limit server option, and do not affect the operation of the table cache.

innodb-safe-binlog

If this option is given, then after a crash recovery by InnoDB, mysqld truncates the binary log after the last not-rolled-back transaction in the log. The option also causes InnoDB to print an error if the binary log is smaller or shorter than it should be.

foreign_key_checks

          If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.

          Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.

Note: Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency.

innodb_commit_concurrency

 

          The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.

          As of MySQL 5.1.36, the value of innodb_commit_concurrency cannot be changed at runtime from zero to nonzero or vice versa. The value can still be changed from one nonzero value to another.

innodb_force_recovery

 

          The crash recovery mode. Possible values are from 0 to 6. This variable should be set greater than 0 only in an emergency situation when you want to dump your tables from a corrupt database! As a safety measure, InnoDB prevents any changes to its data when this variable is greater than 0

 

innodb_stats_on_metadata

 

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not updates statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

 

Creating and Using InnoDB Tables:

 

          To create an InnoDB table, specify an ENGINE = InnoDB option in the CREATE TABLE statement:

 

          mysql> CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

 

          The statement creates a table and an index on column a in the InnoDB tablespace that consists of the data files that you specified in my.cnf. In addition, MySQL creates a file customers.frm in the test directory under the MySQL database directory. Internally, InnoDB adds an entry for the table to its own data dictionary. The entry includes the database name. For example, if test is the database in which the customers table is created, the entry is for ‘test/customers’. This means you can create a table of the same name customers in some other database, and the table names do not collide inside InnoDB.

          You can query the amount of free space in the InnoDB tablespace by issuing a SHOW TABLE STATUS statement for any InnoDB table. The amount of free space in the tablespace appears in the Data_free section in the output of SHOW TABLE STATUS (or the Comment section prior to MySQL 5.1.24). For example:

 

          mysql> SHOW TABLE STATUS FROM test LIKE ‘customers’

 

How to Use Transactions in InnoDB with Different APIs:

 

          By default, each client that connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. To use multiple-statement transactions, you can switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with either COMMIT or ROLLBACK. If you want to leave autocommit on, you can begin your transactions within START TRANSACTION and end them with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

 

shell> mysql test

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a))

-> ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO customer VALUES (10, ‘Heikki’);

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SET autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO customer VALUES (15, ‘John’);

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM customer;

+——+——–+

| a | b |

+——+——–+

| 10 | Heikki |

+——+——–+

1 row in set (0.00 sec)

mysql>

 

Converting Tables from Other Storage Engines to InnoDB:

 

          To convert a non-InnoDB table to use InnoDB use ALTER TABLE:

 

          mysql> ALTER TABLE t1 ENGINE=InnoDB;

 

Note: Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.

 

InnoDB Multi-Versioning:

 

          InnoDB is a multi-versioned storage engine: it keeps information about old versions of changed rows, to support transactional features such as concurrency and rollback. This information is stored in the tablespace in a data structure called a rollback segment (after an analogous data structure in Oracle). InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read. Internal Details of Multi-Versioning Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

 

          Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.

 

Guidelines for Managing Rollback Segments Commit:

 

          your transactions regularly, including those transactions that issue only consistent reads. Otherwise, InnoDB cannot discard data from the update undo logs, and the rollback segment may grow too big, filling up your tablespace.

 

          In the InnoDB multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. InnoDB only physically removes the corresponding row and its index records when it discards the update undo log record written for the deletion. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

 

Clustered and Secondary Indexes:

 

Every InnoDB table has a special index called the clustered index where the data for the rows is stored:         

          If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

 

          If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only   NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

 

          If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

 

          In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

 

6.4.6.  File Space Management

 

          The data files that you define in the configuration file form the InnoDB tablespace. The files are logically concatenated to form the tablespace. There is no striping in use. Currently, you cannot define where within the tablespace your tables are allocated. However, in a newly created tablespace, InnoDB allocates space starting from the first data file.

 

          The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of size 1MB (64 consec-utive pages). The “files” inside a tablespace are called segments in InnoDB. The term “rollback segment” is somewhat confusing because it actually contains many tablespace segments.

 

          When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it individually. After that, InnoDB starts to allocate whole extents to the segment. InnoDB can add up to 4 extents at a time to a large segment to ensure good sequentiality of data.

 

          Two segments are allocated for each index in InnoDB. One is for nonleaf nodes of the B-tree, the other is for the leaf nodes. The idea here is to achieve better sequentiality for the leaf nodes, which contain the data.

 

          Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an InnoDB tablespace cannot be allocated to segments as a whole, but only as individual pages.

 

          When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace. InnoDB always reserves some extents for cleanup and other internal purposes; these reserved extents are not included in the free space.

 

          When you delete data from a table, InnoDB contracts the corresponding B-tree indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads.

 

          The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

 

          If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page. For a column chosen for off-page storage, InnoDB stores the first 768 bytes locally in the row, and the rest externally into overflow pages. Each such column has its own list of overflow pages. The 768-byte prefix is accompanied by a 20-byte value that stores the true length of the column and points into the overflow list where the rest of the value is stored.

 

Defragmenting a Table:

 

          If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

 

          One symptom of fragmentation is that a table takes more space than it “should” take. How much that is exactly, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%. Another symptom of fragmentation is that a table scan such as this takes more time than it “should” take:

 

          mysql> SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

 

          (In the preceding query, we are “fooling” the SQL optimizer into scanning the clustered index rather than a secondary index.) Most disks can read 10MB/s to 50MB/s, which can be used to estimate how fast a table scan should be.

 

          It can speed up index scans if you periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:

 

          mysql> ALTER TABLE tbl_name ENGINE=INNODB;

 

          Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

 

          If the insertions into an index are always ascending and records are deleted only from the end, the InnoDB filespace management algorithm guarantees that fragmentation in the index does not occur.

 

6.4.7  InnoDB Error Handling

 

          Error handling in InnoDB is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction. The following items describe how InnoDB performs error handling:

 

• If you run out of file space in the tablespace, a MySQL Table is full error occurs and InnoDB rolls back the SQL statement.
• A transaction deadlock causes InnoDB to roll back the entire transaction. Retry the whole transaction when this happens.
• A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement.

 

          During implicit rollbacks, as well as during the execution of an explicit ROLLBACK SQL statement, SHOW PROCESSLIST displays Rolling back in the State column for the relevant connection.

 

InnoDB Error Codes:

 

1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

 

1016 (ER_CANT_OPEN_FILE)

Cannot find the InnoDB table from the InnoDB data files, although the .frm file for the table exists.

 

1114 (ER_RECORD_FILE_FULL)

InnoDB has run out of free space in the tablespace. Reconfigure the tablespace to add a new data file.

 

1205 (ER_LOCK_WAIT_TIMEOUT)

Lock wait timeout expired. Transaction was rolled back.

 

1216 (ER_NO_REFERENCED_ROW)

You are trying to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.

 

1217 (ER_ROW_IS_REFERENCED)

You are trying to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.

 

6.4.8.  InnoDB Performance Tuning and Troubleshooting

 

• innodb_buffer_pool_size specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.
• Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure.
• Beware also of other big disk-bound operations. Use DROP TABLE and CREATE TABLE to empty a table, not DELETE FROM tbl_name.
• Place tablespaces in different locations.
• Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The disadvantage of big log files is that the recovery time is longer.
• When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

 

SET autocommit=0;

                   … SQL import statements …

                    COMMIT;

• If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

 

                   SET unique_checks=0;

                   … SQL import statements …

                   SET unique_checks=1;

 

• If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

 

                   SET foreign_key_checks=0;

                   … SQL import statements …

                   SET foreign_key_checks=1;

 

• Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

 

                   mysql> INSERT INTO yourtable VALUES (1,2), (5,5), …;

 

                   This tip is valid for inserts into any table, not just InnoDB tables.

 

SHOW ENGINE INNODB STATUS and the InnoDB Monitors:

 

          InnoDB Monitors provide information about the InnoDB internal state. This information is useful for performance tuning. Each Monitor can be enabled by creating a table with a special name, which causes InnoDB to write Monitor output periodically. Also, output for the standard InnoDB Monitor is available on demand through the SHOW ENGINE INNODB STATUS SQL statement.

 

There are several types of InnoDB Monitors:

 

1. TRANSACTIONS
2. BACKGROUND THREAD
3. BUFFER POOL AND MEMORY
4. ROW OPERATIONS
5. About Log Files
6. FILE I/O

 

TRANSACTIONS

If this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

 

BACKGROUND THREAD

The srv_master_thread lines shows work done by the main background thread. This section is displayed only by InnoDB Plugin.

 

BUFFER POOL AND MEMORY

This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

 

ROW OPERATIONS

This section shows what the main thread is doing, including the number and performance rate for each type of row operation.

 

About LOG Files

This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint.

 

FILE I/O

This section provides information about threads that InnoDB uses to perform various types of I/O. The first few of these are dedicated to general InnoDB processing. The contents also display information for pending I/O operations and statistics for I/O performance.

 

6.4.9  Restrictions on InnoDB Tables:

 

          Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or regenerate them with the mysql_install_db script.

 

Maximums and Minimums:

 

• A table cannot contain more than 1000 columns.
• The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes.
• The combined size of the InnoDB log files must be less than 4GB.
• The minimum tablespace size is 10MB. The maximum tablespace size is 64TB. This is also the maximum size for a table.

 

6.5.  The MERGE Storage Engine

 

          The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order.

 

          An alternative to a MERGE table is a partitioned table, which stores partitions of a single table in separate files. Partitioning enables some operations to be performed more efficiently and is not limited to the MyISAM storage engine.

 

          When you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format, and an .MRG file contains the names of the underlying MyISAM tables that should be used as one. The tables do not have to be in the same database as the MERGE table.

 

          You can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.

 

 Note: The use of MERGE tables entails the following security issue: If a user has access to MyISAM table t, that user can create a MERGE table m that accesses t. However, if the user’s privileges on t are subsequently revoked, the user can continue to access t by doing so through m.

 

          Use of DROP TABLE with a MERGE table drops only the MERGE specification. The underlying tables are not affected.

 

          To create a MERGE table, you must specify a UNION=(list-of-tables) option that indicates which MyISAM tables to use. You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively. If you specify no INSERT_METHOD option or if you specify it with a value of NO, inserts into the MERGE table are not permitted and attempts to do so result in an error.

 

          The following example shows how to create a MERGE table:

 

mysql> CREATE TABLE t1 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES (‘Testing’),(‘table’),(‘t1’);

mysql> INSERT INTO t2 (message) VALUES (‘Testing’),(‘table’),(‘t2’);

mysql> CREATE TABLE total (

-> a INT NOT NULL AUTO_INCREMENT,

-> message CHAR(20), INDEX(a))

-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

 

Note that column a is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce uniqueness over the set of underlying tables. (Similarly, a column with a UNIQUE index in the underlying tables should be indexed in the MERGE table but not as a UNIQUE index.)

 

After creating the MERGE table, you can use it to issue queries that operate on the group of tables as a whole:

 

mysql> SELECT * FROM total;

+—+———+

| a | message |

+—+———+

| 1 | Testing |

| 2 | table |

| 3 | t1 |

| 1 | Testing |

| 2 | table |

| 3 | t2 |

+—+———+

 

          To remap a MERGE table to a different collection of MyISAM tables, you can use one of the following methods:

• DROP the MERGE table and re-create it.
• Use ALTER TABLE tbl_name UNION=(…) to change the list of underlying tables.

 

          Beginning with MySQL 5.1.24, it is also possible to use ALTER TABLE … UNION=() (that is, with an empty UNION clause) to remove all of the underlying tables.

 

MERGE TABLE Limitations:

 

• The underlying table and the MERGE table must have the same number of columns.

 

          If you ALTER the MERGE table and add a column and after that if your trying to fetch the table you will get the below error.

 

          Unable to open underlying table which is differently defined or of non-MyISAM type or doesn’t exist

 

• The column order in the underlying table and the MERGE table must match.
• The column type in the underlying table and the MERGE table must be equal.
• The column length in the underlying table and the MERGE table must be equal.

 

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn’t exist

• The underlying table may have more indexes than the MERGE table, but cannot have fewer.
• The index type of the underlying table and the MERGE table must be the same.
• The number of index parts (that is, multiple columns within a compound index) in the index definition for the underlying table and the MERGE table must be the same.

 

MERGE Table Advantages and Disadvantages:

 

          MERGE tables can help you solve the following problems:

 

• Obtain more speed. You can split a large read-only table based on some criteria, and then put individual tables on different disks. A MERGE table structured this way could be much faster than using a single large table.
• Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the underlying tables for some queries and use a MERGE table for others. You can even have many different MERGE tables that use overlapping sets of tables.
• Perform more efficient repairs. It is easier to repair individual smaller tables that are mapped to a MERGE table than to repair a single large table.
• Exceed the file size limit for the operating system. Each MyISAM table is bound by this limit, but a collection of MyISAM tables is not.
• You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. There should be no really notable performance impact from doing this

 

          The disadvantages of MERGE tables are:

 

• You can use only identical MyISAM tables for a MERGE table.
• Some MyISAM features are unavailable in MERGE tables. For example, you cannot create FULLTEXT indexes on MERGE tables. (You can create FULLTEXT indexes on the underlying MyISAM tables, but you cannot search the MERGE table with a full-text search.)
• Index reads are slower. When you read an index, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches a given index value. To read the next index value, the MERGE storage engine needs to search the read buffers to find the next value. Only when one index buffer is used up does the storage engine need to read the next index block.

 

6.6.  MERGE Table Problems:

 

• If you use ALTER TABLE to change a MERGE table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying MyISAM tables are copied into the altered table, which then uses the specified storage engine.
• The INSERT_METHOD table option for a MERGE table indicates which underlying MyISAM table to use for inserts into the MERGE table. However, use of the AUTO_INCREMENT table option for that MyISAM table has no effect for inserts into the MERGE table until at least one row has been inserted directly into the MyISAM table.
• A MERGE table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT, the data goes into the first or last MyISAM table (as determined by the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not over all the underlying tables in the collection.

 

The MEMORY Storage Engine:

 

          The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

 

          However, MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes. Also, MEMORY does not preserve table contents across server restarts.

 

          To specify that you want to create a MEMORY table, indicate that with an ENGINE table option:

          mysql> CREATE TABLE t (i INT) ENGINE = MEMORY;

 

          As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

 

MEMORY tables have the following characteristics:

 

• Space for MEMORY tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts. No overflow area or extra key space is needed. No extra space is needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table. MEMORY tables also have none of the problems commonly associated with deletes plus inserts in hashed tables.
• MEMORY tables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes.
• MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.
• MEMORY tables cannot contain BLOB or TEXT columns.
• Non-TEMPORARY MEMORY tables are shared among all clients, just like any other non-TEMPORARY table.
• MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion.
• MEMORY tables are never converted to disk tables. If an internal temporary table becomes too large, the server automatically converts it to on-disk storage.
• The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To have larger (or smaller) MEMORY tables, you must change the value of this variable. The value in effect for CREATE TABLE is the value used for the life of the table. (If you use ALTER TABLE or TRUNCATE TABLE, the value in effect at that time becomes the new maximum size for the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.)
• The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.
• Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only when the entire table is deleted. Memory that was previously used for rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
• As mentioned earlier, the max_heap_table_size system variable sets the limit on the maximum size of MEMORY tables. To control the maximum size for individual tables, set the session value of this variable before creating each table. (Do not change the global max_heap_table_size value unless you intend the value to be used for MEMORY tables created by all clients.) The following

                example creates two MEMORY tables, with a maximum size of 1MB and 2MB, respectively:

 

                   mysql> SET max_heap_table_size = 1024*1024;

                   Query OK, 0 rows affected (0.00 sec)

                   mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;

                   Query OK, 0 rows affected (0.01 sec)

                   mysql> SET max_heap_table_size = 1024*1024*2;

                   Query OK, 0 rows affected (0.00 sec)

                   mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;

                   Query OK, 0 rows affected (0.00 sec)

 

                Both tables will revert to the server’s global max_heap_table_size value if the server restarts.

 

          You can also specify a MAX_ROWS table option in CREATE TABLE statements for MEMORY tables to provide a hint about the number of rows you plan to store in them. This does not enable the table to grow beyond the max_heap_table_size value, which still acts as a constraint on maximum table size. For maximum flexibility in being able to use MAX_ROWS, set max_heap_table_size at least as high as the value to which you want each MEMORY table to be able to grow.

 

6.7.  The EXAMPLE Storage Engine

 

          The EXAMPLE storage engine is a stub engine that does nothing. Its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

 

          To enable the EXAMPLE storage engine if you build MySQL from source, invoke configure with the –

-with-example-storage-engine option.

 

          To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution.

 

          When you create an EXAMPLE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. No other files are created. No data can be stored into the table. Retrievals return an empty result.

 

mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;

Query OK, 0 rows affected (0.78 sec)

mysql> INSERT INTO test VALUES(1),(2),(3);

ERROR 1031 (HY000): Table storage engine for ‘test’ doesn’t »

have this option

mysql> SELECT * FROM test;

Empty set (0.31 sec)

 

          The EXAMPLE storage engine does not support indexing.

 

6.8.  The FEDERATED Storage Engine

 

          The FEDERATED storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables.

 

          To include the FEDERATED storage engine if you build MySQL from source, invoke configure with the –

-with-federated-storage-engine option.

 

          Beginning with MySQL 5.1.26, the FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the –federated option.

 

          To examine the source for the FEDERATED engine, look in the storage/federated directory of a MySQL source distribution.

 

6.8.1  FEDERATED Storage Engine Overview:

 

          When you create a table using one of the standard storage engines (such as MyISAM, CSV or InnoDB), the table consists of the table definition and the associated data. When you create a FEDERATED table, the table definition is the same, but the physical storage of the data is handled on a remote server.

 

A FEDERATED table consists of two elements:

 

• A remote server with a database table, which in turn consists of the table definition (stored in the .frm file) and the associated table. The table type of the remote table may be any type supported by the remote mysqld server, including MyISAM or InnoDB.
• A local server with a database table, where the table definition matches that of the corresponding table on the remote server. The table definition is stored within the .frm file. However, there is no data file on the local server. Instead, the table definition includes a connection string that points to the remote table.

 

          When executing queries and statements on a FEDERATED table on the local server, the operations that would normally insert, update or delete information from a local data file are instead sent to the remote server for execution, where they update the data file on the remote server or return matching rows from the remote server.

 

          The basic structure of a FEDERATED table setup is shown below:

 

1. The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.
2. The statement is sent to the remote server using the MySQL client API.
3. The remote server processes the statement and the local server retrieves any result that the statement produces (an affectedrows count or a result set).
4. If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement. The local server communicates with the remote server using MySQL client C API functions.

 

6.8.2  How to Create FEDERATED Tables:

 

          To create a FEDERATED table you should follow these steps:

 

• Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the SHOW CREATE TABLE statement.
• Create the table on the local server with an identical table definition, but adding the connection information that links the local table to the remote table.

 

                   For example, you could create the following table on the remote server:

 

                   CREATE TABLE test_table (

                   id INT(20) NOT NULL AUTO_INCREMENT,

                   name VARCHAR(32) NOT NULL DEFAULT ”,

                   other INT(20) NOT NULL DEFAULT ‘0’,

                   PRIMARY KEY (id),

                   INDEX name (name)

                   )

                   ENGINE=MyISAM;

 

                   To create the local table that will be federated to the remote table, there are two options available. You can either create the local table and specify the connection string (containing the server name, login, password) to be used to connect to the remote table using the CONNECTION, or you can use an existing connection that you have previously created using the CREATE SERVER statement.

 

          To use the first method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. For example:

 

          CREATE TABLE federated_table (

          id INT(20) NOT NULL AUTO_INCREMENT,

          name VARCHAR(32) NOT NULL DEFAULT ”,

          other INT(20) NOT NULL DEFAULT ‘0’,

          PRIMARY KEY (id),

          INDEX name (name)

          )

          ENGINE=FEDERATED

          CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

 

          The CONNECTION string contains the information required to connect to the remote server containing the table that will be used to physically store the data. The connection string specifies the server name, login credentials, port number and database/table information. In the example, the remote table is on the server remote_host, using port 9306. The name and port number should match the host name (or IP address) and port number of the remote MySQL server instance you want to use as your remote table.

 

          The format of the connection string is as follows:

 

          scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

 

Where:

 

• scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
• user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
• password: (Optional) The corresponding password for user_name.
• host_name: The host name or IP address of the remote server.
• port_num: (Optional) The port number for the remote server. The default is 3306.
• db_name: The name of the database holding the remote table.
• tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.

 

Creating a FEDERATED Table Using CREATE SERVER:

 

          If you are creating a number of FEDERATED tables on the same server, or if you want to simplify the process of creating FEDERATED tables, you can use the CREATE SERVER statement to define the server connection parameters, just as you would with the CONNECTION string.

 

          The format of the CREATE SERVER statement is:

 

          CREATE SERVER

          server_name

          FOREIGN DATA WRAPPER wrapper_name

          OPTIONS (option [, option] …)

 

          The server_name is used in the connection string when creating a new FEDERATED table.

 

          For example, to create a server connection identical to the CONNECTION string:

 

          CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

 

          You would use the following statement:

 

          CREATE SERVER fedlink

          FOREIGN DATA WRAPPER mysql

          OPTIONS (USER ‘fed_user’, HOST ‘remote_host’, PORT 9306, DATABASE ‘federated’);

 

          To create a FEDERATED table that uses this connection, you still use the CONNECTION keyword, but specify the name you used in the CREATE SERVER statement.

 

          CREATE TABLE test_table (

          id INT(20) NOT NULL AUTO_INCREMENT,

          name VARCHAR(32) NOT NULL DEFAULT ”,

          other INT(20) NOT NULL DEFAULT ‘0’,

          PRIMARY KEY (id),

          INDEX name (name)

          )

          ENGINE=FEDERATED

          CONNECTION=’fedlink/test_table’;

 

          The connection name in this example contains the name of the connection (fedlink) and the name of the table (test_table) to link to, separated by a slash. If you specify only the connection name without a table name, the table name of the local table is used instead.       

 

6.8.3  FEDERATED Storage Engine Notes and Tips

 

          You should be aware of the following points when using the FEDERATED storage engine:

 

• The remote table that a FEDERATED table points to must exist before you try to access the table through the FEDERATED table.
• It is possible for one FEDERATED table to point to another, but you must be careful not to create a loop.
• The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE.
• Transactions are not supported.
• There is no way for the FEDERATED engine to know if the remote table has changed.
• Any DROP TABLE statement issued against a FEDERATED table drops only the local table, not the remote table.

 

6.9  The ARCHIVE Storage Engine

 

          The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

 

          The ARCHIVE storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the –with-archive-storage-engine option.

          To examine the source for the ARCHIVE engine, look in the storage/archive directory of a MySQL source distribution.

 

          When you create an ARCHIVE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The data file has an extension of .ARZ. (Prior to MySQL 5.1.15, a metadata file with an extension of .ARM is created as well.) An .ARN file may appear during optimization operations.

 

          The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations. The ARCHIVE engine uses row-level locking.

 

          As of MySQL 5.1.6, the ARCHIVE engine supports the AUTO_INCREMENT column attribute. Attempting to create an index on any other column results in an error.

 

Storage: Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http:// www.zlib.net/). You can use OPTIMIZE TABLE to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE, see later in this section). The engine also supports CHECK TABLE. There are several types of insertions that are used:

 

• An INSERT statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A SELECT forces a flush to occur, unless the only insertions that have come in were INSERT DELAYED (those flush as necessary)

 

Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To achieve better compression, you can use OPTIMIZE TABLE or REPAIR TABLE. The number of rows in ARCHIVE tables reported by SHOW TABLE STATUS is always accurate.

 

6.10  The CSV Storage Engine

 

          The CSV storage engine stores data in text files using comma-separated values format. To enable the CSV storage engine if you build MySQL from source, invoke configure with the –with-csv-storage-engine option. To examine the source for the CSV engine, look in the storage/csv directory of a MySQL source distribution.

 

          When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in commaseparated values format.

 

mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)

-> ENGINE = CSV;

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO test VALUES(1,’record one’),(2,’record two’);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test;

+——+————+

| i | c |

+——+————+

| 1 | record one |

| 2 | record two |

+——+————+

2 rows in set (0.00 sec)

 

          Starting with MySQL 5.1.9, creating a CSV table also creates a corresponding Metafile that stores the state of the table and the number of rows that exist in the table. The name of this file is the same as the name of the table with the extension CSM.

 

          If you examine the test.CSV file in the database directory created by executing the preceding statements, its contents should look like this:

 

“1”,”record one”

“2”,”record two”

 

          This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

          You can directly edit the .CSV file to manipulate data, but the changes are visible only after executing the FLUSH TABLES command. But this is not the prefered way.

 

6.10.1.  Repairing and Checking CSV Tables:

 

          Functionality introduced in version 5.1.9

 

          The CSV storage engines supports the CHECK and REPAIR statements to verify and if possible repair a damaged CSV table. When running the CHECK statement, the CSV file will be checked for validity by looking for the correct field separators, escaped fields (matching or missing quotation marks), the correct number of fields compared to the table definition and the existence of a corresponding CSV metafile. The first invalid row discovered will report an error. Checking a valid table produces output like that shown below:

 

mysql> check table csvtest;

+————–+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————–+——-+———-+———-+

| test.csvtest | check | status | OK |

+————–+——-+———-+———-+

1 row in set (0.00 sec)

 

A check on a corrupted table returns a fault:

 

mysql> check table csvtest;

+————–+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————–+——-+———-+———-+

| test.csvtest | check | error | Corrupt |

+————–+——-+———-+———-+

1 row in set (0.01 sec)

 

          To repair a table you can use REPAIR, this copies as many valid rows from the existing CSV data as possible, and then replaces the existing CSV file with the recovered rows. Any rows beyond the corrupted data are lost.

 

mysql> repair table csvtest;

+————–+——–+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————–+——–+———-+———-+

| test.csvtest | repair | status | OK |

+————–+——–+———-+———-+

1 row in set (0.02 sec)

 

Note: Note that during repair, only the rows from the CSV file up to the first damaged row are copied to the new table. All other rows from the first damaged row to the end of the table are removed, even valid rows.

 

6.10.2.  CSV Limitations:

 

          The CSV storage engine does not support indexing.

          Partitioning is not supported for tables using the CSV storage engine.

 

          Beginning with MySQL 5.1.23, tables using the CSV storage engine can no longer be created with NULL columns. However, for backward compatibility, you can continue to use such tables that were created in previous MySQL releases.

 

6.11  The BLACKHOLE Storage Engine

 

          The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result:

 

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test VALUES(1,’record one’),(2,’record two’);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test;

Empty set (0.00 sec)

 

          To enable the BLACKHOLE storage engine if you build MySQL from source, invoke configure with the –

-with-blackhole-storage-engine option. To examine the source for the BLACKHOLE engine, look in the sql directory of a MySQL source distribution.

 

          When you create a BLACKHOLE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. There are no other files associated with the table.

 

          The BLACKHOLE storage engine supports all kinds of indexes. That is, you can include index declarations in the table definition.

 

          Inserts into a BLACKHOLE table do not store any data, but if the binary log is enabled, the SQL statements are logged (and replicated to slave servers). This can be useful as a repeater or filter mechanism. Suppose that your application requires slave-side filtering rules, but transferring all binary log data to the slave first results in too much traffic. In such a case, it is possible to set up on the master host a “dummy” slave process whose default storage engine is BLACKHOLE, depicted as follows:

 

          The dummy process does not actually store any data, so there is little processing overhead incurred by running the additional mysqld process on the replication master host. This type of setup can be repeated with additional replication slaves.

 

          As of MySQL 5.1.4, the BLACKHOLE engine is transaction-aware, in the sense that committed transactions are written to the binary log and rolled-back transactions are not.

 

Blackhole Engine and Auto Increment Columns

 

          The Blackhole engine is a no-op engine. Any operations performed on a table using Blackhole will have no effect. This should be born in mind when considering the behavior of primary key columns that auto increment. The engine will not automatically increment field values, and does not retain auto increment field state. This has important implications in replication.

 

          Consider the following replication scenario where all three of the following conditions apply:

 

1. On a master server there is a blackhole table with an auto increment field that is a primary key.
2. On a slave the same table exists but using the MyISAM engine.
3. Inserts are performed into the master’s table without explicitly setting the auto increment value in the INSERT statement itself or through using a SET INSERT_ID statement.

 

          In this scenario replication will fail with a duplicate entry error on the primary key column.

In statement based replication, the value of INSERT_ID in the context event will always be the same. Replication will therefore fail due to trying insert a row with a duplicate value for a primary key column.

 

          In row based replication, the value that the engine returns for the row always be the same for each insert. This will result in the slave attempting to replay two insert log entries using the same value for the primary key column, and so replication will fail.

Ask Question