MySQL优化

1. 确认optimizer_switch的设置：

早先已经做过一次OLTP性能对比测试：SAS vs SSD各种模式下MySQL TPCC OLTP对比测试结果，这次做OLAP对比测试。

对比测试结果见下：

SSD设备相应的性能提升比例：

测试环境：

tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试，以评估商业分析中决策支持系统（DSS）的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改，强调测试的是数据库、平台和I/O性能，关注查询能力。
官网：http://www.tpc.org/tpch
下载地址：http://www.tpc.org/tpch/spec/tpch_2_14_3.tgz
或
http://www.tpc.org/tpch/spec/tpch_2_14_3.zip

1、编译安装
下载源码包，解压缩，然后：

cp makefile.suite makefile 

修改makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等定义：

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, ORACLE,
#                                  SQLSERVER, SYBASE, TDAT (Teradata)
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

一、简述
是由原来 MySQL 的作者 Michael Widenius 创办的公司所开发的免费开源的数据库服务器。MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX 存储引擎。(摘自：http://baike.baidu.com/view/2521908.htm)
简单的说：MariaDB是MySQL的一个变种，可以作为官方MySQL版本的一个替代品，并且在一些特性上做了增强。
MariaDB的官网见：http://mariadb.org

二、测试环境
2.1 详细测试环境见下图：

之前分别在2006和2009年写过两篇关于discuz优化的文章：MySQL优化 之 Discuz论坛优化、MySQL优化 之 Discuz论坛优化 -- 续，没想到都6年过去了，discuz还在坚挺的使用MyISAM引擎，堪比罚改委...
今日帮朋友优化号称日均数百万PV，数百万UV的论坛，后台DB采用R710(16G Ram，PERC 6/i 256MB BBU，4块 15K RPM SAS盘做raid 1+0，ext3文件系统，E5620 * 2)，这个配置看似也不错了，不过压力仍然较大，大量的请求处于：sending data和statistics状态。
经过分析，确认瓶颈主要在：

1. IO读，IO写倒还好，不算高；因为数据表都是MyISAM，需要产生较高的物理读，不能通过内存有效缓冲；
2. 使用的MySQL是官方5.1版本，InnoDB队列请求排队较严重(部分表已经先转成InnoDB了)；
3. 部分未转换成InnoDB的表MyISAM表级锁比较严重；

综上，建议做以下改进工作：

前言：MySQL发展到今天，InnoDB引擎已经作为绝对的主力，除了像大数据量分析等比较特殊领域需求外，它适用于众多场景。然而，仍有不少开发者还在“执迷不悟”的使用MyISAM引擎，觉得对InnoDB无法把握好，还是MyISAM简单省事，还能支持快速COUNT(*)。本文是由于最近几天帮忙处理discuz论坛有感而发，希望能对广大开发者有帮助。

1. 快速认识InnoDB
InnoDB是MySQL下使用最广泛的引擎，它是基于MySQL的高可扩展性和高性能存储引擎，从5.5版本开始，它已经成为了默认引擎。
InnODB引擎支持众多特性：

a) 支持ACID，简单地说就是支持事务完整性、一致性； 
b) 支持行锁，以及类似ORACLE的一致性读，多用户并发；
c) 独有的聚集索引主键设计方式，可大幅提升并发读写性能；
d) 支持外键；
e) 支持崩溃数据自修复；

InnoDB有这么多特性，比MyISAM来的优秀多了，还犹豫什么，果断的切换到InnoDB引擎吧 :)

2. 修改InnoDB配置选项
可以选择官方版本，或者Percona的分支，如果不知道在哪下载，就google吧。
安装完MySQL后，需要适当修改下my.cnf配置文件，针对InnoDB相关的选项做一些调整，才能较好的运行InnoDB。
相关的选项有：

在各种测试组合方案中，组合10(组合10：SSD * 2, RAID 0, XFS,WB,nobarrier,noop)的综合性能最高，因此以它为基准，其他方案与其对比，下表是各组合和组合10的对比：

相应的对比线形图：

测试环境：

一、前言
Innodb Plugin引擎开始引入多种格式的行存储机制，目前支持：Antelope、Barracuda两种。其中Barracuda兼容Antelope格式。
另外，Innodb plugin还支持行数据压缩特性，不过前提是采用Barracuda行存储格式。
表空间启用压缩的前提是innodb表空间文件存储格式修改成：Barracuda，需要修改2个选项：
innodb_file_format = "Barracuda"
innodb_file_format_max = "Barracuda"

下面是对比测试结果
二、表空间压缩比
1. 某项目数据表压缩比
2.1 数据表tabA
压缩之前
-rw-rw---- 1 mysql mysql 19038208 Mar 21 13:59 tabA.ibd（18.1G）
压缩之后
-rw-rw---- 1 mysql mysql 9.2G Mar 21 19:11 tabA.ibd
相差：12414976 ~= 12124 MB ~= 11.83 Gb，节约49.32%

一、 下载工具包
Tpcc-mysql是percona基于tpcc衍生出来的产品，专用于mysql基准测试，其源码放在bazaar(Bazaar是一个分布式的版本控制系统，采用 GPL 许可协议，可运行于 Windows、GNU/Linux、UNIX 以及 Mac OS 系统之上。Bazaar 由 Canonical 公司（Ubuntu母公司）赞助)上，因此还需要先安装bazaar客户端。

使用root安装rpm包

rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

然后就可以开始安装bzr客户端了：

yum install bzr 

之后，就可以开始用bzr客户端下载tpcc-mysql源码了。

cd tmp
bzr branch lp:~percona-dev/perconatools/tpcc-mysql

二、编译安装
编译非常简单

cd /tmp/tpcc-mysql/src
make

然后就会在 /tmp/tpcc-mysql 下生成 tpcc 命令行工具 tpcc_load 、 tpcc_start

按照惯例，如果前端应用程序采用长连接的话，那么innodb buffer pool最高可设置为物理内存大小的80%。
不过部分在线DB由于并发连接数较高，每个线程分配的内存较多，或由于业务上升，并发事务数突然较大幅度提升，加上innodb buffer pool较大，导致了严重的内存交换(swap)发生。

鉴于此，我们建议在这些活跃度较高/并发连接数较高的在线DB服务器上，适当调低innodb buffer pool的大小(例如先调低为60%)，
同时也适当调低各线程级别的内存参数，例如：tmp_table_size, sort_buffer_size等，避免因为内存交换而影响服务器性能。尤其是 tmp_table_size，不少人以为是全局变量，设置的非常大，甚至见过一个设置为 1GB 的，太吓人了。

如果绝大多数引擎是InnoDB的话，建议调低key_buffer_size到很小的值，同时可以关闭query cache，现在它基本是个鸡肋了，有些时候甚至还会导致性能受到影响。