mysql数据库性能优化 - 参数优化

Mysql Server优化                                                                                                                                

1、查看mysql server参数

• show variables; / show variables like ‘参数名‘;   --mysql服务器配置信息
• show status; / show global status; / show status like ‘字段名‘;  --mysql服务器运行的各种状态
• mysqladmin -uroot variables; --os下直接查看
• mysqladmin -uroot status;
• mysqld --verbose --help|more --查看参数的详细定义
• 日志：show variable like ‘log_%‘;  --是否启用了日志    show master status;  --日志状态    show master log;  --日志信息

2、影响mysql性能的重要参数

1）链接数

• show variables  like ‘max_connections‘;   --查看mysql允许的最大链接数（也即是/etc/my.cnf中配置的该参数）
• show global status like ‘Max_used_connections‘;  --查看mysql以往响应的最大连接数

      比较理想的设置：Max_used_connections / max_connections * 100% = 85%

2）索引未命中缓存的概率

• show global status like ‘key_read%‘;   

     key_cache_miss_rate = key_reads /  key_read_requests * 100%（在0.1%以下都很好，如果在0.01%以下的话，而key_buffer_size分配过多，可以适当减少）

     理解：key_read_requests是从cache中读索引的请求，而key_reads是从物理硬盘上读索引的次数。每次需要使用索引的时候就会先向cache中请求，cache中没有该索引的时候就再从硬盘上读。

3）表描述符缓存

•  show variables like ‘table_open_cache‘;  --表描述符缓存大小，mysql每打开一个表都会读入一些数据到table_open_cache缓存中，当mysql在这个缓存中找不到相应的信息时，才会到磁盘上直接读取。
•  show global status  like ‘open%tables%‘; -- open_tables：打开表的数量；opened_table：打开过的表的数量，如果该过大，说明table_open_cache值可能太小。

比较合适的值：

　　open_tables / opened_tables * 100% >= 85%

　　open_tables / table_open_cache * 100% <= 95%

4）表扫描率

• show global status like ‘Handler_read_rnd_next‘;  --在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
• show global status like ‘com_select‘;  --com_select变量记录的是无缓存的查询次数+错误查询次数+权限检查查询次数
• show variables like ‘read_buffer_size‘;   --表扫描时的缓冲区
• show variables like ‘read_rnd_buffer_size‘;  --随机扫描时的缓冲区，适当调大对orader by 操作的性能有一定效果

     表扫描率 = Handler_read_rnd_next / com_select   --超过400说明有太多的表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8M。

5）innodb_flush_log_at_trx_commit   --控制缓冲区中数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机

• show variables like ‘innodb_flush_log_at_trx_commit‘;

0：log thread每隔1s会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush操作，保证数据确实已经写入到磁盘上面的物理文件。（性能最好，安全性最差）

1：每次事务结束时都会触发log thread将日志缓存中的数据写入到文件并通知文件系统同步文件。（最安全，性能稍差）

2：log thread会在每次事务结束的时候讲数据写入到事务日志，但是这里写入仅仅是调用文件系统的文件写入操作，文件系统什么时候会将缓存中的数据同步到物理文件，log thread就完全不知道。（对数据一致性和完整性要求不高时使用）

一般原则：

• 如果交易数据非常重要（例如银行交易），那么这个参数必须设置为1。
• 如果是一般的互联网应该（例如社区讨论），那么可以设置为2，可以获得比较好的写入性能。
• 在生产环境中，一般没有理由将此参数这是为0。

6）innodb_log_file_size --一个日志组中每个日志文件的大小，默认5M

    show variables like ‘innodb_log_file_size ‘;

    在高写入负载，尤其是大数据的情况下很重要。这个值越大则性能相对越高，但是带来的副作用是，当系统遭难是恢复时间会加长（扫描恢复的时间越长）。一般用64M-512M，具体取决于服务器的空间。