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

1、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. 在一个事务中包含几个修改。如果事务对数据库进行了修改，那么在这个事

2、务提交时 InnoDB 必须刷新日志到磁盘上。因为硬盘的旋转速度通常至多为167 转/ 秒，那么只要磁盘不欺骗操作系统， 提交的事务数目限止也同样为 167 次/秒用户。3. 如果掉失最近的几个事务无所谓的话，可以在 f 文件中将参数 innodb_flush_log_at_trx_commit 设置为 0 。 InnoDB 无论如何总是尝试一秒 刷新 (flush) 一次日志，尽管刷新并不能得到保证。4. 将日志文件 (log files) 设大一点，使日志文件的总和正好与缓冲池 (buffer pool) 一样大。当 InnoDB 用光日志文件的空间时，它不得不在一个时间点上 将缓冲池内修

3、改过的内容写到磁盘上。 小的日志文件可能引起不必要的磁盘写 操作。但是大的日志文件的缺点就是在数据恢复时将占用较长的时间。5. 同样 log buffer 尽量设大点，比如说 8 MB6. 如果要存储变长的字符串或字段可能会包含大量的 NULLs ，请使用VARCHA型字段代替CHAR。一个CHAR(n)字段总是使用n bytes 来存储数 据，即使这个字符串很短或是一个 NULL 值。较小的表更加适合缓冲池同时能 够减少磁盘 I/O 。7. (适合从 3.23.41 以上版本 ) 在某些版本的 Linux 和 Unixes 中，使用 Unix fsync 或其它类似的方法将文件刷新到磁盘是异

4、常地慢的。 InnoDB 默认 的方法就是 fsync 。如果你对数据库系统的磁盘写性能不能感到满意，你可以 尝试在 f 中将 innodb_flush_method 设置为 O_DSYNC尽管 O_DSYN选 项在多数的系统上看起来比较慢。8. 在向 InnoDB 导入数据时，请确认 MySQL 没有打开 autocommit=1 。否 则每个插入语句都要将 log 刷新到磁盘。在你的 SQL 导入文件的第一行加入set autocommit=0;并在最后一行加入commit;如果使用 mysqldump 选项 -opt ，你将会得到一个快速导入 InnoDB 表的转 储 (dump) 文件

5、，甚至可以不再使用上面所提的 set autocommit=0; .commit; 。9. 小心 insert 集全的大回滚 (roolback) ：在插入时 InnoDB 使用插入缓冲 来减少磁盘 I/O ，但在相应的回滚中却没有使用这样的机制。 一个 disk-bound rollback 可能会花费相应插入时间的 30 倍。如果发生一个失控的回滚，你可 以查看第 6.1 章节的技巧来停止它。10. 同样也要小心一个大的 disk-bou nd的操作。使用 DROP TABL或TRUNCATE从MySQL-4.0 以上)来清空一个表，而不要使用DELETE FROMyourtable 。1

