mysql 写性能测试

因为有个项目的场景是，大量写操作，非常少量的读操作

因此这里测试下mysql数据的写性能

建表

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` double DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `monitor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=200002 DEFAULT CHARSET=latin1 

测试1 每插入一条记录，创建关闭一次连接

import time
import MySQLdb
import random


def solve():
	conn=MySQLdb.connect(host="localhost",user="root",passwd="",db="test",charset="utf8")
	cursor = conn.cursor()

	sql = "insert into data(monitor_id, time, value) values(%s, %s, %s)"   
	mid = random.randint(0,2000)	  
	time_str = time.strftime('%Y-%m-%d %H:%M:%S',  time.localtime())
	value = random.uniform(10000, 2000000) 
	param = (mid, time_str, value)      
	n = cursor.execute(sql,param)   
	cursor.close()    
	conn.close() 

t1 = time.time()
num = 100000
for i in range(num):
	solve()

t2 = time.time()

print (t2 - t1)

测试2 在一个连接中提交所有记录

import time
import MySQLdb
import random

conn=MySQLdb.connect(host="localhost",user="root",passwd="",db="test",charset="utf8")
cursor = conn.cursor()
def solve():
        global conn
        global cursor

        sql = "insert into data(monitor_id, time, value) values(%s, %s, %s)"
        mid = random.randint(0,2000)
        time_str = time.strftime('%Y-%m-%d %H:%M:%S',  time.localtime())
        value = random.uniform(10000, 2000000)
        param = (mid, time_str, value)
        n = cursor.execute(sql,param)


t1 = time.time()
num = 100000
for i in range(num):
        solve()

t2 = time.time()

print (t2 - t1)

cursor.close()
conn.close()

实验结果:

类型	record/second
实验1	100000/63=1587
实验2	100000/16=6250

看来单个mysql 的写入速度已经足够快了，没有必要在搞什么集群什么的了。