Converting myisam tables to innodb

Why should you convert myisam tables to innodb ?

For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb.

The simple query which does the trick is

Alter table myisam_table_name engine = innodb;

This query is good for small tables, which get converted in a flash (I am refering to tables smaller than 1 GB). But when you try to run this query to alter bigger tables, it takes a huge amount of time. You have to wait - maybe hours if not days to get your job done.

Recently I had a 30 GB table which i wanted to convert to innodb and the alter query went on for 3 days after which i gave up and killed the query. And then went on finding ways to make this alter happen fast.

There are multiple ways to make your alter fast -

1. create a temporary table with engine = innodb, disable unique checks and insert into the table in batches. The sequence of sql statements for this are

create table new_table_name like old_table_name;
alter table new_table_name engine = innodb;
set unique_checks=0;
// loop till all records are ported
insert into new_table_name select * from old_table_name where key > something and key <= something; set unique_checks=1;
If you have UNIQUE constraints on secondary keys, this speeds up a table import by turning off the uniqueness checks temporarily during the import operation.

2. Increase the buffers. In order to achieve higher I/O, you can increase the buffers - innodb_buffer_pool and innodb_log_file_size. If you are using a 64 bit machine - make the innodb_buffer_pool almost 50% of your available memory and innodb_log_file_size to around 128 MB (atleast) - you can make it 256MB to be a little more aggressive and 512 MB to be very aggressive. But it should be less than 25% of your innodb_buffer_pool size.

in my.cnf enter

innodb_buffer_pool = 2048M
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M


Stop mysql.
Delete the ib_logfile0 & ib_logfile1.
start mysql.

Now running the alter query should be much faster.

A combination of both the above methods could be even more beneficial. Remember to re-tune your innodb settings - as per your requirement before putting live load on the server.

BTW, i tried using the second method to alter the 30 GB table, and looking at its speed, it seems that it will be done in around 10 hours - instead of the 3 days it took earlier.

mysql hack - altering huge tables

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production.

Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack, you will need to take care of the backups - in case anything goes wrong. I have seen this hack work effectively with both MyISAM and InnoDB tables.

Here i have created a simple table to show this hack process. You can assume that this table has billions of rows and is more than 100GB in size.

