9.12 InnoDB Performance Tuning Tips

9.12 InnoDB Performance Tuning Tips

• If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it bigger than 80% of physical memory.

• Wrap several modifications into one transaction. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167/second if the disk does not fool the operating system.

• If you can afford the loss of some of the latest committed transactions, you can set the my.cnf parameter innodb_flush_log_at_trx_commit to 0. InnoDB tries to flush the log once per second anyway, although the flush is not guaranteed .

• Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files will cause many unnecessary disk writes . The drawback of big log files is that recovery time will be longer.

• Make the log buffer quite big as well (say, 8MB).

• Use the VARCHAR column type instead of CHAR if you are storing variable-length strings or if the column may contain many NULL values. A CHAR( N ) column always takes N bytes to store data, even if the string is shorter or its value is NULL . Smaller tables fit better in the buffer pool and reduce disk I/O.

• (Relevant from 3.23.39 up.) In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync () and other similar methods is surprisingly slow. The default method InnoDB uses is the fsync() function. If you are not satisfied with the database write performance, you might try setting innodb_flush_method in my.cnf to O_DSYNC , though O_DSYNC seems to be slower on most systems.

• When importing data into InnoDB , make sure that MySQL does not have autocommit mode enabled because that would require a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements:

  
  

   SET AUTOCOMMIT=0; /* SQL import statements ... */ COMMIT; 

  
  

  If you use the mysqldump option --opt , you will get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.

• Beware of big rollbacks of mass inserts : InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times the time of the corresponding insert. Killing the database process will not help because the rollback will start again at the server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure. See Section 9.9.1, "Forcing Recovery."

• Beware also of other big disk-bound operations. Use DROP TABLE or TRUNCATE TABLE (from MySQL 4.0 up) to empty a table, not DELETE FROM tbl_name .

• Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

  
  

   INSERT INTO yourtable VALUES (1,2), (5,5), ...; 

  
  

  This tip is valid for inserts into any table type, not just InnoDB .

• If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

  
  

   SET UNIQUE_CHECKS=0; 

  
  

  For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch.

• If you have FOREIGN KEY constraints in your tables, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by turning the foreign key checks off for a while in the import session:

  
  

   SET FOREIGN_KEY_CHECKS=0; 

  
  

  For big tables, this can save a lot of disk I/O.

• If you often have recurring queries to tables that are not updated frequently, use the query cache available as of MySQL 4.0:

  
  

   [mysqld] query_cache_type = ON query_cache_size = 10M 

  
  

  In MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.

9.12.1 SHOW INNODB STATUS and the InnoDB Monitors

Starting from MySQL 3.23.42, InnoDB includes InnoDB Monitors that print information about the InnoDB internal state. Starting from MySQL 3.23.52 and 4.0.3, you can use the SQL statement SHOW INNODB STATUS to fetch the output of the standard InnoDB Monitor to your SQL client. The information is useful in performance tuning. If you are using the mysql interactive SQL client, the output is more readable if you replace the usual semicolon statement terminator by \G :

 mysql>  SHOW INNODB STATUS\G  

Another way to use InnoDB Monitors is to let them continuously write data to the standard output of the server mysqld . In this case, no output is sent to clients . When switched on, InnoDB Monitors print data about every 15 seconds. Server output usually is directed to the .err log in the MySQL data directory. This data is useful in performance tuning. On Windows, you must start the server from a command prompt in a console window with the --console option if you want to direct the output to the window rather than to the error log.

Monitor output includes information of the following types:

• Table and record locks held by each active transaction

• Lock waits of transactions

• Semaphore waits of threads

• Pending file I/O requests

• Buffer pool statistics

• Purge and insert buffer merge activity of the main InnoDB thread

To cause the standard InnoDB Monitor to write to the standard output of mysqld , use the following SQL statement:

 CREATE TABLE innodb_monitor(a INT) TYPE=InnoDB; 

The monitor can be stopped by issuing the following statement:

 DROP TABLE innodb_monitor; 

