MySQL Performance Tuning

After reading some material about how to improve the performance with MySQL database, I gather the info and summarise here. The configuration covers from Disk driver, operating system, connectivity, MySQL Server to MySQL database. For MySQL Database, more details about different database engine configuration have not been described here. Also the Distributed Computing including Replication and Clustering have not been stated.

1. MySQL Server Performance Parameters and Tuning

1.1 connectivity

The database connection experience is often the first impression a user will receive of your MySQL server. Some variables can be set to improve the performance.

Use the net_buffer_length (defaule value is 16M) setting to control over the size of messages sent from the MySQL engine to your client application. This variable works in tandem with max_allowed_packet: If a message is larger than net_buffer_length, MySQL automatically raises the limit up to max_allowed_packet, which can hold up to 1GB of information.

The back_log (default value is 50) parameter designates how many connection requests will be allowed to queue up for servicing before the database engine starts ignoring them (and the client receives an error). After the number of connection requests drops below back_log, MySQL again allows these requests to enter the queue.

 

use the max_connections (default value is 100) variable To control the number of actual concurrent client connections.

 

After a client is connected, you can configure MySQL to drop the session under several conditions:

 

Inactivity

If a certain amount of time passes with no activity, you can instruct MySQL to end the conversation.

If your client is one that involves live user interaction with the database (typically, but not always, an application that presents a user interface), set the interactive_timeout (default value is 28800s)variable to the number of seconds to wait before dropping the connection.

