Day30-MySQL内部服务器优化小结-同乐学堂

前言

虽然我们常说约定优于配置，但是在大规模团队的规范日益成熟的情况下，配置也就是最核心的事情了！不管什么事情，一定要先立规范，再去配置！

在前面章节，可能里面包含了很多优化的细节，但是远远还不够，怎么多章节，我们围绕的核心仅仅的是“优化”，可能有些童鞋有疑问，我们有大量的缓存服务器，和代理服务器。数据库我们为什么还要花费精力在那上面，可是有木有想过，终究会有request会渗透到DB上的！那时吼，就只能靠底层DB自身来抗住，数以百万、千万的请求了！

也不是非得花费大精力在优化底层DB上，有的时候，优化业务，前端代码，用户的行为习惯都会解决很大难题，甚至解决DB解决不了的问题。

其实也不是很必要，只要你的架构设计的够轻巧，缓存服务器命中率很高，一般就不用过分担忧DB,除非您的代码，垃圾的不要不要的~

一、优化的五点核心建议

1、DB的配置，应该由工作负载，数据，应用需求，硬件、多方位来结合这些因素考虑分析在进行配置！

2、MySQL的配置文件可修改的参数太多，但是不要随便修改，通常把最基本的配置正确，就可以了! （基本配置例如：（缓冲池和日志文件缓存大小））

3、我们的时间应该花费在schema的优化、索引、查询设计上！

4、如果还有精力，可以微小的去调整其他参数来提高性能，但是前提需要进行迭代的测试，降低潜在的风险（有精力并且公司业务有太大的数据请求、并发量，可以做此项工作）

5、一次只改变一个设置！这是测试改变是否有益的唯一方法。

二、创建MySQL配置文件

要牢牢记住：“ 没有一个适合所有场景的最佳配置文件”和“MySQL编译的默认设置并不都是靠谱的”。

注意事项：InnoDB是现在MySQL的默认存储引擎，但是为了杜绝在使用默认引擎为MyISAM的第三方软件的时候导致不可预估的故障出现！

强烈要求，在使用建表语句中明确指定存储引擎！

如果一开始决定要用哪个存储引擎，一定要显示的设置，防止整岔辟了~。（一直认为我用的是MyISAM,怎么都变成了InnoDB,OnMyGod~）

三、InnoDB缓冲池大小的设置

默认的情况下，要想InnoDB引擎运行的比较稳定：（设置大小合适的缓冲池、和日志文件）是必须的！并且缓冲池应该去设置为服务器内存的75%-80%.

更精确的计算公式：

1、高性能MySQL作者的设置：

【（服务器内存总量）减去（操作系统的内存占用）减去（足够让操作系统缓存InnoDB日志文件的内存）减去（其他配置MySQL缓存和缓存需要的内存[例如：查询缓存、key缓存]）】除以【百分之105 】约等于？（四舍五入）

2、网友调优设置：

查看命令：


show global status like &apos;Innodb_page_size&apos;
show global status like &apos;Innodb_buffer_pool_pages_total&apos;;
show global status like &apos;Innodb_buffer_pool_pages_data&apos;;
show global variables like &apos;innodb_buffer_pool_size&apos;;

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size，

建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)

命令如：SET GLOBAL innodb_buffer_pool_size= 32423423：单位kb

3、一些专业DBA给出的建议是百分之70%。

四、一些注意事项

注意事项

注意的：linux本身的限制，会导致服务器高并发的时候，报错！

OS error code 24: Too many open files

ulimit -a 查看

我们可以看到open files 的最大数为1024，增大其值就可以了！

检查服务器状态变量:(每分钟查看状态变量的增量变化)

mysqladmin -uroot -pxxxx extended-status -ri60

五、线程缓存

线程缓存保存了和当前连接无关的线程。这些线程可以提供新连接使用。

...

只要缓存中有自由的线程，MySQL就能很快的响应连接请求，因为它不需要为每个连接都创建新的线程。

thread_cache_size定义了MySQL能采缓存中保存的线程数量。可以通过观察threads_created变量的值，以确定线程缓存是否足够大。

每秒创建的线程数量少于10个，缓存的大小就是足够的。

一个好办法是观察Threads_connected的值，并且把thread_cache_size的值设置的足够大，以处理波动的负载。

.......

六表缓存

表缓存存储了能表示表的对象。缓存中每个对象都包含了解析表后生成的.frm文件和其他数据，对象中的其他东西依赖于标的存储引擎。

表缓存有助于复用资源。

