InnoDB 中文参考手册 --- 9 性能调整技巧

<script language=javascript src="images/index.js"></script> <script language=javascript> </script> <script language=JavaScript> var currentpos,timer; function initialize() { timer=setInterval("scrollwindow()",10); } function sc() { clearInterval(timer); } function scrollwindow() { currentpos=document.body.scrollTop; window.scroll(0,++currentpos); if (currentpos != document.body.scrollTop) sc(); } document.οnmοusedοwn=sc document.οndblclick=initialize </script>

9 性能调整技巧(Performance tuning tips)

1. 如果 Unix top 或 Windows 任务管理器(Task Manager) 显示服务的 CPU 占用率小于 70%，(shows that the CPU usage percentage with your workload is less than 70 %,）你的系统瓶颈可能在磁盘读写上。或许你提交了大量的事务，或者是缓冲池(buffer pool)太小了。将缓冲池设大点会有所帮助，但一定要注意不能大于物理内存的 80%。

2. 在一个事务中包含几个修改。如果事务对数据库进行了修改，那么在这个事务提交时 InnoDB 必须刷新日志到磁盘上。因为硬盘的旋转速度通常至多为 167 转/秒，那么只要磁盘不欺骗操作系统，提交的事务数目限止也同样为 167 次/秒·用户。

3. 如果掉失最近的几个事务无所谓的话，可以在 my.cnf 文件中将参数 innodb_flush_log_at_trx_commit 设置为 0。InnoDB 无论如何总是尝试一秒刷新(flush)一次日志，尽管刷新并不能得到保证。

4. 将日志文件(log files)设大一点，使日志文件的总和正好与缓冲池(buffer pool)一样大。当 InnoDB 用光日志文件的空间时，它不得不在一个时间点上将缓冲池内修改过的内容写到磁盘上。 小的日志文件可能引起不必要的磁盘写操作。但是大的日志文件的缺点就是在数据恢复时将占用较长的时间。

5. 同样 log buffer 尽量设大点，比如说 8 MB。

6. 如果要存储变长的字符串或字段可能会包含大量的 NULLs，请使用 VARCHAR 型字段代替 CHAR 。一个 CHAR(n) 字段总是使用 n bytes 来存储数据，即使这个字符串很短或是一个 NULL 值。较小的表更加适合缓冲池同时能够减少磁盘 I/O 。

7. (适合从 3.23.41 以上版本) 在某些版本的 Linux 和 Unixes 中，使用 Unix fsync 或其它类似的方法将文件刷新到磁盘是异常地慢的。InnoDB 默认的方法就是 fsync 。如果你对数据库系统的磁盘写性能不能感到满意，你可以尝试在 my.cnf 中将 innodb_flush_method 设置为 O_DSYNC，尽管 O_DSYNC 选项在多数的系统上看起来比较慢。

8. 在向 InnoDB 导入数据时，请确认 MySQL 没有打开 autocommit=1 。否则每个插入语句都要将 log 刷新到磁盘。在你的 SQL 导入文件的第一行加入

set autocommit=0;
 

并在最后一行加入

commit;
　

如果使用 mysqldump 选项 --opt，你将会得到一个快速导入 InnoDB 表的转储(dump)文件，甚至可以不再使用上面所提的 set autocommit=0; ... commit; 。

9. 小心 insert 集全的大回滚(roolback)：在插入时 InnoDB 使用插入缓冲来减少磁盘 I/O，但在相应的回滚中却没有使用这样的机制。一个 disk-bound rollback 可能会花费相应插入时间的 30 倍。如果发生一个失控的回滚，你可以查看第 6.1 章节的技巧来停止它。

10. 同样也要小心一个大的 disk-bound 的操作。使用 DROP TABLE 或 TRUNCATE (从 MySQL-4.0 以上) 来清空一个表，而不要使用 DELETE FROM yourtable。

11. 如果需要插入大量记录行可以使用多行(multi-line)的 INSERT 来减少客户端与服务器端的通信开销：

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

这个技巧对插入任何表均有效，而不仅仅是 InnoDB。

 

12. 如果在辅键上有 UNIQUE 约束，从 3.23.52 和 4.0.3 开始，可以通过在一个导入会话中将唯一键检查(uniqueness check)关闭来提高数据导入速度：

SET UNIQUE_CHECKS=0;
 

一个大的表导入这将减少大量的磁盘 I/O，因为这时 InnoDB 可能使用自身的插入缓冲来分批地记录辅助索引。

　

13. 如果在表中有一个子 FOREIGN KEY 约束，从 3.23.52 和 4.0.3 开始，可以通过在一个导入会话中将外键检查(foreign key check)关闭来提高数据导入速度：

SET FOREIGN_KEY_CHECKS=0;
 

对一个大的表导入这将减少大量的磁盘 I/O。

 

9.1 InnoDB 监视器(Monitors)

从版本 3.23.42 开始，InnoDB 中就包含了 InnoDB Monitors，它可以显示出 InnoDB 的内部状态。从版本 3.23.52 和 4.0.3 开始，你可以使用一个新的 SQL 命令

SHOW INNODB STATUS
 

来读取标准 InnoDB Monitor 给 SQL client 的输出信息。这些信息对性能调整有益。

　

另外一个使用 InnoDB Monitors 方法就是让它在服务程序 mysqld 的标准输出上持续地写出信息。当开关打开时，InnoDB Monitors 大约每 15 秒显示一次数据(注意：MySQL 的客户端并不会显示任何东西)。一个简单地使用它的方法就是以一个命令行方式执行 mysqld 。否则输出将会定向到 MySQL 服务错误日志(error log file)中 'yourhostname'.err (在 Windows 下为 mysql.err)，在 Windows 系统中必须在 MS-DOS 使用提示符下以 --console 选项运行 mysqld-max 来指令信息输出在命令提示符窗口上。

显示的信息包含下列信息：

• 每一个活动的事务(active transaction)保持的表和记录锁定
• 事务的锁等待 (lock waits of a transactions)
• 线程的信号量等待 (semaphore waits of threads)
• 文件 I/O 的等待请求 (pending file i/o requests)
• 缓冲池(buffer pool)的统计信息
• InnoDB 主线程的 purge buffer 和 insert buffer 归并活动(merge activity)

　

通过下列的 SQL 命令，可以使标准的 InnoDB Monitor 记录到标准的 mysqld 的输出上：

CREATE TABLE innodb_monitor(a int) type = innodb;
 

通过它来停止：

DROP TABLE innodb_monitor;
 

CREATE TABLE 句法只不过是为了通过 MySQL SQL 语法分析而提供给 InnoDB 引擎命令的一种方式：那个被创建的表根本与 InnoDB Monitor 无任何关系。如果你在监视器运行着的状态下关闭数据库，并且你需要再次启动监视器， 那么你不得不在发出一个新的 CREATE TABLE 来启动监视器之前先移除(drop)这个表。

　

与之相类似的，你可以启动 innodb_lock_monitor ，它在某些方面与 innodb_monitor 一致，但是它会显示更多的锁定信息。一个单独的 innodb_tablespace_monitor 将显示在现有表空间内所建立的文件段列表以及可以分配数据结构的有效表空间。从 3.23.44 开始，提供了 innodb_table_monitor ，通过它可以获得 InnoDB 内部数据字典的信息。

3.23.52 中 InnoDB 输出的示例：

 =====================================
 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 waited at ../include/btr0btr.ic line 28 for 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 locked in file ../include/btr0btr.ic line 28
 Last time write locked in file ../include/btr0btr.ic line 28
 Mutex spin waits 0, rounds 0, OS waits 0
 RW-shared spins 77, OS waits 33; RW-excl spins 188, OS waits 161
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 0 657853517
 Purge done for trx's n:o < 0 657853429 undo n:o < 0 80
 Total number of lock structs in row lock hash table 22
 020805 22:07:36  LATEST DETECTED 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 BE GRANTED:
 RECORD LOCKS space id 0 page no 104865 n bits 208 table test/ibtest11b index PRI
 MARY trx id 0 657853503 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
 19 lock struct(s), heap 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 200 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.;;
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 TABLE LOCK table test/ibtest11b trx id 0 657853500 lock_mode AUTO-INC waiting
 *** WE 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 heikki update
 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
 ------------------
 ---TRANSACTION 0 657853514, ACTIVE 5 sec, OS thread id 11275 inserting
 LOCK WAIT 13 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 BE GRANTED:
 RECORD LOCKS space id 0 page no 104879 n bits 384 table test/ibtest11d index B t
 rx id 0 657853514 lock_mode X gap type lock waiting
 Record lock, heap no 130 RECORD: info bits 32 0: len 9; hex 6b48646b6b6b6b6b6b;
 asc kHdkkkkkk;; 1:
 ------------------
 ---TRANSACTION 0 657853512, ACTIVE 5 sec, OS thread id 14348 updating or deletin
 g
 20 lock struct(s), heap size 2672, undo log entries 175
 MySQL thread id 5, query id 3874 localhost heikki updating
 delete from ibtest11a where A = 215
 --------
 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
 272 OS file reads, 56 OS file writes, 29 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/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 buffer(s)
 2895.70 hash searches/s, 126.62 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 19 3267291494
 Log flushed up to   19 3267283711
 Last checkpoint at  19 3266545677
 0 pending log writes, 0 pending chkp writes
 30 log i/o's 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  39
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages read 178, created 27, written 50
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 Buffer pool hit rate 1000 / 1000
 --------------
 ROW OPERATIONS
 --------------
 1 queries inside InnoDB, 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
 ============================
 

输出信息的某些注意点：

• 如果 TRANSACTIONS 部分报告锁定等待(lock waits)，那么你的应用程序可能有锁争用(lock contention)。输出信息可以帮助跟踪事务死锁的原因。
• SEMAPHORES 部分报告线程等待信号量以及统计出线程需要旋转(spin)或等待(wait)一个互斥(mutex)或 rw-lock 信号量的次数。一个较大的线程等待信号量的次数可能是由于磁盘 I/O 引起，或 InnoDB 内部的争用问题(contention problems)。争用(Contention)可能是由于比较繁重的并发性查询，或操作系统的线程调度的问题。 在这种情形下，可将 innodb_thread_concurrency 设置地小于默认的 8 。
• FILE I/O 部分列出了文件 I/O 的等待请求。过大的值就意味着磁盘 I/O 瓶颈。
• BUFFER POOL AND MEMORY 部分给出了页面读写的统计。通过这些值可以计算出你的查询通常所需的数据文件 I/O 量。