Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 1: Comparing MySQL to IBM Informix Innovator-C Edition

If you are a MySQL user or are thinking about MySQL for future business requirements, there are strong reasons to consider IBM® Informix® Innovator-C Edition over MySQL. In this article, get an overview of both products, which highlights their key differences, and gain insight to their respective advantages and disadvantages for your business.

Share:

Sanjit Chakraborty (sanjitc@us.ibm.com), Advisory Software Engineer, IBM

Sanjit Chakraborty photoSanjit Chakraborty is a member of the Down System and Diagnostics Team for IBM Informix Technical Support, which is responsible for handling critical customer situations and developing support tools for use by the Technical Support Organization. Sanjit has worked more than 15 years in the information technology industry in various roles. He is an IBM Certified System Administrator for Informix and DB2, and a designated archiving subject matter expert. Sanjit developed several Informix features and Down System Support tools. He is also an author and technical reviewer of many technical articles, tutorials, and training course materials on various Informix topics.



03 February 2011

Also available in Chinese Portuguese

Introduction

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See Introduction to IBM Informix database software

The most common reasons cited for choosing MySQL as a database server include "it's easy to use" and "it's free." But, did you know that IBM Informix offers an edition with the same qualities and more? Informix Innovator-C Edition is an Informix database server providing a robust and powerful database environment, able to support the most demanding production workloads. This edition provides the most widely used data processing functionality, including distributed data replication, and options to achieve continuous data availability, such as clustering and the recently introduced Informix flexible grid.

Informix Innovator-C Edition offers a strong foundation for building on-demand solutions for high-volume online transaction processing (OLTP) and near-zero transaction response time, with high reliability, low cost of administration, and remote management of Informix servers. Informix Innovator-C Edition is "professional-grade," yet easy to use and runs on a wide variety of platforms, including the most popular versions of Linux® and Microsoft® Windows® platforms. The best part is that Informix Innovator-C Edition is absolutely free to download and deploy! If re-distribution is desired, a separate license agreement is required. Informix Innovator-C Edition is ready to download and use for development, testing, and end-user production workloads without any license fee.

From a feature perspective, MySQL 5.1 and Informix Innovator-C Edition are similar in many regards, including supported data-types, stored procedures, functions, high-availability solutions, hot backups, cold backups, full-text search, indexes, views, and more. Both MySQL and Informix Innovator-C Edition are similar in their dedicated server thread-based architecture. The two database systems support primary keys, along with key indices that allow you to speed up queries and constrain input. Informix remains ahead in terms of its cost-based optimizer and complex query optimization, as well as its high concurrency and utilization of large SMP boxes. Informix also remains ahead in areas such as clustering, data replication and grid-based computing.


Common noticeable differences

Apart from MySQL being an open-source product, what else distinguishes these two database products, and which one is right for you?

Open-source or proprietary

When it comes to these two databases, the differences begin with the open-source nature of MySQL as oppposed to the proprietary structure of Informix. MySQL is an extensible, open storage database engine. The root of MySQL lies in the open source tradition, where product development is a "do-it-yourself" and "roll-your-own" model, while Informix provides many named and proven paths to solve specific problems. However, both Informix and MySQL can integrate seamlessly with a number of programming languages and other web-based technologies.

Licensing

Contrary to popular belief, MySQL servers are not always free. Licensing fees are required for commercial use of MySQL. With Oracle's acquisition of MySQL, the future of MySQL's licensing fees is uncertain. Oracle recently warned customers of unspecified price increases in the near future as well as plans to eliminate the two lower-tier, less-expensive licensing plans for the database that were suited for smaller organizations with tighter budgets. Please refer to the Resources section for news on the possible MySQL license cost increase.

The Informix product family offers both "for-purchase" and "free" editions. These editions are tailored from a price and functionality perspective to meet a broad range of requirements for companies of all sizes. The Informix for-purchase editions offer some sub-capacity pricing models, giving you the flexibility to buy just what you need to support your data services requirements without having to worry about the total processing power of the hardware on which Informix is installed.

Regardless of the edition you use, Informix has a dynamic scalable architecture with unmatched performance, reliability, ease of use, and availability. In some cases, particularly with the free editions, there are restrictions on the breadth and depth of scalability and functionality available. However, the Informix Innovator-C Edition is a full-function Informix data server, providing a robust and powerful database environment and the most widely used data processing functionality required.


Technical differences

The open-source/proprietary argument alone is a leading reason as to why some users choose one database server over the other. However, there are a few differences from a technical aspect. For instance, MySQL doesn't offer full support for foreign keys, meaning it doesn't have all the relational features of Informix, which is a complete relational database. Some versions of MySQL also lack full support for stored procedures—the biggest disadvantage being the MyISAM system, which doesn't support transactions.

