Re: mysql scaling questions

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: gergely.czuczy@xxxxxxxxxxx

--
Weenies test. Geniuses solve problems that arise.

Attachment: pgp7q5Tw6xrX9.pgp
Description: PGP signature

Relevant Pages

  • Re: FORTH levels
    ... cat, mkdir, rm, mv, echo they have. ... Albert van der Horst, UTRECHT,THE NETHERLANDS ...
    (comp.lang.forth)
  • Re: FORTH levels
    ... cat, mkdir, rm, mv, echo they have. ... swapoff swapon sync sysctl tail tar tee telnet telnetd test todos ...
    (comp.lang.forth)
  • Re: system documentation script
    ... echo "\n "$TITLE ... lsdev -C | /bin/grep Available ... echo "Checking LV mirroring, mirror strictness, mirror parallel schedule ... cat /etc/filesystems ...
    (AIX-L)
  • Abers remotely
    ... I need to confirm whether or not a remote SSL Server is outdated? ... $ cat openssl_enum_ssl2 ... echo + $g at $a ... Download FREE whitepaper on how a managed service can ...
    (Pen-Test)
  • Re: mysql apache - page never finish loading
    ... my PHP pages don't want to finish loading. ... echo 'a'; ... Opera and IE behaves somewhat differently. ... All this happens whenver I connect to mysql. ...
    (comp.lang.php)