Mysql Scalability(5)benchmark

Mysql Scalability(5)benchmark


>bin/benchmark -P 3306 -c 50 -f ./benchmark/query.xml -h ubuntu-master -n 100000 -u root -p kaishi


Prepare 1000000
Here is the initData.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> <properties version="1.0"> <entry key="class">com.meidusa.amoeba.mysql.net.packet.QueryCommandPacket</entry> <entry key="command">3</entry> <entry key="query"><![CDATA[   insert into sillycat.branduser (id, username, age) values ( ${@Math@abs( 1 + #atomicLong.getAndIncrement())}, "test",32); ]]> </entry> </properties>

-c 50 means 50 connections.
-f ./beachmark/query.xml means execute the SQL in that file.
-n 100000 means totally run 100000 query.

Insert 1000,000 data
>bin/benchmark -P 3306 -c 50 -f ./benchmark/sql/insertData.xml -h ubuntu-master -n 1000000 -u root -p kaishi

Here the insertData.xml
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> <properties version="1.0"> <entry key="class">com.meidusa.amoeba.mysql.net.packet.QueryCommandPacket</entry> <entry key="command">3</entry> <entry key="query"><![CDATA[   insert into sillycat.branduser (id, username, age) values ( ${@Math@abs( 1000000 + #atomicLong.getAndIncrement())}, "test",32); ]]> </entry> </properties>

Here is the queryData.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> <properties version="1.0"> <entry key="class">com.meidusa.amoeba.mysql.net.packet.QueryCommandPacket</entry> <entry key="command">3</entry> <entry key="query"><![CDATA[ select id, username, age from sillycat.branduser where id=${@Math@abs(#random.nextInt(1000000))} LIMIT ${@Math@abs(#random.nextInt(100))}, ${@Math@abs(#random.nextInt(100))}; ]]> </entry> </properties>

Query 1000,000 data
>bin/benchmark -P 3306 -c 50 -f ./benchmark/sql/queryData.xml -h ubuntu-master -n 1000000 -u root -p kaishi


Results from Single Mysql
Query Result
completed requests total=1000000, errorNum=0, cost=582804ms , TPS=1715/s min=0ms max=610ms average=29ms create Connections time=308ms TPS(after connected)=1716/s

Insert Result
completed requests total=1000000, errorNum=0, cost=601626ms , TPS=1662/s min=0ms max=637ms average=30ms create Connections time=150ms TPS(after connected)=1662/s


Run from Amoeba through 4 Machines

>bin/benchmark -P 8066 -c 50 -f ./benchmark/sql/initData.xml -h ubuntu-client4 -n 100000 -u root -p kaishi

>bin/benchmark -P 8066 -c 50 -f ./benchmark/sql/insertData.xml -h ubuntu-client4 -n 100000 -u root -p kaishi

>bin/benchmark -P 8066 -c 50 -f ./benchmark/sql/queryData.xml -h ubuntu-client4 -n 100000 -u root -p kaishi


Configuration and running ha proxy
Download the latest haproxy
>sudo apt-get install build-essential
>sudo apt-get install libpcre3-dev
>make TARGET=custom CPU=native USE_PCRE=1 USE_LIBCRYPT=1 USE_LINUX_SPLICE=1 USE_LINUX_TPROXY=1
>make install DESTDIR='/home/carl/tool/haproxy-1.5.3' PREFIX=''

>sudo sbin/haproxy -f conf/haproxy.conf 


References:
http://docs.hexnova.com/amoeba/jvm-settings.html
http://louwrentius.com/how-to-compile-haproxy-from-source-and-setup-a-basic-configuration.html