MySQL Support Blog

Discussion

The state “converting HEAP to MyISAM” happens when a query that needs a temporary table is converting from an in-memory temporary table to a disk-based temporary table.

MySQL uses memory-based temporary tables up to the size limit set by the tmp_table_size system variable. If a query needs a temporary table larger than this it will be converted to a disk-based temporary table using the MyISAM storage engine.

GROUP BY queries and ORDER BY queries that can’t use an index for the ordering are the most common causes of temporary table creation.

Solution

You could consider raising the per-session value of tmp_table_size if you have sufficient memory. Use the SHOW GLOBAL STATUS statement to see the value of the Created_tmp_tables variable. It will show the total number of temporary tables that have been created:

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 13    |
+--------------------+-------+

The Created_tmp_disk_tables variable shows how many of those have been converted to disk temporary tables:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+

Discussion

Informix Shared Memory is divided in three major areas: Resident Portion, Virtual Portion, and IPC Communication Portion.

MySQL allocates memory for various internal buffers and caches. These buffers fall into two major groups: global buffers and per-connection (thread) buffers. As their name implies, global buffers are shared among all the connections in MySQL.

For MyISAM storage engine, key buffer (configuration parameter: key_buffer_size) is very important. Remember, MySQL stores only indexes and not data in key buffer for MyISAM storage engine. For InnoDB storage engine, buffer pool (configuration parameter: innodb_buffer_pool_size) is very important. MySQL stores Indexes and data in buffer pool for InnoDB storage engine.

Discussion

Migrating one table’s worth of data between DB2 and MySQL is a fairly easy task, and does not necessarily require the use of 3rd party tools or programming. We will cover here specifically using DB2’s Export program to export data and mysqlimport to then import that data into MySQL.

Schema Migration

Any data migration must first be preceded by a schema migration. It may be that you have a schema definition already that you used with DB2 to create the table, which you can then tweak following some of the guidelines presented in data type and index migration articles, and use it to create the equivalent table in MySQL. One can use the DB2 db2look command to extract appropriate ddl for the tables in the database.

Data Migration

Now that we have the table created in MySQL, we can export the data using DB2’s Export and import the data using MySQL’s mysqlimport tool. Both tools support by default a text file format that has tab delimited columns with each row of data on a separate line.

As an example, to export the authors table from the pubs schema with Export:

c:\...> db2 export to authorsexprt
            of DEL messages authorsmsg
            select * from authors

We chose to use the name authors for the out export file name intentionally as mysqlimport program will need this. We import this file using this command:

c:\...> mysqlimport --local --delete --user=root test authors

test.authors: Records: 23  Deleted: 0  Skipped: 0  Warnings: 0

We have successfully imported the authors table into the test schema. The --local flag allowed the import file to be located in the local file system directory. The --delete option told the import program to first delete the rows in authors, so that this method could be repeated for the same results. To confirm the import took place successfully, you might check the table status by entering the following SQL statement:

SHOW TABLE STATUS LIKE 'authors' \G

*************************** 1. row ***************************
           Name: authors
         Engine: MyISAM
        Version: 9
     Row_format: Fixed
           Rows: 23
 Avg_row_length: 152
    Data_length: 3496
Max_data_length: 652835028991
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2004-12-29 18:55:34
    Update_time: 2004-12-29 19:16:04
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Related Issues

One final note, the decision of what Engine a table is being stored as is critical to your database, so please make sure that you really want to use MyISAM if that is how the table migration resulted. If you wanted instead a transaction safe and ACID compliant storage engine, then using InnoDB would be advised, and you could change the table type like this:

ALTER TABLE authors type=InnoDB;

Query OK, 23 rows affected, 1 warning (0.61 sec)
Records: 23  Duplicates: 0  Warnings: 0

Discussion

Indexes allow quick access to data sets in tables, and are vital to the proper functioning of most databases. SQL Server 2000 provides two basic types of indexes, non-clustered and clustered, and we will examine how to implement these indexes in MySQL. Because MySQL’s index features are dependent on the underlying storage engine that a table is using, we will specify the relevant storage engines being used during the index migration.

The non-clustered index is a typical style BTREE index and maps easily to MySQL’s BTREE index, supported both in MyISAM and InnoDB storage engines. To create such an index, one can use the same syntax as in SQL Server:

	CREATE [UNIQUE] INDEX emp_phone on employee(phone)

or the alternate MySQL specific syntax:

	ALTER TABLE employee ADD [UNIQUE] INDEX (phone)

The clustered index in SQL Server saves the table data in the leaves of the index itself, allowing very fast access to the full row data in the table based on the indexed columns of the table. MySQL’s InnoDB storage engine requires that each table store its data in this clustered way based on a PRIMARY KEY or UNIQUE NOT NULL column specified for a table, so if there was a clustered index like this in SQL Server:

	CREATE UNIQUE CLUSTERED INDEX emp_clustered ON employee(phone)

then with MySQL, the same could be accomplished with a table defined like:

	CREATE TABLE employee (phone varchar(20) PRIMARY KEY, ... ) ENGINE=INNODB