If your client is more of a machine-to-machine application (that is, it doesn't present a user interface for direct database interaction), set the wait_timeout (default value is 28800s) variable instead.

Errors

The net_read_timeout (default value is 30s) and net_write_timeout (default value is 60s) parameters work in conjunction, with the former tracking the amount of seconds that MySQL has waited when reading from a client and the latter monitoring the length of time to write to the client. If either of these values is tripped, MySQL terminates the connection.

Interruptions

The net_retry_count (default value is 10) variable specifies the number of times you are willing to let MySQL attempt to recover from a communication interruption. If you want to block a repeatedly interrupted, troublesome client, use the max_connect_errors (default value is 10) parameter.

1.2 Memory management

1.2.1 Locking mysqld in Memory

you have the option to force the mysqld process to remain in memory rather than face the possibility swapping to disk to improve the performance, To lock mysqld in memory, launch it with the --memlock (the defaule value is FALSE) option enabled.

By forcing mysqld to remain resident in memory, you introduce the prospect of other performance problems because a significant amount of RAM is now unavailable to other processes. It's a better idea to first investigate why the swapping is happening

1.2.2 Thread Memory setting

Threads are the internal process mechanisms that MySQL uses to perform work as well as communicate with connected clients. You can control a few settings, but it's quite likely that the default values are fine for most applications.

You can use the thread_cache_size (default value is 0) parameter to specifie how many threads should be left alive and in the cache to service new connections.

1.2.3 Memory Tables

MEMORY (previously known as HEAP) tables are very fast, in-memory storage available for developers who need these capabilities. These tables are also used by MySQL itself for many smaller temporary tables.

you can decide how much memory you want to allocate for developer-generated tables by setting the max_heap_table_size (default value is 16G) variable. This value, measured in bytes, tells MySQL how large one of these tables can become.

For server-generated temporary tables, use the tmp_table_size (default value is 16G) setting instead. It also tells MySQL how many bytes a temporary table can consume in memory before being swapped out to a disk-based, MyISAM temporary table.

1.2.4 Caching

MySQL also offers an optional, cross-engine query cache to buffer frequently submitted queries, along with their resultsets. If the cache is enabled, MySQL first consults the query cache and might find that the query and its results are already resident in memory. Because memory access is always faster than disk access, finding results in the query cache translates into significantly faster performance.

1.2.4.1 Enable Query Cache

You can enable the query cache by specifying a value for query_cache_size (default value is 0), either via SET GLOBAL or by including it in the MySQL configuration file.

Your next decision is when you want queries to use the query cache. You have three choices when setting the query_cache_type system variable:

No access to the query cache— Setting this variable to 0/OFF

Implicit access to the query cache— Setting this variable to 1/ON

Explicit access to the query cache— Setting this variable to 2/DEMAND

1.2.4.2 Configure Query Cache

After you've decided to enable the query cache, the next step is to pick a value for the amount of memory to allocate for caching. the query_cache_size setting is where you specify your choice.

Your next decision is to determine the maximum amount of memory that you want to allocate within the query cache for an individual query's resultset. Queries with result sets larger than query_cache_limit(default value is 1G) will not be cached.

1.2.4.3 Improve Table scan performance

Table scans can be extremely expensive because MySQL must read each row to determine its results. try tuning the read_buffer_size(default value is 128M) variable to reduce the number of time-consuming disk-seeks required for queries.

1.2.4.4 Improve Join Performance

you can use the join_buffer_size(default value is 128M) setting to request that each MySQL thread that works to process a nonindexed join set aside memory to help finish the join as fast as possible.

1.2.4.5 Improve sort performance

Sorting large blocks of information via ORDER BY or GROUP BY can consume significant amounts of system resources. To help MySQL perform these operations in memory, specify a value for the sort_buffer_size(default value is 2G) variable. Each MySQL thread that is tasked with completing one of these tasks will request this many bytes from memory.

1.3 Application Control

1.3.1 Resources

MySQL connections claim system resources for all tables that they access. The table_open_cache(default value is 64) parameter lets you dictate how many tables can be opened at one time for all applications that are using the database server. Recognize that you never want this number to exceed the open_files_limit(default value is 1024) variable, which apprises MySQL of the total number of file descriptors that your operating system will allow it to consume at any one time.

1.3.2 Optimizer Control

MySQL optimizer Uses all available information about the table(s), pick an action plan that correctly satisfies the user's request in the most efficient way. on occasion the optimizer might incorrectly choose table scans rather than using an available index, by setting the max_seeks_for_key(the default value is 18446744073709551615) variable to a low number, the optimizer assumes that it is efficient to use the index.

For complex, multitable queries, it's possible that the optimizer could take longer to generate a query plan than to get the data itself. To help the optimizer from straying down the wrong path, leave the optimizer_prune_level(the default value is 1) variable to its default of one; this tells the optimizer to abandon work on unfinished query plans that start to appear inefficient rather than continuing to build the complete query plan.

The optimizer_search_depth(the default value is 62) variable provides guidance about whether the optimizer should pick a multitable query plan quickly, or evaluate all join permutations. In most cases, leave this value at zero unless you are having serious performance problems with a query. This setting means that the optimizer does its best to balance the benefits between a rapid versus comprehensive query plan search.

1.3.3 Concurrency

1.3.3.1 Delayed Insert

Delayed insert instructs MySQL to allow other threads that are reading from a table to continue before it allows the INSERT to proceed.

The max_delayed_threads(the default value is 20) parameter instructs MySQL to create no more than this many threads to handle delayed inserts.

You can configure the queue size for each table by tuning the delayed_queue_size(the default value is 1000) variable. A larger value means that MySQL allows more rows to build up in this queue before delaying any new INSERT DELAYED requests.

MySQL uses the delayed_insert_limit(default value is 100) setting to determine how frequently to check for waiting queries while an INSERT DELAYED operation is under way.

You can control how many seconds the dedicated insert delayed thread remains alive and waits for new delayed insert rows by setting the delayed_insert_timeout(default value is 300s) variable.

1.3.3.2 Unlocking Table

MySQL issues table locks under a number of conditions. Although they usually have minimal impact on others, they do have the potential to impact concurrency if not freed quickly enough. One way to get these locks freed sooner is to set the SQL_BUFFER_RESULT(the default value is 0) variable to 1.

2. Improve Disk Speed

2.1 Deploy the Fastest Possible Disk Drives

2.2 Configure Memory Caches

The best way to reduce the cost of disk access is to simply reduce or eliminate as much disk access as possible. MySQL's specialized memory caches do an excellent job of letting the database server spend more time working within memory-based structures rather than having to continually interact with the disk drive.

2.3 Implement a RAID Strategy

A redundant array of independent disks (RAID) lets administrators spread the processing load among multiple disk drives, while also helping to reduce the risk of catastrophic data loss should something happen to a particular disk drive.

After deciding to deploy a RAID implementation, your next step is to establish the exact strategy that you will follow. Again, administrators must choose among several options:Hardware-based RAID, Operating system software-based RAID, MySQL software- based RAID.

2.4 Distribute Temporary Directores

The tmpdir server variable lets you instruct MySQL to use, in a round-robin fashion, a collection of devices for its temporary file needs.

3. Operating System, Web Server and Connectivity

3.1 Operating System

As the underlying platform upon which your MySQL database resides, it's imperative that your server operating system be as efficient as possible.

3.1.1 Linux/Unix

On Linux systems, MySQL relies on a number of key kernel features found in more recent versions. In the absence of these capabilities, your performance might be adversely affected. These kernel enhancements include the following:

Multithreading support

Exploiting symmetric multiprocessors

SCI Sockets (for faster communication among clustered database servers)

if at all possible, it is wise to upgrade the kernel to at least version2.4.

default packages and values for their Linux or Unix platforms always containing Many unnecessary software packages, if choose to install the default package, numerous extraneous processes are now a permanent part of the server landscape.think carefully before installing any of the following server packages or processes on your platform:

Remote shell

BIND

DNS server

FTP

Mail

3.1.2 Windows

Windows administrators had to take care to choose the correct server process for optimal MySQL performance.

Microsoft has released many variations of the Windows operating system. Some of these varieties are more appropriate than others for a relational database server such as MySQL. Whether you are purchasing and configuring a new computer, or repurposing an existing machine, try to select a Microsoft operating system designed to run as a server.

When you install Windows, shoud disable extraneous services.

If your database server computer is used for any web browsing at all, or if a user might have downloaded software from other sources, it's worth the time and effort to run one of the many free or commercially available utilities to check for spyware.

3.2 Web Server

3.2.1 Choose the Right Server Topology

When designing your web and database server configuration, the first decision you face is where to run each of these important processes. For web servers that see relatively low hit rates, it might be sufficient to run both processes on the same hardware. However, even these small implementations benefit from a multi-CPU server; any division of labor can only help these CPU-intensive web and database server applications.

This single-computer topology will not scale for larger, more heavily trafficked sites. In these cases, it's imperative that you place the web and database servers on their own, dedicated machines.

3.3 Connectivity

3.3.1 Protocol Choices

MySQL offers a number of different protocols for communication between clients and the database server.

The first choice is TCP/IP. In most cases, this is the right choice. In fact, it is the only choice if the database client and server are not resident on the same machine. Even if they are on the same machine, computers running certain older versions of Windows have no option but to use this protocol.

Administrators can choose the named pipe protocol for Windows-based computers with the database server and clients both present on the same machine. To launch the server with named pipes enabled, specify -–enable-named-pipe when starting MySQL.

Linux/Unix can use sockets as the protocol for clients and servers running on the same computer by specifying localhost in the connection string.

MySQL introduced shared memory connections in version 4.1 for Windows servers. that this is only relevant if the clients are running on the server.

SCI. Used in conjunction with clustering, this technology can greatly boost the communication speed among servers participating in a NDB Cluster.

3.3.2 Cost of Hostname Resolution

To ensure that a new client has the right to connect to your database, MySQL performs a series of steps to validate this client. Part of this validation includes looking up the hostname of the client and then matching it against the internal grant tables.

If you are concerned that these lookups might be causing undue delays, and if your clients have known, static IP addresses, one alternative is to simply use these IP addresses when granting access:

GRANT ALL PRIVILEGES ON high_hat.* TO 'melvin'@'218.22.192.244';

If you elect to follow this route, you must also tell MySQL not to perform the DNS lookup by launching mysqld with the –-skip-name-resolve option enabled.

If you have no way of knowing these IP addresses in advance, hard-coding numeric IP addresses don't solve the problem. A truly radical approach is to use –skip-grant- tables, which disables verification of client connections via the grant tables.

3.3.3 Cost of Create and Destroy Connections

Creating and Destroying the connections are very time consuming, one way to avoid it is to use the connection pool.

3.3.4 Cost of SSL

SSL technology is the groundwork of much of the Internet's security. This security comes at a cost. However, if your clients and database servers communicate over an open network, you really have no choice but to implement SSL. Fortunately, MySQL gives you the flexibility of using SSL on a per-connection basis. Given that option, it's wisest to mandate SSL for only those clients that are not connecting securely. Other clients can continue using their default connections.

4. MySQL Database Configuration

4.1 Database configuration

4.1.1 Choose Storage Engine

InnoDB is MySQL's high-performance, transaction-safe engine. InnoDB is also a wise choice when you are faced with the need to store very large amounts of data in a table.

MYISAM excels at high-speed operations that don't require the integrity guarantees (and associated overhead) of transactions.

If there is no transaction need, MYISAM is the right choice, if there is transaction need, we had better choose InnoDB.

4.1.2 Optimize the Table Structure

For MYISAM, you can request that the engine store rows in fixed or dynamic format. If your table contains no TEXT or BLOB columns, MySQL chooses the fixed option by default, which automatically converts VARCHAR columns to CHAR. running the myisampack command instructs MySQL to compress the table into a smaller, read-only format.

Fixed format tables translate to less work (and better response) for the MySQL engine when accessing, caching, and updating information. They are also less likely to become corrupted. If disk space is not at a premium, fixed format tables are probably the best choice.

When you're using a dynamic, MyISAM table, you can use the AVG_ROW_LENGTH and MAX_ROWS options to tell MySQL how large you expect your table to be. Knowing this value helps the engine construct index keys in a more optimal way.

When using MyISAM tables, you can request that the engine compress index keys by specifying the PACK_KEYS option when creating a table. You might find that your application's read operations improve; there might be an offsetting added cost for write operations.

4.1.3 Using view to boost performance

Using view can reduce returned column, reduce returned rows, reduce ineficient joins and simpler updating with integrity checking.

4.2 Using Contraints to Improve Performance

As a centralized set of rules that are processed for all changes by all users to a given table, constraints greatly reduce the chances that someone will mistakenly introduce a data integrity problem.

Constraints run on the database server. In most cases, this is faster than manually coding and downloading (or installing) the same logic on a client.

The available contraints are:

UNIQUE— Guarantees that there will be no duplicate values in a column

PRIMARY KEY— Identifies the primary unique identifier of a row

FOREIGN KEY— Codifies and enforces the relationships among two or more tables with regard to appropriate behavior when data changes

DEFAULT— Provides an automatic value for a column if a user omits entering data

NOT NULL— Forces users to provide information for a column when inserting or updating data

ENUM— Allows you to set a restricted list of values for a particular column, although it is not a true constraint

SET— Allows you to store combinations of predefined values within a string column, although it is not a true constraint

4.3 Using MySQL Optimizer

MySQL can Use all available information about the table(s), pick an action plan that correctly satisfies the user's request in the most efficient way.

4.4 Using Index Strategies

Indexes may help to improve the speed of application in a broad range of situations.