MyException - 我的异常网
当前位置:我的异常网» 数据库 » Mysql innodb储存引擎的性能优化二

Mysql innodb储存引擎的性能优化二

www.MyException.Cn  网友分享于:2015-08-26  浏览:7次
Mysql innodb存储引擎的性能优化二

3. InnoDB日志

3.1. Innodb_log_buffer_size

3.1.1. 不要设置超过2-9M,除非你使用大量的超大文件,日志文件都会被刷新在每秒执行完毕后。

3.1.2. 检查innodb_os_log_written的增长来看你的日志文件的写入。

3.1.3. Innodb日志是物理逻辑的,不是基于页的,所以他们是非常紧凑的。

3.2. Innodb_flush_logs_at_trx_commit

3.2.1. 默认日志被刷新到磁盘上在每次事务提交后。这个是为了保证ACID(原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)),所以开销非常大。

3.2.2. 可以设置2和0,如果你能接受丢失最后一次的事务。

4. InnoDB日志重新设置大小

4.1. 这个不是简单修改选项和重启下能够完成的。

4.2. 首先要关闭MysQL服务器

4.3. 确认它是正常关闭的(检查有没有错误日志)

4.4. 移动所有InnoDB日志文件到其它地方

4.5. 修改配置文件然后重新启动MySQL服务器

4.6. 检查错误日志并查看是否产生新的日志文件。

5. InnoDB_flush_method

5.1. 指定一个方法让innodb跟OS文件系统一起工作

5.2. Windows: 总是使用没有buffer的IO方法

5.3. Unix:可以使用fsync()或者O_SYNC/O_DSYNC进行刷新文件。

5.3.1. Fsync()通常是更快的,允许累计多个写操作然后并发执行。

5.3.2. 一些操作系统允许关闭OS级别的缓冲针对InnoDB的数据文件。这样非常好,你总不希望数据被缓冲2次吧。

5.4. Linux:O_DIRECT 使用直接的非缓冲IO。避免双重缓冲,可能会让写更慢。

6. Innodb_file_per_table

6.1. InnoDB可以存储每个表在单独的文件中。

6.2. 对于系统需要的主表空间还是需要的。

6.3. 能够帮助拆封不同的表到多个磁盘上。

6.4. 如果表被删除允许回收空间。

6.5. 有时候使用连续的fsync()写会更慢。

6.6. 当有大量的表的时候会增加启动和关闭的时间。

7. 其它文件IO设定

7.1. Innodb_autoextend_increment–这个参数指定了对于共享表空间的增量(不是为了单独表空间的)。大的值可以有效的减少碎片。

7.2. Innodb_file_io_thread–改变IO线程的数量,只对windows有效,所有4个线程可以做不同的事情。

7.3. Innodb_open_file–这个值是用作指定每个表空间所允许打开文件的数量。如果你有很多表那就要增加它。

7.4. Innodb_support_x–把这个值设置定为0的时候能够减少innodb的工作在事务提交时。Binlog 能够通过异步的方式同步。

8. 最小化重启时间

8.1. Innodb缓存池可能有一些未写入到磁盘的数据。所以关闭的时候需要非常的时间。

8.2. 如果你想最小化关闭时间,那需要如下设定:

8.2.1. SET GLOBAL innodb_max_dirty_pages_pct=0

8.2.2. 执行show status后观察 innodb_buffer_pool_pages_dirty

8.2.3. 当这个值变为0的时候就可以关闭服务器了

8.3. 在执行这个操作时候会让性能降低,因为InnoDB将立刻要将脏数据页写入到磁盘上。

9. 排错逃离清除

9.1. InnoDB不会通过delete来移除一行(和在更新时候旧的行),因为这些行可能会被其他事务使用。

9.2. 清除线程被用在清除这些没有用到的行。

9.3. 在一些工作负载,清除线程可能不能保持这些表空间无限的增长。通过show innodb status观察transactions部分。

9.4. Innodb_max_purge_lag–这个是用来限制事务每次所能更新或者删除最大的行数。将会延迟insert/update操作,所以清除线程会被保持。

9.5. 为什么我们不用多个清除线程呢?

10. 并发控制设置

10.1. 设置可以帮助InnoDB适应于控制大量的并发事务。

