Slow MySQL 5.5.22 database engine

16 05 2012

Slooooooow UPDATE query with MySQL 5.5

I am using a local MySQL server a lot to handle, prepare and restructure big research tables. Ubuntu Precise uses MySQL server 5.5 while the previous distros used 5.1. I thought that might be good until I tried to import a table with some dozen variables and some thousand rows with and UPDATE statement which took some seconds (10 min!!).

nick rulez on forum.mysql.com quantified this fact and revealed that the default database engine changed from “MyISAM” to “InnoDB” and that indeed InnoDB is considerably slower in this regard.

So I want MyISAM back.

To list the available and default engines:

show engines
which produces

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Setting the database 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:

CREATE TABLE t (i INT) ENGINE = INNODB;

If you omit the ENGINE option, the default storage 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:

SET storage_engine=MYISAM;

[…]

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

ALTER TABLE t ENGINE = MYISAM;

I want MyISAM all the time so I decided for the my.cnf option. But where is my.cnf? According to debianadmin:

sudo nano /etc/mysql/my.cnf

Now append
default-storage-engine = MyISAM
Safe and exit with Ctrl-o, Ctrl-x and restart the server.

sudo restart mysql

and MyISAM it is.

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: