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

原创 2003年05月22日 08:27:00

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 导入文件的第一行加入


并在最后一行加入 

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

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

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

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


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

 

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


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

 

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


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

 

9.1 InnoDB 监视器(Monitors)

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


来读取标准 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 
句法只不过是为了通过 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 输出的示例:


输出信息的某些注意点: 
  • 如果 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 量。

 

AM335X ARM CORTEX-A8 微处理器技术参考手册 (中文版详解)1

第一章、简介 1.1、AM335X家族 1.1.1、设备特性 对于不同的设备,其体系结构由不同的特性集合配置的。该技术参考手册详细的描述了AM335X设备的当前和以后的所以有用的特性。一些特性对于你的...
  • u012106397
  • u012106397
  • 2013年12月29日 20:46
  • 4779

了解人的社交风格,达到优质沟通的利器--社交风格手册(老虎 孔雀 考拉 猫头鹰)

老虎型(支配型Dominance) 典型特征: 企图心强,高支配性,自信权威,分析敏锐,决断力高,个性积极,竞争性强,喜欢冒险,独立自主,行动力强,讲求效率,坦率果决结果导向,喜欢评估。不喜...
  • kaka1121
  • kaka1121
  • 2016年12月15日 14:58
  • 1541

InnoDB性能调优基础(进阶)

InnoDB性能调优基础(进阶)
  • zhu19774279
  • zhu19774279
  • 2014年08月17日 22:54
  • 3189

InnoDB 中文参考手册(CHM)

  • 2004年09月10日 00:26
  • 160KB
  • 下载

mc9s12xe系列单片机参考手册--中文(01)

By: 江威 Date: 2011-11-03 Version: 0.1 Email: killkeil54@gmail.com Copyright: ...
  • fangwei1991
  • fangwei1991
  • 2011年11月10日 22:51
  • 2979

mc9s12xe系列单片机参考手册--中文(02)

2.1简介 2.1.1概述 S12XE系列端口集成模块建立了与外设模块之间的接口 包括非复用的外部总线接口模块(S12X_EBI)和所有端口I / O引脚。 它控制了引脚的电气特性以及信号优先...
  • fangwei1991
  • fangwei1991
  • 2011年11月10日 22:53
  • 1617

XMLHttpRequest中文参考手册(9)

  • 2013年06月06日 14:20
  • 66KB
  • 下载

VFP9CHS中文参考手册

  • 2008年08月28日 15:26
  • 9.85MB
  • 下载

libevent参考手册9

译自http://www.wangafu.net/~nickm/libevent-book/Ref6a_advanced_bufferevents.html   本章描述buffereve...
  • u011382745
  • u011382745
  • 2015年06月07日 08:41
  • 221

MySQL中文参考手册--获得数据库和表的信

确保你连接上了服务器,如在先前的章节讨论的。这样做本身将不选择任何数据库来工作,但是那很好。从这点讲,知道关于如何出询问的一点知识,比马上跳至创建表、给他们装载数据并且从他们检索数据要来的重要写。本节...
  • ANLJF
  • ANLJF
  • 2011年07月08日 16:45
  • 655
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:InnoDB 中文参考手册 --- 9 性能调整技巧
举报原因:
原因补充:

(最多只允许输入30个字)