InnoDB Chinese Reference Manual---9 Performance tuning tips

Reference | reference Manual | skills | performance | Chinese InnoDB Chinese reference Manual---Dog dog (heart sail) Translation 9 Performance tuning tips (performance tuning tips)
1. If Unix top or Windows Task Manager shows the service's CPU occupancy rate less than 70%, (shows that CPU usage percentage with your I s less than 70%, your system bottleneck may be on disk read/write. Maybe you've committed a lot of transactions, or the buffer pool is too small. Setting a large buffer pool is helpful, but be careful not to be more than 80% of the physical memory.

2. Include several modifications in a transaction. If a transaction modifies the database, InnoDB must refresh the log to disk when the transaction is committed. Because the hard disk rotates at a rate of up to 167 rpm, the number of transactions committed is limit 167 times per second, as long as the disk does not deceive the operating system.

3. If you lose some of the most recent transactions, you can set the parameter innodb_flush_log_at_trx_commit to 0 in the my.cnf file. InnoDB always try to refresh (flush) One second of the log in any case, although refreshing is not guaranteed.

4. Make the log file larger so that the sum of the log files is just as large as the buffer pool. When InnoDB runs out of space in the log file, it has to write the modified contents of the buffer pool to disk at a point in time. Small log files can cause unnecessary disk write operations. But the disadvantage of a large log file is that it takes a long time to recover data.

5. The same log buffer as far as possible to set a large point, such as 8 MB.
6. If you want to store variable length strings or fields that may contain a large number of NULLs, use the VARCHAR field instead of CHAR. A CHAR (n) field always uses n bytes to store data, even if the string is short or a NULL value. Smaller tables are better suited to buffer pools while reducing disk I/O.
7. (Suitable for version 3.23.41) In some versions of Linux and Unixes, it is unusually slow to flush files to disk using Unix Fsync or other similar methods. The default method for InnoDB is Fsync. If you are not satisfied with the disk write performance of the database system, you can try to set the Innodb_flush_method to O_dsync in My.cnf, although the O_dsync option looks slow on most systems.

8. When importing data to InnoDB, make sure that MySQL does not have autocommit=1 turned on. Otherwise, each INSERT statement flushes the log to disk. Add in the first line of your SQL import file
Set autocommit=0;
And join in the last line
Commit

If you use the mysqldump option--opt, you will get a dump file that quickly imports InnoDB tables, and you can even stop using the set autocommit=0 mentioned above; .. a commit;.

9. Be careful with the large rollback of the insert set (roolback): When inserting, InnoDB use Insert buffering to reduce disk I/O, but this mechanism is not used in the corresponding rollback. A disk-bound rollback may cost 30 times times the corresponding insertion time. If a runaway rollback occurs, you can check the tip of Section 6.1 to stop it.

10. Also be careful of a large disk-bound operation. Use DROP table or TRUNCATE (more than MySQL-4.0) to empty a table, instead of using the DELETE from yourtable.

11. If you need to insert a large number of record rows, you can use multiple-line (multi-line) inserts to reduce the communication overhead between the client and the server:
INSERT into Yourtable VALUES (1, 2), (5, 5);

This technique works for inserting any table, not just InnoDB.



12. If there is a unique constraint on the secondary key, starting with 3.23.52 and 4.0.3, you can improve the data import speed by closing the unique key check (uniqueness check) in an import session:
SET unique_checks=0;
A large table import this will reduce the amount of disk I/O, as InnoDB may use its own insert buffer to record secondary indexes in batches.
　

13. If you have a child FOREIGN key constraint in the table, starting with 3.23.52 and 4.0.3, you can improve the data import speed by closing the foreign key check (FOREIGN key check) in an import session:
SET foreign_key_checks=0;

Importing to a large table this will reduce the amount of disk I/O.


9.1 InnoDB Monitor (monitors)
Starting with version 3.23.42, InnoDB contains the InnoDB monitors, which can show the internal state of the InnoDB. Starting with version 3.23.52 and 4.0.3, you can use a new SQL command
Show INNODB STATUS
To read the output information of standard InnoDB Monitor to SQL client. This information is useful for performance tuning.
　

