3、测试mysqlfabric的qbs

　　结论
　　从以上数据说明，mysqlfabric的ips和qbs的性能是随着进程数的增加而呈正比例上升的，上升的幅度不大，以上测试采用的是默认的mysql配置，可以调整innodb_buffuer_size这个参数来增加mysql的性能，得出来的不尽如人意。
　　此外，在测试mysqlfabric的过程中，还有以下几点问题需要解决：
　　a.    分片时，发生以下错误
　　[ERROR] 1420771468.040900 - Executor-24 - Error in _check_shard_information.
　　Traceback (most recent call last):
　　File "/usr/local/lib/python2.7/site-packages/mysql/fabric/executor.py"， line 378， in execute
　　self.__result = self.__action(*self.__args， **self.__kwargs)
　　File "/usr/local/lib/python2.7/site-packages/mysql/fabric/events.py"， line 80， in wrapped
　　return wrapped.function(*args， **kwargs)
　　File "/usr/local/lib/python2.7/site-packages/mysql/fabric/services/resharding.py"， line 250， in _check_shard_information
　　upper_bound
　　File "/usr/local/lib/python2.7/site-packages/mysql/fabric/sharding.py"， line 979， in split_value
　　upper_bound = int(upper_bound， 16)
　　TypeError: int() can't convert non-string with explicit base
　　Procedure :
　　{ uuid = 859e1cdd-24fd-4ccd-9363-186d7df91971，
　　finished = True，
　　success = False，
　　return = TypeError: int() can't convert non-string with explicit base，
　　activities =
　　}
　　对于python来说，是进制之间不能正常转换，因此，在添加分片的数据库的数据，不能只添加自增长id的数据
　　b.mysqlfabric进程数的限制
　　配置文件/etc/mysql/fabric.cfg，当threads=100、executors=49，会发现执行mysqlfabric manage start &报错
　　Procedure :
　　{ uuid = fea93aaf-4f01-43ae-b1ca-89d4abee6b2e，
　　finished = True，
　　success = False，
　　return = ConfigurationError: Too many threads requested. Session threads (100)， Executor threads (49)， Control threads (1) and Failure Detector threads (0). The maximum number of threads allowed is (150). Increase the maximum number of connections in the state store in order to increase this limit.，
　　activities =
　　}
　　由源代码可知，进程数与执行数之和不能大于150-1
　　c.mysqlfabric程序本身的不稳定性
　　mysqlfabrci程序本身容错性比较弱，当操作失误时，有时会卡住不执行，然后需要将进程kill掉重新启动，使用stop命令停不掉；另外，当在分片时，进程突然中断，重启mysqlfabric后，不会继续执行上次分片的位置，而是需要重新分片。
　　d.mysqlfabric同时进行多进程插入查询时，会报错
　　在进行测试时，当同时插入数据的进程数或查询的进程数大于40，会报以下错误
Process Process-25:
Traceback (most recent call last):
File "/usr/local/lib/python2.7/multiprocessing/process.py"， line 258， in _bootstrap
self.run()
File "/usr/local/lib/python2.7/multiprocessing/process.py"， line 114， in run
self._target(*self._args， **self._kwargs)
File "thread_tps_test.py"， line 43， in run_test
conn = create_conn_by_fabric()
File "thread_tps_test.py"， line 23， in create_conn_by_fabric
user="root"， password=""， autocommit=True，database='test'
File "/usr/local/lib/python2.7/site-packages/mysql/connector/__init__.py"， line 144， in connect
return mysql.connector.fabric.connect(*args， **kwargs)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/__init__.py"， line 53， in connect
return MySQLFabricConnection(**kwargs)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 809， in __init__
self.store_config(**kwargs)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 948， in store_config
self._configure_fabric(config['fabric'])
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 929， in _configure_fabric
self._fabric.seed()
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 332， in seed
fabinst.connect()
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 763， in connect
self._proxy = self._xmlrpc_get_proxy()
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 747， in _xmlrpc_get_proxy
proxy._some_nonexisting_method() # pylint: disable=W0212
File "/usr/local/lib/python2.7/xmlrpclib.py"， line 1224， in __call__
return self.__send(self.__name， args)
File "/usr/local/lib/python2.7/xmlrpclib.py"， line 1578， in __request
verbose=self.__verbose
File "/usr/local/lib/python2.7/site-packages/mysql/connector/fabric/connection.py"， line 272， in request
raise InterfaceError("Connection with Fabric failed: " + msg)
InterfaceError: Connection with Fabric failed: <urlopen error [Errno 110] Connection timed out>
　　5.    测试mysqlfabric性能的脚本
　　测试mysqlfabric ips
__author__ = 'kw107301'
import mysql.connector
from mysql.connector import fabric
import random
import sys
from multiprocessing import Process
import time
table_count=1000
requests = int(sys.argv[1])
type = int(sys.argv[2])
threads = int(sys.argv[3])
print "requests:%d， type:%d， threads:%d"%(requests，type，threads)
print time.strftime('%Y-%m-%d %H:%M:%S'，time.localtime(time.time()))
cur = None
def create_conn_by_fabric():
conn = mysql.connector.connect(
fabric={"host" : "localhost"， "port" : 32274，
"username": "admin"， "password" : "admin"
}，
user="root"， password=""， autocommit=True，database='test'
)
conn.set_property(tables=["test.subscribers"]， scope=fabric.SCOPE_LOCAL， mode=fabric.MODE_READONLY)
return conn
def get_result(conn):
sub_no = random.randint(1， table_count)
if type == 1:
conn = create_conn_by_fabric()
conn.set_property(tables=["test.subscribers"]， key=sub_no， mode=fabric.MODE_READONLY)
"""conn.reset_cache()"""
cur = conn.cursor()
cur.execute(
"INSERT INTO subscribers VALUES (%s， 'Billy'， 'Fish')"，
(sub_no，first_name， last_name)
)
def run_test():
conn = create_conn_by_fabric()
for i in range(requests/threads):
get_result(conn)
if __name__ == '__main__':
thd = []
s_time = time.time()
for i in range(threads):
t = Process(target=run_test)
thd.append(t)
t.start()
for m in thd:
m.join()
print "test finished， total cost %.2f second， TPS is %.2f"%((time.time()-s_time)，requests/(time.time()-s_time))
　　测试mysqlfabric qbs
import mysql.connector
from mysql.connector import fabric
import random
import sys
from multiprocessing import Process
import time
table_count=10000
requests = int(sys.argv[1])
type = int(sys.argv[2])
threads = int(sys.argv[3])
print "requests:%d， type:%d， threads:%d"%(requests，type，threads)
print time.strftime('%Y-%m-%d %H:%M:%S'，time.localtime(time.time()))
cur = None
def create_conn_by_fabric():
conn = mysql.connector.connect(
fabric={"host" : "localhost"， "port" : 32274，
"username": "admin"， "password" : "admin"
}，
user="root"， password=""， autocommit=True，database='test'
)
conn.set_property(tables=["test.subscribers"]， scope=fabric.SCOPE_LOCAL， mode=fabric.MODE_READONLY)
return conn
def get_result(conn):
sub_no = random.randint(0， table_count)
if type == 1:
conn = create_conn_by_fabric()
conn.set_property(tables=["test.subscribers"]， key=sub_no， mode=fabric.MODE_READONLY)
cur = conn.cursor()
cur.execute(
"SELECT * FROM subscribers "
"WHERE sub_no = %s"， (sub_no， )
)
def run_test():
conn = create_conn_by_fabric()
for i in range(requests/threads):
get_result(conn)
if __name__ == '__main__':
thd = []
s_time = time.time()
for i in range(threads):
t = Process(target=run_test)
thd.append(t)
t.start()
for m in thd:
m.join()
print "test finished， total cost %.2f second， QPS is %.2f"%((time.time()-s_time)，requests/(time.time()-s_time))