percona server 5.6.24安装
yum安装
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list | grep percona
yum install Percona-Server-client-56 Percona-Server-server-56

 RPM安装
 wget -r -l 1 -nd -A rpm -R "*devel*,*debuginfo*"  \
http://www.percona.com/downloads/Percona-Server-5.6/LATEST/RPM/rhel6/x86_64/

rpm -ivh Percona-Server-shared-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm \
Percona-Server-client-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm \
Percona-Server-server-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm


rpm -ivh Percona-Server-shared-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm
rpm -ivh Percona-Server-client-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm
rpm -ivh Percona-Server-server-56-5.6.6-alpha60.1.285.rhel6.x86_64.rpm



源码安装

https://www.percona.com/doc/percona-server/5.6/installation.html
系统环境：


CentOS 6.5 / 7.0 x86_64
Fedora 20 x86_64


安装依赖
yum install gcc-c++ make cmake bison bison-devel ncurses-devel libaio-devel perl
对于 Fedora 20 和 CentOS 7 需要另外安装一个 perl-Data-Dumper 包：

# yum install  perl-Data-Dumper

创建 MySQL 用户
# groupadd mysql
# useradd -g mysql -s /sbin/nologin -M mysql


安装
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DCMAKE_INSTALL_PREFIX=/usr/local/percona56 \
-DMYSQL_DATADIR=/usr/local/percona56/data \
-DMYSQL_UNIX_ADDR=/usr/local/percona56/mysqld.sock \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=33061 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 

make
make install

jemalloc优化MySQL:
安装jemalloc

wget http://www.canonware.com/download/jemalloc/jemalloc-3.5.0.tar.bz2
tar xjf jemalloc-3.5.0.tar.bz2
cd jemalloc-3.5.0
./configure
make && make install
echo '/usr/local/lib' > /etc/ld.so.conf.d/local.conf
ldconfig

lib/libjemalloc.a

编译安装

cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DCMAKE_INSTALL_PREFIX=/usr/local/percona56 \
-DMYSQL_DATADIR=/usr/local/percona56/data \
-DMYSQL_UNIX_ADDR=/usr/local/percona56/mysqld.sock \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=33061 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DCMAKE_EXE_LINKER_FLAGS="-ljemalloc" \
-DWITH_SAFEMALLOC=OFF


make && make install

验证tcmalloc是否在mysql中生效
service percona56 start
lsof -n | grep jemalloc
bash       2180      root  cwd       DIR              253,0       4096    1463505 /root/jemalloc-3.5.0
mysqld    30138     mysql  mem       REG              253,0    5026934    1221166 /usr/local/lib/libjemalloc.so.1


Tcmalloc优化Mysql
http://ftp.twaren.net/Unix/NonGNU//libunwind/
tar xvf libunwind-1.1.tar.gz 
cd libunwind-1.1
./configure
make && make install




tar xvf gperftools-2.1.tar.gz
cd gperftools-2.1
./configure
make && make install
echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf
ldconfig


cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DCMAKE_INSTALL_PREFIX=/usr/local/percona56 \
-DMYSQL_DATADIR=/usr/local/percona56/data \
-DMYSQL_UNIX_ADDR=/usr/local/percona56/mysqld.sock \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=33061 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DCMAKE_EXE_LINKER_FLAGS="-ltcmalloc" \
-DWITH_SAFEMALLOC=OFF


make && make install


验证tcmalloc是否在mysql中生效
service percona56 start
lsof -n | grep tcmalloc
mysqld    60887     mysql  mem       REG              253,0   2171746    1221241 /usr/local/lib/libtcmalloc.so.4.1.2


通过jemalloc和Tcmalloc，编译时间大大缩短


注：
嵌入式服务器 --with-embedded-server -DWITH_EMBEDDED_SERVER=1
-DCMAKE_BUILD_TYPE=RelWithDebInfo \ # 开启优化并生成调试信息,其中Debug:-g Release:-O2 RelWithDebInfo:-O2 -g MinSizeRel:-Os
-DBUILD_CONFIG=mysql_release \ # 该选项表示,我们使用与Oracle官方相同的参数进行编译


错误：
CMake Error at cmake/readline.cmake:291 (MESSAGE):
解决：
yum -y install ncurses-devel
通过find命令找到所有CMakeCache.txt文档的位置
#find / -name CMakeCache.txt

错误：CMake Error at cmake/readline.cmake:271 (MESSAGE):
解决：yum install  readline-devel

初始化数据：
 ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/percona56 --datadir=/usr/local/percona56/data


错误：
[ERROR] /usr/local/percona56/bin/mysqld: unknown option '--ndbcluster'
解决：mv /etc/my.cnf /etc/my.cnf.bak
原先的my.cnf文件是ndbcluster的配置文件


启动服务：
cp support-files/mysql.server /etc/init.d/percona56
chown -R mysql:mysql /usr/local/percona56/
service percona56 start


账号安全修改：
/usr/local/percona56/bin/mysql_secure_installation -S /usr/local/percona56/mysqld.sock




./bin/mysql -uroot -p123456 -h127.0.0.1 -P 33061
show plugins;
show engines;


配置 my.cnf
# vi /usr/local/percona56/etc/my.cnf


