使用ICC优化编译Mysql percona 分支(Compile mysql-percona v5.0.87)

生产环境跑的是打了google mysql-patch v4的mysql, 运行效果一直不错. Percona提供的mysql补丁集也不错,
尤其是增加了很多有用的信息,在运行时分析性能瓶颈很有用. Google的v3/v4补丁相对来说就少了一些.

最新的5.0.97b20出来后,我决定替换slave,目的是希望更方便的分析运行期统计信息.
和google v4一样,我使用了新的icc v11.1.x进行了优化编译.
步骤如下:

1. 编译libunwind
CC=icc \
CXX=icpc \
LD=xild \
AR=xiar \
CFLAGS=’-O3 -ipo -no-prec-div -xSSE3 -axSSE4.2,SSE4.1,SSE3,SSE2′ \
CXXFLAGS=’-O3 -ipo -no-prec-div -xSSE3 -axSSE4.2,SSE4.1,SSE3,SSE2′ \
./configure –prefix=/opt/local
make install

2.编译google-perftools-1.4
CC=icc \
CXX=icpc \
LD=xild \
AR=xiar \
CPPFLAGS=” -I/opt/local/include ” \
CXXFLAGS=’-xSSE3 -axSSE4.2,SSE4.1,SSE3,SSE2 -O3 -ip -no-prec-div ‘ \
LDFLAGS=’ -L/opt/local/lib ‘ \
./configure –prefix=/opt/local
make install

3.编译mysql-percona 5.0.87b20

#!/bin/bash
ICC_FLAGS=’-O3 -no-prec-div -ip -unroll2 -restrict -fno-implicit-templates -fno-exceptions -fno-rtti -static-intel -static-libgcc -xSSE3 -axSSE2,SSE3,SSE4.1,SSE4.2′
MYSQL_ROOT=/opt/mysql-percona
BUILD_VERSION=’ICC v11.1.059/Percona v5.0.87-b20′
ICC=icc
ICPC=icpc
build_client() {
CFLAGS=”$ICC_FLAGS” \
CXXFLAGS=”$ICC_FLAGS” \
CPPFLAGS=’-I/opt/local/include’ \
LDFLAGS=’-L/opt/local/lib’ \
LD=xild \
AR=xiar \
CC=$ICC \
CXX=$ICPC \
./configure \
–prefix=$MYSQL_ROOT \
–with-server-suffix=’-cv-mysql’ \
–with-comment=”$BUILD_VERSION” \
–with-collation=utf8_general_ci \
–with-charset=utf8 \
–with-extra-charsets=complex \
–with-client-ldflags=’-all-static’ \
–enable-thread-safe-client \
–enable-assembler \
–with-fast-mutexes \
–with-innodb \
–with-pic \
–enable-assembler \
–enable-local-infile \
–without-server \
–without-ndbcluster \
–without-embedded-server\
–without-example-storage-engine \
–without-archive-storage-engine \
–without-blackhole-storage-engine \
–without-csv-storage-engine \
–without-federated-storage-engine \
–with-zlib-dir=bundled \
–without-debug \
–with-readline
make -j8
make install
}
build_server(){
CFLAGS=”$ICC_FLAGS” \
CXXFLAGS=”$ICC_FLAGS” \
CPPFLAGS=’-I/opt/local/include’ \
LDFLAGS=’-L/opt/local/lib’ \
CC=$ICC \
CXX=$ICPC \
LD=xild \
AR=xiar \
./configure \
–disable-shared \
–prefix=/opt/mysql-percona \
–with-server-suffix=’-cv-mysql’ \
–with-comment=”$BUILD_VERSION” \
–with-collation=utf8_general_ci \
–with-charset=utf8 \
–with-extra-charsets=complex \
–with-mysqld-ldflags=’-all-static -ltcmalloc_minimal’ \
–enable-thread-safe-client \
–enable-assembler \
–with-innodb \
–with-pic \
–with-fast-mutexes \
–enable-assembler \
–enable-local-infile \
–without-bench \
–without-extra-tools \
–without-docs \
–without-man \
–without-ndbcluster \
–without-embedded-server\
–without-example-storage-engine \
–without-archive-storage-engine \
–without-blackhole-storage-engine \
–without-csv-storage-engine \
–without-federated-storage-engine \
–with-zlib-dir=bundled \
–without-debug \
–with-readline
make -j8
install -s -D sql/mysqld $MYSQL_ROOT/libexec/mysqld
}
make clean distclean
build_client
make clean distclean
build_server

