InnoDB的性能调优(一)

作者：lg   来自：china-lg.com

性能调优的几个提示： 1. 如果 CPU 资源利用率小于 70% 的话，你的负载可能是由于磁盘引起的，可能有太多的事务提交，或者缓冲池太小。建议增加缓冲池。 2. 把多个修改包在一个事务里面， InnoDB 每次提交事务需要刷新日志到磁盘，而磁盘的速度和 CPU 相比一般都很慢，因此，建议不要频繁提交事务。 3. 如果你能承受最近几个事务的丢失，建议设置 innodb_flush_log_at_trx_commit 为 0 ，InnoDB 每秒刷新一次日志。 4. 加大日志文件，组合起来的所有日志文件大小应该和缓冲池一样大，很小的日志文件将会导致很多不必要的磁盘写操作，大日志文件的缺陷是恢复时间会加长。 5. 同样需要加大日志缓冲，例如 8 MB。 6. 采用 VARCHAR 类，而不是 CHAR 类型，小的表更适合缓冲池，从而较少了磁盘 I/O。 7. 在 Linux 和一些 Unix 系统上，采用 Unix fsync 命令刷新文件，但是有些情况下，可能会很慢，可以修改 innodb_flush_method 为 O_DSYNC 来提高写性能。 8. 导入数据时，要确保 autocommit=1 没有打开，在导入的 SQL 文件最前面加入： set autocommit=0; commit; 如果采用 mysqldump --opt 选项，你得到的 dump 文件能快速导入 InnoDB 表。 9. 留意大批量插入时的回滚操作。 10. 留意那些消耗磁盘资源的操作，采用 DROP TABLE 或者 TRUNCATE 来清空表，而不是DELETE FROM 。. 11.如果要插入多行时， 使用多行 INSERT ，来减轻客户端和服务器端的通信量。 INSERT INTO yourtable VALUES (1, 2), (5, 5); 上面的多行（原文为 multi-line，我认为应该为 multi-row）。这条提示不仅仅适用于 InnoDB ，对于其他表类型也适用。 从 3.23.42 开始，InnoDB 包括了 InnoDB 监视器，能输出InnoDB 内部状态的信息，这些数据在性能调优时十分有用，当打开时，监视器能每15秒在服务器端输出数据到标准输出，如果不是从命令行启动的话，这些输出会写入到 .err 文件中，在 Windows 平台上，必须在 DOS 命令行下以 --standalone --console 参数启动。 主要信息包括： 每个激活的事务锁住的表以及记录 事务的锁等待 线程的信号等待 延迟的文件 i/o 请求 缓冲池统计以及删除和插入缓冲合并的活动 启动的命令为： CREATE TABLE innodb_monitor(a int) type = innodb; 停止的语法是： DROP TABLE innodb_monitor; 如果数据库关闭时，监视器还在运行，下次启动监视器前必须先关闭。 用同样的语法你可以启动 innodb_lock_monitor 以及 innodb_tablespace_monitor 性能优化的一些主要参数： set-variable = max_connections=200 set-variable = record_buffer=1M set-variable = sort_buffer=1M # 如果为 MyISAM 表的话，设置 key_buffer 为内存的 5 - 50 % ， # 但是对于 InnoDB 来说，必须保证 key_buffer+ InnoDB 缓冲池小于内存的 80%。 set-variable = key_buffer=... 另外值得注意的几个参数是： innodb_file_io_threads 文件 i/o 的线程数，一般为 4，但是在 Windows 下，可以设置得较大。 innodb_fast_shutdown InnoDB 缺省在关闭之前清理缓冲，这个过程可能需要几分钟，在极端的情况下可能需要几个小时，如果这个参数设置为 1 的话，InnoDB 就略过这个过程，从 3.23.50 开始这个参数的缺省值为 1 。 innodb_thread_concurrency InnoDB 努力把操作系统线程保留下来，缺省的值是 8，如果你的性能比较低， innodb_monitor 显示很多线程等待信号，应该把这个值设置得小一些，如果你的系统有很多处理器和磁盘则可以调高这个值。建议的值为 处理器数目加上磁盘数。 配置好 my.cnf 或者 my.ini 的参数之后，就可以启动 MySQL 建立 InnoDB 了，第一次建立之前，建议你手工启动，这样你就可以看到 InnoDB 的建立过程： heikki@donna:~/mysql-3.23.48/sql> mysqld 020204 23:17:12 InnoDB: The first specified data file /dr2/tmp/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /dr2/tmp/heikki/data/ibdata1 size to 20 MB InnoDB: Database physically writes the file full: wait... 020204 23:17:16 InnoDB: Data file /dr2/tmp/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /dr2/tmp/heikki/data/ibdata2 size to 200 MB InnoDB: Database physically writes the file full: wait... 020204 23:17:41 InnoDB: Data file /dr2/tmp/heikki/data/ibdata3 did not exist: new to be created InnoDB: Setting file /dr2/tmp/heikki/data/ibdata3 size to 1000 MB InnoDB: Database physically writes the file full: wait... 020204 23:21:37 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 10 MB InnoDB: Database physically writes the file full: wait... 020204 23:21:39 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 10 MB InnoDB: Database physically writes the file full: wait... 020204 23:21:41 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020204 23:21:45 InnoDB: Started mysqld: ready for connections 如果采用 mysqladmin shutdown 命令关闭时，显示： 020204 23:34:45 mysqld: Normal shutdown 020204 23:34:45 InnoDB: Starting shutdown... 020204 23:34:47 InnoDB: Shutdown completed 020204 23:34:47 mysqld: Shutdown Complete 进入日志文件所在的目录，我们可以看到 ib_arch_log_0000000000 这样的文件。 下面我们来建立一个 InnoDB 表，假设我们用 mysql test 进入： CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB; 查看状态： SHOW TABLE STATUS FROM test LIKE 'CUSTOMER' 需要注意的是，不要手工删除 InnoDB 数据库目录下的 .frm 文件，而是采用 create table 和 drop table 的方法。 把 MyISAM 转化为 InnoDB *注意* MySQL 的系统表， user 或者 host 等不能转化为 InnoDB 格式，他们必须采用 MyISAM 格式！！ 如果你想以后建立的所有表都为 InnoDB 格式，在配置文件中加入： default-table-type=innodb InnoDB 没有一个特殊的索引建立优化机制，因此不能采取 export/import ，然后建立索引的办法来转化，最快的办法是把表类型改变为 InnoDB ，然后直接插入数据： ALTER TABLE ... TYPE=INNODB 或者建立一个空的具有同样结构的 InnoDB 表，然后插入数据： INSERT INTO ... SELECT * FROM .... 为了更好的控制数据插入进程，最好把很大的表分批插入： INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something AND yourkey <= somethingelse; 等数据全部插入完毕后，可以重新命名表名。 在插入过程中，要把 InnoDB 缓冲池设置得大点，以减少磁盘 I/O。同时还应该把日志文件和日志缓冲加大。 需要注意的是，不要让表空间用尽，如果 ALTER TABLE 用完了表空间，将会回滚，如果磁盘不够的话，这个过程会持续几个小时。 外键约束 从 MySQL 3.23.43b 开始， InnoDB 就支持外键约束，作为数据引用完整性的重要特色，InnoDB 在外键支持上开创了 MySQL 各种表类型的先河。 建立外键的方法： 例子： CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; 两表都必须为 InnoDB 类型，外键和被引用的键，必须是索引中的一个列，InnoDB 不会自动为外键建立索引，必须明确指明。 如果 CREATE TABLE 语句发生 1005 号错误，错误信息字符串引用 150 号错误，那么就是外键约束起了作用。 只有在父行中包含 NULL 的列，才允许在子行中包含 NULL。 目前任何 ALTER TABLE 操作都会删除外键约束，因此，建议采用 DROP TABLE 然后 CREATE TABLE 来修改 schema。因为 ALTER TABLE 在内部使用 RENAME TABLE 操作，这样就能混淆外键的引用，同样的 CREATE INDEX 也是作为 ALTER TABLE 来处理的，也不能用于外键约束的表。 InnoDB 允许你 drop 任何表，即使这样会打破外键，这样操作的结果就是约束也被 drop 了。 查看表 T 中的外键约束状态： SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' InnoDB 还不支持 CASCADE ON DELETE 或者其特殊的约束选项。（我们知道 Oracle 里面有这个东东了） 处理自动增量的列的时候需要注意的问题是：自动增量的列不参加锁机制和事务处理，因此回滚时，会造成序列中间的空档。 增加 InnoDB 数据和日志文件 不能增大 InnoDB 数据文件的长度，增加表空间的办法是新添加数据文件： innodb_data_file_path，然后重新启动 MySQL 。 同样的，目前你还不能删除数据文件，为了减小数据文件的长度，需要导出数据，建立新数据库，然后再导入。 如果需要改变日志文件的大小，可以先关闭 MySQL ，然后把旧的日志文件移动到一个备份目录，修改 my.cnf ，重新启动 MySQL ，确信 InnoDB 正常启动后，可以把旧的日志文件删除了。 InnoDB 数据库的备份： 关闭 MySQL ，并确保正常关闭。 复制所有的数据文件。 复制所有的 InnoDB 日志文件。 复制 my.cnf 配置文件。 复制所有 InnoDB 表的 .frm 文件。 目前还没有热备份或者增量备份的工具。对于要求高可靠的服务器而言，可以采用复制的形式。 数据恢复的过程，就是把前面备份的这些文件重新考回原来的位置。这种情况只有在数据文件损坏的情况下，才有必要。一般的数据库瘫痪，重新启动 MySQL ，InnoDB 会恢复事务。 如果确实有表空间不能回复的话，可以采用 SELECT INTO OUTFILE 来恢复数据。这个时候需要在 my.cnf 中设置： set-variable = innodb_force_recovery = 4 InnoDB 的数据文件在所有平台上二进制兼容，因此只要只要两台机器的浮点数格式一致，你就可以在这两台机器之间移动文件。