Đã lâu không test performance mysql.

Giờ down bản mới 5.5.11 về test performance đồng thời test thêm một số cái khác:

Install:

Bạn có thể cài từ yum nếu là centos hoặc emerge nếu là gentoo.Nếu khi không thấy hỗ trợ test mysql,thì ta phải compile từ source thôi:

http://nchc.dl.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz

localhost # ./configure –with-mysql –with-mysql-includes=/usr/include/mysql/

–with-mysql-libs=/usr/lib64/mysql/

Nếu bị lỗi:

[... some lines removed ...]
/bin/sh ../libtool --tag=CC   --mode=link gcc -pthread -g -O2      -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/usr/local/mysql/lib/ -lmysqlclient_r   -lrt -lm
../libtool: line 838: X--tag=CC: command not found
../libtool: line 871: libtool: ignoring unknown tag : command not found
../libtool: line 838: X--mode=link: command not found
../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
../libtool: line 2231: X-g: command not found
../libtool: line 2231: X-O2: command not found
../libtool: line 1951: X-L/usr/local/mysql/lib/: No such file or directory
../libtool: line 2400: Xsysbench: command not found
../libtool: line 2405: X: command not found
../libtool: line 2412: Xsysbench: command not found
../libtool: line 2420: mkdir /.libs: No such file or directory
../libtool: line 2547: X-lmysqlclient_r: command not found
../libtool: line 2547: X-lrt: command not found
../libtool: line 2547: X-lm: command not found
../libtool: line 2629: X-L/root/sysbench-0.4.12/sysbench: No such file or directory
../libtool: line 2547: X-lmysqlclient_r: command not found
../libtool: line 2547: X-lrt: command not found

Chạy lại lênh sau:

 ./autogen.sh
Nếu autogen.sh có lỗi:
./autogen.sh
sysbench/drivers/mysql/Makefile.am:17: library used but `RANLIB' is undefined
sysbench/drivers/mysql/Makefile.am:17:   The usual way to define `RANLIB' is to add `AC_PROG_RANLIB'
sysbench/drivers/mysql/Makefile.am:17:   to `configure.ac' and run `autoconf' again.
sysbench/drivers/oracle/Makefile.am:17: library used but `RANLIB' is undefined  
Ta mờ file configure.ac và add biến này vào AC_PROG_RANLIB  
Chạy lại nếu có lỗi: thì comment biến này lại AC_PROG_LIBTOOL
configure.ac:75: error: possibly undefined macro: AC_PROG_LIBTOOL
      If this token and others are legitimate, please use m4_pattern_allow.
      See the Autoconf documentation.
 Chạy lại autogen.sh
 Và ./configure --with-mysql --with-mysql-includes=/usr/include/mysql/ 
                --with-mysql-libs=/usr/lib64/mysql/;make;make install
Giờ ta bắt đầu test:
Tạo database test với 1 triệu row:
sysbench --test=oltp --mysql-table-engine=innodb --db-driver=mysql --mysql-host=x.x.x.x --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456  --mysql-db=test --num-threads=80 prepare
Bắt đầu test:
sysbench --test=oltp --mysql-table-engine=innodb --db-driver=mysql --mysql-host=x.x.x.x --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456  --mysql-db=test --num-threads=100 run
Kết quả test với server ibm Intel(R) Xeon(TM) CPU 3.20GHz,4 Gb ram
OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (343.57 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (6527.82 per sec.)
    other operations:                    20000  (687.14 per sec.)