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.
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.
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.
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.
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.
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.
- 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 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
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
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
|MySQL||Informix Innovator-C Edition|
|Max database size||Unlimited||128PB|
|Max table size||MyISAM storage limits: 256TB|
Innodb storage limits: 64TB
|Max row size||64KB¹||32765 bytes|
(exclusive of large objects)
|Max columns per row||4KB²||32765 bytes|
|Max blob/clob size||4GB||4TB|
|Max CHAR size||64KB (text)||32765 bytes|
|Max NUMBER size||64 bytes||10^32|
|Min DATE value||1000||12/31/1900|
|Max DATE value||9999||12/31/9999|
1. InnoDB is limited to 8000 bytes (excluding VARBINARY, VARCHAR, BLOB, or
2. InnoDB is limited to 1000 columns.
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!
- Informix Innovator-C Edition: Learn more about Informix Innovator-C Edition.
- IBM Informix Dynamic Server v11.70 Information Center: Learn more about Informix. Find the information that you need to use Informix products and features.
- "Informix Dynamic Server 11: Advanced Functionality for Modern Business" (IBM Redbooks, October 2007): Get an overview of Informix Dynamic Server 11.
- "Migrating from Microsoft SQL Server to IBM Informix" (IBM Redbooks, July 2010): Understand the considerations and explore a methodology for transitioning from Microsoft SQL Server 2008 to the Informix Dynamic Server.
- "MySQL Restrictions and Limitations" (Oracle, 2011): Understand the restrictions that apply to the use of MySQL features, such as subqueries or views.
- Comparison of relational database management system (Wikipedia): Find general and technical information for a number of relational database management systems.
- "Migrate from MySQL or PostgreSQL to DB2 Express-C" (developerWorks, June 2006): Move to DB2 in three easy steps.
- "Oracle to eliminate budget plans in MySQL license hike" (InfoWorld, October 2010): Learn more about the potential pricing increase of licensing fees.
- "IBM Migration Toolkit support for migrating data from MySQL to DB2 and Informix" (developerWorks, July 2008): Learn how this toolkit can help you migrate DDL and DML statements, and see how to map data types.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
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.
- 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.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.