A non-unique clustered index in SQL Server does not map to MySQL so readily, since InnoDB will only cluster a table based on a PRIMARY KEY or UNIQUE column, but to achieve the similar effect, one might have a PRIMARY KEY based on a natural key starting with the column that one is interested in indexing for clustering purposes.

Note when managing indexes with MySQL, that multiple index changes should be made in one ALTER TABLE statement because MySQL will rebuild all of the data and indexes associated with a table whenever the indexes on that table are modified. Such a statement would look like:

	ALTER TABLE employee drop index phone, add index (phone,dept)

There are some options that one can use when creating an index in SQL Server 2000, and will suggest here comparable options for enabling the desired functionality where possible:

Related Issues

Other index related issues include MySQL having additional FULLTEXT and SPATIAL indexes implemented with its MyISAM storage engine for special types of searching that BTREE indexes do not well support.

Also MySQL does not currently support indexed views, and views themselves are only available starting in the 5.0.1 release of MySQL.

Discussion

Indexes allow quick access to data sets in tables, and are vital to the proper functioning of most databases. Informix provides two basic types of indexes: non-clustered and clustered. This article will examine how to implement these indexes in MySQL. Because MySQL’s index features are dependent on the underlying storage engine that a table is using, the relevant storage engines being used during the index migration need to be specified.

The non-clustered index is a typical style BTREE index and maps easily to MySQL’s BTREE index, which is supported both in MyISAM and InnoDB storage engines. To create such an index, you can use the same syntax as in Informix:

   CREATE [UNIQUE] INDEX emp_phone on employee(phone);

An alternative, MySQL specific syntax is this:

   ALTER TABLE employee ADD [UNIQUE] INDEX (phone);

The clustered index in Informix allows data records to be clustered on pages based on the sequence of a particular index, and maintains that clustering as much as possible over the course of insert activity. Clustering increases the efficiency of data retrieval when it involves accessing sequential value ranges for the clustering index. MySQL’s InnoDB storage engine requires that each table store its data in this clustered way based on a PRIMARY KEY or UNIQUE NOT NULL column specified for a table. For example, suppose there is a clustered index like this in Informix:

   CREATE UNIQUE CLUSTER INDEX emp_clustered ON employee(phone);

Based on the above, the same index could be accomplished in MySQL with a table defined like this:

   CREATE TABLE employee (phone varchar(20) PRIMARY KEY, ... ) ENGINE=INNODB;

A non-unique clustered index in Informix does not map to MySQL so readily, since InnoDB will only cluster a table based on a PRIMARY KEY or UNIQUE column. However, to achieve a similar effect, you might have a PRIMARY KEY based on a natural key starting with the column in which you’re interested in indexing for clustering purposes.

Note that when managing indexes with MySQL, multiple index changes should be made in one ALTER TABLE statement. This is because MySQL will rebuild all of the data and indexes associated with a table whenever the indexes on that table are modified. Such a statement would look like this:

   ALTER TABLE employee drop index phone, add index (phone,dept)

There are some options that one can use when creating an index in Informix, and will suggest here comparable options for enabling the desired functionality where possible:

Related Issues

Other index related issues include MySQL having additional FULLTEXT and SPATIAL indexes implemented with its MyISAM storage engine for special types of searching that BTREE indexes do not support very well.

MySQL does not support indexed views in the current stable release. Views are available starting in the 5.0.1 release of MySQL.

More Information

Indexes allow quick access to data sets in tables, and are vital to the proper functioning of most databases. DB2 provides two basic types of indexes, non-clustered and clustered, and we will examine how to implement these indexes in MySQL. Because MySQL’s index features are dependent on the underlying storage engine that a table is using, we will specify the relevant storage engines being used during the index migration.

The non-clustered index is a typical style BTREE index and maps easily to MySQL’s BTREE index, supported both in MyISAM and InnoDB storage engines. To create such an index, one can use the same syntax as in DB2:

   CREATE [UNIQUE] INDEX emp_phone on employee(phone);

or the alternate MySQL specific syntax:

   ALTER TABLE employee ADD [UNIQUE] INDEX (phone);

The clustered index in DB2 allows data records to be clustered on pages based on the sequence of a particular index, and maintains that clustering as much as possible over the course of insert activity. Clustering increases the efficiency of data retrieval when it involves accessing sequential value ranges for the clustering index. MySQL’s InnoDB storage engine requires that each table store its data in this clustered way based on a PRIMARY KEY or UNIQUE NOT NULL column specified for a table, so if there was a clustered index like this in DB2:

   CREATE UNIQUE INDEX emp_clustered ON employee(phone) CLUSTER;

Then with MySQL, the same could be accomplished with a table defined like:

   CREATE TABLE employee (phone varchar(20) PRIMARY KEY, ... ) ENGINE=INNODB

A non-unique clustered index in DB2 does not map to MySQL so readily, since InnoDB will only cluster a table based on a PRIMARY KEY or UNIQUE column, but to achieve the similar effect, one might have a PRIMARY KEY based on a natural key starting with the column that one is interested in indexing for clustering purposes.

