-
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 %,
）你的系统瓶颈可能在磁盘
读写上。或许
你提交了大量的事务，或者是缓冲池
(buffe
r pool)
太小了。将缓冲池设大点会
有所帮助，但一定要
注意不能大于物理内存的
80%
。
2.
在一个事务中包含几个修改。如
果事务对数据库进行了修改，那么在这个事
务提交时
InnoDB
必须刷新日志到磁盘上。因为硬盘的旋转速度通常至多为
167
转
/
秒，
那么只要磁盘不欺骗操作系统，
提交的事务数目限止也同样
为
167
次
/
秒
·
用户。
3.
如果掉失最近的几个事务无所谓的话，可以在
文件中将参数
innodb_flu
sh_log_at_trx_commit
设置为
0
。
InnoDB
< br>无论如何总是尝试一秒
刷新
(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
。如果你对数据库系统的
磁盘写性能不能感到满意，你可以
尝试在
中将
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 <
/p>
倍。如果发生一个失控的回滚，你可
以查看第
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
开始，可以通过在
一个导入会话中将唯一键检查
(uniq
ueness 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 Moni
tors
，
它可以显示
出
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
下为
)
，在
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)
这个表。