Performance

In terms of performance, Informix is the clear leader, mainly due to the high volume online transaction processing (OLTP), application integration, and its small footprint, using little disk space, memory, and CPU. With a proven history of stability and reliability, Informix is an obvious choice for retailers, banks, and communications companies.

MySQL's strength is that its "plug-in" architecture enables extensible features available from other database servers. And perhaps that is also the biggest disadvantage for MySQL. Most of these features are designed for some "jazzy" functionality, but often at the expense of other indispensable elements. The price here is complexity and the resources hog in the way of memory and storage, which often leads to poorer performance.

Security

MySQL provides simple and very effective security mechanisms, but it is widely accepted that MySQL's security model is not as elaborate as other popular databases. The default installation of MySQL, particularly the empty root password and the potential vulnerability to buffer overflow, makes the database server an easy target for attacks.

Informix integrates operating system security functions for user authentication and network permissions. The role separation divides the security duty among administrators. Auditing enables the database server to log sensitive operations performed by users and administrators for analysis and identifying system misuses. Discretionary access control (DAC) is the primary access control mechanism that enables access to SQL objects using privileges and roles.

Recovery

As far as recovery goes, Informix has a definite advantage over MySQL, which tends to fall a little short with its MyISAM system configuration. An Uninterruptible Power Supply (UPS) is mandatory with MyISAM if uninterrupted operation is required. In case of a power outage, you could risk the corruption and loss of critical data in a MySQL database. With Informix, data corruption is very unlikely since data regularly travels through various checkpoints. Additionally, Informix keeps track of the process for a successful recovery, even if the system unexpectedly shuts down.


Architectural overview and comparison

There are many significant differences between Informix Innovator-C Edition and MySQL. Let's start by looking at some of the fundamental architectural ways that these database servers are different or similar. (Note: At the time of writing this article, we used MySQL 5.1 and Informix Innovator-C Edition 11.7 versions for comparison purposes.)

  • Architectural model
    • Informix Innovator-C Edition and MySQL database server both use a dedicated server thread-based architecture.
  • Storage access
    • Informix Innovator-C Edition uses RSAM as the storage access method.
    • MySQL database uses pluggable storage engine architecture.
  • Table space model
    • Informix Innovator-C Edition table spaces can span multiple databases.
    • MySQL uses table space for InnoDB and NDB storage engines.
  • Schema support
    • Informix Innovator-C Edition has true schema support.
    • MySQL does not have true schema support. A schema in MySQL can be thought as a MySQL database.
  • Case-sensitivity for database object names
    • With Informix Innovator-C Edition, tables and columns are case-insensitive on both UNIX and Windows, and are stored in lower case.
    • MySQL uses case-sensitive names for database, tables, and columns on Linux.
  • Authentication
    • Informix Innovator-C Edition performs authentication using database users/roles. It provides access control and encryption levels for all database dimensions, including individual rows, columns, tables, or databases. You can also use various external security schemes, such as the operating system, PAM, Kerberos, Active Directory, and so on.
    • MySQL implements authentication at the database level, and passwords are encrypted.
  • Instance architecture
    • Informix Innovator-C Edition provides the flexibility of many database server instances on a single machine, and one instance can manage different databases.
    • A MySQL instance manages different databases, and there could be many MySQL instances on the same machine.

Figure 1 and Figure 2 depict architectural diagrams of MySQL and Informix Innovator-C Edition. We drew the architectural diagrams for MySQL to the best of our ability after reading through several documents. If you find a discrepancy, please let us know and we will correct it.

MySQL

MySQL and Informix Innovator-C Edition both use a thread-based architecture. A MySQL instance, as you see in Figure 1, can manage many databases. There is a common system catalog named INFORMATION_SCHEMA shared by all MySQL databases in an instance.

An instance in Informix Innovator-C Edition is similar, but it includes its system catalog, storage spaces, buffer pools, threads, and so on.

A single instance with many databases can be visualized in MySQL and Informix Innovator-C Edition. When you have several MySQL instances running on a machine and each instance managing several databases, you can migrate each MySQL instance as an Informix Innovator-C Edition instance.

One of the interesting features of MySQL is pluggable storage engines. You can choose either MyISAM, InnoDB, Archive, Federated, Memory, Merge, Cluster, NDB, or Custom storage engine. Each storage engine has different characteristics, and you can choose a particular storage engine based on specific requirements. For comparison purposes, we find InnoDB to be closest to a relational database.

Figure 1. MySQL architecture and processes overview
Figure 1 is a high-level design of MySQL architecture and processes. Each client application's request is handled by a mysqld thread on the MySQL server side. Depending on the operation, the mysqld thread uses a different server process to retrieve data from the database using memory.

