MySQL 5.5.8 vs Percona 5.5.7

• Hardware: Cisco UCS C250 http://www.percona.com/docs/wiki/benchmark:hardware:cisco_ucs_c250
• Storage: Virident tachIOn 400G
• Benchmark: tpcc-mysql, 1000W (~100GB ), innodb_buffer_pool_size=13,52,144G; 32 users, 1 hour
• Date: 18-Dec, 2010

[mysqld]
gdb
innodb_file_per_table = true
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 52G
innodb_buffer_pool_instances=16
innodb_log_file_size = 2000M
innodb_log_files_in_group = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_io_capacity=20000
port = 3306
back_log = 50
max_connections = 400
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 0
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
server-id = 1
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-grant-tables
socket=/tmp/mysql.sock
user=root
[mysql]
no-auto-rehash

[mysqld]
gdb
innodb_read_ahead = none
innodb_flush_neighbor_pages = 0
innodb_adaptive_checkpoint = keep_average
innodb_file_per_table = true
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 52G
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_io_capacity = 500
port = 3306
back_log = 50
max_connections = 400
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 0
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
server-id = 1
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
socket=/tmp/mysql.sock
user=root
skip-grant-tables
[mysql]
no-auto-rehash

set -u
set -x
set -e
export LD_LIBRARY_PATH=/usr/local/Percona-Server/lib/mysql/
MYSQLDIR=/usr/local/mysql
ulimit -c unlimited
BD=/mnt/fio160/back/tpc1000w
DR="/mnt/tachion/tpc1000w"
CONFIG="/etc/my.y.558.cnf"
WT=10
RT=10800
ROWS=80000000
log2="/data/bench/"
function restore {
mkdir -p $DR
rm -fr $DR/*
rm -f $log2/ib_log*
echo $log2
cp -r $BD/mysql $DR
pagecache-management.sh cp -r $BD/tpcc1000 $DR
pagecache-management.sh cp -r $BD/ibdata1 $log2
sync
echo 3 > /proc/sys/vm/drop_caches
chown mysql.mysql -R $DR
chown mysql.mysql -R $log2
}
function waitm {
while [ true ]
do
mysql -e "set global innodb_max_dirty_pages_pct=0" mysql
wt=`mysql -e "SHOW ENGINE INNODB STATUS\G" | grep "Modified db pages" | sort -u | awk '{print $4}'`
if [[ "$wt" -lt 100 ]] ;
then
mysql -e "set global innodb_max_dirty_pages_pct=90" mysql
break
fi
echo "mysql pages $wt"
sleep 10
done
}
RUN_NUMBER=-1
if [ -f ".run_number" ]; then
  read RUN_NUMBER < .run_number
fi
if [ $RUN_NUMBER -eq -1 ]; then
        RUN_NUMBER=0
fi
OUTDIR=res$RUN_NUMBER
mkdir -p $OUTDIR
RUN_NUMBER=`expr $RUN_NUMBER + 1`
echo $RUN_NUMBER > .run_number
for trxv in 2
do
for logsz in 2000M
do
for par in 52 144
do
runid="par$par.log$logsz.trx$trxv."
restore
export OS_FILE_LOG_BLOCK_SIZE=4096
/usr/local/mysql/bin/mysqld --defaults-file=$CONFIG --datadir=$DR --innodb_data_home_dir=$log2 --innodb_log_group_home_dir=$log2 --innodb_thread_concurrency=0 --innodb-buffer-pool-size=${par}GB --innodb-log-file-size=$logsz --innodb_flush_log_at_trx_commit=$trxv  &
MYSQLPID=$!
set +e
while true;
do
mysql -Bse "SELECT 1" mysql
if [ "$?" -eq 0 ]
then
  break
fi
sleep 30
echo -n "."
done
set -e
iostat -dmx 10 2000 >> $OUTDIR/iostat.${runid}res &
PID=$!
vmstat 10 2000 >> $OUTDIR/vmstat.${runid}res &
PIDV=$!
./virident_stat.sh >> $OUTDIR/virident.${runid}res &
PIDVS=$!
$MYSQLDIR/bin/mysqladmin ext -i10 -r >> $OUTDIR/mysqladminext.${runid}res &
PIDMYSQLSTAT=$!
cp $CONFIG $OUTDIR
cp $0 $OUTDIR
mysqladmin variables >>  $OUTDIR/mysql_variables.res
./tpcc_start localhost tpcc1000 root "" 1000 32 10 3600 | tee -a $OUTDIR/tpcc.${runid}.out
kill $PIDMYSQLSTAT
kill -9 $PID
kill -9 $PIDV
kill -9 $PIDVS
kill -9 $MYSQLPID
done
done
done