|< Day Day Up >|
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:
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:
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:
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
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:
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
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 ============================
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
|< Day Day Up >|