python tests the complete instance of mysql write performance
- 2020-07-21 08:52:29
- OfStack
This paper focuses on python to test mysql write performance and shares 1 piece of complete code, which is described as follows.
Test environment:
(1) Aliyun server centos 6.5
(2) 2G memory
(3) Ordinary hard disk
(4) mysql 5.1.73 Database storage engine is InnoDB
(5) python 2.7
(6) Client module ES22en. connector
Test method:
(1) Ordinary write
(2) Batch write
(3) Transaction plus batch write
Common write:
def ordinary_insert(count):
sql = "insert into stu(name,age,class)values('test mysql insert',30,8)"
for i in range(count):
cur.execute(sql) Batch write, each batch write 20 pieces of data
def many_insert(count):
sql = "insert into stu(name,age,class)values(%s,%s,%s)"
loop = count/20
stus = (('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32))
# More data in a tuple is not always better
for i in range(loop):
cur.executemany(sql, stus) Transaction plus batch writes, each batch writes 20 pieces of data, every 20 batch writes commits as a transaction
def transaction_insert(count):
sql = "insert into stu(name,age,class)values(%s,%s,%s)"
insert_lst = []
loop = count/20
stus = (('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32))
# More data in a tuple is not always better
for i in range(loop):
insert_lst.append((sql,stus))
if len(insert_lst) == 20:
conn.start_transaction()
for item in insert_lst:
cur.executemany(item[0], item[1])
conn.commit()
print '0k'
insert_lst = []
if len(insert_lst) > 0:
conn.start_transaction()
for item in insert_lst:
cur.executemany(item[0], item[1])
conn.commit() The experimental results are as follows
The number of Ordinary writing many write The transaction with many write
1 wan 26.7s 1.7s 0.5s
10 wan 266s 19s 5s
100 wan 2553s 165s 49s
Batch write, compared with the ordinary multiple write, reduce the number of network transmission, so write faster.
Whether it is a single write or batch write, a transaction should be opened inside the database to ensure the integrity of the write action. If we open things by ourselves in the application layer, then we can avoid the overhead of opening transactions every time the database writes, thus improving the write speed.
Transaction plus batch write is about 3 times faster than batch write and 50 times faster than normal write.
The complete test code is as follows:
#coding=utf-8
'''''
using 3 Method test mysql.connector right mysql Write performance , Others such as mysqldb and pymysql The write performance of the client library should be and mysql.connector1 to
When using batch write , Speed is increased by reducing the number of network transmissions
Open the transaction , Commit the transaction after multiple writes , Its write speed will also increase significantly , This is due to the single insert, Transactions are also opened within the database to ensure that 1 Integrity of secondary writes
If I start a transaction , Multiple writes are performed within the transaction , So you avoid every 1 Each write opens the transaction , It also saves time
From the test results , Transaction plus batch write is about as fast as batch write 3 times , It's written normally 50 times
The number of Ordinary writing many write The transaction with many write
1 wan 26.7s 1.7s 0.5s
10 wan 266s 19s 5s
100 wan 2553s 165s 49s
will autocommit Set to true, perform insert Writes directly to the database , Otherwise in execute When inserting commands , Open things by default , It has to be at the end commit, Doing so actually slows down the insertion speed
There is another thing to note mysql The database storage engine if is MyISAM, So transactions are not supported ,InnoDB Support transaction
'''
import time
import sys
import mysql.connector
reload(sys)
sys.setdefaultencoding('utf-8')
config = {
'host': '127.0.0.1',
'port': 3306,
'database': 'testsql',
'user': 'root',
'password': 'sheng',
'charset': 'utf8',
'use_unicode': True,
'get_warnings': True,
'autocommit':True
}
conn = mysql.connector.connect(**config)
cur = conn.cursor()
def time_me(fn):
def _wrapper(*args, **kwargs):
start = time.time()
fn(*args, **kwargs)
seconds = time.time() - start
print u"{func} Function of each {count} Time to write the number of pieces of data {sec} seconds ".format(func = fn.func_name,count=args[0],sec=seconds)
return _wrapper
# Ordinary writing
@time_me
def ordinary_insert(count):
sql = "insert into stu(name,age,class)values('test mysql insert',30,8)"
for i in range(count):
cur.execute(sql)
# batch
@time_me
def many_insert(count):
sql = "insert into stu(name,age,class)values(%s,%s,%s)"
loop = count/20
stus = (('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32))
# More data in a tuple is not always better
for i in range(loop):
cur.executemany(sql, stus)
# Transaction plus batch
@time_me
def transaction_insert(count):
sql = "insert into stu(name,age,class)values(%s,%s,%s)"
insert_lst = []
loop = count/20
stus = (('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 30), ('test mysql insert', 30, 31), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32)
,('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32), ('test mysql insert', 30, 32),
('test mysql insert', 30, 32), ('test mysql insert', 30, 32))
# More data in a tuple is not always better
for i in range(loop):
insert_lst.append((sql,stus))
if len(insert_lst) == 20:
conn.start_transaction()
for item in insert_lst:
cur.executemany(item[0], item[1])
conn.commit()
print '0k'
insert_lst = []
if len(insert_lst) > 0:
conn.start_transaction()
for item in insert_lst:
cur.executemany(item[0], item[1])
conn.commit()
def test_insert(count):
ordinary_insert(count)
many_insert(count)
transaction_insert(count)
if __name__ == '__main__':
if len(sys.argv) == 2:
loop = int(sys.argv[1])
test_insert(loop)
else:
print u' Parameter error ' conclusion
That's all for this article on the python test mysql write performance complete example, I hope to help you. Interested friends can continue to refer to other related topics in this site, if there is any deficiency, welcome to comment out. Thank you for your support!