MySQL 5.5.4 tpcc benchmarks

tpcc-like benchmark, 200W (around 18GB of data)

Hardware:

• http://www.percona.com/docs/wiki/benchmark:hardware:R900
• FusionIO 320GB MLC IODrive card with XFS filesystem (nobarrier mount options)

Notes:

• MySQL-5.5.4 was run with –innodb_buffer_pool_instances=16
• Buffer pool varies from 6GB to 20GB
• Measurement is New Order Transactions per 10 sec, benchmark was run for 2000 sec

When data fits into memory ( buffer_pool=24Gb), it seems innodb_io_capacity=2000 will help to avoid periodical drops in MySQL 5.5.4.

It does not help though when buffer_pool is smaller than dataset.

[mysqld]
basedir=/usr/local/mysql
user=root
socket=/var/run/mysqld/mysqld.sock

server_id=1

local_infile=1
tmpdir=/mnt/fio

datadir=/mnt/fio320/
skip-grant-table

innodb_buffer_pool_size=24G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_log_files_in_group=2

innodb_log_file_size=1900M

innodb_thread_concurrency=16
innodb_flush_method             = O_DIRECT

innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_io_capacity=500
innodb_purge_threads=1



innodb_max_dirty_pages_pct=90


max_connections=3000
query_cache_size=0
skip-name-resolve

table_cache=10000

[mysqld]
basedir=/usr/local/mysql
user=root
socket=/var/run/mysqld/mysqld.sock

server_id=1

local_infile=1
tmpdir=/mnt/fio

datadir=/mnt/fio320
skip-grant-table

innodb_buffer_pool_size=24G

innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1


innodb_log_buffer_size=8M
innodb_log_files_in_group=2
innodb_log_file_size=1900M

innodb_thread_concurrency=0
innodb_flush_method             = O_DIRECT

innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_io_capacity=500

innodb_max_dirty_pages_pct=90

max_connections=3000
query_cache_size=0
skip-name-resolve

table_cache=10000

#!/bin/sh
set -u
set -x
set -e

DR="/mnt/fio320"
BD="/mnt/fio/back"

WT=10
RT=3600

ROWS=80000000

#log2="/bench/"
log2="$DR/"

# restore from backup

rm -fr $DR/*

echo $log2
for nm in ibdata1 ib_logfile0 ib_logfile1
do
rm -f $log2/$nm
cp $BD/$nm $log2
done


cp -r $BD/* $DR

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

}

for bp in 20 18 16 14 12 10 8 6
do

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.552.cnf --basedir=/usr/local/mysql --datadir=$DR  --innodb_data_home_dir=$log2 --innodb_log_group_home_dir=$log2 --innodb_thread_concurrency=0 --innodb_buffer_pool_size=${bp}GB  --innodb_buffer_pool_instances=16 &

sleep 60

./tpcc_start localhost tpcc root "" 200 16 10 2000 | tee -a tpcc.bp${bp}.second_run.out


waitm


mysqladmin  shutdown

done