Skip to main content

优化MySQL的21个建议

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.
今天一个朋友向我咨询怎么去优化 MySQL，我按着思维整理了一下，大概粗的可以分为21个方向。 还有一些细节东西（table cache, 表设计，索引设计，程序端缓存之类的）先不列了，对一个系统，初期能把下面做完也是一个不错的系统。

1. 要确保有足够的内存

数据库能够高效的运行，最关建的因素需要内存足更大了，能缓存住数据，更新也可以在内存先完成。但不同的业务对内存需要强度不一样，一推荐内存要占到数据的15-25%的比例，特别的热的数据，内存基本要达到数据库的80%大小。

2. 需要更多更快的CPU

MySQL 5.6可以利用到64个核，而MySQL每个query只能运行在一个CPU上，所以要求更多的CPU，更快的CPU会更有利于并发。

3. 要选择合适的操作系统

在官方建议估计最推荐的是Solaris, 从实际生产中看CentOS, REHL都是不错的选择，推荐使用CentOS, REHL 版本为6以后的，当然Oracle Linux也是一个不错的选择。虽然从MySQL 5.5后对Windows做了优化，但也不推荐在高并发环境中使用windows.

4. 合理的优化系统的参数

更改文件句柄  ulimit –n 默认1024 太小
进程数限制  ulimit –u   不同版本不一样
禁掉NUMA  numctl –interleave=all

5. 选择合适的内存分配算法

默认的内存分配就是c的malloc 现在也出现许多优化的内存分配算法:
jemalloc and tcmalloc
从MySQL 5.5后支持声明内存储方法。
[mysqld_safe]
malloc-lib = tcmalloc

或是直接指到so文件
[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

6. 使用更快的存储设备ssd或是固态卡

存储介质十分影响MySQL的随机读取，写入更新速度。新一代存储设备固态ssd及固态卡的出现也让MySQL 大放异彩，也是淘宝在去IOE中干出了一个漂亮仗。

7. 选择良好的文件系统

推荐XFS, Ext4,如果还在使用ext2,ext3的同学请尽快升级别。　推荐XFS，这个也是今后一段时间Linux会支持一个文件系统。
文件系统强烈推荐: XFS

8. 优化挂载文件系统的参数

挂载XFS参数：
(rw, noatime,nodiratime,nobarrier)
挂载ext4参数：
ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)
如果使用SSD或是固态盘需要考虑：
• innodb_page_size = 4K
• Innodb_flush_neighbors = 0

9. 选择适合的IO调度

正常请下请使用deadline 默认是noop
echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

10. 选择合适的Raid卡Cache策略

请使用带电的Raid，启用WriteBack，　对于加速redo log ,binary log, data file都有好处。

11. 禁用Query Cache

Query　Cache在Innodb中有点鸡肋，Innodb的数据本身可以在Innodb buffer pool中缓存，Query Cache属于结果集缓存，如果开启Query Cache更新写入都要去检查query cache反而增加了写入的开销。
在MySQL　5.6中Query　cache是被禁掉了。

12. 使用Thread Pool

现在一个数据对应5个以上App场景比较，但MySQL有个特性随着连接增多的情况下性能反而下降，所以对于连接超过200的以后场景请考虑使用thread pool. 这是一个伟大的发明。

13. 合理调整内存

13.1 减少连接的内存分配

连接可以用thread_cache_size缓存，观查属于比较属不如thread pool给力。数据库在连上分配的内存如下：
max_used_connections * (
read_buffer_size +
read_rnd_buffer_size +
join_buffer_size +
sort_buffer_size +
binlog_cache_size +
thread_stack +
2 * net_buffer_length …
)

13.2 使较大的buffer pool

要把60-80%的内存分给innodb_buffer_pool_size.  这个不要超过数据大小了，另外也不要分配超过80%不然会利用到swap.


14. 合理选择LOG刷新机制

Redo Logs:
– innodb_flush_log_at_trx_commit  = 1 // 最安全
– innodb_flush_log_at_trx_commit  = 2 // 　较好性能
– innodb_flush_log_at_trx_commit  = 0 // 　最好的情能
binlog :
binlog_sync = 1  需要group commit支持，如果没这个功能可以考虑binlog_sync=0来获得较佳性能。
数据文件：
innodb_flush_method = O_DIRECT

15. 请使用Innodb表

可以利用更多资源，在线alter操作有所提高。 目前也支持非中文的full text， 同时支持Memcache API访问。目前也是MySQL最优秀的一个引擎。
如果你还在MyISAM请考虑快速转换。

16. 设置较大的Redo log