A MySQL server process (mysqld) can create a number of threads:

  • A global thread (per server process) is responsible for creating and managing each user connection thread.
  • A thread is created to handle each new user connection.
  • Each connection thread also performs authentication and query execution.
  • On Windows, there is a named pipe handler thread that does the same work as the connection thread for named pipe connection requests.
  • A signal thread handles alarms and forces timeouts on connections that have been idle too long.
  • A thread is allocated to handle shutdown events.
  • There are threads for handling synchronization of master and slave servers for replication.
  • Threads are used for table flushing, maintenance tasks, and so on.

MySQL uses a data cache, record cache, key cache, table cache, hostname cache, and privilege cache for the caching and retrieval of different types of data used by all the threads executing within the server process.

In addition, the MySQL main process (mysqld) has threads to handle database management activities such as backup, restore, concurrency control, and so on.

Informix Innovator-C Edition

Figure 2 provides a visual overview of the Informix Innovator-C Edition architecture. The Informix Innovator-C Edition database server architecture is based on advanced technology that efficiently uses virtually all of today's hardware and software resources. Called the Dynamic Scalable Architecture (DSA), it fully exploits the processing power available in SMP environments by performing similar types of database activities, such as I/O, complex queries, index builds, log recovery, inserts, and backups/restores. The DSA design architecture includes built-in multi-threading capabilities, dynamic and self-tuning shared memory components, and intelligent logical data storage capabilities, supporting the most efficient use of all available system resources.

An Informix Innovator-C Edition process is called a virtual processor (VP) because the way that it functions is similar to the way that a CPU functions in a computer. Just as a CPU runs multiple operating system processes to service multiple users, an Informix virtual processor runs multiple threads to service multiple SQL client applications.

A virtual processor is a process that the operating system schedules for processing. Informix Innovator-C Edition virtual processors are multi-threaded because they run multiple concurrent threads. Figure 2 illustrates the relationship of client applications to virtual processors. A small number of virtual processors serve a much larger number of client applications or queries.

Compared to a database server process that services a single client application, the dynamic, multi-threaded nature of Informix provides the following advantages:

  • Virtual processors can share processing.
  • Virtual processors save memory and resources.
  • Virtual processors can perform parallel processing.
  • You can start additional virtual processors and terminate active CPU virtual processors while the database server is running.
  • You can bind virtual processors to CPUs.

A virtual processor runs threads on behalf of SQL client applications (session threads) and also to satisfy internal requirements (internal threads). In most cases, for each connection by a client application, Informix runs one sqlexec thread. Informix runs internal threads to accomplish, among other things, database I/O, logging I/O, page cleaning, and administration tasks.

A user thread is a database server thread that services requests from client applications. User threads include session threads, called sqlexec threads, which are the primary threads that the database server runs to service client applications.

Figure 2. Informix Innovator-C Edition architecture and processes overview
Figure 2 is a high-level design of Informix architecture and processes. Each client application's request is handled by a sqlexec thread onthe Informix server side. sqlexec threads use different types of VPs, depending on the kind of operation. CPU, AIO, ADM, Crypto, and Java are some examples of VPs. The VPs access data from different storage spaces using shared memory segments.

Comparison on data size limits

Table 1 compares MySQL and Informix Innovator-C Edition data size limits. This is not an all-inclusive list, but some of the most noticeable limitations.

Table 1. MySQL and Informix data size limits
MySQLInformix Innovator-C Edition
Max database sizeUnlimited128PB
Max table sizeMyISAM storage limits: 256TB
Innodb storage limits: 64TB
128PB
Max row size64KB¹32765 bytes
(exclusive of large objects)
Max columns per row4KB²32765 bytes
Max blob/clob size4GB4TB
Max CHAR size64KB (text)32765 bytes
Max NUMBER size64 bytes10^32
Min DATE value100012/31/1900
Max DATE value999912/31/9999

1. InnoDB is limited to 8000 bytes (excluding VARBINARY, VARCHAR, BLOB, or TEXT columns).
2. InnoDB is limited to 1000 columns.


Conclusion

This article has described many architectural differences between MySQL and Informix, and has shown some of the differences between the two database technologies. Part 2 of this series will be a tutorial, walking you through the actual process of migration. With proper planning and familiarization with Informix, you can be up and running on a sophisticated, scalable, and world class database platform: enjoy the benefits of Informix!

Resources

Learn

Get products and technologies

  • IBM Migration Toolkit: Learn more about or get this easy-to-use tool that allows you to migrate your data from a wide variety of source databases to either DB2 or Informix, regardless of platform.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

  • Participate in developerWorks blogs and get involved in the developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=620642
ArticleTitle=Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 1: Comparing MySQL to IBM Informix Innovator-C Edition
publish-date=02032011