CREATE TABLE `testhack` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unq` (`unq`)
) ENGINE=MyISAM

I need to drop the unique key. So, i create a new table testhack_new with the following schema

CREATE TABLE `testhack_new` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

Flush both tables with read lock

mysql> Flush tables with read lock;

Open another terminal. And go to the mysql/data/<database> directory. Do the following:

mysql/data/test $ mv testhack.frm testhack_old.frm; mv testhack_new.frm testhack.frm; mv testhack_old.frm testhack_new.frm; mv testhack.MYI testhack_old.MYI; mv testhack_new.MYI testhack.MYI; mv testhack_old.MYI testhack_new.MYI;

So, what is happening here is that the index, table definitions are being switched. After this process, the table definition of testhack will not contain the unique key. Now unlock the tables in the main window. And run repair tables to remove any issues.

mysql> unlock tables;
mysql> repair tables testhack;

+---------------+--------+----------+-------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                  |
+---------------+--------+----------+-------------------------------------------+
| test.testhack | repair | warning  | Number of rows changed from 0 to 20000000 | 
| test.testhack | repair | status   | OK                                        | 
+---------------+--------+----------+-------------------------------------------+

The repair table rebuilds the indexes. It is faster since it skips the use of key_cache for rebuilding the index (which is used in a normal alter table scenario).

MySQL versus PostgreSQL

I created and ran some simple tests on mysql and postgresql to figure out which one is faster. It is already known that postgresql is more stable and reliable than mysql. pgsql has a rich set of features. It is a complete RDBMS and also supports fulltext search.

All benchmarks were done on my laptop - Intel core 2 duo (2.0 GHz) with 4MB L2 cache & 2 GB ram. I have 64 Bit ubuntu system loaded with MySQL 5.1.24-rc (64 bit binary) and PostgreSQL 8.3.1 (compiled from source).

I used python as a scripting language for writing down my benchmark scripts. I used psycopg2 as a connector from python to postgres and mysql-python as a connector from python to mysql.

The benchmarking was done in phases. Firstly simple Insert, update and select queries were run to check the raw speed of these queries. Then threads were created to run simultaneous insert, update, select and delete queries. I checked the benchmark times for different number of concurrent threads.

I created a simple table on both mysql and pgsql. I used the MyISAM database engine to create table in mysql. :

ABC(id int not null auto_increment primary key, value varchar(250));

Queries that were run are:

Insert(I) : Insert ignore into ABC (id, value) ...(For pgsql, a rule has to be created to ignore duplicate inserts)
Update(U) : Update ABC set value=<something> where id=<random_id>
Select(S) : Select * from ABC where id=<random_id>
Delete(D) : Delete from ABC where id=<random_id>

• Insert - 100000 rows in 1 thread
  Time taken for Mysql : 20.8 seconds
  Time taken for Pgsql : 58.1 seconds
  So, raw insert speed of mysql is much better as compared to pgsql


• 100000 selects in 1 thread
  Time taken for Mysql : 21.76 seconds
  Time taken for Pgsql : 20.15 seconds
  Raw selects are better in pgsql as compared to mysql


• Selects - 2 threads of 100000 selects
  Time taken for Mysql : 40.46 seconds
  Time taken for Pgsql : 27.38 seconds
  So, if i increase the concurrency of selects, pgsql perfors much than mysql


• Update - 2 threads of 50000
  Time taken for Mysql : 23.97 seconds
  Time taken for Pgsql : 34.03 seconds
  Mysql looks better in handling updates here.


• 4 Threads
  Run 1 : [100000 Selects, 50000 Inserts, 50000 Updates, 20000 Deletes]
  Time taken for Mysql : 45.25 seconds
  Time taken for Pgsql : 54.58 seconds
  Run 2 : [100000 Selects, 100000 Inserts, 50000 Updates, 10000 Deletes]
  Time taken for Mysql : 59.05 seconds
  Time taken for Pgsql : 69.38 seconds
  Run 3 : [100000 Selects, 20000 Inserts, 20000 Updates, 1000 Deletes]
  Time taken for Mysql : 35.54 seconds
  Time taken for Pgsql : 31.23 seconds
  These runs show that Mysql is good when you have very large no of inserts/updates/deletes as compared to selects. But pgsql's performance surpasses that of mysql when the number of selects are much higher.


• Finally, lets approach the real life scenario where generally the number of selects are much more than the number of inserts and there are multiple threads performing selects and inserts.
  I will use the following notification here - <no_of_threads> X <no_of_operations(select/insert/update/delete)_per_thread>
  So, for example 3 X 20 Selects = 3 threads of 20 Selects in each thread
  
  Run 1 : [2 X 30000 selects, 3 X 20000 selects, 1 X 20000 inserts, 2 X 10000 inserts, 2 X 100000 updates, 2 X 1000 deletes] Total - 12 threads
  Time taken for Mysql : 42.28 seconds
  Time taken for Pgsql : 44.28 seconds
  Both Mysql and Pgsql are almost at par.
  
  Run 2 : [2 X 50000 selects, 2 X 40000 selects, 1 X 30000 selects, 1 X 20000 inserts, 2 X 15000 inserts, 2 X 15000 updates, 2 X 2000 deletes] Total - 12 threads but number of selects are quite high
  Time taken for Mysql : 61.02 seconds
  Time taken for Pgsql : 48.60 seconds
  So, as we increase the number of operations (specially selects) mysql's performance degrades, whereas pgsql's performance remains almost the same
  
  Run 3 : [4 X 50000 selects, 4 X 40000 selects, 2 X 30000 selects, 2 X 20000 inserts, 3 X 15000 inserts, 3 X 15000 updates, 2 X 3000 deletes] Total - 20 threads (10 threads for select, 5 for insert, 3 for update and 2 for delete) Which is the normal trend in database servers.
  Time taken for Mysql : 169.31 seconds
  Time taken for Pgsql : 128.7 seconds
  Bingo, so as concurrency increases pgsql becomes faster than mysql.

My earlier benchmarks with pgsql 7.x was not as good as this one. With postgresql 8.3.1, the speed of serving concurrent requests has increased a lot. So, in a high concurrency environment, i would generally recommend to go ahead with using postgresql rather than mysql.

Please check the comments section. We have some really interesting comments there...

myisamchk - checking and repairing myisam tables in mysql

First of all, how and why does a mysql table go corrupt?

Tables generally go corrupt when mysql server crashes and the tables are not closed properly. They remain in an inconsistent state when mysql is again restarted. What generally happens when a table gets corrupted is that the index file (.MYI) and the data file(.MYD) do not synchronize properly. So there might be some records in the data file for which index entries are not proper.

I have also seen cases where the table goes corrupt just like that. Which might be due to
a) the disk being bad
b) some bug in mysql leading to inconsistent reads and writes.

And one of the ways to repair corrupted mysql (MYISAM) tables is to use the myisamchk utility.

Generally i would do the following (sweet and simple)

mysql> CHECK TABLE ABC;
mysql> REPAIR TABLE ABC;

But there are cases when these queries fail to recover the table. I had a case where "REPAIR TABLE" used to give me "OK" but "Check table" used to give me "table marked as crashed". To recover from such cases the only method to use is the myisamchk utility.

./bin/myisamchk [options] ./data/<database_name>/<table_name>.MYI

A myisamchk --help would list down all the options.

Note: No other program should be accessing the table when myisamchk is repairing a table. I prefer to turn off the mysql on the machine where table is corrupted unless it is repaired.

The best option for full recovery is --safe-recover. Which can be used to repair tables which cannot be repaired by any other option. If the table cannot be repaired using this option then you might have to give up on the table.

But --safe-recover takes up a lot of time.

The way to speed up myisamchk is to increase the buffers that it uses to read, write and process.

--read_buffer_size and --write_buffer_size is used to read and write data from the table and to the temporary file.

--sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover

--key_buffer_size is used when you are checking the table with --extend-check or when the keys are repaired by inserting keys row by row into the table, that is using --safe-recover option. Repairing using the key buffer is slower and also takes much less disk space as compared to sorting.

To speed up table repairing use large values for key buffer and sort buffer. Both values can be made very large cause only one of them is used at a time.

Eg: myisamchk --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=8M --write_buffer_size=8M ./path/to/database/table.MYI

mysql storage engines

I would be discussing mysql storage engines/table types over here. MySQL supports pluggable storage engine architecture, which means that you can create your own storage engine and ask MySQL to store data for you using the sql query syntax defined in MySQL. Sounds cool right, but lets not dig that deep now. Lets first see what types of storage engines does MySQL provide and their benefits/drawbacks.

MYISAM Storage Engine

Based on the older IASM storage engine and currently the default storage engine in MySQL. So, if you create a table in MySQL and dont define a storage engine then, the table would use MYIASM as the storage engine. MySQL databases are generally directories and MYISAM tables are stored using 3 files -
.frm => the table format
.MYD => table data
.MYI => table indexes

Points to Remember:

• The tables are machine/os independent and can be shifted by simply copying the 3 files to another installation of mysql on another machine.


• Limit of 2^32 rows which can be increased to (2^32)^2.


• Maximum no of indexes per table = 64, which can be increased upto 128 by recompiling.


• Maximum no of columns per index = 16.


• Blob and TEXT columns can be indexed.


• Table size is almost unlimited (256TB). Though the table gives an error of "Table is full" when it reaches 4 GB. Then some parameters (MAX_ROWS & AVG_ROW_LENGTH) in the table definition needs to be changed to make the table expandable.


• Transactions are not supported
• 
  
• Fulltext indexing and searching is supported though it is very slow.


• Granuality of locking is TABLE. So if an insert and select happens one after another, the insert will lock the complete table and the select will have to wait till the lock is removed.


• The tables do get corrupted sometimes and to recover it, you would need to run the "REPAIR TABLE" command.


• Does not provide "Foreign key" usage.

InnoDB Storage Engine

InnoDB provides transactions and relational database support in MySQL - supports foreign keys and commit, rollback and crash recovery capabilities. InnoDB creates two log files namely "ib_logfile0" and "ib_logfile1" and a data file "ibdata1" in the MySQL data directory where it stores its tables. You can specify the path for creation of data file and the initial/max size of the file and also the no of data files to create. Placing different data files on different disks would of course lead to increased efficiency.

The table definitions are stored in database directory with a .frm extension whereas the data is stored in the "ibdata1" - tablespace. InnoDB has something known as "clustered index" where the data for the rows is stored. 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.

innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend:max:500M


Points to remember:

• Moving databases/tables is not easy. You need to take a dump and import it in the other installation of mysql


• Table cannot contain more than 1000 columns


• Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR columns with a combined size larger than 65535.


• combined size of the InnoDB log files must be less than 4GB


• Minimum tablespace size is 10MB. And maximum tablespace size is 64TB. This is also the maximum size for a table


• Does not support FULLTEXT indexes.


• InnoDB does not keep an internal count of rows in a table. So to process a select count(*) from tablename, InnoDB must scan the complete index which may take some time.


• Delete from tablename does not regenerate the table, but deletes rows one by one.


• Supports foreign keys constrains and transactions (commit and rollback)


• Has row level locking and also provides consistent non-locking read in select statements. Which provides better multi-user concurrency and performance.

Memory/Heap Storage Engine

As the name suggests, the tables are made in memory/RAM. Since the data for the tables are stored in memory they are lost if the mysql server is shut down or if it crashes, but the tables continue to exist since the table definition is a .frm file stored in the database directory. On a mysql restart, all data would have been lost and the tables would be empty. The indexes made on these tables are HASH indexes which makes access to these tables extremely fast. You can create temporary HEAP tables which can be used for high frequency reads & writes.

Points to remember:

• Can have 32 indexes per table and 16 columns per index.


• Type of indexing can be decided to be either HASH or BTREE.


• Using Hash indexes can lead to slower updates and deletes on the table if the degree of key duplication is high.


• Cannot contain BLOB or TEXT columns


• Server needs sufficient memory to maintain all MEMORY tables at one point of time


• To populate a MEMORY table when MySQL server is started, the --init-file option can be used


• It has table level locking


• Memory can be freed up by using either truncate/drop table command

Merge Storage Engine

Using MERGE storage engine, we can make a collection of identical MYISAM tables look and work like one. Each MERGE table is made up of two files
.frm - table definition/format
.MRG - names of tables that should be used as one
MERGEd tables support SELECT, DELETE, UPDATE and INSERT. On DROPPING a merge table, only the merge specification is dropped. The underlying tables are unaffected.

Points to remember:

• MERGE table cannot enforce uniqueness over the underlying table.


• MERGE table can be used to spilt big tables into parts and refer to them using the merged table name


• MERGE table does not have its own indexes, it uses indexes of its underlying tables.


• You cannot search the MERGE tables using FULLTEXT indexes. The FULLTEXT indexes can be created on the underlying MyIASM tables but they cannot be used through the MERGE table.


• Merge tables use more file descriptors. If 10 clients are using a MERGE table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors.


• Key reads are slower, since the on performing a SELECT, the MERGE storage engine has to issue a call on all the underlying tables one by one.


• INSERTS can be directed to either the first or the last table using INSERT_METHOD parameter during MERGE table creation.


• REPLACE does not work


• When you create a MERGE table, there is no check to ensure that the underlying tables exist and have identical structures.


• There is a limit of 2^32 rows to a MERGE table.

BDB(BerkeleyDB) Storage Engine

Sleepycat's Berkeley DB is a database engine that provides developers with fast, reliable, local persistence with zero administration (check www.sleepycat.com ). Mysql has been provided the BDB transactional storage engine. You need to have BDB installed on your system and compile MySQL with BDB to use the BDB storage engine. Each BDB table is stored in two files
.frm -> table definition/format
.db -> contains table data and indexes

Points to remember:

• BDB tables can have up to 31 indexes per table and 16 columns per index.


• MySQL needs a primary key for each BDB table. If not provided, MySQL would create a hidden primary key and use it.


• If all columns that you access in a BDB table are part of the same index or part of the primary key, MySQL can execute the query without having to access the actual row.


• Sequential scanning is slower for BDB tables than for MyISAM tables because the data in BDB tables is stored in B-trees and not in a separate data file.


• There are often holes in the BDB table to allow you to insert new rows in the middle of the index tree. This makes BDB tables somewhat larger than MyISAM tables.


• SELECT COUNT(*) FROM tablename is slow for BDB tables, because no row count is maintained in the table.


• Internal locking in BDB tables is done at the page level.


• Opening many BDB tables at the same time may be quite slow.


• Each BDB table stores in its .db file the path to the file as it was created. So it is not possible to move BDB table files from one database directory to another. To shift a BDB table mysqldump and import are required to be done.

FEDERATED Storage Engine

The federated storage engine can be used to access data in tables of remote databases locally. When a federated table is created a .frm file is created in the database directory. This file just contains the table definition. The actual table remains in the remote mysql database.
Flow of information in case of federated tables is as follows :

SQL calls issued locally
MySQL handler API (data in handler format)
MySQL client API (data converted to SQL calls)
Remote database -> MySQL client API
Convert result sets (if any) to handler format
Handler API -> Result rows or rows-affected count to local


The structure of the federated table must be exactly similar to the remote table structure except that the ENGINE should be FEDERATED and CONNECTION parameter should contain the connection string that indicates to the federated engine how to connect to the remote mysql server and access the table over there.

connection string format:
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Points to remember:

• 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.


• Transactions are not supported


• There is no way for the FEDERATED engine to know if the remote table has changed.


• The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements other than DROP TABLE. The DROP TABLE statement would drop only the local table and not the remote table.


• FEDERATED tables do not work with the query cache.

ARCHIVE Storage Engine

Used for storing large amounts of data without indexes in a very small footprint. When an archive table is created, following files are created in the database directory.
.frm -> table definition
.ARZ -> DATA file
.ARM -> METADATA file
The engine uses zlib lossless data compression (check www.zlib.net ).

The Archive table is almost 75% smaller in size then the MyISAM table and almost 83% smaller in size then the transactional InnoDB engine. Even after compression of MyISAM table, Archive tables are still 7% smaller. Insert operations on an Archive table is faster than that on a MyISAM table. If table scans on both tables are compared, table scan on an Archive table is found to be faster.

Points to remember:

• Supports only INSERTS and SELECTS


• Does NOT support DELETE, REPLACE and UPDATE


• Has row level locking


• Rows are compressed as they are inserted.


• A SELECT statement performs a complete table scan and uncompresses rows on demand. There is no ROW cache.

CSV Storage Engine

Stores data in text files using comma-separated values format. When a table is created 2 files are created in the database directory
.frm -> table definition
.CSV -> data file. A normal text file with comma separated values.

This engine does not support INDEXING. The data file (.csv) file can be read by spreadsheet applications like openoffice, MS office or star office.

In addition to these, there are two other storage engines:

EXAMPLE Storage engine: It is a stub engine and does nothing. You cannot perform anything on this engine except creation of tables. Tables are created as .frm files in the database directory. No data file is created and no data can be stored using this engine.
BLACKHOLE Storage engine: It acts as a black hole, accepts data and throws it away. It does not store anything. Selects always return an empty result set.

This summarizes the mysql storage engines. For details the following url can be referred.

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

The following link can be used to select a storage engine.

http://dev.mysql.com/doc/refman/5.1/en/pluggable-storage-choosing.html

Next ... lets c what i can write