Previous Section  < Day Day Up >  Next Section

15.7 InnoDB Maintenance

InnoDB was introduced during MySQL 3.23 development and is a standard feature in binary distributions as of MySQL 4. That is, support for InnoDB is included in MySQL 4 unless you build it from source and explicitly exclude the InnoDB storage engine using the --without-innodb configuration option.

If a given MySQL server has the InnoDB storage engine compiled in, but you're sure that InnoDB tables will not be needed, you can disable InnoDB support at runtime by starting the server with the --skip-innodb option. Disabling InnoDB reduces the server's memory requirements because it need not allocate any InnoDB-related data structures. Disabling InnoDB also reduces disk requirements because no InnoDB tablespace or log files need be allocated.

A server that has InnoDB enabled uses a default configuration for its tablespace and log files unless you provide configuration options. This section describes how to configure InnoDB explicitly, and how to obtain status information from InnoDB while the server is running.

15.7.1 Configuring the InnoDB Tablespace

The InnoDB storage engine manages the contents for all InnoDB tables in its tablespace. The tablespace stores data rows and indexes. It also contains a rollback segment consisting of undo log records for ongoing transactions, in case they need to be rolled back. The tablespace has the following general characteristics:

  • It can consist of one file or multiple files.

  • Each component file of the tablespace can be a regular file or a raw partition (a device file). A given tablespace can include both types of files.

  • Tablespace files can be on different filesystems or physical disk drives. One reason to place the files on multiple physical drives is to distribute InnoDB-related disk activity among them.

  • The tablespace size can exceed the limits that the filesystem places on maximum file size. This is true for two reasons. First, the tablespace can consist of multiple files and thus can be larger than any single file. Second, the tablespace can include raw partitions, which are not bound by filesystem limits on maximum file size. InnoDB can use the full extent of partitions, which makes it easy to configure a very large tablespace.

  • The last component of the tablespace can be auto-extending, with an optional limit on how large the file can grow.

If you don't specify any tablespace configuration options at all, InnoDB creates a tablespace consisting of a single 10MB auto-extending regular file named ibdata1 in the data directory. To control the tablespace configuration explicitly, use the innodb_data_file_path and innodb_data_home_dir options:

  • innodb_data_file_path names each of the files in the tablespace, their sizes, and possibly other optional information. The parts of each file specification are delimited by colons. If there are multiple files, separate their specifications by semicolons. The minimum combined size of the files is 10MB.

  • innodb_data_home_dir specifies a pathname prefix that is prepended to the pathname of each file named by innodb_data_file_path. By default, tablespace files are assumed to be located in the data directory. You can set the home directory to the empty value if you want filenames in innodb_data_file_path to be treated as absolute pathnames. This is useful when you want to place tablespace files on different filesystems or if you want to use raw partitions.

Normally, you place the settings for these options in an option file to make sure that the server uses the same tablespace configuration each time it starts. The following examples show various ways to set up an InnoDB tablespace:

  • A tablespace consisting of a single 100MB file named innodata1 located in the data directory:

    




    
[mysqld]

innodb_data_file_path = innodata1:100M

    It's unnecessary to specify a value for the innodb_data_home_dir option in this case because the data directory is its default value.

  • A tablespace like that in the previous example, except that the file is auto-extending:

    




    
[mysqld]

innodb_data_file_path = innodata1:100M:autoextend

  • A tablespace like that in the previous example, but with a limit of 500MB on the size to which the auto-extending file may grow:

    




    
[mysqld]

innodb_data_file_path = innodata1:100M:autoextend:max:500M

  • A tablespace consisting of two 500MB files named innodata1 and innodata2 located in the data directory:

    




    
[mysqld]

innodb_data_file_path = innodata1:500M;innodata2:500M

  • A tablespace like that in the previous example, but with the files stored under the E:\innodb directory rather than in the data directory.

    




    
[mysqld]

innodb_data_home_dir = E:/innodb

innodb_data_file_path = innodata1:500M;innodata2:500M

    Note that backslashes in Windows pathnames are written as forward slashes in option files.

  • A tablespace consisting of two files stored on different filesystems. Here the home directory is set to an empty value so that the file specifications can be given as absolute pathnames on different filesystems:

    




    
[mysqld]

innodb_data_home_dir =

innodb_data_file_path = E:/innodata1:500M;D:/innodata2:500M

When you first configure the tablespace, any regular files named by the configuration options must not exist. InnoDB will create and initialize them when you start the server.

Any raw partitions named in the configuration must exist but must have the modifier newraw listed after the size in the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server, change newraw to raw in the partition specification, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:







[mysqld]

innodb_data_home_dir =

innodb_data_file_path = /dev/hdc6:10Gnewraw

Start the server and let InnoDB initialize the tablespace. Then tell the server to shut down and change the configuration from newraw to raw:







[mysqld]

innodb_data_home_dir =

innodb_data_file_path = /dev/hdc6:10Graw

Then restart the server.

In MySQL 4.0, InnoDB does not support the use of partitions (drives) in the tablespace for Windows. That capability is added in MySQL 4.1, but it not covered here.

15.7.2 Configuring InnoDB Buffers and Logs

InnoDB uses a buffer pool to hold information read from InnoDB tables. The buffer pool serves to reduce disk I/O for information that is frequently accessed, and a larger buffer more effectively achieves this goal. To change the size of the buffer pool, set the innodb_buffer_pool_size option. Its default value is 8MB. If your machine has the memory available, you can set the value much higher.