6、1. 如果需要插入大量记录行可以使用多行 (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 可能使用自身的 插入缓冲来分批地记录辅助索引。1

7、3. 如果在表中有一个子 FOREIGN KEY勺束，从 3.23.52 和4.0.3 开始， 可以通过在一个导入会话中将外键检查 (foreign key check) 关闭来提高数据导 入速度：SET FOREIGN_KEY_CHECKS=0;对一个大的表导入这将减少大量的磁盘 I/O9.1 InnoDB 监视器 (Monitors)从版本3.23.42 开始，InnoDB 中就包含了 InnoDB Monitors,它可以显示出 InnoDB 的内部状态。从版本 3.23.52 和 4.0.3 开始，你可以使用一个 新的 SQL 命令SHOW INNODB STATUS来读取标准 Inn

8、oDB Monitor 给 SQL client 的输出信息。 这些信息对性能调 整有益。另外一个使用 InnoDB Monitors 方法就是让它在服务程序 mysqld 的标准输 出上持续地写出信息。当开关打开时， InnoDB Monitors 大约每 15 秒显示 一次数据(注意：MySQL的客户端并不会显示任何东西)。一个简单地使用它的 方法就是以一个命令行方式执行 mysqld 。否则输出将会定向到 MySQL 服务 错误日志 (error log file) 中 'yourhostname'.err ( 在 Windows 下为 mysql.err )，在 Win

9、dows 系统中必须在 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 和 inse

10、rt buffer 归并活动 (merge activity)通过下列的 SQL 命令，可以使标准的 InnoDB Monitor 记录到标准的 mysqld 的输出上：CREATE TABLE innodb_monitor(a int) type = innodb;通过它来停止：DROP TABLE innodb_monitor;CREATE TABLED法只不过是为了通过 MySQL SQL 语法分析而提供给 InnoDB 引擎命令的一种方式：那个被创建的表根本与 InnoDB Monitor 无 任何关系。 如果你在监视器运行着的状态下关闭数据库， 并且你需要再次启动监 视器，那么你不得

11、不在发出一个新的 CREATE TABLED启动监视器之前先移除 (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 MON

12、ITOR OUTPUTPer second averages calculated from the last 3 secondsSEMAPHORESOS WAIT ARRAY INFO: reservation count 194, signal count 193-Thread 7176 has waited at ./include/btr0btr.ic line 28 for 0.00 seconds the semaphore:X-lock on RW-latch at 44d980bc created in file buf0buf.c line 354a writer (thre

13、ad id 7176) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 1Last time read locked in file ./include/btr0btr.ic line 28Last time write locked in file ./include/btr0btr.ic line 28Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 77, OS waits 33; RW-excl spins 188, OS wai

14、ts 161TRANSACTIONSTrx id counter 0 657853517Purge 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:36LATEST DETECTED DEADLOCK:* (1) TRANSACTION:TRANSACTION 0 657853503, ACTIVE 0 sec, OS thread id 15373 insertingLOCK WAIT 3

15、 lock struct(s), heap size 336MySQL 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 PRIMARY trx id

16、 0 657853503 lock_mode X waitingRecord lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; ascsupremum.;* (2) TRANSACTION:TRANSACTION 0 657853500, ACTIVE 0 sec, OS thread id 11275 setting auto-inc lock19 lock struct(s), heap size 2672, undo log entries 5MySQL thread id 2, query id

17、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 PRIMARY trx id 0 657853500 lock_mode XRecord lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73

18、757072656d756d00; ascsupremum.;* (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 lockLO

19、CK WAIT 1 lock struct(s), heap size 336MySQL 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-IN

20、C waiting-TRANSACTION 0 657853514, ACTIVE 5 sec, OS thread id 11275 insertingLOCK WAIT 13 lock struct(s), heap size 2672, undo log entries 2MySQL thread id 2, query id 3898 localhost heikki update insert into ibtest11d (D, B, C) values (5, 'khdkkkk' ,'khdkkkk') TRX HAS BEEN WAITING 5

21、 SEC FOR THIS LOCK TO BEGRANTED:RECORD LOCKS space id 0 page no 104879 n bits 384 table test/ibtest11d index B trx id 0 657853514 lock_mode X gap type lock waitingRecord lock, heap no 130 RECORD: info bits 32 0: len 9; hex 6b48646b6b6b6b6b6b;asc kHdkkkkkk; 1:-TRANSACTION 0 657853512, ACTIVE 5 sec, O

22、S thread id 14348 updating or deleting20 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 = 215FILE I/OI/O thread 0 state: waiting for i/o requestI/O thread 1 state: waiting for i/o requestI/O thread 2 state

23、: waiting for i/o requestI/O thread 3 state: waiting for i/o requestPending normal aio reads: 0, aio writes: 0,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0272 OS file reads, 56 OS file writes, 29 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 wr

24、ites/s, 0.00 fsyncs/sINSERT BUFFER AND ADAPTIVE HASH INDEXIbuf for space 0: size 1, free list len 5, seg size 7,0 inserts, 0 merged recs, 0 mergesHash table size 124633, used cells 1530, node heap has 4 buffer(s)2895.70 hash searches/s, 126.62 non-hash searches/sLOGLog sequence number 19 3267291494L

25、og flushed up to19 3267283711Last checkpoint at19 32665456770 pending log writes, 0 pending chkp writes30 log i/o's done, 0.00 log i/o's/secondBUFFER POOL AND MEMORYTotal memory allocated 82593970; in additional poolallocated 1406336Buffer pool size 1920Free buffers 1711Database pages 205Modified db pages 39Pending reads 0Pending writes: LRU 0, flu