Chapter 5. mSQL
Database concepts and design are all extremely important, but you probably want to dive in and work with mSQL or MySQL. Chances are that you have chosen one database engine or the other to serve your needs. Perhaps, however, you are looking to this book to help you with that decision. In this chapter, we start diving into the details with mSQL. If you are already a committed MySQL user, you can skip over this chapter. If, on the other hand, you are a committed mSQL user or you want to learn about both database engines, this chapter is the place to start.
mSQL is the relational database management system (RDBMS) that initiated the era of cheap SQL database engines for small-to-medium sized database needs. Its small footprint, impressive speed, and short learning curve turned it into an excellent database choice for the growing population of start-up web developers who do not want to spend the time to become expert database programmers. The author of the mSQL database engine intentionally included all of these advantages when he set out to create a database product that could fill a gaping hole in database products.
5.1. Design
David Hughes had three specific design goals in mind for mSQL:
mSQL had to be fast.
mSQL had to have a small footprint.
mSQL had to be able to handle multiple simultaneous connections.
Speed was the primary motivation behind mSQL. Because most commercial SQL servers try to implement the full SQL2 specification in addition to their own proprietary extensions, they pay for that support in terms of performance and footprint. mSQL, however, sacrifices some of the more advanced features of the commercial database engines for speed. Minerva needed to be able to run many simple SQL queries quickly. mSQL does exactly that.
Speed and footprint go hand-in-hand. As Hughes discovered, if you start from the ground up and implement only the necessary functionality, you can design an SQL server that does not take up so many resources that it must be run on a machine by itself in order to be useful. As a result, mSQL has a large amount of the functionality of the major database engines using a fraction of the resources.
The speed and footprint improvements of mSQL would have been enough to make it a viable replacement for Postgres on the Minerva project. Hughes, however, also wanted to change the behavior that caused him to seek an alternative in the first place. Hughes specifically designed mSQL to handle multiple simultaneous connections within a single process. The result of these design goals is a small, fast, efficient SQL server capable of handling multiple connections, locally or over the network.
In order to implement this design, Hughes first needed to limit the functionality of the server. The mSQL dialect of SQL is a subset of the ANSI SQL2 standard that contains the most commonly used statements like CREATE, INSERT, SELECT, UPDATE, and DELETE.[6] Hughes left out resource intensive operations like transaction support. Because of the kind of applications mSQL supports, the functionality Hughes left out is generally not needed.
[6]We will cover the mSQL SQL dialect in Chapter 6, "SQL According to MySQL and mSQL".
mSQL is a queuing, single-threaded server. Any number of clients may connect to mSQL at the same time -- up to a defined limit. As each of these clients send queries to mSQL, the database engine sticks the queries into a synchronous queue and processes each query one at a time. The efficiency of this design is thus dependent on the ability of the server to handle each query quickly. If the queries are not processed in a timely manner, the queue will grow and eventually the server will crash from exceeding system limitations. Speed is therefore critical to the successful operation of mSQL. Figure 5-1 illustrates mSQL's queuing, single-threaded processing.
Figure 5-1. The client/server design of mSQL
The single-threaded nature of mSQL eliminates the need for batch processing. Because queries are handled one at a time, SQL statements do not have the ability to overwrite each other. It would, of course, be nice if mSQL supported transactions, but they are not necessary for the proper operation of a database engine with mSQL's design goals.
On the client side, mSQL supports two kinds of connections. Remote clients connect to the server through a well-known TCP/IP port. By basing connectivity on TCP/IP, an mSQL database can be made available to any machine in the world via the Internet. Local connections can also make use of the TCP/IP port, but they can get better performance by using a standard Unix socket. Unix socket connections are about 20 percent faster than TCP/IP connections.
Bundled with mSQL is a suite of programs that enables complete access to the database system. The mSQL monitor, msql, enables a user to submit queries directly to the server. While this tool is useful during development, most users will want to interface with the database through some sort of application. In order to support application development, mSQL has a built-in C API that enables any C program to connect to a mSQL server through either a TCP/IP or Unix socket.
mSQL also provides a network protocol that enables other languages to connect to mSQL over a network without the need for using the C API. With these two types of interfaces, developers in a host of languages have put together libraries for connecting to mSQL in just about any language you can imagine. In this book, we cover the C, Perl, Java, and Python APIs.
 |  |  |
| 4.6. Performance Tuning |  | 5.2. mSQL Versions |
Copyright © 2001 O'Reilly & Associates. All rights reserved.