Another way to use InnoDB monitors is to have it continue to write information on the standard output of the service program mysqld. When the switch is turned on, the InnoDB monitors displays data approximately every 15 seconds (note: MySQL's client does not show anything). A simple way to use it is to execute mysqld in a command-line fashion. Otherwise, the output will be directed to the MySQL service error log (error log file) ' Yourhostname '. ERR (Mysql.err under Windows) and must be--cons at the MS-DOS prompt in the Windows system The OLE option runs Mysqld-max to instruct information output at the Command Prompt window.

The information that is displayed contains the following information: The semaphore waiting for each active transaction (active transaction) to hold the table and record locked transactions (lock waits of a transactions) thread wait (semaphore waits of thre ADS) file I/O wait requests (pending file I/O requests) buffer pool statistics InnoDB main thread purge buffer and insert buffer merge activity (merge AC tivity)
　

The following SQL commands enable the standard InnoDB Monitor to be recorded on the standard mysqld output:
CREATE TABLE innodb_monitor (a int) type = InnoDB;
To stop by it:
DROP TABLE Innodb_monitor;
The CREATE TABLE syntax is simply a way to provide the InnoDB engine command through MySQL SQL parsing: The table that was created has nothing to do with InnoDB Monitor. If you shut down the database while the monitor is running and you need to start the monitor again, you will have to remove the table before issuing a new CREATE table to start the monitor.
　

Similarly, you can start Innodb_lock_monitor, which in some ways is consistent with innodb_monitor, but it will show more locking information. A separate innodb_tablespace_monitor will display a list of file segments established in the existing table space and a valid tablespace that can allocate data structures. Starting with 3.23.44, Innodb_table_monitor is available to obtain information about the INNODB internal data dictionary.

Examples of InnoDB output in 3.23.52:
===================================== 020805 22:07:41 INNODB MONITOR OUTPUT ===================================== per Second averages calculated from the last 3 seconds----------semaphores----------OS wait ARRAY info:reservation count 194, signal count 193--thread 7176 has at.. /include/btr0btr.ic line to 0.00 seconds the S emaphore:x-lock on Rw-latch at 44D980BC created in file BUF0BUF.C line 354 a writer (thread ID 7176) has reserved it in mode wait exclusive number of readers 1, Waiters flag 1 last time read L ocked in file. /include/btr0btr.ic line the last time write locked in file. /include/btr0btr.ic line Mutex Spin waits 0, rounds 0, os waits 0 rw-shared spins, OS waits 33; RW-EXCL spins 188, OS waits 161------------transactions------------Trx ID counter 0 657853517 Purge do for Trx ' s n:o < 0 657853429 undo N:o < 0 total number of lock structs in row lock hash table 020805 22:07:36 LATEST detecte D Deadlock: * * * (1) Transaction:transaction 0 657853503, ACTIVE 0 sec, OS thread ID 15373 inserting lock wait 3 lock struct (s), heap size 336 MySQL thread ID 6, Query ID 3741 localhost Heikki update insert INTO ibtest11b (D, B, C) VALUES (5, ' khdkkkk ', ' khdkkkk ') * * * (1) Waiting for This LOCK to is Granted:record LOCKS Space ID 0 page no 104865 n bits table test/ibtest11b index PRI MARY Trx ID 0 6 57853503 lock_mode X waiting record lock, heap No 1 record:info bits 0 0:len 9; Hex 73757072656d756d00; ASC supremum.;; (2) transaction:transaction 0 657853500, ACTIVE 0 sec, OS thread ID 11275 setting Auto-inc lock lock struct (s), he AP size 2672, undo log Entries 5 MySQL thread ID 2, query ID 3750 localhost Heikki update insert INTO ibtest11b (D, B, C) VALUES (5, ' KhD ', ' KhD ') * * * * * (2) holds the LOCK (S): Record LOCKS Space ID 0 page no 104865 n bits table test/ibtest11b Index PRI MARY Trx ID 0 657853500 lock_mode X record Lock, heap No 1 record:info bits 0 0:len 9; Hex 73757072656d756d00; ASC supremum.;; (2Waiting for this lock to is granted:table lock TABLE test/ibtest11b Trx ID 0 657853500 lock_mode auto-inc waiting * * W E ROLL back TRANSACTION (2) LIST of transactions for each session:---TRANSACTION 0 657853516, ACTIVE 5 sec, OS thread ID 15373 setting auto-inc lo ck lock wait 1 lock struct (s), heap size 336 MySQL thread ID 6, query id 3895 localhost u Pdate INSERT INTO ibtest11b (D, B, C) VALUES (5, ' khdkkkk ', ' khdkkkk ')-------TRX has BEEN waiting 5 SEC for this LOCK to Be granted:table LOCK TABLE test/ibtest11b Trx ID 0 657853516 lock_mode auto-inc waiting---------------------transacti On 0 657853514, ACTIVE 5 sec, OS thread ID 11275 inserting lock-wait lock struct (s), Heap size 2672, undo log Entries 2 MySQL thread ID 2, query ID 3898 localhost Heikki update insert INTO ibtest11d (D, B, C) VALUES (5, ' khdkkkk ', ' khdkkkk ') -------TRX has BEEN waiting 5 SEC for this LOCK to is Granted:record LOCKS Space ID 0 page no 104879 n bits 384 table t EST/IBTEST11D Index B tRx ID 0 657853514 lock_mode X gap type lock waiting record lock, heap no 130 record:info bits 0:len 9; Hex 6b48646b6b6b6b6b6b; ASC KHDKKKKKK;; 1:---------------------TRANSACTION 0 657853512, ACTIVE 5 sec, OS thread ID 14348 updating or Deletin G lock struct (s) , Heap Size 2672, undo log Entries 175 MySQL thread ID 5, query ID 3874, localhost heikki updating delete from ibtest11a WH ere A = 215--------FILE I/o--------I/O thread 0 state:waiting for I/O request I/OS thread 1 state:waiting for I/O req uest I/O thread 2 state:waiting for I/O request I/OS 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; Buffer pool:0 272 OS file reads, OS file writes, OS Fsyncs 0.00, 0 avg reads/s, bytes/read 0.00, WRITES/S 0.00 CS/S-------------------------------------INSERT BUFFER and adaptive HASH INDEX------------------------------------- Ibuf for Space 0:size 1, freE list len 5, seg size 7, 0 inserts, 0 merged RECs, 0 merges Hash table size 124633, used cells, 1530, node heap has 4 buff ER (s) 2895.70 hash searches/s, 126.62 non-hash searches/s---Log---log sequence number 3267291494 log flushed up to 3267283711 checkpoint at 3266545677 0 pending log writes, 0 pending CHKP writes log I/O done, 0.00 log I/O ' S/second----------------------BUFFER POOL and MEMORY----------------------total MEMORY allocated 82593970; In additional pool allocated 1406336 Buffer pool size 1920 free buffers 1711 Database pages 205 Modified db pages A-Pendi Ng reads 0 Pending writes:lru 0, flush list 0, single page 0 Pages read 178, created, written 0.00 READS/S, 0.00 CR EATES/S, 0.00 writes/s Buffer Pool hit rate 1000/1000--------------ROW OPERATIONS--------------1 queries inside Inno DB, 0 queries in queue; Main thread:purging number of rows inserted 2008, updated 264, deleted 162, read 9 0.00 inserts/s, 0.00 UPDATES/S, 14.66 DELETES/S, 0.00 reads/s----------------------------End of INNODB MONITOR OUTPUT ============================
Some of the note points of the output information: If the transactions part reports lock Wait (lock waits), your application may have lock contention (lock contention). The output information can help track the cause of transaction deadlock. The semaphores section reports the number of times a thread waits for semaphores and counts how many threads need to rotate (spin) or wait for (waiting) a mutex or rw-lock semaphore. The number of times a larger thread waits for semaphores may be due to disk I/O or InnoDB internal contention (contention problems). Contention (contention) may be due to a heavier concurrency query, or an operating system thread scheduling problem. In this case, you can set the innodb_thread_concurrency to less than the default of 8. The file I/O section lists the wait requests for files I/O. An too large value means a disk I/O bottleneck. The BUFFER POOL and MEMORY section gives statistics on page reading and writing. These values allow you to calculate the amount of data file I/O that your query typically requires.