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)) |
