多线程测试mysql数据库压力python脚本

以下脚本主要是测试分库分表并发insert操作。可根据具体需求进行sql修改。

部署操作：

1、安装必要包并复制文本：

yum install -y freetds-devel.x86_64 / apt-get install freetds-dev
pip install pymysql

vim polar_test.py
i

2、付可执行权限并执行：

chmod +x polar_test.py

./polar_test.py

 

#! /usr/bin/env python  
#coding:utf-8

import time
import threading
from time import ctime

row_count=1000000000 #共计插入数据令
threads_count = 48 #并发线程数

import pymysql
def time_me(fn):
    '''
        记录方法执行时间
        :param args:
        :param kwargs:
        :return:
        '''
    def _wrapper(*args, **kwargs):
        start = time.time()
        fn(*args, **kwargs)
        seconds = time.time() - start
        print("{func}函数每{count}条数数据写入耗时{sec}秒".format(func='ordinary_insert', count=args[0], sec=seconds))
    return _wrapper

@time_me
def ordinary_insert(count):
    db  = pymysql.connect(host='xxxxxx',
                                   user='userName',
                                   passwd='pwd',
                                   port=3306,
                                   db='test_sjz_drds_db',
                                   charset="utf8")
    cur = db.cursor()
    for i in range(count):

       #具体sql
        sql = '''INSERT INTO `rs_plate_trace` ( `PARK_ID`, `PARK_CODE`, `BAR_CODE`, `BERTH_NUMBER`, `PARK_SPACE_ID`, `DATA_SOURCE`, `IMAGE_TYPE`, \
  `IMG_PATH`, `UPLOAD_TIME`, `CREATOR_TIME`) VALUES ( '8f3a1714_d288_11e8_92d0_6c92bf3bb2e1', 'PA20181018114739', {0}, \
  '1003', NULL, '12', '1', 'http://sjz-full-image.oss-cn-beijing.aliyuncs.com/2018/10/31/PA20181018114739/64f8d280-49c9-43c7-9a25-4f835dc6f13b_full.jpg', \
  '2018-10-31 18:03:03', '2018-10-31 18:04:02')'''.format(i)
        cur.execute(sql)
        db.commit() #每次都提交
    db.close() #关闭连接

local_var=threading.local()
def Clean(args):
    local_var.name =args
    ordinary_insert(int(row_count/threads_count))

threads=[]
for i in range(threads_count):
    t = threading.Thread(target=Clean, args=(i,))
    threads.append(t)

print ('start:', ctime())
start = time.time()

if __name__ == '__main__':
    for i in threads:
        i.start()
    for i in threads:
        i.join()

seconds = time.time() - start
print ('end:', ctime())
print("{func}函数每{count}条数数据写入耗时{sec}秒".format(func='ordinary_insert', count=row_count, sec=seconds))