Logic in DB
MS SQL 2005
MS SQL 2005
MySQL 5.0 Adds Features for Enterprise Developers and DBAs
by Ken North
Baseball legend Satchel Paige is famous for having said "Don't look back, something might be gaining on you." Companies selling a commercial SQL database management system (DBMS) know its MySQL that's gaining on them. With an already large installed base, MySQL is set to attract new users because of the feature set of version 5.0. It includes capabilities for which developers have often turned to commercial SQL products.
The purposes for which we use personal, mobile, workgroup, departmental, enterprise and web databases are diverse. Application requirements are a primary determinant of the capacity and features we need from an SQL DBMS. For example, a high-volume transaction processing web site places greater demands on a database than a contact list manager for laptops and small business servers.
A Web Techniques magazine article, "Web Databases: Fun with Guests or Risky Business?" discussed features that characterize an industrial-grade SQL DBMS. It explained SQL security and mission-critical databases, defined as
"A database is mission critical if its lack of data integrity has serious consequences, such as causing the loss of customers or even lives."
Maintaining data integrity is implicit -- that's a prime directive for a DBMS. The article explained other features that enterprise developers look for in an SQL platform:
... mission-critical applications require features such as intrinsic security, transaction journaling, concurrency controls and the ability to enforce data integrity constraints. Without those features, you do not have secure, robust databases. Connecting a database to a Web server adds other requirements, such as a multithreaded architecture and the ability to do database backups without taking the server down.
Freeware and PC DBMSs are suitable for certain classes of applications, but not for high-volume Web sites and mission-critical databases. In any case, don't bet your business, or lives, on such software unless you have the source code and the expertise to understand and repair it.
Since that article appeared in print, improvements to MySQL have removed the "not ready for prime time" label. Features described in that article are now available to MySQL users:
- concurrency control, locking, SQL standard isolation levels
- intrinsic security
- integrity constraints
- thread-based memory allocation.
Fast, reliable data access for ODBC, JDBC, ADO.NET and XML
MySQL uses separate threads to handle TCP/IP and named pipes connections, authentication, signaling, alarms and replication. The combination of threaded architecture and MySQL clustering provides powerful parallel processing capabilities. MySQL can process transactions in parallel with separate connections on separate processors using separate threads.
A decade of development has moved MySQL out of the bare-bones DBMS category, enlarged its user base, and turned MySQL AB into a profitable company. One of the important milestones was integration of the InnoDB engine with MySQL 4.0. That upgrade gave MySQL multiple tablespaces, tables greater than 4GB and support for transaction processing. Other enhancements included OpenGIS spatial data types and hot backups. The latter enables a DBA to perform a backup without taking the DBMS offline. Hot backup software is available as a commercial add-on for databases using the InnoDB storage engine.
MySQL 5.0, the newest version, is a major milestone. There have been enhancements to the tool sets, storage engines, types and metadata. MySQL 5.0 includes features enterprise developers have come to expect from commercial SQL products.
- capacity for very large databases
- stored procedures
- named-updateable views
- server-side cursors
- type enhancements
- standards-compliant metadata (INFORMATION_SCHEMA)
- XA-style distributed transactions
- hot backups.
MySQL has a demonstrated capacity for managing very large databases. Mytrix, Inc. maintains an extensive collection of Internet statistics in a one terabyte (1 TB) data warehouse that contains 20 billion rows of data. Sabre Holdings runs the oldest and largest online travel reservation system. It replicates 10-60 gigabytes per day from its master database to a MySQL server farm. The MySQL databases are used to support a shopping application that can accommodate a million fare changes per day.
Stored procedures and triggers are a form of logic in the database. They enable developers to partition application logic and use the database as an active enforcer of data integrity rules. Multiprocessor architectures, server-side cursors, and stored procedures deliver performance. They've been important features of commercial SQL servers for more than a decade.
1 2 >>
Database Server Watch SQL Summit Home Page Articles
© 2005, Ken North Computing LLC, All rights reserved.