以前Percona 5.5和官方MySQL 5.5比拼性能时，胜出的一个Tips就是分配了超过4G的Redo log ,而官方MySQL5.5 redo log不能超过4G. 从 MySQL 5.6后可以超过4G了，通常建Redo log加起来要超过500M。 可以通过观查redo log产生量，分配Redo log大于一小时的量即可。

17. 优化磁盘的IO

innodb_io_capactiy 在sas 15000转的下配置800就可以了，在ssd下面配置2000以上。
在MySQL 5.6:
innodb_lru_scan_depth =  innodb_io_capacity / innodb_buffer_pool_instances
innodb_io_capacity_max  =  min(2000, 2 * innodb_io_capacity)

18. 使用独立表空间

目前来看新的特性都是独立表空间支持:
truncate table 表空间回收
表空间传输
较好的去优化碎片等管理性能的增加，
整体上来看使用独立表空间是没用的。

19. 配置合理的并发

innodb_thread_concurrency =并发这个参数在Innodb中变化也是最频繁的一个参数。不同的版本，有可能不同的小版本也有变动。一般推荐：
在使用thread pool 的情况下：
innodb_thread_concurrency = 0 就可以了。
如果在没有thread pool的情况下：
5.5 推荐：innodb_thread_concurrency =16 – 32
5.6 推荐innodb_thread_concurrency = 36

20. 优化事务隔离级别

默认是 Repeatable read
推荐使用Read committed  binlog格式使用mixed或是Row
较低的隔离级别 = 较好的性能

21. 注重监控


任环境离不开监控，如果少了监控，有可能就会陷入盲人摸象。 推荐zabbix+mpm构建监控。
Labels:

Comments

Post a Comment

Popular posts from this blog

北美 加州 草坪维护 草黄了怎么办

当今中国有句顺口溜， 叫做：穷的时候种稻， 富的时候种草。我虽然 不富，但也种了几年的 草。我对种草其实懂得 不多，虽自学不辍，但 终未成才。不过到底种 了几年的草，心得和体 会总是有的。现在把我 的心得体会写出来，希 望对房前屋后有块小草 坪的朋友们，不管是穷 还是富，都有些帮助。 草地上的草，都属于禾 本科（Grass Family），与竹 子、水稻、小麦、甘蔗 和狗尾巴花是同一个科 的植物。从个体的数量 和分布的范围来讲，禾 本科植物应该是植物界 最大的科了。草本的禾 本科植物通常有两种生 长的形态，一种叫蓬草 (bunch grass)，一种叫 坪草(sod grass)。用来做 草坪的草，当然都是坪 草 。坪草的物种和品种很 多，不同地区不同气候 条件种的草不一样，但 也有一些共性。一般来 说，当地商店里买的草 籽（grass seeds）和草皮（ sod），都是比较适 合当地的自然条件的。 一．维护和保养草坪必 做的四件事情 1．割草。定期割草， 不仅能使草坪美观，而 且也可防止或减缓坪草 从营养生长到生殖生长 的过渡。营养生长就是 长叶子，生殖生长就是 开花结籽。很多人都知 道，春天的时候有一段 时间不割草，草就开花 结籽了。大家也见过， 边边角角割不到草的地 方，那里的草就会开花 结籽。 2．浇水。对北美大部 分地区来说，春秋天时 可视情况少浇水甚至不 浇水。草地最需要浇水 的时候是夏天，因为夏 天最热最干，水分蒸发 快。至于浇水要多频繁 ，那就看你想草地是保 绿，还是保命。如果想 保绿，那草地每星期得 有一英寸的水量。除非 有自动浇水系统，浇水 是很费时间的，也费钱 。我只想我的草地保命 ，所以我的草地夏天基 本不浇水，只在特别容 易干死的草地或在特别 干旱的年份浇点水。所 以到8-9月份的时候 ，我的草地就黄黄的了 ，有些难看。不过，一 场秋雨来，草地依旧绿 。 3．施肥。草地的肥料 应以氮素为主，钾素得 有一点，但磷的含量可 以很低。我用的最多的 草坪肥料是30-0- 4。现在有一种运动， 推崇不含磷素的草地肥 料，因为磷素施用过多 ，会引起水体（如湖泊 ，池塘）的富营养化。 施肥的话，还是得买个 撒肥机（spread er/broadca ster），手持的或 手推的型号都行，当地 商店都有卖的。没有撒 肥机，肥料会撒得很不 均匀。 4．杂草...
Post a Comment
Read more

湾区好吃的中餐馆