client和server是分别编译的,server是static.

修改调优mysql的配置
cat /etc/my.cnf

[mysqld]

# generic configuration options
port = 3306
socket = /tmp/mysql.sock
datadir = /db/data

back_log = 50
max_connections = 500
max_connect_errors = 100
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = InnoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
long_query_time = 3
log_long_format

replicate-same-server-id
server-id = 100
binlog-ignore-db=mysql
binlog-ignore-db=test
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:5G;idbdata2:10G;idbdata3:30G;idbdata4:40G
innodb_data_home_dir = /db/tb
innodb_file_io_threads = 4
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_log_group_home_dir= /db/tlog
innodb_max_dirty_pages_pct = 80
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
auto_increment_increment=2
auto_increment_offset=1
expire_logs_days=3
allow_view_trigger_sp_subquery
#google patch
innodb_adaptive_checkpoint
innodb_adaptive_checkpoint=1
innodb_write_io_threads=4
innodb_io_capacity=200
#percona only
rpl_transaction_enabled=1
rpl_mirror_binlog_enabled
sync-mirror-binlog
#slow log
#sql_log_filename=/db/slowlog/s2.log
#log_slow_queries=/db/slowlog/s2.log
#log_queries_not_using_indexes

几个重要的参数:
innodb_adaptive_checkpoint=1
要开启
innodb_max_dirty_pages_pct
要根据运行时信息进行微调
innodb_io_capacity=200 or 300
这里的数量是raid中磁盘stripe size*100
例如raid10,2*2, 设置为200, 2*3则可设置为300

rpl_transaction_enabled=1
rpl_mirror_binlog_enabled
sync-mirror-binlog
和replication相关.需要手动打补丁
mirror-binlog.patch

update:(当前补丁列表,自己打补丁按此顺序):

show_patches.patch
microslow_innodb.patch
profiling_slow.patch
userstatv2.patch
microsec_process.patch
innodb_io_patches.patch
mysqld_safe_syslog.patch
innodb_locks_held.patch
innodb_show_bp.patch
innodb_check_fragmentation.patch
innodb_io_pattern.patch
innodb_fsync_source.patch
innodb_show_hashed_memory.patch
innodb_dict_size_limit.patch
innodb_extra_rseg.patch
innodb_thread_concurrency_timer_based.patch
innodb_use_sys_malloc.patch
innodb_recovery_patches.patch
innodb_misc_patch.patch
innodb_split_buf_pool_mutex.patch
innodb_rw_lock.patch
mysql-test.patch

November 29, 2009 | Filed Under Technotes, icc

Comments

