Benchmark of FlashCache with sysbench oltp, data is bigger than cache — 2010/05/18

Binary package of FlashCache for CentOS 5.5, Kernel 2.6.18-194.el5 is there http://www.percona.com/downloads/TESTING/FlashCache/centos-2.6.18-194.tar.gz

• Hardware: Dell PowerEdge R900
• IO subsystems
  • RAID10: 8 disks SAS 2.5” 15K
  • SSD: Intel X25-M 160GB ( 2 gen ) ( Model Number: INTEL SSDSA2M160G2GC, Firmware Revision: 2CV102HA )
  • FlashCache: build over SSD
• Filesystem XFS, builds as: mkfs.xfs -f -d su=16384,sw=16 /dev/sdb
  • mounted with -o nobarrier,swalloc option
• InnoDB files layout: ibdata1 and ib_logfile* are placed on separate RAID partition ( not on FlashCache or SSD)

Benchmark: sysbench oltp ( read-only and read-write modes), 300mln rows (~70GB of data) FlashCache is configured to store 35GB flashcache_create -b 4k -s 35g cachedev /dev/sdb /dev/sda2

Read-write tests were done with done with different dirty pages thresholds in FlashCache: 20%

special distribution is used for generating random requests

[mysqld]
basedir=/usr/local/mysql
user=root
socket=/var/lib/mysql/mysql.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_fast_checksum=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=4
innodb_read_io_threads=4
innodb_io_capacity=500

innodb_max_dirty_pages_pct=90

max_connections=3000
query_cache_size=0
skip-name-resolve

table_cache=10000

[mysql]

socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

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


EXPER="sysbench 300mln cachedev rw special no batt"

DR="/bench"
BD="/mnt/fio320"

WT=1800
RT=500

ROWS=300000000

log2="/data/log/"
#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

#sync ; echo 3 > /proc/sys/vm/drop_caches

function waitm {

while [ true ]
do

mysql -e "set global innodb_max_dirty_pages_pct=0" sbtest

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" sbtest
break 
fi

echo "mysql pages $wt"
sleep 10
done

}

for bp in 20 16 10

do

/usr/local/mysql/libexec/mysqld --defaults-file=/etc/my.opt.cnf --basedir=/usr/local/mysql --datadir=$DR  --innodb_data_home_dir=$log2 --innodb_log_group_home_dir=$log2 --innodb_thread_concurrency=16 --innodb_buffer_pool_size=${bp}GB   &
#/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=16 --innodb_buffer_pool_size=${bp}GB &

sleep 60
#time mysql -e "select avg(id) from sbtest;" sbtest

#exit 

echo "$EXPER bp ${bp} long" >> /tmp/bench.log

sysbench --test=oltp --oltp-table-size=$ROWS --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=special --max-time=$WT  --mysql-user=root --mysql-socket=/var/lib/mysql/mysql.sock --batch --batch-delay=10 --oltp-dist-pct=1 run  |  tee -a oltp_long.bp$bp.res
sleep 30
waitm
echo "$EXPER bp ${bp} long END" >> /tmp/bench.log


mysqladmin  shutdown

done