一个伪吃货在湾区的checklist 来源： 徐聪的日志 海鲜： Boiling Crab （San Jose）， Joe's Crab Shack (San Francisco) ， Tomi Sushi & Seafood Buffet（San Jose）， Tatami Sushi & Seafood Buffet（Cupertino） 番外：Pier 39  的大螃蟹  （San Francisco） Boiling Crab的螃蟹从来都是酒香不怕巷子深，关键在于它家的酱绝对不会让人想起它 是一家西餐馆。它家的龙虾也是一绝，当然一旦点了龙虾还打算吃饱的话人均基本在40 刀以上。它家在San Jose有两家分店，如果想避免排长队的话推荐周末中午11点50点以 前（12点开门）去101高速边上那家，屡试不爽。 Joe's Crab Shack是一家全美连锁的海鲜餐馆，一锅端 (Steampot) 的吃法很有特色。 Tomi Sushi & Seafood Bufferz中文名叫涛味，排在Tatami之前的原因是它家的口味比 较偏中餐，龙虾膏蟹做的不错（不是每天都有）。最近中午去过一次，东西很少，不推 荐中午去（虽然价格便宜一些）。 Tatami跟南加著名的Todai都是差不多的日式海鲜自助，生鱼片比较新鲜，不过没有 Todai每小时限量的烤龙虾尾。 渔人码头的螃蟹主要的砝码是价格，但跟Boling Crab一比也不见得能便宜多少。有一 家摊位上面写着“我们通晓国、粤、英语”，每次都会去。 川菜： 御食园（San Francisco），金饭碗（Berkeley）， 红翻天（Foster City）， 吃香喝 辣（Newark）， 老赵川菜（Mountain View）， 鹿鸣春（Berkeley）， 福恩园（ Menlo Park），麻辣诱惑（Fremont）， 巴山蜀水（Milpitas） ， 福恩园（San  Mateo）， 大四川（Palo Alto）， 麻辣诱惑（San Jose）， 山城私房菜（Milpitas ），麻辣帝国（San Mateo） 川菜的菜品基本都是那几样，就不单独推荐了。 排名第一的Z&Y我觉得就不用...
Post a Comment
Read more

California life 新手的湾区 购房 全攻略 (转)

原帖在华人网，感觉写得太全面了，本人非房黑房托，只做技术性讨论，欢迎发表意见 第一阶段：全面了解 去年12月12号的时候，我正好怀孕37周，冒着风险跨东西海岸大搬家到了Bay。刚下飞 机的时候正好下着雨，但是一点也不冷，反而很舒服。来到LG租的apartment，还是吃 惊了一小下，因为从Atlanta rent $750 每月 1000sqft的1bed1bath搬到 $1700每月 1000sgft的2bed2bath，这个落差还是比较强烈滴。。。 不过呢，既来之则安之，我立马投入了100%的热情开始shopping，布置这个临时的新家 来迎接宝宝的出生。要知道在37周之前，我连最基本的crib都没准备，更不用说其他的 了。然后就是宝宝出生，出了月子我开始努力换工作，接下来去新公司上班，等到一切 稳定下来，大概是4月份的样子，我打算开始做买房的初步了解了。。。 首先就是找agent了，一般就是靠熟人推荐。我找的agent优点很明显，经验很丰富，很 热情，很耐心，不push，缺点也很明显，就是不太愿承担责任，她会很小心的提供涉及 到关键问题的建议，就怕给你产生误导。这样也不是不好，但是对于我们这样没有经验 的新手来说，从agent那里拿到的答案总是模棱两可的，我们就很难做出判断。但是 buyer和agent其实就是一个team，买房就是一个teamwork，在这个过程中，通过一次次 的沟通，最终我们还是磨合好了。整个过程下来，很难说我们对agent是不是满意，但 是只要帮助买到了满意的房子，那就应该是一个好的agent。 4月份和agent初步聊过后，我们当时给出的dream house的条件和大多数刚刚从东部， 中西部农村搬来bay的同学基本上是一样的： 1。 SFH，房子要大，1700sqft以上，越新越好，最好2层的。 （atlanta 基本配置都 是这样的阿。。。） 2。 院子小点没关系  （在atlanta见过的朋友家的院子都是草坪，就没有精心收拾过 的，所以那个时候对后院完全没有概念。。。） 3。 学区中等就行，小学800+。（小孩才刚出生，到上学还早呢，如果到时候来不及换 房子，那就上私立好了。。。） 4。 commute 30-40分钟都可以。 （在atlanta我们还经常开车1个小时去吃饭呢，高速 30分钟也...
Post a Comment
Read more