8 Responses to “使用ICC优化编译Mysql percona 分支(Compile mysql-percona v5.0.87)”

  1. jackbillow on December 11th, 2009 2:51 pm

    http://www.percona.com/mysql/5.0.87-b20/source/mysql-5.0.87-percona-src.tar.gz

    这个包里面直接带mirror binlog patch？

  2. nightsailer on December 11th, 2009 6:06 pm

    highperf 包
    http://www.percona.com/mysql/5.0.87-b20/source/mysql-5.0.87-percona-highperf-src.tar.gz

    刚查了下这个版本的patch list没有包括mirror-binlog
    补丁在这里:
    http://www.percona.com/mysql/5.0.87-b20/patches/mirror_binlog.patch

  3. jackbillow on December 12th, 2009 10:52 am

    非常感谢：）nightsailer

  4. jackbillow on December 12th, 2009 10:55 am

    你测试的mirror binlog性能怎么样？我准备用他做master(or relay) 的HA.

  5. jackbillow on December 12th, 2009 11:23 am

    编译完之后在cnf文件里面加入这些参数：
    rpl_transaction_enabled=1
    rpl_mirror_binlog_enabled
    sync-mirror-binlog
    就启动不了。奇怪

  6. nightsailer on December 13th, 2009 12:52 am

    @ jackbillow

    Sorry, 我犯了个愚蠢的错误. 把google v4的my.cnf和percona的my.cnf混淆了(cat 错了).

    rpl_transaction_enabled=1 这个参数是google patch中的,percona中无效,去掉吧.
    后2个参数是mirror-binlog.patch的,目前没有在包括在patch中. 所以无法使用. 但可以自己手动打mirror-binlog.patch.(会和mysql-test.patch有冲突,但应无妨).
    如果写的数据不是那么巨大和频繁,只是master-master,master/slave这种同步, 也没有必要打这个补丁.
    因为即便是google的patch,也说有在特定场景可能会有些问题(虽然我自己使用了10个月还算稳定).
    HA我使用mmm来部署, 可靠性足够了.

  7. rainbowbridg on January 14th, 2010 4:35 pm

    google mysql-patch v4的补丁如何打呢?
    能发个文档给我吗,谢谢!
    dai_zhy@hotmail.com

  8. nightsailer on January 26th, 2010 10:39 pm

    首先安装google-perftools,然后
    1. 下载mysql-5.0.37.tar.gz
    2. 下载google的all.v4-mysql-5.0.37.patch.gz
    3. 打补丁
    4. 使用以下编译脚本编译

    [root@night9.cn deploy]# cat compile-mysql-google-v4.sh
    build_client() {
    make clean distclean
    CFLAGS=’ -O3 -no-prec-div -ip -unroll2 -mp -xSSE3 ‘ \
    CXXFLAGS=’ -O3 -no-prec-div -ip -unroll2 -mp -xSSE3 ‘ \
    CPPFLAGS=’-I/opt/local/include’ \
    LDFLAGS=’-L/opt/local/lib’ \
    CC=icc \
    CXX=icpc \
    LD=xild \
    AR=xiar \
    ./configure \
    –prefix=/opt/mysql-google-v4 \
    –without-server \
    –with-pic \
    –with-collation=utf8_general_ci \
    –with-charset=utf8 \
    –with-extra-charsets=complex \
    –enable-thread-safe-client \
    –without-embedded-server \
    –enable-assembler \
    –with-libevent \
    –with-fast-mutexes \
    –without-debug \
    –enable-local-infile \
    –with-readline
    make
    make install
    }

    build_server() {
    make clean distclean
    CFLAGS=’ -O3 -no-prec-div -ipo -unroll2 -mp -xSSE3 ‘ \
    CXXFLAGS=’ -O3 -no-prec-div -ipo -unroll2 -mp -xSSE3 ‘ \
    CPPFLAGS=’-I/opt/local/include’ \
    LDFLAGS=’-L/opt/local/lib -ltcmalloc_minimal’ \
    CC=icc \
    CXX=icpc \
    LD=xild \
    AR=xiar \
    ./configure \
    –prefix=/opt/mysql-google-v4 \
    –disable-shared \
    –with-collation=utf8_general_ci \
    –with-charset=utf8 \
    –with-extra-charsets=complex \
    –with-innodb \
    –with-csv-storage-engine \
    –with-archive-storage-engine \
    –with-blackhole-storage-engine \
    –with-federated-storage-engine \
    –without-bench \
    –without-embedded-server \
    –without-bench \
    –with-big-tables \
    –without-extra-tools \
    –with-fast-mutexes \
    –with-libevent \
    –with-mysqld-ldflags=’-static-intel -all-static ‘

    make
    strip sql/mysqld
    cp -av sql/mysqld /opt/mysql-google-v4/libexec/mysqld-static
    cp -av sql/mysqld /opt/mysql-google-v4/libexec/mysqld
    }
    build_client
    build_server

Leave a Reply