Convert Typo InnoDB > MyISAM

Simple script to convert your Typo database tables from InnoDB to MyISAM format.

use mytypo_production;
ALTER TABLE articles_tags ENGINE = MyISAM;
ALTER TABLE blacklist_patterns ENGINE = MyISAM;
ALTER TABLE blogs ENGINE = MyISAM;
ALTER TABLE categories ENGINE = MyISAM;
ALTER TABLE categorizations ENGINE = MyISAM;
ALTER TABLE contents ENGINE = MyISAM;
ALTER TABLE feedback ENGINE = MyISAM;
ALTER TABLE notifications ENGINE = MyISAM;
ALTER TABLE page_caches ENGINE = MyISAM;
ALTER TABLE pings ENGINE = MyISAM;
ALTER TABLE redirects ENGINE = MyISAM;
ALTER TABLE resources ENGINE = MyISAM;
ALTER TABLE schema_info ENGINE = MyISAM;
ALTER TABLE sessions ENGINE = MyISAM;
ALTER TABLE sidebars ENGINE = MyISAM;
ALTER TABLE sitealizer ENGINE = MyISAM;
ALTER TABLE tags ENGINE = MyISAM;
ALTER TABLE text_filters ENGINE = MyISAM;
ALTER TABLE triggers ENGINE = MyISAM;
ALTER TABLE users ENGINE = MyISAM;

Now What?

Change mytypo_production to your Typo database's name.

Save it as convert.sql

Run it via,

mysql -u root
source convert.sql

You will get a load of check messages and hopefully no errors or warnings. If all goes ok you should now be over to MyISAM.

Convert Typo MyISAM > InnoDB

use mytypo_production;
ALTER TABLE articles_tags ENGINE = INNODB;
ALTER TABLE blacklist_patterns ENGINE = INNODB;
ALTER TABLE blogs ENGINE = INNODB;
ALTER TABLE categories ENGINE = INNODB;
ALTER TABLE categorizations ENGINE = INNODB;
ALTER TABLE contents ENGINE = INNODB;
ALTER TABLE feedback ENGINE = INNODB;
ALTER TABLE notifications ENGINE = INNODB;
ALTER TABLE page_caches ENGINE = INNODB;
ALTER TABLE pings ENGINE = INNODB;
ALTER TABLE redirects ENGINE = INNODB;
ALTER TABLE resources ENGINE = INNODB;
ALTER TABLE schema_info ENGINE = INNODB;
ALTER TABLE sessions ENGINE = INNODB;
ALTER TABLE sidebars ENGINE = INNODB;
ALTER TABLE sitealizer ENGINE = INNODB;
ALTER TABLE tags ENGINE = INNODB;
ALTER TABLE text_filters ENGINE = INNODB;
ALTER TABLE triggers ENGINE = INNODB;
ALTER TABLE users ENGINE = INNODB;

Also...

Also make sure you Tune your MySQL Install...

• Tuning MySQL

And optimize the Tables,

• Typo Optimization

Took some doing to make the install cleaner, but yes; this site and my other baby app atompad are now proudly running under NGINX.

NGINX is an extremely lightweight and very memory efficient webserver for Linux, and is absolutely ideal for Ruby on Rails applications; plus it isn't that hard to setup.

So glad I made the move away from Apache, it's good but not ideal for Rails apps.

Along with this change I've move my XEN box onto Ubuntu Gutsy 7.10, MySQL 5 (InnoDB), 2 efficient clusters, load balancer, and upgraded Rails to the fresh new 2.0 release out today.

So far all running well and much better than before.

I'll post what I did to make the move in a follow-up article but for now, I'm gonna put my feet up and watch Heroes.

All the best,

John.

MySQL is a very fast and very flexible multi-user / multi-threaded database system used most popularly for Web Applications and offers the simple startup a good platform to build from at very little cost.

Despite other more pedigree Database Systems (SQL Server, Oracle), it offers some fairly advanced functions to the developer and has gained a good reputation for it’s large support base and adherence to standards.

Supporting the ANSI 99 query set, stored prodedures, cursors, triggers, updatable views, text indexing, SSL and even Database Clustering, it’s grown up a bit from it’s relatively meager beginnings.

It can be expanded upon and can quite successfully grow as your company grows with famous users like YouTube , Adobe and Flickr most of which handling millions of transactions within a single hour. Okay the unicode support isn’t completely there, but it’s on the way and release 6 isn’t that far off so watch this space.

MySQL Storage Engines

On top of all this, MySQL offers different ways to handle and manage your data on top of the standard collation types. Allowing you to basically choose an engine that closely fits your company needs.

So if you’re a large archive house that only needs to query old records you can opt for the ARCHIVE engine or if your accessing many remote sources you can choose the FEDERATED engine. The key here is that each one is designed around a specific daily function, allowing you to optimize your hardware for the best performance.

Here’s a look at what’s offered,

MyISAM

(default storage engine, best performance overall)

• Default install: Yes
• Data limitations: None
• Index limitations: 64 indexes per table (32 pre 4.1.2); max 16 columns per index
• Transaction support: No
• Locking level: Table
  
  h4. MERGE

(allows to combine a number of identical tables into one)

• Data limitations: Underlying tables must be MyISAM
• Index limitations: N/A
• Transaction support: No
• Locking level: Table
  
  h4. MEMORY

(stores all data in memory, if power failure, you lose it all good for quick access, calculations, rapid temp tables)

• Data limitations: BLOB and TEXT types not supported
• Index limitations: None
• Transaction support: No
• Locking level: Table
  
  h4. FEDERATED

(allows remote data access, combining many sources into one system)

• Data limitations: Limited by remote database
• Index limitations: N/A
• Transaction support: No
• Locking level: No
  
  h4. ARCHIVE

(insert & select only supported, compressed, good for logs, old data)

• Data limitations: Data can only be inserted (no updates)
• Index limitations: N/A
• Transaction support: No
• Locking level: N/A
  
  h4. CSV

(stores as comma-separated data, good for data transport)

• Data limitations: None
• Index limitations: Indexing is not supported
• Transaction support: No
• Locking level: Table

BLACKHOLE

(allows you to test out possible data structures, schemas)

• Data limitations: No data is stored, but statements are written to the binary log (and therefore distributed to slave databases)
• Index limitations: N/A
• Transaction support: No
• Locking level: N/A
  
  h4. ISAM

(original engine, included only for backwards compatibility)

• Data limitations: Limited maximum database size (4GB)
• Index limitations: Maximum 16 indexes per table, 16 parts per key
• Transaction support: No
• Locking level: Table
  
  h4. BERKELEY DB

(hash-based storage engine, very quick to access & recover great for accessing data that does not change much, due to it’s table locking)

• Data limitations: None
• Index limitations: Max 31 indexes per table, 16 columns per index; max key size 1024 bytes
• Transaction support: Yes
• Locking level: Page (8192 bytes)
  
  h4. INNO DB

(based on myisam + adds database cacheing & indexing, in memory and disk, very fast recovery, less table-locking issues, speeds up recovery & storage. there is a management overhead with InnoDB that requires your system to be optimised to use it but great if you go that extra mile)

• Data limitations: None
• Index limitations: None
• Transaction support: Yes (ACID compliant)
• Locking level: Row

EXAMPLE

(used as a blueprint to create your own storage engine)

Uses

Having a lot of different storage engines available is a major bonus, but just because I’m nice I’ll let you into what I use. It’s really a mixture but mainly InnoDB (major apps) because it’s less limiting and I like to have total control, but I do have a couple of sites I run for friend’s using the Berkeley DB engine (blogs).

Enjoy,