mysql scaling questions

[Gergely CZUCZY]

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 at harmless.hu

-- 
Weenies test. Geniuses solve problems that arise.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 2994 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20071230/0b314b12/attachment.pgp