表缓存的设计有点以MyISAM为中心，对于InnoDB来说，表缓存没那么重要，InnoDB在很多方面都不会依赖于它（例如保存文件描述符，InnoDB为此有自己的表缓存）。但是，InnoDB还是可以从解析后的.frm文件中获益。

表缓存被分成两个部分：一部分为打开表。另一部分为表的定义（通过table_open_cache和talbe_definition_cache定义).....通常可以吧table_definition_cache的值设置的足够高，以缓存所有表的定义。

Opened_tables的值很大或者正在上升，就说明表缓存不够大，应该增加系统变量table_cache的值（在MySQL中时table_open_cache)

如果MySQL不能打开更多文件的错误提示，更改open_files_limit解决这个问题。

线程和表缓存其实都不会使用太多内存，他们的好处在于可以保存资源。在高并发条件下，提供高效率。

七、 InnoDB数据字典

InnoDB自己有对每个表的缓存，叫做"表定义缓存"或者"数据字典"，它是不可配置的。

当InnoDB打开一个表的时候，它就向字典中添加一个相应的对象。表关闭时，他不会被从字典中删除。

如果用innode_file_per_table选项，那么对InnoDB任何时候能打开的.ibd文件数量还有另一个限制。这是由InnoDB存储引擎处理的，不是MySQL服务器，它受innodb_open_files的控制。

InnoDB为每个.idbw文件使用全局文件描述符。给innodb_open_files设置足够大，这样服务器就可以保留所有同时打开的.ibd文件。

八、 MySQL I/O调优

一些配置选项可以影响MySQL把数据同步到硬盘和进行恢复的方式。
他们通常对性能有很大影响。因为这其中涉及了昂贵得I/O操作。他们代表了性能和数据安全的折中。
通常来说，保证数据被立即而连续地写入磁盘代价是很高的。

如果愿意承担数据不能被真正写入的风险，可以增加并发减少I/O等待时间，但是要决定能承受多大风险。

8.1 MyISAM I/O调优 -- 因不用此存储引擎，此处忽略

8.2 InnoDB I/O调优

可以控制InnoDB如何恢复，还能控制他如何打开表及刷写数据，他们极大地影响了恢复和总体性能。

innoDB恢复过程是自动的并且在InnoDB启动的时候总会运行，但是还是可以影响他的行为。

它有复杂的链式缓冲区和文件，使它可以改进性能并且保证ACID属性，并且链上每一个环节都是可配置的。

对于普通使用者，一个很重要的配置是InnoDB日志文件的大小，InnoDB如何刷写日志缓冲区，以及InnoDB如何执行I/O.

<strong style="margin: 0px; padding: 0px;"--<InnoDB事务日志

InnoDB使用日志来减少提交事务的开销。它不是在每次事务提交的时候就把缓冲池刷写磁盘上,而是记录了事务。
事务对数据和索引做出的改变通常会被映射到表空间的随机位置，所以将这些改变写到磁盘上会引起随机I/O.
作为一条原则，随机I/O比顺序I/O开销要高得多，因为它需要时间在磁盘上找到正确的位置，并且还要等磁头移到相应位置上。

InnoDB使用自身的日志把随机I/O转换为顺序I/O。一旦日志被记录到磁盘上,事务就是持久的了,尽管这时修改还没有被写到数据文件中.如果发生了一些坏事（比如断电）,InnoDB可以回放日志并回复提交了的事务。

InnoDB最终要把改变写到数据文件中，因为日志大小是固定的。它以循环的方式写日志，当记录达到日志底部，就会又从顶部开始。它不会覆盖改变没有被应用到数据文件的记录，因为这会消除提交的事务唯一持久性的记录。

InnoDB使用后台线程智能地把改变写入到文件中。实际上，事务日志把随机数据文件I/O转换为顺序日志文件和数据文件I/O.把刷写工作变成后台进行可以让查询更迅速。

日志文件大小由innodb_log_file_size和innodb_log_files_in_group控制，并且他们对写入的性能影响极大。这两个文件默认大小都是5MB,总计为10MB。对于高性能负载，这个大小是不够的。...

<strong style="margin: 0px; padding: 0px;"--<日志文件大小和日志缓冲。

在InnoDB改变数据的时候，他会把这次改动的记录写到日志缓冲里面。日志缓冲保存在内存中。缓冲写满，事务提交或每一秒钟，不管哪种情况发生，InnoDB都会把缓冲区写到磁盘上的日志文件中。如有大型事务，就可以增加缓冲文件来减少I/O动作。用innodb_log_buffer_size控制。除非写入大量巨型BLOB记录，否则缓冲区1MB到8MB足够了。（够1秒用的就行)