10.2. Innodb_thread_concurrency–InnoDB内核中同时可以允许最大的在线程数量(0表示没有限制),2*(CPU核数+磁盘数量)在理论上是一个合理的值,在实际应用中设置的更小一点可能会让性能更好一点。

10.3. Innodb_commit_concurrency–允许同一时间内事务提交的最大线程数。

10.4. Innodb_concurrency_tickets–在不得不退出内核空间和等待之前能运行线程的数量。

10.5. Innodb_thread_sleep_delay

10.6. Innodb_sync_spin_loops

11. 获得高性能的不安全方式

11.1. Innodb有一些检查和方法是数据不被最小化丢失和错误。

11.2. Innodb_doublewrite–这个值是用来保护部分页的修改,只是禁止假如OS保证它不会发生。

11.3. Innodb_checksums–在页中的数据校验码,帮助发现文件系统错误,内存损坏和其它问题。

11.3.1. 导致一部分大工作负载的机器会过载。

11.3.2. 当性能是第一的情况下可以禁止这个参数。

12. Innodb SHOW STATUS部分

12.1. Mysql5.0有一些性能计数信息通过show status能够显示出来。

12.1.1. 它们都是全局的,虽然大部分其它技术信息都是针对每个线程的。

12.1.2. 它们大部分都是从show innodb status中获取的。

12.2. 下面将显示其中的一些指标。

12.3. Innodb_buffer_pool_pages_misc–其他缓存页需要的在innodb buffer中所使用到的页。

12.4. Innodb_buffer_pool_read_ahead_rnd–innodb处理的随机预读的数量。

12.5. Innodb_buffer_pool_read_request, innodb_buffer_pool_reads 这2个值是用来计算缓存读的命中率的。

13. Show innodb status

13.1. 这个是innodb故障处理的工具。当发生问题时就输入“show innodb status”

13.2. 这时候就会显示一些比show status更详细的信息。

13.3. 一些关于现在正在运行中的事务的信息(列入它们的锁等等)

13.4. 最新的一个死锁和外键的信息等等

13.5. 一些关于latches,spinlocks(自旋锁),操作系统等待信息

13.6. 更详细的参考http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

14. Show mutex(互斥量) status

14.1. 一个用来显示在你的工作负载下哪些热门的互斥量的工具

14.2. 显示了哪些是真正发生的互斥量,自旋锁的细节,以及操作系统等待。

14.3. Timed_mutexes – 跟踪操作系统等待了多久

硬件和操作系统的选择

15. 硬件和操作系统选择的检查列表

15.1. 使用什么CPU,以及多少个?

15.2. 使用多大的内存?

15.3. 如何建立IO子系统(如RAID)?

15.4. 使用哪个文件系统是最好的选择?

16. 选择CPU

16.1. 不同的CPU/架构对于innodb的扩展性能是不同的。

16.2. 老的“netburst”基于intel的xeon的扩展能力是比较弱的。

16.3. 新的酷睿基于xeon和 AMD的opterons具有更好的可扩张性

16.4. X86_64是非常必须的。

16.5. 使用多核处理器会让Innodb工作的更好

16.6. 每个系统拥有8核是一个比较合理的限制。

16.6.1. 必须根据实际的工作负载来进行调配

16.6.2. Innodb必须要根据未来的预期负载进行

16.7. 外部扩展,使用多台性能 较低的终端服务器。

16.8. 32位的CPU马上就要被淘汰了,所以请不要再使用32位的操作系统。

17. 使用多大的内存

17.1. 内存经常是对于很好的应用程序调优的性能瓶颈。

17.2. Innodb可以很有效的使用大量的内存。

17.3. 工作设定必须合理设置内存

17.3.1. 因为数据页是最常被访问

17.3.2. 不要通过行进行计数:100byte的行大概是1亿行,随机100万行的工作设定能够产生大部分的数据页。

17.4. 是总数据库大小的5%能够导致50%空间占用。

17.5. 确定使用64位的平台,操作系统和mysql版本。

18. 如何建立IO子系统

18.1. Innodb可以很好的加载大部分的磁盘驱动。每个节点有6-8个是一个看上去最优的配置。

18.2. 直接能够访问到的存储通常工作的最好

18.3. SAN–会增加恢复时间,而且价格昂贵