The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled back transaction, it results in writes that, with a larger buffer, would not need to have been made at all. To set the size of the log buffer, use the innodb_log_buffer_size option. The default value is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

By default, InnoDB creates two 5MB log files in the data directory named ib_logfile0 and ib_logfile1. To configure the InnoDB log files explicitly, use the innodb_log_files_in_group and innodb_log_file_size options. The first controls how many log files InnoDB uses and the second how big each file is. For example, to use three log files of 50MB each, configure the log like this:







[mysqld]

innodb_log_files_in_group = 3

innodb_log_file_size = 50M

The product of the two values is the total size of the InnoDB log files. Information is logged in circular fashion, with old information at the front of the log being overwritten when the log fills up. However, the log entries cannot be overwritten if the changes they refer to have not yet been recorded in the tablespace. Consequently, a larger log allows InnoDB to run longer without having to force changes to be applied to the tablespace on disk.

The innodb_flush_log_at_trx_commit setting affects how InnoDB transfers log information from the log buffer in memory to the log files on disk. The buffer contains information about committed transactions, so it is important that it be written properly. However, it is one thing to perform a write operation, and another to make sure that the operating system actually has written the information to disk. Operating systems typically buffer writes in the filesystem cache briefly and do not actually perform the write to disk immediately. To ensure that buffered information has been recorded on disk, InnoDB must perform a write operation to initiate a disk transfer and a flush operation to force the transfer to complete.

InnoDB tries to flush the log approximately once a second in any case, but the innodb_flush_log_at_trx_commit option can be set to determine how log writing and flushing occurs in addition. The setting of this option is directly related to the ACID durability property and to performance as follows:

  • If you set innodb_flush_log_at_trx_commit to 1, changes are written from the log buffer to the log file and the log file is flushed to disk for each commit. This guarantees that the changes will not be lost even in the event of a crash. This is the safest setting, and is also the required setting if you need ACID durability. However, this setting also produces slowest performance.

  • A setting of 0 causes the log file to be written and flushed to disk approximately once a second, but not after each commit. On a busy system, this can reduce log-related disk activity significantly, but in the event of a crash can result in a loss of about a second's worth of committed changes.

  • A setting of 2 causes the log buffer to be written to the log file after each commit, but file writes are flushed to disk approximately once a second. This is somewhat slower than a setting of 0. However, the committed changes will not be lost if it is only the MySQL server that crashes and not the operating system or server host. The reason for this is that if the machine continues to run, the changes written to the log file will be in the filesystem cache and eventually will be flushed normally.

The tradeoff controlled by the innodb_flush_log_at_trx_commit setting therefore is between durability and performance. If ACID durability is required, a setting of 1 is necessary. If a slight risk to durability is acceptable to achieve better performance, a value of 0 or 2 may be used.

15.7.3 Monitoring InnoDB

You can ask the InnoDB storage engine to provide information about itself by means of SHOW statements.

SHOW INNODB STATUS requires the SUPER privilege and displays extensive information about InnoDB's operation:







mysql> SHOW INNODB STATUS\G

*************************** 1. row ***************************

Status:

=====================================

030914 17:44:57 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 35 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 65, signal count 65

Mutex spin waits 1487, rounds 28720, OS waits 51

RW-shared spins 28, OS waits 13; RW-excl spins 1, OS waits 1

------------

TRANSACTIONS

------------

Trx id counter 0 31923

Purge done for trx's n:o < 0 21287 undo n:o < 0 0

Total number of lock structs in row lock hash table 0

LIST OF TRANSACTIONS FOR EACH SESSION:

--------

FILE I/O

--------

I/O thread 0 state: waiting for i/o request

I/O thread 1 state: waiting for i/o request

I/O thread 2 state: waiting for i/o request

I/O thread 3 state: waiting for i/o request

Pending normal aio reads: 0, aio writes: 0,

 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

77 OS file reads, 10959 OS file writes, 5620 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 83.20 writes/s, 41.88 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf for space 0: size 1, free list len 0, seg size 2,

0 inserts, 0 merged recs, 0 merges

Hash table size 34679, used cells 1, node heap has 1 buffer(s)

6.06 hash searches/s, 36.68 non-hash searches/s

---

LOG

---

Log sequence number 0 1520665

Log flushed up to   0 1520665

Last checkpoint at  0 1520665

0 pending log writes, 0 pending chkp writes

10892 log i/o's done, 82.80 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 18373254; in additional pool allocated 725632

Buffer pool size   512

Free buffers       447

Database pages     64

Modified db pages  0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 22, created 42, written 141

0.00 reads/s, 0.46 creates/s, 1.49 writes/s

Buffer pool hit rate 1000 / 1000

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

Main thread id 10836480, state: waiting for server activity

Number of rows inserted 5305, updated 3, deleted 0, read 10

41.08 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

SHOW TABLE STATUS, when used with any InnoDB table, displays in the Comment field of the output the approximate amount of free space available in the InnoDB tablespace:







mysql> SHOW TABLE STATUS LIKE 'CountryList'\G

*************************** 1. row ***************************

           Name: CountryList

           Type: InnoDB

     Row_format: Fixed

           Rows: 171

 Avg_row_length: 287

    Data_length: 49152

Max_data_length: NULL

   Index_length: 0

      Data_free: 0

 Auto_increment: NULL

    Create_time: NULL

    Update_time: NULL

     Check_time: NULL

 Create_options:

        Comment: InnoDB free: 13312 kB

    Previous Section  < Day Day Up >  Next Section