MySQL 第17天 优化MySQL server

本章将分为两部分,

第一部分介绍MySQL Server 端参数的查看方法;

第二部分将详细介绍 MySQL Server 中一些与性能相关的重要参数的优化设置方法

查看 MySQL Server 参数

用 SHOW VARIABLES 和 SHOW STATUS 命令查看 MySQL 的服务器静态参数值和动态运行状态信息

其中前者是在数据库启动后不会动态更改的值，如缓冲区大小、字符集、数据文件名称等;

后者是数据库运行期间的动态变化的信息,比如锁等待、当前连接数等

MySQL 服务器的参数很多,如果需要了解某个参数的详细定义,可以使用以下命令

[mysql@bj72 mysql]$ mysqld --verbose --help|more

输出结果分为两部分:第一部分是对服务器参数的介绍,第二部分
是当前服务器的实际参数值。如果需要查询某个参数的定义和当前值,可以用操作系统命令进行过滤

影响 MySQL 性能的重要参数

这些参数分为两部分,前两节介绍的参数“key_buffer_size”和“table_cache”仅仅适用于 MyISAM 存储引擎;
后面几节介绍的也仅仅适用于 InnoDB 存储引擎,这些参数很容易辨认,因为它们都是以“innodb_”开始。

key_buffer_size 的设置

这个参数是用来设置索引块(Index Blocks)缓存的大小,它被所有线程共享,此参数只适用于 MyISAM 存储引擎

1   建立一个索引缓存

mysql> set global hot_cache2.key_buffer_size=128*1024;

global 表示对每一个新的连接,此参数都将生效。 hot_cache2 是新的 key_buffer 名称。

如果需要更改参数值,可以随时进行重建

2     然后可以把相关表的索引放到指定的索引缓存中

mysql> cache index sales,sales2 in hot_cache2;

3     要想将索引预装到默认 key_buffer 中,可以使用 LOAD INDEX INTO CACHE 语句。例如,
下面的语句可以预装表 sales 的所有索引:

mysql> load index into cache sales ;

4      如果需要删除索引缓存,则要使用下面命令:

mysql> set global hot_cache2.key_buffer_size=0;

请注意不能删除默认 key_buffer。

5      查看key_buffer_size的情况

mysql> show variables like 'key_buffer_size';

每次服务器重启时
key_buffer 中的数据将清空。如果想要每次服务器重启时相应表的索引能自动放到 key_buffer
中,可以在配置文件中设置 init-file 选项来指定包含 cache index 语句的文件路径,然后在对
应的文件中写入 cache index 语句

table_cache 的设置

这个参数表示数据库用户打开表的缓存数量。每个连接进来,都会至少打开一个表缓存。
因此,table_cache 与 max_connections 有关

可以通过检查 mysqld 的状态变量 open_tables 和 opened_tables 确定这个参数是否过小,
这两个参数的区别是前者表示当前打开的表缓存数,如果执行 FLUSH TABLES 操作,则此系
统会关闭一些当前没有使用的表缓存而使得此状态值减小;后者表示曾经打开的表缓存数,
会一直进行累加,如果执行 FLUSH TABLES 操作,值不会减少。下面的例子验证了这个过程。

mysql> flush tables;

mysql> show global status like 'open_tables';

对于已经在此链接中打开过一次，所以保存在缓存中。再次运行，open_tables   opened_tables   次数不会增加

innodb_buffer_pool_size 的设置

这个参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和
MyISAM 存储引擎不同,MyISAM 的 key_buffer_size 只缓存索引键,而 innodb_buffer_pool_size
却是同时为数据块和索引块做缓存,这个特性和 Oracle 是一样的。这个值设得越高,访问
表中数据需要的磁盘 I/O 就越少。在一个专用的数据库服务器上,可以设置这个参数达机器
物理内存大小的 80%。尽管如此,还是建议用户不要把它设置得太大,因为对物理内存的竞
争可能在操作系统上导致内存调度

innodb_flush_log_at_trx_commit 的设置

这个参数是用来控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的
操作时机。对这个参数的设置可以对数据库在性能与数据安全之间进行折中

 当这个参数是 0 的时候,日志缓冲每秒一次地被写到日志文件,并且对日志文件做
向磁盘刷新的操作,但是在一个事务提交不做任何操作。
 当这个参数是 1 的时候,在每个事务提交时,日志缓冲被写到日志文件,并且对日
志文件做向磁盘刷新的操作。
 当这个参数是 2 的时候,在每个事务提交时,日志缓冲被写到日志文件,但不对日
志文件做向磁盘刷新的操作,对日志文件每秒向磁盘做一次刷新操作。

innodb_flush_log_at_trx_commit 参数的默认值是 1,也是最安全的设置,即每个事务提
交的时候都会从 log buffer 写到日志文件,而且会实际刷新磁盘,但是这样性能有一定的损
失。如果可以容忍在数据库崩溃的时候损失一部分数据,那么设置成 0 或者 2 都会有所改善。
设置成 0,则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务,最多丢失 1 秒钟
的事务,这种方式是最不安全的,也是效率最高的。设置成 2 的时候,因为只是没有刷新到
磁盘,但是已经写入日志文件,所以只要操作系统没有崩溃,那么并没有丢失数据,比设置
成 0 更安全一些。

innodb_additional_mem_pool_size 的设置

这个参数是 InnoDB 存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小,其默认值是 1MB。

应用程序里的表越多,则需要在这里分配越多的内存。如果 InnoDB 用光
了这个池内的内存,则 InnoDB 开始从操作系统分配内存,并且往 MySQL 错误日志写警告信
息。没有必要给这个缓冲池分配非常大的空间,在应用相对稳定的情况下,这个缓冲池的大
小也相对稳定,系统默认值是 1MB。

innodb_lock_wait_timeout 的设置

MySQL 可以自动地监测行锁导致的死锁并进行相应的处理,但是对于表锁导致的死锁不
能自动的监测,所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。系统
默认值是 50 秒,用户可以根据应用的需要进行调整。

innodb_support_xa 的设置

通过该参数设置是否支持分布式事务,默认值是 ON 或者 1,表示支持分布式事务。如
果确认应用中不需要使用分布式事务,则可以关闭这个参数,减少磁盘刷新的次数并获得更
好的 InnoDB 性能。

innodb_log_buffer_size 的设置

含义是日志缓存的大小

默认的设置在中等强度写入负
载以及较短事务的情况下,一般都可以满足服务器的性能要求。如果存在更新操作峰值或者
负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存,因为它每
秒都会刷新一次,因此无需设置超过 1 秒所需的内存空间。通常设置为 8~16MB 就足够了。
越小的系统它的值越小。系统默认值是 1MB。

innodb_log_file_size 的设置

该参数含义是一个日志组(log group)中每个日志文件的大小。此参数在高写入负载尤
其是大数据集的情况下很重要。这个值越大则性能相对越高,但是带来的副作用是,当系统
灾难时恢复时间会加大。系统默认值是 5MB。