LRU auto dump / restore

Hardware: Cisco UCS

Benchmark tpcc-mysql, 1000W ( 100GB ), buffer_pool 140GB, 10 connections, 3600 sec run.

[mysqld]
basedir=/usr/local/Percona-Server/
user=root
server_id=1
local_infile=1
datadir=/mnt/tachion/
tmpdir=/mnt/tachion/
skip-grant-table
innodb_buffer_pool_size=140G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=256M
innodb_log_files_in_group=2
innodb_log_file_size=3G
innodb_thread_concurrency=24
innodb_flush_method             = O_DIRECT
innodb_max_purge_lag=100000
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_io_capacity=500
innodb_flush_neighbor_pages = 0
innodb-auto-lru-dump=300
innodb_max_dirty_pages_pct=90
max_connections=3000
query_cache_size=0
skip-name-resolve
table_cache=10000
[mysql]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock

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

#export LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql/
export LD_LIBRARY_PATH=/usr/local/Percona-Server/lib/mysql/

ulimit -c unlimited

#DR="/mnt/fio320"
BD=/mnt/tachion/tpc1000w
#DR=/data/db/bench
DR="/data/db/bench"

WT=10
RT=10800

ROWS=80000000

#log2="/data/log/"
log2="$DR/"

# restore from backup


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

}



# Determine run number for selecting an output directory
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 par in  1 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 43
#for par in   13 26 39 52 65 78
for par in  24
do
export OS_FILE_LOG_BLOCK_SIZE=4096
/usr/local/mysql/libexec/mysqld --defaults-file=/etc/my.cnf --datadir=$DR  --innodb_log_group_home_dir=$log2 --innodb_thread_concurrency=0  --log-error=error.log &

sleep 900

set +e

while true;
do
mysql -Bse "SELECT 1" mysql

if [ "$?" -eq 0 ]
then
  break
fi

sleep 30

echo -n "."
done
set -e




iostat -dx 5 2000 >> $OUTDIR/iostat.${par}res &
PID=$!
vmstat 5 2000 >> $OUTDIR/vmstat.${par}res &
PIDV=$!
./tpcc_start localhost tpcc1000 root "" 1000 10 10 3600 | tee -a $OUTDIR/tpcc.${par}.out
kill -9 $PID
kill -9 $PIDV

waitm


mysqladmin  shutdown

done