可以通过检查SHOW INNODB STATUS命令的LOG部分检测InnoDB的日志和日志缓冲性能，还可以观察Innodb_os_log_written了解InnoDB向日志写入了多少数据。

<span style="margin: 0px; padding: 0px; color: rgb(0, 0, 255);"--<InnoDB如何刷写日志缓冲。日志缓冲区必须被刷写到持久性存储中，以保证提交了的事务能完全持久化。如果比起持久性，更在意性能，可以改变innodb_log_at_trx_commit的值来控制日志缓存被刷写到什么地方及刷写频率

<strong style="margin: 0px; padding: 0px;"--<InnoDB 如何打开并清写日志和数据文件innodb_flush_method选项让你可以配置InnoDB实际与文件系统进行交互的方式。--给默认值就行--先不管

<span style="margin: 0px; padding: 0px; color: rgb(0, 0, 255);"--<InnoDB表空间

InnoDB把数据保存在表空间中。
表空间实际上是快月了磁盘上的一个或多个文件的虚拟文件系统。
InnoDB出于很多考虑使用表空间，不仅为了存储表和索引。它保留了自己的撤销日志，插入缓存，双写缓存，以及表空间的其他内部结构。

配置表空间。可以使用innodb_data_file_path定义表空间文件。这些文件都在innodb_data_home_dir定义的目录中。
eg:
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

autoextend:表空间耗尽后自动增长。
max:限制延伸文件最大为xx

innodb_file_per_table:使InnoDB为每个表使用一个文件，它在数据库目录中以“表名.ibd”保存数据。

旧数据版本和表空间。InnoDB的表空间在写入负荷很重的环境中会增长得很大。
有的问题不是出在没提交的事务上，而是出在工作负载上，清理进程是单线程的，它跟不上需要被清理的老数据的数量。
SHOW INNODB STATUS的输出有助于锁定问题。...
如果有很多未被清理的事物并且表空间因它而增长，就可以强制mysql变慢，以使清理线程能跟上数据的变化。
为了减缓写入，可以把innodb_max_purge_lag设置为0以外的值。这个值表示等待清理的事物的最大数量，一单超过这个值，InnoDB就会延迟更新数据的事物，要知道负载才能决定这个值的最佳大小。

<span style="margin: 0px; padding: 0px; color: rgb(0, 0, 255);"--<双写缓冲InnoDB在对页面进行部分写入的时候使用了双缓冲，防止数据损坏。
部分写入发生在磁盘写入没有全部完成，并且只有16KB页面的一部分被写入的时候。
有很多原因(崩溃，缺陷等)会导致数据被部分写入,双写缓存在这种情况下保护了数据。

<span style="margin: 0px; padding: 0px; color: rgb(0, 0, 255);"--<另外的I/O调优 Sync_binlog

九、 MySQL并发调优

9.1 MyISAM并发调优 --越过

9.2 InnoDB并发调优

InnoDB是为高并发设计的，但是并不完美。InnoDB的结构仍然基于有限内存，单CPU和磁盘系统。
InnoDB某些方面的性能在高并发条件下下降的很快，唯一的解决办法是限制并发。
通过SHOW INNODB STATUS 输出中的 SEMAPHORES部门来确认是否发生了并发问题。
InnoDB用自己的“线程调度”程序来控制线程如何进入InnoDB的内核访问数据，以及进入内核之后可以执行的动作。

innodb_thread_concurrency变量限制了一次多少线程进入内核。
并发 = CPU数量x硬盘的数量x2

innodb_thread_sleep_delay 默认值是10000毫秒，当CPU大量线程处理在"进入队列前的休眠"，因为没有被充分利用时

改变这个值可能会有帮助，在有大量的小查询的时候，这个值可能需要调小点!

十、优化排序（Filesorts）

如果查询中所有需要的列和order by的列总大小超过max_length_for_sort_data字节，则采用two-pass算法，否则采用single-pass算法。

可以用sustring 把text或者Blob 转换一下，就可以用single-pass算法了1

MySQL有两个变量可以控制排序怎样执行，通过修改max_length_for_sort_data的值。

mysql无法查看到排序用的具体算法，所以谨慎调节max_length_for_sort_data,因为增加它会增加磁盘使用率，降低cpu使用率。

排序具体案例分析

http://www.cnblogs.com/cchust/p/5304594.html

参考来源

1、高性能MySQL