Performance Comparison
The Transaction Processing Performance Council is an independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy. The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.
As of June 2006, SQL Server 2000 holds the third position in the OLTP Test in price by performance results and Microsoft SQL Server 2005×64 holds the first place. However MySQL does not participate in these tests, so it is difficult to make a head on comparison using TPC benchmarks.
In some benchmarks, MySQL has proven to be faster in its basic table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. Additional performance gains can be had by using MySQL on a 64-bit processor, because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal and Slashdot use MySQL as a back-end database.
It’s worth noting that both systems will work well within either .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.
Given the lack of a standard benchmark upon which to compare the two databases, it’s not possible to conclusively rule on which database has the upper hand in performance.
Features Comparison
Regardless of other differences, the SQL Server 2000 and MySQL have a widely different feature set.
SQL Server 2000 and MySQL v5.0 limits
Although many of the limits placed by the database are for the purists, for example, maximum length of the column name would rarely be a problem.
Some of these built-in limitations may be crucial for database design. Varchar size especially can be problematic, as often stored data such as comments or articles may be forced into BLOBs or TEXT columns. Total row size is also important for this reason.
| Feature | SQL Server 2000 | MySQL v5.0 |
| Column name length | 128 | 8 |
| Index name length | 128 | 8 |
| Table name length | 128 | 8 |
| Max indexes per table | 250 | 8 |
| Index length | 900 | 1024 |
| Max index column length | 900 | 255 |
| Columns per index | 16 | 16 |
| Max char size | 8000 | 255 |
| Max varchar size | 8000 | 65532 |
| Max blob size | 2147483647 | 2147483647 |
| Max columns in GROUP BY | Limited by number of bytes (8060) | 64 |
| Max columns in ORDER BY | Limited by number of bytes (8060) | 64 |
| Tables per SELECT statement | 256 | 31 |
| Max columns per table | 1024 | 3398 |
| Max table row length | 8036 | 65534 |
| Longest SQL statement | 16777216 | 1048574 |
| Constant string size in SELECT | 16777207 | 1048565 |
Data Storage
SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL extensible, open storage engine. Its Sybase-derived database engine boasts of an adaptive algorithm that does most of the tuning that earlier needed to done manually. While SQL Server 2000 chooses the strategy of a single engine doing all the work, MySQL supports pluggable storage engines that can be chosen depending upon the facilities needed.
MySQL’s strategy is useful for various types of database use: quick read access to data without the need for transactional overhead is provided by the built in MyISAM engine, whereas InnoDB, a third party database engine owned by Oracle and licensed under the GPL is most often used for transactions and other features at the cost of some read performance. The MEMORY storage engine creates tables with contents that are stored in memory, and is useful for embedded database applications. NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers.
The FEDERATED storage engine stores data in a remote database. In its current release, it works with MySQL only but its future releases will be able to connect to other data sources using other driver or client connection methods.
InnoDB engine has the most advanced database feature set. The disadvantage to MySQL’s pluggable database engine scheme is that care must be given when selecting the engine to use when designing the database before use.
MySQL database engine feature comparison| MyISAM | InnoDB | MEMORY | NDB | |
| Multi-statement transactions, ROLLBACK | - | X | - | X |
| Foreign key constraints | - | X | - | - |
| Locking level | table | row | table | row |
| BTREE indexes | X | X | - | X |
| FULLTEXT indexes | X | - | - | - |
| HASH lookups | - | X | X | X |
| Other in-memory tree-based index | - | - | 4.1.0 | - |
| GIS, RTREE indexes | 4.1.0 | - | - | - |
| Unicode | 4.1.0 | 4.1.2 | - | - |
| Merge (union views) | X | - | - | - |
| Compress read-only storage | X | - | - | - |
| Relative disk use | low | high | - | low |
| Relative memory use | low | high | low | high |
Database Features
One of the critical features of any database engine is data integrity. ACID (Atomic, Consistent, Isolated, and Durable) compliance is a qualification that assures data integrity. ACID essentially means that when a transaction is performed within a database, either the whole transaction is successful and the information is written to the database, or nothing is written. Both SQL Server 2000 and MySQL supports ACID-compliant transaction functionality. SQL Server locks are dynamically applied at various levels of granularity, in order to select the least restrictive lock required for the transaction.
InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.
With the new release of MySQL 5.0, it has now incorporated several features that were missing earlier, and thus some of the reasons that caused you to choose SQL Server 2000 are no longer valid, for example, the need of Views, Cursors and Procedures. One thing now lacking is the full support for triggers. Another thing in which MySQL lags behind is XML support, and with the release of Yukon, this is going to look like a big gap in functionality.
| Feature | | SQL Server 2000 | MySQL v5.0 |
| ACID | | Yes | Yes |
| Referential Integrity | | Yes | Yes |
| Transactions | | Yes | Yes |
| Temporary Table | Yes | Yes | |
| Views | | Indexed views | Updateable views |
| Indexes | | ||
| | R-/R+ tree | ? | MyISAM only |
| Hash | ? | MEMORY only | |
| Expression | ? | No | |
| Partial | ? | No | |
| Reverse | ? | No | |
| Bitmap | ? | No | |
| Cursor | Yes | Yes | |
| Trigger | Yes | Rudimentary | |
| Function | Yes | Yes | |
| Procedure | Yes | Yes | |
| External routine | Yes | Yes | |
| Partitioning | | ||
| | Range | Yes | NDB only |
| | Hash | No | No |
| Composite | No | No | |
| | List | No | No |
| XML support | Yes | No | |
| Unicode | Yes | Yes |
Database Warehousing
The data warehouses or data marts can be used for sophisticated enterprise intelligence systems that process queries required to discover trends and analyze critical factors. These systems are called online analytical processing (OLAP) systems. The data in data warehouses and data marts is organized differently than in traditional transaction processing databases.
While SQL Server 2000 provides a lot of tools for creating and managing data warehouses, MySQL does not have any significant support for it. Data Transformation Services (DTS) provides a set of services used to build a data warehouse or data mart. Analysis Services is an excellent tool for multidimensional analysis of data in SQL Server 2000 databases. Analysis Services supports multidimensional queries against cubes with hundreds of millions of dimensions and you can even control cube security down to the level of cells and members. Data mining allows you to define models containing grouping and predictive rules that can be applied to data in either a relational database or multi-dimensional OLAP cubes. These predictive models are then used to automatically perform sophisticated analysis of the data to find trends.
Application Development
SQL Server 2000 and MySQL both support ODBC and JDBC for network connectivity, as well as native database access methods. These native methods provide access via the network in both plain text methods and, for a higher level of security, SSL-encrypted methods.
Another important part of database interface methods is authentication for the database. MySQL uses a simple method to store all of its authentication information inside a table. When users attempt to access a database, MySQL compares their credentials against this database, verifying from which machines the users can connect and to what resources they have access. SQL Server 2000 also has integrated Windows authentication.
A number of programming methods also provide ways to access these databases. Both SQL Server 2000 and MySQL support access via C/C++ , Java, Perl, Python, and PHP. One thing that differentiates SQL Server 2000 is its XML capabilities. Using SQLXML package with SQL Server 2000, it is possible to query relational data using XQuery and even expose stored procedures as web services.
Installation
Hardware and Software Requirements
Installation of Microsoft SQL Server requires a Windows operating system. So, if you need to run the database on a Linux box, the comparison is clearly in favor of MySQL. MySQL has been ported to all major operating systems and can be ported to any os that has a C++ compiler and a working implementation of POSIX threads. Using GNU autoconf, MySQL happily compiles on various flavors of UNIX, Windows, Linux and Mac OS X.
Hardware cost is not as much of an issue as it once was, with RAM and hard disk space costs being low. But for the hardware conscious, SQL server can look like a hog compared to MySQL. While SQL server requires a minimum of 128 MB of RAM for reasonable performance, MySQL can easily run on a Linux with as little as 32 MB of RAM, this is likely that is the reason that the MySQL installation manual doesn’t bother specifying hardware requirements.
Although it is not too difficult to compile the MySQL source code, a ready installer is always welcome to get you up and running quickly. With the release of MySQL 4.1.5, MySQL AB has introduced an installer for the Windows version of MySQL, combined with a new GUI Configuration Wizard. This combination automatically installs MySQL, creates an option file, starts the server, and secures the default user accounts.
| Hardware | SQL Server 2000 | MySQL 5.0 |
| Operating system | Windows XP, Windows 2000 Windows 9x, Me, NT, 2000, XP, and 2003 | Linux 2.0+ Mac OSX AIX 4.x, 5.x BSDI 3.0, 3.1 and 4.x FreeBSD 3.x, 4.x, 5.x OpenBSD 2.5+ Digital Unix 4.x HP-UX 10.20, 11.x NetBSD 1.3/1.4 Intel, 1.3 Alpha SCO Open Server, UnixWare 7.1 SGI Irix 6.5 Solaris 2.5 |
| Memory | Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB or more recommended Standard Edition and Developer Edition: 64 MB minimum Personal Edition and Desktop Engine (MSDE 2000): 128 MB minimum on Windows XP 64 MB minimum on Windows 2000 32 MB minimum on other Windows | 32 MB of RAM |
| Hard disk space | Enterprise, Enterprise Evaluation, Standard, Developer, and Personal Editions require: 95 to 270 MB of available hard disk space for the database engine; 250 MB for a typical installation. 50 MB of available hard disk space for a minimum installation of Analysis Services; 130 MB for a typical installation. 80 MB of available hard disk space for English Query. Desktop Engine (MSDE 2000): 44 MB minimum | 60 to 85 MB depending on the components and operating system; 200 MB recommended for Windows. |
Installation
Microsoft products have always been known for the painless and quick installation through intuitive wizards. MySQL, despite being an open source product is no less, and you can expect the same kind of breezy installation. The installer will even create a Windows service for the MySQL database.
Administration and Maintenance
MySQL provides two types of backup, one where the database dump is taken and another where it emits all the SQL statements required to create the database. SQL Server 2000 only provides the first option. When it comes to hot backups, or backing up your database without shutting it down, both database solutions have methods for doing so. MySQL supports incremental backup strategy for minimizing the amount of time taken if an existing database backup is present.
SQL Server 2000 failover clustering provides high availability support. For example, during an operating system failure or a planned upgrade, you can configure one failover cluster to fail over to any other node in the failover cluster configuration. In this way, you minimize system downtime, thus providing high server availability. But for this, you will need to buy the Enterprise version. MySQL also provides clustering through its NDB database engine, but it is slightly difficult to configure.
MySQL supports one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server writes updates to its binary log files and the slave servers periodically connect to get updates. SQL Server 2000 offers far more facilities for replication. It offers three choices – Snapshot, Transactional and Merge. While the transactional facility is similar to the MySQL master slave replication, Snapshot is useful where data has changed a lot and so the entire snapshot is sent to the slave. Using Merge replication, two SQL servers can send updates to each other and are a perfect choice for geographically separated SQL servers.
Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer’s directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.
Stability is one area where MySQL, in its MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints and SQL Server remembers where it was in the process even if it happens to be shut down without warning.| | SQL Server 2000 | MySQL v5.0 |
| Hot backups | Yes | Yes |
| Replication | Snapshot, Transactional, Merge | One-way only |
| Clustering | Yes | Yes |
| Security features | High | High |
| Locking and concurrency support | Fully automated | Row-locking (InnoDB) |
| Stability | High | High |
| GUI Administration Tools | Yes | Yes (download) |
Price
When it comes to licensing costs, MySQL definitely has an edge as it is free and open source software licensed under the GPL. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free “development use only” SQL Server license.
SQL Server 2000 is currently available under two licensing options:- Processor license
- Server/per-seat client access license (CAL)
The Server/per-seat client access license (CAL) requires a license for the server and the licenses for each client device. You can use this licensing option when the customers do not need access beyond the firewall and the number of clients is low (for example, 10-20 users for SQL Server 2000 Standard Edition or 30-40 users for SQL Server 2000 Enterprise Edition).
Lately, Microsoft Database Engine (MSDE) which is actually a scaled down version of SQL Server has been made available as a free download. It is a very attractive choice if you need an embedded database for your windows applications, but is not recommended for use as a server in production environments.
| Licensing Options | SQL Server 2000 Standard Edition | SQL Server 2000 Enterprise Edition |
| Processor | $4,999 per processor | $19,999 per processor |
| Server/Per-Seat CAL | with 5 CAL – $1,489 with 10 CAL – $2,249 | with 25 CAL – $11,099 |
The MySQL version 5.0 is Dual Licensed. Users can choose to use the MySQL software as an Open Source/Free Software product under the terms of the GNU General Public License or can purchase a standard commercial license from MySQL AB.
The GPL license grants the user a right to use the database at no cost with the condition that any changes made to the source code of the database would have to be made available under GPL. Since most of the applications simply use the database, it is usually a zero-cost alternative.
The MySQL v5.0 Server commercial license is per database server (single installed MySQL binary). The price comparisons below were based on the MySQL Licensing Prices from MySQL AB.
| Number of licenses | Price per copy (EUR) | Price per copy (USD) |
| 1..9 | 440 | 495 |
| 10..49 | 315 | 360 |
| 50..99 | 255 | 290 |
| 100..249 | 195 | 220 |
| 250..499 | 155 | 175 |
Overall, MySQL has the edge in price as it is free, and performance can’t be conclusively shown to be any worse than MS SQL—it is also used extensively for high profile web applications such as various Yahoo properties and Google’s Adwords. Certain advanced features are not bundled with MySQL, but for basic simple development without licensing cost, MySQL can be seen to have the edge.
MS SQL 2000 and now 2005, are geared towards Microsoft style development on Windows Servers. Installation and configuration may be easier in MS SQL, with advanced database features present by default. MS SQL also implements features not found in any MySQL database engine, and is not overly high in license costs compared to some high end databases. For certain mid-level uses on Windows Servers, MS SQL probably wins out.
Both databases have a wide traction in the market, but being an open source project, MySQL community is somewhat more open, and being completely free, MySQL likely has a broader base of support.
No comments:
Post a Comment