Tài liệu MySQL Administrator''''s Bible- P9 doc

MySQL Server Tuning 10

InnoDB storage engine options

InnoDB is a very widely used storage engine in production systems. There are a number of con￾figuration options for the InnoDB engine, and Table 10-4 covers these options.

TABLE 10-4

InnoDB Configuration Options

Option Name Purpose

innodb_buffer_pool_size =

buffer_size

A static variable that specifies the size of the cache

for InnoDB data and indexes.

innodb_flush_log_at_trx_

commit = number

There are three possible options {0|1|2}. This

dynamic system variable manages how often the

InnoDB log buffer is written (flushed) to the log file.

innodb_flush_method =

IO_access_method

This static variable determines how the InnoDB

storage engine interacts with the operating system

with respect to I/O operations.

innodb_log_buffer_size =

buffer_size

Buffer used for writes to the InnoDB logs. Unless you

use very large BLOBs this static variable should not

be over 8 MB, and can be set to 2 Mb.

innodb_log_file_size =

log_file_size

A static variable that determines the size of each

Innodb log file (ib_logfile).

innodb_log_files_in_group =

number_log_files

A static variable that determines the total number of

Innodb log files.

innodb_max_dirty_pages_pct= N This dynamic variable specifies the maximum

percentage of pages in the in Innodb buffer pool that

can be dirty — that is, changed in the buffer pool in

memory without being saved to disk. Defaults to 90

(%).

innodb_thread_concurrency = N This dynamic variable determines the maximum

number of system threads inside InnoDB. A good

number to start is twice the number of CPUs.

All of the InnoDB server variables are GLOBAL in nature.

The single most important InnoDB configuration variable is the innodb_buffer_pool_size.

Assuming the server is only running mysqld and most of your tables are InnoDB tables, the

majority of your memory should be dedicated to the InnoDB buffer pool. It is safe to begin at

367

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

50–70 percent of your system memory allocated the InnoDB buffer, and adjust up or down as

you need to.

If you have a large amount of RAM (16 Gb or more) on a dedicated MySQL server, then

the buffer pool can be an even larger percentage of overall memory. When configuring a

server, choose a starting value for the InnoDB buffer pool, set the other configuration values,

then determine how much memory is still available. On Unix, the vmstat, top, and free

commands show memory information. In Windows, the Task Manager can show you memory

usage.

To determine if the InnoDB buffer pool is appropriately sized, run:

SHOW GLOBAL STATUS LIKE ’innodb_buffer_pool_pages%’;

■ Innodb_buffer_pool_pages_data is the total number of used data pages (clean and

dirty).

■ Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of

clean data pages can be calculated from these first two status variables.

■ Innodb_buffer_pool_pages_flushed is the number of data pages that have been

flushed to disk.

■ Innodb_buffer_pool_pages_free is the number of unused data pages.

■ Innodb_buffer_pool_pages_misc is the number of data pages used for InnoDB over￾head.

■ Innodb_buffer_pool_pages_total is the total number of pages.

Calculate the ratio of unused data pages to the total number of pages:

Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total

If the ratio is high (close to 1), then the InnoDB buffer pool is probably set too high. A less

likely cause is that the innodb_max_dirty_pages_pct system variable is set too low, and

dirty pages are being flushed very often, freeing up pages long before they are needed.

Conversely, if the ratio is low, the size of the InnoDB buffer pool may need to be set higher.

Using the information you have about the free memory on your system, increase the InnoDB

buffer pool size, restart mysqld, and continue to monitor the status variables after the newly

sized InnoDB buffer pool has been used for a while. Continue the adjust-monitor-adjust cycle,

and once your system is at the right level, continue to monitor levels, making sure to check

performance once every month or two.

Make sure to always leave a buffer of a half-gigabyte or so of memory because mysqld performs

very poorly when it is forced to use swap space. Keep in mind that under high load, mysqld

will use more memory.

368

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

MySQL Server Tuning 10

The innodb_flush_log_at_trx_commit system variable is used to manage how often

the InnoDB log buffer sends writes to the InnoDB log. When this parameter is set to 0, the

log buffer is written every second and the logs file flushes to disk. When this value is 1 (the

default), every commit will make the log buffer write to the log file. The log file is flushed to

disk on each commit as well. This is required for ACID compliance. For more information on

ACID compliance and transactions, see Chapter 9.

When set to 2, every commit makes the log buffer write to the file, just as when the value is 1.

However, the log file flushes to disk every second, just as when the value is 0. Setting this vari￾able to 0 or 2 changes the database to no longer be ACID–compliant — it does not meet the

requirements for durability. Because of the log file flushing being different from the transaction

commit, it is possible that a crash could lose a second of transactions (actually, slightly more

than a second, because of process-scheduling issues). When the variable is set to 0, a crash of

mysqld or the operating system may cause this lack of durability; when this variable is set to 2,

only an operating system crash may cause this lack of durability.

Note that many operating systems and some disk hardware tell mysqld that the flush has

taken place even though a flush has not happened. In these cases, the durability requirement of

ACID compliance not met, regardless of the value of innodb_flush_log_at_trx_commit.

A crash (for example, due to a power outage) can even corrupt the InnoDB database. Using a

battery-backed disk cache in the disk or disk controller will protect against this scenario, and

regular file flushes will be faster, too.

The innodb_flush_method variable has three possible values:

■ fsync is the default option and uses the fsync() system call to flush both data and

log files.

■ O_DIRECT will bypass the operating system cache for both reads and writes of data and

log files.

■ O_SYNC uses the fsync() system call for data files but for log files uses O_SYNC.

There are many times when using O_DIRECT will significantly improve performance of mysqld.

This is because it removes the buffering of the operating system. Do not use O_DIRECT with￾out using a RAID controller that has a battery backed write cache. This can overcome problems

because of operating system crashes that otherwise do not complete a write to the hard drives.

In addition, you should enable what is called writeback on the cache. When the server sends

data to be flushed to the RAID controller, the controller immediately tells the server the flush is

complete. The server considers it committed and is free to do other task. The RAID controller

then flushes the writes stored in the cache periodically. This batches the writes and makes them

more efficient.

If you are considering using O_DIRECT, carefully test your setup to make sure you are getting

the best performance possible. With some configurations using O_DIRECT can actually impede

performance so be careful!

369

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

Using O_SYNC is usually a slower alternative than using O_DIRECT, but there are some edge

cases where it can prove to be faster. As with O_DIRECT, it is important that you test your setup

to see if server performance benefits.

On very write-intensive systems a performance boost can be found by creating larger InnoDB log

files. The reason why is that the larger the size of the log file, the less checkpoint flush activity,

which saves disk I/O. However, the larger your log files are the longer the recovery time will be

after a crash. The default size for the InnoDB log files is only 5 MB. Even with 64-MB log files

you should have recovery times under a minute. The maximum size is 4 GB for all the InnoDB

log files. It is very common to set these to between 128 and 256 MB. See tip for how to change

the InnoDB log file size.

To change the size of your InnoDB log files:

■ Shut down mysqld.

■ Edit the configuration file, setting a new log file size with the innodb_log_file_size

option.

■ Move the existing InnoDB log files to a backup location.

■ Start mysqld.

■ Verify that the new log files are the correct size.

■ The previous InnoDB log files can be deleted.

The innodb_max_dirty_pages_pct server variable sets the percentage of pages allowed to be

changed ("marked dirty") before a flush to disk is performed. A page in this context is a fixed

amount of system memory. With the InnoDB storage engine a page is 16k in size. Allowing a

higher percentage of dirty pages before a disk flush could increase performance. The default is

90 percent.

Falcon storage engine options

The new Falcon storage engine is designed to utilize large amount of memory to increase perfor￾mance. Table 10-5 shows the configuration option that will affect performance on your server.

TABLE 10-5

Falcon Configuration Options

Option Name Purpose

falcon_record_memory_max=buffer_size Sets the maximum size of the data cache

370

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

MySQL Server Tuning 10

The falcon_record_memory_max variable is used to determine the size of the buffer used for

the Falcon storage engine. If your server is only using Falcon tables then this should be set to

use most of the available memory (much like the previous suggestions for the InnoDB buffer

pool).

Maria storage engine options

The Maria storage engine is designed to be a replacement for MyISAM. It has many similar

characteristics to MyISAM but includes transactional support and automatic crash recovery.

Table 10-6 shows the configuration option that affects performance for the Maria storage engine.

TABLE 10-6

Maria Configuration Options

Option Name Purpose

maria_pagecache_

buffer_size

Configures the cache size for data and index pages. This

is similar to the InnoDB buffer pool.

If you are using a large number of Maria tables you should increase the buffer size. By default it

is only 8 MB.

Query cache options

Effectively utilizing the query cache can significantly improve the performance of mysqld. The

query cache is covered in great detail in Chapter 12, including explanations of the options and

how to tune the query cache.

Dynamic variables

So far, we have been discussing how to change your system variables by modifying the con￾figuration file. While changing the option file is necessary for a change that will persist across

mysqld restarts, there are times when you do not want to restart mysqld to change a system

variable — perhaps you are testing and only want to set a variable temporarily.

However, it is possible to dynamically change many of the server variables. For example, the

variables relating to query cache setup and management can be changed without a server restart.

However, the innodb_buffer_pool_size variable cannot be changed dynamically and

requires a server restart with the option specified. Session variables, by their nature, are always

dynamic, as they are set per session.

371

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.