On Sun, Dec 30, 2007 at 02:35:56PM +0100, Kris Kennaway wrote: > Gergely CZUCZY wrote: > > >>I appreciate that you might be constrained by local requirements, but it's > >>really not meaningful to compare different mysql versions > >>if your goal is to study OS performance. > >It'd be a PITA to install the both versions. Maybe now, that the ports > >freeze is over, i can > >do something. But honestly, every mysql version was faster on linux, than > >eny on FreeBSD, > >even the .22 one was faster. We start work on Jan02, I will see what can I > >do. > > Regardless, we need a stable baseline to compare to. Cannot do it sooner then january. Any recommended versions?
>
> >>* What database engine are you using? I have only tested with innodb but
> >>maybe you are using myisam? Please provide your exact
> >>sysbench command lines.
> >MyISAM, of course. InnoDB is not any good with replication, and we need
> >backup.
>
> OK. I tested briefly and InnoDB is almost 20 times faster on my tests with
> default myisam settings. myisam runs into the usual
> serious mysql scaling problems at concurrency > 8 threads (contention within
> the mysql application, not a FreeBSD issue).
So, that won't my work easier :) Currently it's impossible to make this box to
run FreeBSD this time, because my workplace suffers in heroic linuxism, i just
took a temporalily downed box to do some tests.
> Still waiting for your sysbench command lines :)
There were 2 scripts and a sources file with options:
# cat sysbench.common
#!/bin/sh
#dbtype=mysql
dbtype=pgsql
tablesize="10000000"
case $dbtype in
mysql)
connect="--mysql-host=10.0.0.1 --mysql-port=3306 --mysql-db=test
--mysql-user=root"
common="--test=oltp --db-driver=mysql --oltp-table-size=${tablesize}"
;;
pgsql)
connect="--pgsql-host=10.0.0.1 --pgsql-port=5432 --pgsql-db=test
--pgsql-user=root"
common="--test=oltp --db-driver=pgsql --oltp-table-size=${tablesize}"
;;
*)
echo "Unknown dbtype";
exit 1;
;;
esac
# cat sysbench.common
#!/bin/sh
#dbtype=mysql
dbtype=pgsql
tablesize="10000000"
case $dbtype in
mysql)
connect="--mysql-host=10.0.0.1 --mysql-port=3306 --mysql-db=test
--mysql-user=root"
common="--test=oltp --db-driver=mysql --oltp-table-size=${tablesize}"
;;
pgsql)
connect="--pgsql-host=10.0.0.1 --pgsql-port=5432 --pgsql-db=test
--pgsql-user=root"
common="--test=oltp --db-driver=pgsql --oltp-table-size=${tablesize}"
;;
*)
echo "Unknown dbtype";
exit 1;
;;
esac
sol:~# cat sysbench.prepare
#!/bin/sh
. ~/sysbench.common
cmd="sysbench ${common} ${connect} prepare"
echo $cmd
$cmd
# cat sysbench.test
#!/bin/sh
. ~/sysbench.common
steps="1 2 4 8 16 32 64 128 192 256 384 512"
requests="10000"
# system setup
# - fbsd7-ufs, fbsd7-zfs
# - linux26-cdb2
#flavor="linux26-mysql5041-tcmalloc"
#flavor="linux26-mysql5041"
#flavor="fbsd7-zfs-8k"
#flavor="fbsd7-ufs-pgsql-skiptxn"
#flavor="fbsd7-zfs-pgsql-4BSD"
flavor="fbsd7-zfs-pgsql-4BSD-nosync"
#flavor="fbsd7-ufs-4BSD"
# test type, ro/rw
testtype="rw"
# resultdir
resultdir='/root/results/'
# args
args="${common} ${connect} --max-time=60 --max-requests=0
--oltp-test-mode=complex --oltp-dist-iter=16"
case ${testtype} in
ro)
args="${args} --oltp-read-only --oltp-skip-trx=on"
;;
rw)
test ${dbtype} = "mysql" && args="${args} --oltp-skip-trx=on"
;;
esac
resultfile() {
local threads=$1
echo ${resultdir}/${flavor}.${testtype}.${threads}
}
plotdata=`echo ${resultdir}/${flavor}.${testtype}.plot | tr '.-' _`
rm -f ${plotdata}
rm -f ${resultdir}/${flavor}.${testtype}.*
#cmd="sysbench --num-threads=16 ${args} run"
#echo $cmd
#$cmd
#exit
echo -n > ${plotdata}
for threads in ${steps}
do
cmd="sysbench --num-threads=${threads} ${args} run"
out=`resultfile ${threads}`
echo ${cmd} > ${out}
echo "Testing with ${threads}"
success=0
while test ${success} -eq 0;
do
date
date >> ${out}
$cmd >> ${out} && success=1
test ${success} -eq 0 && (echo "Test failed, restarting")
done
cat ${out}
awk '/read.write requests/ {sub("^.", "", $4); print $4}' ${out} >>
${plotdata}
done
basicall that's everything. I know it's a but complex, but this was all behind
it.
Sincerely,
Gergely Czuczy
mailto: [EMAIL PROTECTED]
--
Weenies test. Geniuses solve problems that arise.
pgpvSmNjIQgk0.pgp
Description: PGP signature