Note when managing indexes with MySQL, that multiple index changes should be made in one ALTER TABLE statement because MySQL will rebuild all of the data and indexes associated with a table whenever the indexes on that table are modified. Such a statement would look like:

   ALTER TABLE employee drop index phone, add index (phone,dept)

There are some options that one can use when creating an index in DB2, and will suggest here comparable options for enabling the desired functionality where possible:

Related Issues

Other index related issues include MySQL having additional FULLTEXT and SPATIAL indexes implemented with its MyISAM storage engine for special types of searching that BTREE indexes do not well support.

Also MySQL does not currently support indexed views, and views themselves are only available starting in the 5.0.1 release of MyS

Discussion

ISAM is not created to handle crashes gracefully. There are no undo or redo logs, it’s just a flat text-file with your data rows stored in it.

There is a chance you can recover most of the files with isamchk, however. There may still be lost data changes, or if unlucky the repair will have to remove rows it can’t repair. This means that to get the biggest chance that your data is not corrupted in the event of a crash, you should recover from a backup and apply your binary log. If you want a more crash durable storage engine, you should look into using InnoDB or a similar storage engine.

Discussion

Users who plan on deploying the NDB cluster with MySQL will benefit from attending the MySQL Cluster for High Availabilitycourse offered by the training team of MySQL AB.

The course will cover the following topics:

1. Clustered Database Technology in general
   • Shared-nothing versus shared-disk database systems
   • Three different types of database clustering:
     • Physical (disk-based) clustering
     • Storage level clustering
     • Database server clustering
   • Network partitioning protocols to handle split-brain situations
   • Component recovery (checkpointing, logs or redundant components)
   • Cluster membership handling
   • Messaging services
   • Introduction to replication technology
     • Synchronous versus Asynchronous replication
     • Two-phase commit
   • Cluster frameworks
2. The concepts of MySQL Cluster
   • The benefits of MySQL Cluster
   • Typical usage scenarios of MySQL Cluster
   • Nodes (storage nodes, management servers, applications)
   • Data distribution and replication within MySQL Cluster storage engine
   • The distributed transaction model
   • The storage engine file system
   • MySQL Cluster components (Transporters, Blocks, Signals)
   • Node recovery protocol
   • System recovery protocol
   • System logging (REDO log, Local and Global Checkpoints, UNDO log)
3. Installing and configuring MySQL Cluster
   • Install MySQL Cluster on a set of computers
   • Compiling MySQL Cluster from the source code distribution
   • Configure MySQL Cluster using the configuration files
   • Configuring MySQL servers
   • How to configure the topology of MySQL Cluster
4. Administrating and using MySQL Cluster
   • Get system status and viewing the cluster log
   • Start and stop storage nodes, management servers
   • Using the management client
   • Using several tools programs like flexBench, show_tables, select_all
   • Example MySQL Cluster setup
   • Executing queries on multiple MySQL servers
   • Using non-clustered MyISAM and InnoDB tables in MySQL Cluster
5. High Performance features of MySQL Cluster
   • Scan and lookup of data
   • Parallel scanning
   • Data filtering techniques
   • Description of join query execution
6. High Availability features of MySQL Cluster
   • Replication (configuration, administration, replication servers)
   • Hot Backup and Restore (restoring meta data, restoring data)
   • Node recovery
   • System recovery

Discussion

Support for RAID options in MyISAM tables has been removed as of version 5.0 of MySQL. The reason for removing RAID is that typically now all systems support files with sizes greater than 4GB, so RAID is not as necessary as it was previously.

Work Around

If you have tables that use these options, you should convert them before upgrading. One way to do this is to dump them with the mysqldump utility. Then edit the dump file with a text editor to remove the RAID options in the CREATE TABLE statements, and reload the dump file. Note, do not edit the dump file with a word processing programs as it may introduce binary code to the text file. Instead use a simple text editor.

Another possibility is to use the CREATE TABLE statement with the SELECT clause to create a new table from the RAID table. This can be done like so:

CREATE TABLE new_table TYPE=innodb SELECT * FROM raid_table;

When using this method, the CREATE TABLE component of the statement must contain sufficient information to recreate column attributes as well as indexes. Otherwise column attributes may be lost and indexes will not appear in the new table. If the new table is created without problems, you can delete the old RAID table with the DROP TABLE statement and rename the new table to the name of the old table with RENAME TABLE. When you’re done you can then upgrade to version 5.0

Discussion

As of version 5.0 of MySQL, the query cache works automatically with MySQL Cluster. This is assuming the query_cache is turned on, like with any other storage engine. In prior versions, such as MySQL 4.1, the query cache does not work with MySQL Cluster.

Best Practice

Keep in mind that there is extra overhead with the query cache in MySQL Cluster compared to regular storage engines, since it requires a distributed invalidation when the data changes. Therefore, you will need a higher hit rate to justify the overhead than would be needed with MyISAM/InnoDB, where the overhead is quite low. If the vast majority of your tables are changing frequently, you should experiment with the query cache off as well as on, to see which setting produces the best results.