[client]
port=33061
socket=/usr/local/percona56/mysqld.sock
default-character-set=utf8

[mysqld]


#--general configure
port=33061
datadir=/usr/local/percona56/data
socket=/usr/local/percona56/mysqld.sock
symbolic-links=0
bind-address = 0.0.0.0
open_files_limit = 65535
external-locking = FALSE  
pid-file=/usr/local/percona56/data/node4.pid
character-set-server=utf8


#---connection configure
max_connections=1000
back_log=600
#cat /proc/sys/net/ipv4/tcp_max_syn_backlog
max_connect_errors=10000
connect_timeout=10
wait_timeout=100
interactive_timeout=100
#show processlist;
max_allowed_packet=64m
max_user_connections=0
skip_name_resolve
skip-external-locking


#---server configure
thread_cache_size=300
#(1- threads_created/threads_connected) 
query_cache_size=64m
query_cache_limit = 4M   
query_cache_min_res_unit = 2k 
query_cache_type=1
#prod environmentnot recommended use qc
table_open_cache=128
table_definition_cache=128
#show global status like 'open_tables';show global status like 'opened_tables';
binlog_cache_size=16m  #show global status like 'binlog_cache_use';total=max_connections*binlog_cache_size
max_binlog_size=1g
tmp_table_size = 256M   
max_heap_table_size = 256M
sort_buffer_size = 8M  
join_buffer_size = 8M 
read_buffer_size = 2M  
read_rnd_buffer_size = 16M   
bulk_insert_buffer_size = 64M   

slow_query_log = 1
long_query_time = 1 
slow_query_log_file = /usr/local/percona56/data/mysql-slow.log

log_bin = mysql-bin
binlog_format = row
expire_logs_days = 30 

#---inndbo configure
innodb_thread_concurrency=0   #show engine innodb status\G;"transaction 1…..waiting in innodb queue"
#innodb_concurrency_tickets=500
#innodb_thread_concurrency=2cpu 
innodb_buffer_pool_size=256m
innodb_additional_mem_pool_size=20M
#innodb_buffer_pool_size=mem*50%-80%
#hit:1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)
innodb_buffer_pool_instances=16
innodb_max_dirty_pages_pct=25
innodb_file_per_table=1
innodb_commit_concurrency=0
innodb_change_buffering=all
innodb_log_file_size=1g
innodb_log_files_in_group=3
innodb_log_buffer_size=16m
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_io_capacity=2000
innodb_adaptive_flushing=1
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_log_group_home_dir=/usr/local/percona56/data/
innodb_data_home_dir=/usr/local/percona56/data/
innodb_data_file_path = ibdata1:2G;ibdata2:16M:autoextend
#innodb_flush_method=fdatasync #在大量随机写的环境中O_DIRECT要比fdatasync效率更高些，顺序写多的话，还是默认
的fdatasync更高效。
innodb_flush_method=O_DIRECT  #RAID and battery
binlog_format=row                         #finance,pay industry
transaction-isolation=READ-COMMITTED
#transaction_isolation = REPEATABLE-READ 
innodb_stats_on_metadata=0


#---thread pool   
thread_handling=pool-of-threads 
thread_pool_max_threads=300 
thread_pool_size=16

#--myisam configure
key_buffer_size=64m

[mysqld_safe]
log-error=/usr/local/percona56/data/mysqld-err.log
pid-file=/usr/local/percona56/data/percona56.pid


[mysqldump]
quick
max_allowed_packet = 32M



压力测试

sysbench 0.5


安装：
rpm -ivh http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster/5.5.37-25.10/RPM/rhel6/x86_64/Percona-XtraDB-Cluster-shared-55-5.5.37-25.10.756.el6.x86_64.rpm
rpm -ivh sysbench-0.5-3.el6_.x86_64.rpm 

[root@server16 tmp]# find / -name "oltp.lua"
/usr/share/doc/sysbench/tests/db/oltp.lua


sysbench --test=/soft/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-host=192.168.168.159  --mysql-port=33061 --mysql-user=root --mysql-password=123456  \
--oltp-test-mode=complex --mysql-table-engine=innodb \
--oltp-table-size=100000000 --mysql-db=test --oltp-table-name=test_tbl --num-threads=128 --max-requests=50000000 prepare

sysbench --test=/soft/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-host=192.168.168.159  --mysql-port=33061 --mysql-user=root --mysql-password=123456  \
--oltp-test-mode=complex --mysql-table-engine=innodb \
--oltp-table-size=100000000 --mysql-db=test --oltp-table-name=test_tbl --num-threads=128 --max-requests=50000000 run

sysbench --test=/soft/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-host=192.168.168.159  --mysql-port=33061 --mysql-user=root --mysql-password=123456  \
--oltp-test-mode=complex --mysql-table-engine=innodb \
--oltp-table-size=100000000 --mysql-db=test --oltp-table-name=test_tbl --num-threads=128 --max-requests=50000000 cleanup


测试模式：complex,simple,nontrx
通用基准：
-最大请求数：5,000,000
-最大线程数：8-512


基准-OLTP：
-mode=complex
-engine=innodb
-oltp-table-size=100,000,000