18.4. NAS–可以避免很多数据错误的风险

18.5. ISCSI–在大部分业务中都是非常适合的,也会增加恢复时间

18.6. RAID–电池备份cache是非常重要的。一定要确认启动了在写入cache的时候有电池备份。

18.7. 硬盘自身的缓存一般都是需要关闭。或者确认使用fsync()写入数据到磁盘或者当操作系统崩溃的时候能够产生数据腐坏。

19. 本地存储的配置

19.1. 日志最好放到单独的raid1中。这个是非常有帮助的,在很多情况比放在跟数据一起更好。

19.2. 二进制日志最好放到单独的卷中–能够很好的帮助备份和恢复。

19.3. RAID10对于表空间非常好。比预期下降的性能更多。

19.4. RAID5对于特定的工作负载时好的。仅仅需要确认是否需要降低性能。

19.5. 大的RAID条带大小(128K+)在理论是最好的,但是很多RAID控制器不能非常好的去控制。

19.6. 软RAID也是不错的,特别是RAID1。

20. 操作的选择也有会有问题?

20.1. 需要考虑性能,工具的有效性,社区等等。

20.2. Windows–试用于开发环境,很低的扩展性的WEB/企业项目上。

20.3. Solaris–提供了一些非常好的工具,也能对Mysql提供很好的支持,但是缺乏社区支援。

20.4. FreeBSD–历史上Mysql有很多问题在这个平台上,现在是好多了,但是只有很少的工具可用,很少在生产环境中使用。

20.5. Linux–在生产环境中和开发环境中最常使用的平台。有一些像LVM,JFS这样的工具可以使用。

21. 文件系统的选择

21.1. 主要是linux环境中有很多文件系统可以选择

21.2. EXT3–很多Linux发行版的默认文件系统,工作的非常好对于终端安装。

21.3. ReiserFS–很多Linux支持这个迁移。通常没有打的问题在标准的Mysql工作负载下。

21.4. XFS–被用在有RAID驱动的情况下,能够提供不错的性能提升。

21.5. JFS–很少被使用

21.6. Raw分区(原始分区)针对innodb表空间—很少使用

21.7. 通常非常高的性能提升预期都是通过更改文件文件系统来获得。

最近InnoDB的性能开发

22. InnoDB可伸缩性的补丁

22.1. 减少了buffer pool也中竞争。在5.0中直接可用,在4.1中需要自己移植。

22.2. 在MySQL5.1提升了sync_array的实现。

22.3. 基于工作负载,硬件环境,并发上的性能提升跟原来有所不同。

22.4. 从很少的百分比到很多次的进行排序。

22.5. 性能还是会在很大数量的并发线程的情况下有所下降。

22.6. 未来可扩展实现补丁的原型都是来源于社区的。

23. 其它改进

23.1. 在Mysql5.1中行级别复制减轻了间隙锁的问题。

23.2. 在没有auto_increment”talbe locks”能够继续工作。

23.3. 数据库页中支持ZIP压缩

23.4. 更快的索引建立

23.4.1. 不再需要全表重建

23.4.2. 可以根据物理分片的索引进行排序。

文章评论

那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
我是如何打败拖延症的
我是如何打败拖延症的
程序员应该关注的一些事儿
程序员应该关注的一些事儿
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
每天工作4小时的程序员
每天工作4小时的程序员
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
那些争议最大的编程观点
那些争议最大的编程观点
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
程序员都该阅读的书
程序员都该阅读的书
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
漫画:程序员的工作
漫画:程序员的工作
总结2014中国互联网十大段子
总结2014中国互联网十大段子
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
一个程序员的时间管理
一个程序员的时间管理
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
旅行,写作,编程
旅行,写作,编程
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
Java程序员必看电影
Java程序员必看电影
 程序员的样子
程序员的样子
程序员的鄙视链
程序员的鄙视链
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
如何成为一名黑客
如何成为一名黑客
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
我的丈夫是个程序员
我的丈夫是个程序员
中美印日四国程序员比较
中美印日四国程序员比较
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
10个调试和排错的小建议
10个调试和排错的小建议
程序员和编码员之间的区别
程序员和编码员之间的区别
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
老程序员的下场
老程序员的下场
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
代码女神横空出世
代码女神横空出世
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有