Thursday Oct 30, 2008

Scale-out MySQL Study on Solaris Nevada

HW configuration:

Server A: v20z Server(CPU: 2x2193 Mhz, Memory: 4032 MB)

Server B: x4150 Server(CPU: 8x2826 Mhz, Memory: 16384 MB)

SW configuration:

MySQL5.1.24-rc, MySQL proxy 0.61, Amoeba for MySQL 0.31, DBT2 for MySQL

OS versions: Solaris Nevada snv_91

Test: MySQL DBT2 Test using Amoeba For MySQL

amoeba configuration and query routing rule:

#cat amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<server>

<property name="port">2066</property>

<property name="ipAddress">HostA</property>

<property name="readThreadPoolSize">600</property>

<property name="clientSideThreadPoolSize">600</property>

<property name="serverSideThreadPoolSize">600</property>

<property name="netBufferSize">300</property>

<property name="tcpNoDelay">no</property>

<property name="user">root</property>

</server>

<connectionManagerList>

<connectionManager name="defaultManager">

<className>com.meidusa.amoeba.net.AuthingableConnectionManager</className>

</connectionManager>

</connectionManagerList>

<dbServerList>

<dbServer name="HostB">

<factoryConfig>

<className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className>

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">HostB</property>

<property name="schema">dbt2</property>

<property name="user">root</property>

</factoryConfig>

<poolConfig>

<className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>

<dbServer name="HostA">

<factoryConfig>

<className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className>

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">HostA</property>

<property name="schema">dbt2</property>

<property name="user">root</property>

</factoryConfig>

<poolConfig>

<className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>

<dbServer name="multiPool" virtual="true">

<poolConfig>

<className>com.meidusa.amoeba.server.MultipleServerPool</className>

<property name="loadbalance">1</property>

<property name="poolNames">HostB,HostA</property>

</poolConfig>

</dbServer>

</dbServerList>

<queryRouter>

<className>com.meidusa.amoeba.mysql.parser.MysqlQueryRouter</cla

<property name="needParse">true</property>

</queryRouter>

</amoeba:configuration>


#cat rule.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">

<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">

<tableRule name="warehouse" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="item" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="customer" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="district" schema="dbt2" defaultPools="HostB">

</tableRule>

<tableRule name="history" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="new_order" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="orders" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="order_line" schema="dbt2" defaultPools="HostB">

</tableRule>

<tableRule name="stock" schema="dbt2" defaultPools="HostB">

</tableRule>

</amoeba:rule>


Test 1. number of warehouse: 3

DBT2 Test results using single MySQL vs. two MySQLs using Amoeba:

Threads#

1 mysqld(Server A)

CPU(usr/sys/idle)

Threads#

2 mysqld(Server A,B)

16

9576 TPM

80/19/1

16

6928TPM

32

9628 TPM

79/21/0

32

9310TPM

64

9574 TPM

78/20/2

64

9357TPM

128

8918 TPM

80/19/1

128

9629 TPM

256

8425 TPM

81/19/0

256

9792 TPM

512

8012 TPM

82/18/0

512

9564 TPM







CPU statistics on Server A(DBT2 app, MySQL server)

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl

0 64 0 5 2392 2099 9095 149 1124 130 0 42926 45 30 0 25

1 60 0 1 1059 1 10658 329 1125 124 0 48039 50 23 0 26

CPU statistics on Server B(Amoeba for MySQL: bind with CPU 0-5, MySQL Server:Use CPU6,7)

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl

0 3 0 1 10916 10413 13315 375 4122 1368 0 30541 35 38 0 28

1 4 0 3 501 0 18041 588 4673 891 0 42709 41 28 0 31

2 2 0 0 435 46 14415 380 3665 1022 0 26819 36 17 0 46

3 3 0 0 365 0 14087 362 3689 739 0 29129 39 20 0 41

4 2 0 1 319 2 11197 299 2582 793 0 19806 34 14 0 52

5 4 0 1 434 3 15149 457 3935 750 0 33424 39 22 0 39

6 3 0 0 33 0 4206 32 446 134 0 20105 22 7 0 71

7 1 0 0 37 0 4202 34 448 143 0 20119 21 7 0 71


Test 2. number of warehouse: 100

DBT2 Test results using single MySQL vs. two MySQLs using Amoeba:

 

 

 

 

 

 

 

 

 

 

 

 

Disk statistics on Server A(Single MySQL server Test)

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

95.8 3.8 28064.7 22.1 0.0 28.0 0.0 281.6 0 100 c1t1d0

extended device statistics

Disk statistics on Server A(Two MySQL servers Test)

extended device statistics

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

62.6 3.2 32931.2 227.2 0.0 7.6 0.0 115.5 0 100 c1t1d0

Conclusion:

  1. Use two mysql servers with amoeba proxy to divide duties can significantly improve MySQL performance when the single MySQL server encountered disk I/O bottleneck

  2. The limitation of Amoeba proxy for MySQL is that it consumed many CPU resources and had overhead on extra network I/Os. In DBT2 benchmark test, using two MySQL servers can't achieve more performance gain when the single MySQL server encountered CPU bottleneck, however, it scale better with number of concurrent users than the single MySQL server.

About

luojiac

Search

Top Tags
Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today