The CREATE TABLE syntax is just a way to pass a command to the InnoDB engine through the MySQL SQL parser: The only things that matter are the table name innodb_monitor and that it be an InnoDB table. The structure of the table is not relevant at all for the InnoDB Monitor. If you shut down the server when the monitor is running, and you want to start the monitor again, you have to drop the table before you can issue a new CREATE TABLE statement to start the monitor. This syntax may change in a future release.

In a similar way, you can start innodb_lock_monitor , which is otherwise the same as innodb_monitor but also prints a lot of lock information. A separate innodb_tablespace_monitor prints a list of created file segments existing in the tablespace and also validates the tablespace allocation data structures. Starting from 3.23.44, there is innodb_table_monitor with which you can print the contents of the InnoDB internal data dictionary.

A sample of InnoDB Monitor output:

 mysql>  SHOW INNODB STATUS\G  *************************** 1. row *************************** Status: ===================================== 030709 13:00:59 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 413452, signal count 378357 --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135 a writer (thread id 32782) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 731 Last time write locked in file btr0sea.c line 1347 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 030709 13:00:59 Transaction: TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser ting 15 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') Foreign key constraint fails for table test/ibtest11a: ,   CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)  ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index PRIMARY tuple:  0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;  hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex  00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;; But in parent table test/ibtest11b, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ...... ;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 030709 12:59:58 *** (1) TRANSACTION: TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser ting LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146 MySQL thread id 21, query id 4553379 localhost heikki update INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f' ,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290252780 lock mode S waiting Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a a35818;; 1: *** (2) TRANSACTION: TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser ting 130 lock struct(s), heap size 11584, undo log entries 437 MySQL thread id 23, query id 4554396 localhost heikki update REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,' h396', NULL, NULL, 7.31,7.31,7.31,200) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks rec but not gap Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a a35818;; 1: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa 35720;; 1: *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 Total number of lock structs in row lock hash table 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002 MySQL thread id 32, query id 4668737 localhost heikki show innodb status ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in serting 1 lock struct(s), heap size 320 MySQL thread id 29, query id 4668736 localhost heikki update insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co mmitting 1 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 19, query id 4668734 localhost heikki update insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st arting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update update ibtest11a set B = 'kHdkkkk' where A = 89572 ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM ARY trx id 0 290328327 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc  supremum.;; ------------------ ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro llback of SQL statement ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782 58 lock struct(s), heap size 5504, undo log entries 159 MySQL thread id 23, query id 4668732 localhost heikki update REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f ','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'), ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in serting 4 lock struct(s), heap size 1024, undo log entries 165 MySQL thread id 21, query id 4668735 localhost heikki update INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL, 'h321', NULL, NULL, 7.31,7.31,7.31,200) -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) 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 151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs 25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 19, seg size 21, 85004 inserts, 85004 merged recs, 26669 merges Hash table size 207619, used cells 14461, node heap has 16 buffer(s) 1877.67 hash searches/s, 5121.10 non-hash searches/s --- LOG --- Log sequence number 18 1212842764 Log flushed up to   18 1212665295 Last checkpoint at  18 1135877290 0 pending log writes, 0 pending chkp writes 4341 log i/o's done, 1.22 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 84966343; in additional pool allocated 1402624 Buffer pool size   3200 Free buffers       110 Database pages     3074 Modified db pages  2674 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 171380, created 51968, written 194688 28.72 reads/s, 20.72 creates/s, 47.55 writes/s Buffer pool hit rate 999 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 3004, id 7176, state: purging Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779 1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.05 sec) 

Some notes on the output:

• If the TRANSACTIONS section reports lock waits, your application may have lock contention . The output can also help to trace the reasons for transaction deadlocks.

• The SEMAPHORES section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or an rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB . Contention can be due to heavy parallelism of queries, or problems in operating system thread scheduling. Setting innodb_thread_concurrency smaller than the default value of 8 can help in such situations.

• The BUFFER POOL AND MEMORY section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

• The ROW OPERATIONS section shows what the main thread is doing.