快乐学习
前程无忧、中华英才非你莫属!

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 'Innodb_page_size'
show global status like 'Innodb_buffer_pool_pages_total';
show global status like 'Innodb_buffer_pool_pages_data';
show global variables like 'innodb_buffer_pool_size';

计算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           

2、mysql配置参数:http://www.jb51.net/article/48082.htm

3、MySQL 排序分析:http://www.cnblogs.com/cchust/p/5304594.html

4、一些专业DBA的建议

打赏

未经允许不得转载:同乐学堂 » Day30-MySQL内部服务器优化小结

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!