MySQL 5 Certification Guide ( DBA 1 Exam)

Chapter 23. MySQL Architecture

 

23.1. Client/Server Overview The MySQL database system operates using a client/server architecture. The server is the central program that manages database contents, and client programs connect to the server to retrieve or modify data. MySQL also includes non-client utility programs and scripts. Thus, a complete MySQL installation consists of three general categories of programs: MySQL Server. This is the mysqld program that manages databases and tables. Most users choose a binary (precompiled) MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source yourself. The types of distributions available are discussed in Chapter 24, "Starting, Stopping, and Configuring MySQL." Client programs. These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example, you can use the mysql client to send queries to the server, and the server returns the query results. The client programs included with MySQL distributions are character-based programs that display output to your terminal. MySQL AB also produces clients that provide a graphical interface. MySQL Query Browser is a general-purpose client for interacting with the server to perform data analysis. MySQL Administrator is oriented more toward managing the server itself. These graphical clients are not included with MySQL distributions but can be obtained from the MySQL AB Web site. Non-client utility programs. These are programs that generally are used for special purposes and do not act as clients of the server. That is, they do not connect to the server. For example, mysqld_safe is a script for starting up and monitoring the server. myisamchk is a standalone utility for table checking and repair. It accesses or modifies table files directly. Utilities such as myisamchk must be used with care to avoid unintended interaction with the server. If table files are used by two programs at the same time, it's possible to get incorrect results or even to cause table damage. In addition to the types of programs just described, MySQL AB also makes available several interfaces that can be used by third-party client programs to access the server. These include the API that is provided in the form of a client library written in C that can be linked into other programs, and a family of MySQL Connectors. The connectors are drivers that act as bridges to the MySQL server for client programs that communicate using a particular protocol. Currently, MySQL AB provides MySQL Connector/OBDC, MySQL Connector/J, and MySQL Connector/NET, which are connectors for clients that use the ODBC, JDBC, or .NET protocols. The C client library is available as part of MySQL distributions. The connectors are available as separate packages. (See Chapter 4, "MySQL Connectors.") The MySQL database system has several important characteristics that enable it to be used in many computing environments: MySQL is supported on multiple operating systems, and runs on many varieties of Windows, Unix, and Linux. MySQL works in distributed environments. A client program can connect locally to a server running on the same computer or remotely to a server running on a different computer. MySQL provides cross-platform interoperability and can be used in heterogeneous networks. Client computers need not run the same operating system as the server computer. For example, a client running on Windows can use a server running on Linux, or vice versa. Most of the concepts discussed here apply universally to any system on which MySQL runs. Platform-specific information is so indicated. Unless otherwise specified, "Unix" as used here includes Linux and other Unix-like operating systems. 23.2. Communication Protocols A MySQL client program can connect to a server running on the same machine. This is a local connection. A client can also connect to a server running on another machine, which is a remote connection. MySQL supports connections between clients and the server using several networking protocols, as shown in the following table. Protocol Types of Connections Supported Operating Systems TCP/IP Local, remote All Unix socket file Local only Unix only Named pipe Local only Windows only Shared memory Local only Windows only Some protocols are applicable for connecting to either local or remote servers. Others can be used only for local servers. Some protocols are specific to a given operating system. TCP/IP connections are supported by any MySQL server unless the server is started with the --skip-networking option. Unix socket file connections are supported by all Unix servers. Named-pipe connections are supported only on Windows and only if you use one of the servers that has -nt in its name (mysql-nt, mysql-max-nt). However, named pipes are disabled by default. To enable named-pipe connections, you must start the -nt server with the --enable-named-pipe option. Shared-memory connections are supported by all Windows servers, but are disabled by default. To enable shared-memory connections, you must start the server with the --shared-memory option. From the client perspective, a client run on the same host as the server can use any of the connection protocols that the server supports. If the client is run on a different host, connections always use TCP/IP. To enable you to indicate which kind of connection to use and which server to connect to, MySQL client programs understand a standard set of command-line options. Section 1.2, "Invoking Client Programs," discusses the syntax for these options and how to use them when invoking client programs. MySQL communication protocols are implemented by various libraries and program drivers. Client programs included with MySQL distributions (mysql, mysqladmin, and so forth) establish connections to the server using the native C client library. However, other interfaces are available, such as the MySQL Connectors mentioned in Section 23.1, "Client/Server Overview." The different connection methods are not all equally efficient: In many Windows configurations, communication via named pipes is much slower than using TCP/IP. You should use named pipes only when you choose to disable TCP/IP (using the --skip-networking startup parameter) or when you can confirm that named pipes actually are faster for your particular setup. On Unix, a Unix socket file connection provides better performance than a TCP/IP connection. On any platform, an ODBC connection made via MySQL Connector/ODBC is slower than a connection established directly using the native C client library. This is because ODBC is layered on top of the C library, which adds overhead. On any platform, a JDBC connection made via MySQL Connector/J is likely to be roughly about the same speed as a connection established using the native C client library. 23.3. The SQL Parser and Storage Engine Tiers A client retrieves data from tables or changes data in tables by sending requests to the server in the form of SQL statements such as SELECT, INSERT, or DELETE. The server executes each statement using a two-tier processing model: The upper tier includes the SQL parser and optimizer. The server parses each statement to see what kind of request it is, then uses its optimizer to determine how most efficiently to execute the statement. However, this tier does not interact directly with tables named by the statement. The lower tier comprises a set of storage engines. The server uses a modular architecture: Each storage engine is a software module to be used for managing tables of a particular type. The storage engine associated with a table directly accesses it to store or retrieve data. MyISAM, MEMORY, and InnoDB are some of the available engines. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time. New engines also can be added relatively easily. For the most part, the SQL tier is free of dependencies on which storage engine manages any given table. This means that clients normally need not be concerned about which engines are involved in processing SQL statements, and can access and manipulate tables using statements that are the same no matter which engine manages them. Exceptions to this engine-independence of SQL statements include the following: CREATE TABLE has an ENGINE option that enables you to specify which storage engine to use on a per-table basis. ALTER TABLE has an ENGINE option that enables you to convert a table to use a different storage engine. Some index types are available only for particular storage engines. For example, only the MyISAM engine supports full-text or spatial indexes. COMMIT and ROLLBACK have an effect only for tables managed by transactional storage engines such as InnoDB. 23.4. How MySQL Uses Disk Space MySQL Server uses disk space in several ways, primarily for directories and files that are found under a single location known as the server's data directory. The server uses its data directory to store all the following: Database directories. Each database corresponds to a single directory under the data directory, regardless of what types of tables you create in the database. For example, a given database is represented by one directory whether it contains MyISAM tables, InnoDB tables, or a mix of the two. Table format files (.frm files) that contain a description of table structure. Every table has its own .frm file, located in the appropriate database directory. This is true no matter which storage engine manages the table. Data and index files are created for each table by some storage engines and placed in the appropriate database directory. For example, the MyISAM storage engine creates a data file and an index file for each table. The InnoDB storage engine has its own tablespace and log files. The tablespace contains data and index information for all InnoDB tables, as well as the undo logs that are needed if a transaction must be rolled back. The log files record information about committed transactions and are used to ensure that no data loss occurs. By default, the tablespace and log files are located in the data directory. The default tablespace file is named ibdata1 and the default log files are named ib_logfile0 and ib_logfile1. (It is also possible to configure InnoDB to use one tablespace file per table. In this case, InnoDB creates the tablespace file for a given table in the table's database directory.) Server log files and status files. These files contain information about the statements that the server has been processing. Logs are used for replication and data recovery, to obtain information for use in optimizing query performance, and to determine whether operational problems are occurring. Chapter 24, "Starting, Stopping, and Configuring MySQL," contains additional information about configuration-related aspects of the data directory, such as how to determine its location or set up logging. Chapter 29, "Storage Engines," discusses how storage engines manage table files under the data directory. 23.5. How MySQL Uses Memory MySQL Server memory use includes data structures that the server sets up to manage communication with clients and to process the contents of databases. The server allocates memory for many kinds of information as it runs: Thread handlers. The server is multi-threaded, and a thread is like a small process running inside the server. For each client that connects, the server allocates a thread to it to handle the connection. For performance reasons, the server maintains a small cache of thread handlers. If the cache is not full when a client disconnects, the thread is placed in the cache for later reuse. If the cache is not empty when a client connects, a thread from the cache is reused to handle the connection. Thread handler reuse avoids the overhead of repeated handler setup and teardown. Threads also may be created for other purposes. Individual storage engines might create their own threads, and replication uses threads. The server uses several buffers (caches) to hold information in memory for the purpose of avoiding disk access when possible: Grant table buffers. The grant tables store information about MySQL user accounts and the privileges they have. The server loads a copy of the grant tables into memory for fast access-control checking. Client access is checked for every query, so looking up privilege information in memory rather than from the grant tables on disk results in a significant reduction of disk access overhead. A key buffer holds index blocks for MyISAM tables. By caching index blocks in memory, the server often can avoid reading index contents repeatedly from disk for index-based retrievals and other index-related operations such as sorts. In contrast to the handling of MyISAM indexes, there are no buffers specifically for caching MyISAM table data rows because MySQL relies on the operating system to provide efficient caching when reading data from tables. The table cache holds descriptors for open tables. For frequently used tables, keeping the descriptors in the cache avoids having to open the tables again and again. The server supports a query cache that speeds up processing of queries that are issued repeatedly. This feature is discussed in detail in Section 39.4, "Using the Query Cache." The host cache holds the results of hostname resolution lookups. These results are cached to minimize the number of calls to the hostname resolver. The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits, the log buffer is flushed to the InnoDB log files, providing a record on disk that can be used to recommit the transaction if it is lost due to a crash. If the transaction rolls back instead, the flush to disk need not be done at all. The MEMORY storage engine creates tables that are held in memory. These tables are very fast because no transfer between disk and memory need be done to access their contents. The server might create internal temporary tables in memory during the course of query processing. If the size of such a table exceeds the value of the tmp_table_size system variable, the server converts it to a MyISAM-format table on disk and increments its Created_tmp_disk_tables status variable. The server maintains several buffers for each client connection. One is used as a communications buffer for exchanging information with the client. Other buffers are maintained per client for reading tables and for performing join and sort operations. Several SHOW statements enable you to check the sizes of various memory-related parameters. SHOW VARIABLES displays server system variables so that you can see how the server is configured. SHOW STATUS displays server status variables. The status indicators enable you to check the runtime state of caches, which can be useful for assessing the effectiveness with which they are being used and for determining whether you would be better off using larger (or in some cases smaller) buffers. Server memory use can be tuned by setting buffer sizes using command-line options or in an option file that the server reads at startup time. For more information, see Chapter 39, "Optimizing the Server." Chapter 24. Starting, Stopping, and Configuring MySQL This chapter discusses the issues involved in setting up and configuring MySQL. It covers the following exam topics: Types of MySQL distributions available Starting and stopping MySQL Server on Windows Starting and stopping MySQL Server on Unix and Unix-like systems such as Linux Specifying options for server runtime configuration Log and status files Loading the time zone tables for named time zone support Security-related configuration options Setting the default SQL mode Upgrading an older installation to a newer version of MySQL This chapter covers general methods for starting and stopping MySQL Server. The material here assumes that MySQL has been installed already and does not go into detail about installation procedures. Those details can be found in the installation chapter of the MySQL Reference Manual. If you need to install MySQL, you can get any distribution files you need from the MySQL AB Web site (http://dev.mysql.com). 24.1. Types of MySQL Distributions MySQL is available for several operating systems. Those covered in this chapter are Windows and Unix. Unless otherwise specified, "Unix" as used here includes Linux and other Unix-like operating systems. You can install MySQL from a binary distribution that contains precompiled programs ready to run, or you can compile MySQL yourself from a source distribution. This section describes the various types of MySQL distributions from which you can choose. 24.1.1. MySQL Binary Distributions On Windows, you can choose from these types of binary distributions: An Essentials distribution contains the minimum set of files needed to install MySQL, as well as the Configuration Wizard. This is the recommended package for most users. A Complete distribution contains all files for a MySQL installation, as well as the Configuration Wizard. A No-install distribution contains all files for a MySQL installation, but does not have an installer or the Configuration Wizard. The installer included with Essentials and Complete distributions allows you to choose where to install MySQL. By default, it installs MySQL 5.0 in %ProgramFiles%\MySQL\MySQL Server 5.0, where %ProgramFiles% has a value such as C:\Program Files. The No-install distribution is just a Zip archive. To install it, unpack it and move it to the desired installation location.